mysql 5.6 从现有表 建内存表_Mysql内存表主从复制【译】

Some Applications need to store some transient data which is frequently regenerated and MEMORY table look like a very good match for this sort of tasks. Unfortunately this will bite when you will be looking to add Replication to your environment as MEMORY tables do not play well with replication.

有些应用程序需要频繁存储短期数据，mysql内存表看起来很适合承担这类任务。然而当你在内存表上应用主从复制的时候，会很受伤~

The reason is very simple – both STATEMENT and ROW replication contain the changes to the data in binary logs. This requires the data to be same on Master and Slave. When you restart the slave you will lose contents of your MEMORY tables and replication will break. STATEMENT replication will often continue to run, with contents of the table just being different as there is a little checks whenever statements produce the same results on the slave. ROW replication will complain about ROW not exist for UPDATE or DELETE operation.

原因很简单——语句(STATEMENT)和行(ROW)复制包含数据的改变都记录在binlog中，主从库上的数据必须保持一致。当你出于某种原因重启从库后，将丢失内存表中的数据进而导致复制失败。语句复制将持续执行，表中内容不同时会进行一些检查并在从库上产生同样的结果。行复制在UPDATE或DELETE操作时会报错说行不存在。

So what you can do ? 如此，咋整?

Use Innodb Table Instead Innodb is quite fast when it fits in memory so for most applications this performance will be enough and it will save you from all complexity of different workarounds.

使用Innodb表替代内存表，Innodb对于绝大多数应用来说性能是足够的，且可以将你从各种复杂的场景中解脱出来。

Do not replicate MEMORY tables If you do not really need MEMORY table on the slaves you can skip replicating it specifying replicate-ignore-table=db.memory_table. Note you should not be using STATEMENT level replication with INSERT … SELECT into this memory table for this to work. Be careful using data on the Slave in this case as table will be empty. Another nice trick sometimes is to make slave to generate its own copy of the table, for example by running the same cron jobs MASTER runs to refresh this table periodically.

不复制内存表，如果你的从库上确实不需要内存表，你可以通过设置 replicate-ignore-table=db.memory_table 参数来忽略内存表的主从复制。注意，使用此参数时，不要在内存表上进行语句级的复制——INSERT ... SELECT，这样做从库上的表会是空的。另一个小技巧是在从库上周期性的执行一个计划任务，将主库上的数据刷新到从库中。

Restart Slaves Carefully

I would not use this as long term solution as there are going to be the reasons when SLAVE will not restart normally – power goes down MySQL crashes etc.

If you however are using MEMORY table in replication and just want to do a restart without replication breaking you can do the following:

Add skip-slave-start in your my.cnf; run SLAVE STOP; dump all your memory tables using MySQLDump; Restart the MySQL As planned; Load Dumped tables; run SLAVE START; Remove skip-slave-start from config file. Be careful using it with MASTER-MASTER or CHAIN/TREE replication. In this case you will need to disable binary logging while loading data from mysqldump as you may not want these changes to be replicated.

谨慎的重启从库

长远看，还是不要使用内存表，因为从库可能因各种原因宕掉——掉电、mysql进程崩溃等等。

如果你正在使用内存表并且想在主从复制时不会中断可以这样做：

在my.cnf中添加 skip-slave-start，

执行SALVE STOP；

用mysqldump导出所有内存表；

重启mysql；

导入刚才的表格；

执行SLAVE START；

在my.cnf中去掉 skip-slave-start。

使用MASTER-MASTER或CHAIN/TREE复制时要小心，这种情况下你需要禁用二进制日志，来确保导入数据时不复制某些改变。

What could have done better ?有更好的招儿没?

MySQL could have features to make it more convenient. It would be great to have MEMORY table option which would save table to on disk file on shutdown and load it back on startup. Of course you would lose the data on unclear start, but it is still handy for a lot of cases.

MySQL具备许多特性使其用来更方便。要是能在数据库(正常)关闭时自动将内存表保存到磁盘上，在启动时自动恢复到内存里就太好了。当然非正常关闭后启动依然会丢失数据，但在多数情况下这特性还是很有用好使的。

We could have the option similar to skip-slave-errors but specified on per-table basics. This would allow me to simply allow to avoid all replication errors for MEMORY table which would make things more robust if table is regenerated periodically. It can be helpful in many other cases too.

我们可以选择性的设置 skip-slave-errors 而其他表使用基本设置。这样我能简单的忽略内存表的复制错误，使得表格在周期性的再生数据时程序运行得更加健壮。这种方法对于许多其他案例也适用。