MySQL replication error: Error_code: 1032

MySQLmysql-replication

I setup MySQL read replica that is read only. Both master and slave are running MySQL 5.6. The slave never gets written to directly, but I can't seem to keep it synced for more than an hour or two. After running for a bit, I constantly encounter this type of error:

Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log

I then have to go through the process of recreating the slave from a MySQL dump, but no matter what I do I get this error again. Does anyone have a clue why this might be happening?

Best Answer

Depending on what engine(s) you are using, whether you are using multiple schemata, and the options you are using with mysqldump you might not be getting a consistent dump.

If you have two schemata, let's say one named development and another named production, mysqldump locks the tables separately for each schema. That means while you're backing up the development schema the production schema is still writable and being updated.

Now that you have your dump of the two schemata and start up replication, the two schemata are actually on different binlog positions. That means when you get Error_code 1032 you really don't have that key.

If all of the tables that you need to backup are InnoDB you should look into the --single-transaction option to mysqldump. If you have a mix of InnoDB and MyISAM then only the InnoDB tables are guaranteed to be consistent. The MyISAM tables will still be written to using this option.

If you have a mix of InnoDB and MyISAM or are pure MyISAM only, the best option (using mysqldump) is to use --lock-all-tables. That does exactly what it sounds like, nothing will be written to until the dump is complete. This has the major drawback that your application or website that depends on the database is locked too (since it can't write).

The best option IMO is to move everything to InnoDB if it isn't already and use Percona Xtrabackup. It will still work fine with MyISAM tables as well, but through a different tool than the InnoDB tables (cp or rsync). It still will need to lock the tables while copying the MyISAM tables but it does its' best to minimize that time. If you use the rsync method then the tool will first copy all of your MyISAM tables, then lock the tables, and then it copies any tables that have been updated since the first copy. The lock only needs to be held during that second rsync.

Related Solutions

Mysql – Is replication from MySQL 5.5 master to a 5.1 slave possible

What you are trying to do is not generally supported. Quote from the Replication Compatibility Between MySQL Versions docs:

MySQL supports replication from one major version to the next higher major version. For example, you can replicate from a master running MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL 5.0 to a slave running MySQL 5.1, and so on.

and

Replication from newer masters to older slaves may be possible, but is generally not supported.

It might work for those versions, but it's far from a recommended setup. My opinion: don't do it.

Also read the Upgrading a Replication Setup

In other words, when upgrading to MySQL 5.5, the slaves must be MySQL 5.5 before you can upgrade the master to 5.5.

(and maybe this snippet although I don't think it's directly relevant if you re-import to 5.1)

Downgrading a replication setup to a previous version cannot be done once you have switched from statement-based to row-based replication, and after the first row-based statement has been written to the binlog.

MySQL binlog format dilemma

I assume you did this on one setup without cleaning up the slave and setting it up from scratch.

As the error message for the statement based replication tells you your application used some commands which can't be replicated using statement based replication. An example is a query like

INSERT INTO t (t) VALUES(NOW())

where NOW() will return different values when executed on the master and on the slave.

By doing this you have different data on the master and slave. Which is bad as depending on your slave your clients will read different data and subsequent writes will change other data, so you end up with data which is even more different.

Now you switch over to mixed replication, which might use row-based replication for some statements. With RBR you really need the exact same data as it has a hard time to identify the rows which were changed and update them.

So what should you do? - Configure your master to use mixed logging and then setup the slave using a consistent snapshot.

Best Answer

Related Solutions

Mysql – Is replication from MySQL 5.5 master to a 5.1 slave possible

MySQL binlog format dilemma

Related Topic