為了資料安全,資料庫需要定期備份,這個大家都懂,然而資料庫備份的時候,最怕寫操作,因為這個最容易導致資料的不一致,松哥舉一個簡單的範例大家來看:
假設在資料庫備份期間,有用戶下單了,那麼可能會出現以下問題:
show variables like 'read_only';
read_only 是OFF,也就是關閉狀態,我們先把它改為ON,執行如下SQL:
set global read_only=1;
read_only 對super 用戶無效,所以設定完成後,接下來我們退出來這個會話,然後建立一個不包含super 權限的用戶,用新用戶登錄,登入成功之後,執行一個插入SQL,結果如下:
read_only 我們通常用來識別一個MySQL 實例是主函式庫還是從函式庫:
read_only 這個屬性其實不適合用來做備份,而且如果使用了
read_only 屬性將整個函式庫設定為readonly之後,如果客戶端發生異常,則資料庫就會一直保持readonly 狀態,這會導致整個庫長時間處於不可寫入狀態,風險很高。
flush tables with read lock (FTWRL)。當你需要讓整個函式庫處於唯讀狀態的時候,可以使用這個指令,之後其他執行緒的增刪改等操作就會被阻塞。
flush tables with read lock; 指令可以鎖定表格;使用
unlock tables;指令則可以完成解鎖操作(會話中斷時也會自動解鎖)。
可重複讀( REPEATABLE READ),這也是MySQL 預設的隔離等級。
换言之,在 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中文網其他相關文章!