Mysql replication error
I set up nodebalancer with two wordpress installs, following this guide:
When I add one post in the first wordpress admin section.
This post only shows up on the first host, but is not being tranfered to the second host.
I have mysql-sync and lsyncd enabled, the picure files of the post is transfered to the second.
After seeing the following error In Mysql error.log:
170221 13:57:50 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT… ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO wp_options
I tried to do row-based replication,
I restarted mysql on each host after adding the following to my.cnf:
[mysqld]
binlog-format = row
Now, the last entry in error.log on the first host:
170222 10:26:49 [Note] Slave I/O thread: connected to master 'replication@192.168.149.24:3306',replication started in log 'mysql-bin.000014' at position 640062
Now, the last entries in error.log on the second host:
170222 10:26:09 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000013' at position 107, relay log '/var/log/mysql/mysql-relay-bin.000024' position: 253
170222 10:26:09 [Note] Slave I/O thread: connected to master 'replication@192.168.146.77:3306',replication started in log 'mysql-bin.000013' at position 107
170222 10:26:49 [Warning] IP address '192.168.146.77' could not be resolved: Name or service not known
170222 12:00:31 [ERROR] Slave SQL: Could not execute Updaterows event on table wordpress.wppostmeta; Can't find record in 'wppostmeta', Errorcode: 1032; handler error HAERRKEYNOTFOUND; the event's master log mysql-bin.000014, endlogpos 5014, Error_code: 1032
170222 12:00:31 [Warning] Slave: Can't find record in 'wppostmeta' Errorcode: 1032
170222 12:00:31 [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.000014' position 4755
The replication fails on the second host.
Any help appreciated.
Best regards,
Fabian
3 Replies
Seems the replication went out of sync, check the output of "SHOW SLAVE STATUS \g;" and try to re-sync the db by dumping, importing and updating the masterlogfile and masterlogpos values on the other server.
Thanks for your reply.
I re-synced using masterlogfile and masterlogpos values on the other server.
And the sync failed again.
Error.log on first host:
170223 10:16:02 [Note] 'CHANGE MASTER TO executed'. Previous state masterhost='192.168.149.24', masterport='3306', masterlogfile='mysql-bin.000016', masterlogpos='107'. New state masterhost='192.168.149.24', masterport='3306', masterlogfile='mysql-bin.000016', masterlogpos='107'.
170223 10:16:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000016' at position 107, relay log '/var/log/mysql/mysql-relay-bin.000001' position: 4
170223 10:16:55 [Note] Slave I/O thread: connected to master 'replication@192.168.149.24:3306',replication started in log 'mysql-bin.000016' at position 107
Error.log on second host:
170223 10:13:09 [Note] 'CHANGE MASTER TO executed'. Previous state masterhost='192.168.146.77', masterport='3306', masterlogfile='mysql-bin.000015', masterlogpos='28183'. New state masterhost='192.168.146.77', masterport='3306', masterlogfile='mysql-bin.000015', masterlogpos='28183'.
170223 10:13:22 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000015' at position 28183, relay log '/var/log/mysql/mysql-relay-bin.000001' position: 4
170223 10:13:22 [Note] Slave I/O thread: connected to master 'replication@192.168.146.77:3306',replication started in log 'mysql-bin.000015' at position 28183
170223 18:42:36 [ERROR] Slave SQL: Could not execute Updaterows event on table wordpress.wpoptions; Can't find record in 'wpoptions', Errorcode: 1032; handler error HAERRKEYNOTFOUND; the event's master log mysql-bin.000015, endlogpos 44570, Error_code: 1032
170223 18:42:36 [Warning] Slave: Can't find record in 'wpoptions' Errorcode: 1032
170223 18:42:36 [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.000015' position 42148
Any help appreciated.
Best regards,
Fabian
Assuming you stopped the replication, transferred the new SQL dump, re-imported the SQL dump and re-synced the replication numbers correctly, the other option would be to play with SET GLOBAL SQLSLAVESKIP_COUNTER on the slave.