MySQL怎麼保證備份資料的一致性

前言

為了資料安全，資料庫需要定期備份，這個大家都懂，然而資料庫備份的時候，最怕寫操作，因為這個最容易導致資料的不一致，松哥舉一個簡單的範例大家來看：

假設在資料庫備份期間，有用戶下單了，那麼可能會出現以下問題：

• ##庫存錶扣庫存。

• 備份庫存表。

• 備份訂單表資料。

• 訂單表新增訂單。

• 用戶表扣除帳戶餘額。

• 備份使用者表。

假如依照上述邏輯操作，備份檔案中的訂單表會缺少一筆記錄。如果使用該備份檔案來還原數據，會缺少一筆記錄，導致資料不一致。

為了解決這個問題，MySQL 中提供了許多方案，我們來逐一進行解說並分析其優劣。

1. 全庫只讀

要解決這個問題，我們最容易想到的辦法就是在資料庫備份期間設定資料庫唯讀，不能寫，這樣就不用擔心資料不一致了，設定全庫只讀的方法也很簡單，首先我們執行如下SQL 先看看對應變數的值：

show variables like 'read_only';

可以看到，預設情況下，

read_only 是OFF，也就是關閉狀態，我們先把它改為ON，執行如下SQL：

set global read_only=1;

1 表示ON，0 表示OFF，執行結果如下：

這個

read_only 對super 用戶無效，所以設定完成後，接下來我們退出來這個會話，然後建立一個不包含super 權限的用戶，用新用戶登錄，登入成功之後，執行一個插入SQL，結果如下：

可以看到，這個錯誤訊息中說，現在的MySQL 是唯讀的（只能查詢），不能執行當前SQL。

加了唯讀屬性，就不用擔心備份的時候發生資料不一致的問題了。

但

read_only 我們通常用來識別一個MySQL 實例是主函式庫還是從函式庫：

• read_only=0，表示該實例為主庫。資料庫管理員 DBA 可能每隔一段時間就會對該實例寫入一些業務無關的資料來判斷主庫是否可寫，是否可用，這就是常見的探測主庫實例是否活著的。

• read_only=1，表示該實例為從函式庫。通常，從函式庫進行定期探活時，只會執行一些讀取操作，例如執行 "select 1;" 這樣的語句。

所以，

read_only 這個屬性其實不適合用來做備份，而且如果使用了read_only 屬性將整個函式庫設定為readonly之後，如果客戶端發生異常，則資料庫就會一直保持readonly 狀態，這會導致整個庫長時間處於不可寫入狀態，風險很高。

因此這種方案不合格。

2. 全域鎖定

全域鎖，顧名思義，就是把整個函式庫鎖起來，鎖起來的函式庫就不能增刪改了，只能讀了。

那我們看看怎麼使用全域鎖定。 MySQL 提供了一個加上全域讀鎖定的方法，指令是

flush tables with read lock (FTWRL)。當你需要讓整個函式庫處於唯讀狀態的時候，可以使用這個指令，之後其他執行緒的增刪改等操作就會被阻塞。

從圖中可以看到，使用

flush tables with read lock; 指令可以鎖定表格；使用unlock tables;指令則可以完成解鎖操作（會話中斷時也會自動解鎖）。

和第一小節的方案相比，FTWRL 有一點進步，即：執行FTWRL 命令之後如果客戶端發生異常斷開，那麼MySQL 會自動釋放這個全域鎖，整個庫回到可以正常更新的狀態，而不會一直處於唯讀狀態。

但是！ ！ ！

加上了全域鎖，就表示整個資料庫在備份期間都是唯讀狀態，那麼在資料庫備份期間，業務就只能停擺了。

所以這種方式也不是最佳方案。

3. 事務

不知道小夥伴們是否還記得松哥之前和大家分享的資料庫的隔離級別，四種隔離級別中有一個是

可重複讀（ REPEATABLE READ），這也是MySQL 預設的隔離等級。

如果在該隔離等級下，使用者在另一個交易中執行多次相同的 SELECT 語句，結果始終相同。 （因為正在執行的事務所產生的資料變更不能被外部看到）。

换言之，在 InnoDB 这种支持事务的存储引擎中，那么我们就可以在备份数据库之前先开启事务，此时会先创建一致性视图，然后整个事务执行期间都在用这个一致性视图，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作，并且这些更新操作不会被当前事务看到。

在可重复读的隔离级别下，即使其他事务更新了表数据，也不会影响备份数据库的事务读取结果，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

具体操作也很简单，使用 mysqldump 备份数据库的时候，加上 -–single-transaction 参数即可。

为了看到 -–single-transaction 参数的作用，我们可以先开启 general_log，general_log 即 General Query Log，它记录了 MySQL 服务器的操作。当客户端连接、断开连接、接收到客户端的 SQL 语句时，会向 general_log 中写入日志，开启 general_log 会损失一定的性能，但是在开发、测试环境下开启日志，可以帮忙我们加快排查出现的问题。

通过如下查询我们可以看到，默认情况下 general_log 并没有开启：

我们可以通过修改配置文件 my.cnf(Linux)/my.ini（Windows），在 mysqld 下面增加或修改（如已存在配置项）general_log 的值为1，修改后重启 MySQL 服务即可生效。

也可以通过在 MySQL 终端执行 set global general_log = ON 来开启 general log，此方法可以不用重启 MySQL。

开启之后，默认日志的目录是 mysql 的 data 目录，文件名默认为 主机名.log。

接下来，我们先来执行一个不带 -–single-transaction 参数的备份，如下：

mysqldump -h localhost -uroot -p123 test08 > test08.sql

大家注意默认的 general_log 的位置。

接下来我们再来加上 -–single-transaction 参数看看：

mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql

大家看我蓝色选中的部分，可以看到，确实先开启了事务，然后才开始备份的，对比不加 -–single-transaction 参数的日志，多了开启事务这一部分。

以上是MySQL怎麼保證備份資料的一致性的詳細內容。更多資訊請關注PHP中文網其他相關文章！