2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

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.

有些應(yīng)用程序需要頻繁存儲(chǔ)短期數(shù)據(jù)，mysql內(nèi)存表看起來(lái)很適合承擔(dān)這類任務(wù)。然而當(dāng)你在內(nèi)存表上應(yīng)用主從復(fù)制的時(shí)候，會(huì)很受傷~

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.

原因很簡(jiǎn)單——語(yǔ)句(STATEMENT)和行(ROW)復(fù)制包含數(shù)據(jù)的改變都記錄在binlog中，主從庫(kù)上的數(shù)據(jù)必須保持一致。當(dāng)你出于某種原因重啟從庫(kù)后，將丟失內(nèi)存表中的數(shù)據(jù)進(jìn)而導(dǎo)致復(fù)制失敗。語(yǔ)句復(fù)制將持續(xù)執(zhí)行，表中內(nèi)容不同時(shí)會(huì)進(jìn)行一些檢查并在從庫(kù)上產(chǎn)生同樣的結(jié)果。行復(fù)制在UPDATE或DELETE操作時(shí)會(huì)報(bào)錯(cuò)說(shuō)行不存在。

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表替代內(nèi)存表，Innodb對(duì)于絕大多數(shù)應(yīng)用來(lái)說(shuō)性能是足夠的，且可以將你從各種復(fù)雜的場(chǎng)景中解脫出來(lái)。

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.

不復(fù)制內(nèi)存表，如果你的從庫(kù)上確實(shí)不需要內(nèi)存表，你可以通過(guò)設(shè)置 replicate-ignore-table=db.memory_table 參數(shù)來(lái)忽略內(nèi)存表的主從復(fù)制。注意，使用此參數(shù)時(shí)，不要在內(nèi)存表上進(jìn)行語(yǔ)句級(jí)的復(fù)制——INSERT ... SELECT，這樣做從庫(kù)上的表會(huì)是空的。另一個(gè)小技巧是在從庫(kù)上周期性的執(zhí)行一個(gè)計(jì)劃任務(wù)，將主庫(kù)上的數(shù)據(jù)刷新到從庫(kù)中。

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.

謹(jǐn)慎的重啟從庫(kù)

長(zhǎng)遠(yuǎn)看，還是不要使用內(nèi)存表，因?yàn)閺膸?kù)可能因各種原因宕掉——掉電、mysql進(jìn)程崩潰等等。

如果你正在使用內(nèi)存表并且想在主從復(fù)制時(shí)不會(huì)中斷可以這樣做：

在my.cnf中添加?skip-slave-start， 執(zhí)行SALVE?STOP； 用mysqldump導(dǎo)出所有內(nèi)存表； 重啟mysql； 導(dǎo)入剛才的表格； 執(zhí)行SLAVE?START； 在my.cnf中去掉?skip-slave-start。

使用MASTER-MASTER或CHAIN/TREE復(fù)制時(shí)要小心，這種情況下你需要禁用二進(jìn)制日志，來(lái)確保導(dǎo)入數(shù)據(jù)時(shí)不復(fù)制某些改變。

What could have done better ? 有更好的招兒沒(méi)?

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具備許多特性使其用來(lái)更方便。要是能在數(shù)據(jù)庫(kù)(正常)關(guān)閉時(shí)自動(dòng)將內(nèi)存表保存到磁盤上，在啟動(dòng)時(shí)自動(dòng)恢復(fù)到內(nèi)存里就太好了。當(dāng)然非正常關(guān)閉后啟動(dòng)依然會(huì)丟失數(shù)據(jù)，但在多數(shù)情況下這特性還是很有用好使的。

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.

我們可以選擇性的設(shè)置 skip-slave-errors 而其他表使用基本設(shè)置。這樣我能簡(jiǎn)單的忽略內(nèi)存表的復(fù)制錯(cuò)誤，使得表格在周期性的再生數(shù)據(jù)時(shí)程序運(yùn)行得更加健壯。這種方法對(duì)于許多其他案例也適用。

轉(zhuǎn)載于:https://my.oschina.net/newlooper/blog/507507

總結(jié)

以上是生活随笔為你收集整理的Mysql内存表主从复制【译】的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。