Considerations for master-slave synchronization of MySQL memory tables

Some applications need to store some temporary data. At this time, the temporary table seems to be a good choice, but the memory table is not so good in the master-slave database.

The reason is simple. Both statement-based and row-based replication must contain changed data in binary logs. Therefore, the data on the master and slave hosts must be consistent. When the slave database is restarted, data in the memory table is lost and the replication is interrupted.

What should we do?

1. Replace the InnoDB table

The InnoDB table is very fast and can meet our performance requirements.

2. Ignore memory tables during replication

If not necessary, ignore copying the memory table. Use this option.Replicate-ignore-table = dB. memory_table.Note that statement replication should not use insert... select to add data to the memory table. If you want to use insert... select, the table on the slave will be empty, and sometimes the memory table will not be copied to the slave. Another solution is to deploy the same scheduled task on the Master/Slave machine to refresh the table.

3. Restart the slave with caution

In the long run, I will not use memory tables as a solution.

Translated from:

Http://www.mysqlperformanceblog.com/2010/10/15/replication-of-memory-heap-tables/

In addition, if the memory table is used at work and the MySQL on the slave machine is restarted, and you want to synchronize data with MySQL on the host, there is a solution here.

Http://forge.mysql.com/tools/tool.php? Id = 163