Description:
Dear all,
After setting up fresh MySQL 5.6.17s i setup simple master->slave replication using this procedures:
On Master Server:
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '$slavepass45#';
Query OK, 0 rows affected (0,00 sec)
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0,00 sec)
mysql> CREATE USER 'remote'@'%' IDENTIFIED BY '12345';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on *.* to 'remote'@'%' with grant option;
Query OK, 0 rows affected (0,00 sec)
On SLave Server:
mysql> CREATE USER 'remote'@'%' IDENTIFIED BY '12345';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on *.* to 'remote'@'%' with grant option;
Query OK, 0 rows affected (0,00 sec)
On Master server:
mysqldbexport --server=remote:12345@192.168.1.77:3306 --export=both --rpl=master --all --output-file /home/export7.sql --rpl-user=repl:'$slavepass45#' -vvv --bulk-insert --locking=snapshot
* scp /home/export7.sql root@192.168.1.66:/home
On Slave server:
mysql> reset master;
Query OK, 0 rows affected (0,02 sec)
[root@linuxsrv4 ~]# mysqldbimport --server=remote:12345@192.168.1.88:3306 /home/export7.sql
# Source on 192.168.1.88: ... connected.
# Importing definitions from /home/export7.sql.
#...done.
And Slave starts normally it is worked.
But when i use mysql_secure_installation on master side it gives error on slave:
Note: i choose YES for all in mysql_secure_installation script input.
Error from Slave side:
From slave status:
Last_Error: Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 2008425
From Slave error log:
2014-05-07 11:05:22 3526 [Note] Slave I/O thread: connected to master 'repl@192.168.1.77:3306',replication started in log 'FIRST' at position 4
2014-05-07 11:05:22 3526 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/lib/mysql/data/mysql-relay-bin.000001' position: 4
2014-05-07 11:06:18 3526 [ERROR] Slave SQL: Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 2008425, Error_code: 1032
2014-05-07 11:06:18 3526 [Warning] Slave: Can't find record in 'user' Error_code: 1032
2014-05-07 11:06:18 3526 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 2007359
How to repeat:
Install Fresh 2 MySQL 5.6.17. Setup simple replication between them and then run mysql_secure_installation on master side.
Suggested fix:
Maybe not to use mysql_secure_installation on working master and slave?
