Mysql replication error

Hi,

I set up nodebalancer with two wordpress installs, following this guide:

https://www.linode.com/docs/websites/cm … -wordpress">https://www.linode.com/docs/websites/cms/high-availability-wordpress

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

Hello,

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.

Hi,

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

Hello,

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.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct