Comment MySQL assure-t-il la cohérence des données de sauvegarde ?

Avant-propos

Pour la sécurité des données, la base de données doit être sauvegardée régulièrement. Cependant, lors de la sauvegarde de la base de données, vous avez le plus peur des opérations d'écriture, car c'est la plus susceptible de provoquer une incohérence des données. donne un exemple simple que tout le monde peut regarder :

Supposons qu'un utilisateur passe une commande pendant la période de sauvegarde de la base de données, les problèmes suivants peuvent survenir :

• Inventaire de déduction de la table d'inventaire.

• Tableau d'inventaire de sauvegarde.

• Sauvegardez les données du tableau de commande.

• Ajoutez des commandes au formulaire de commande.

• La table utilisateur déduit le solde du compte.

• Tableau des utilisateurs de sauvegarde.

Si vous suivez la logique ci-dessus, il manquera un enregistrement dans le tableau des commandes dans le fichier de sauvegarde. Si vous utilisez ce fichier de sauvegarde pour restaurer des données, un enregistrement sera manquant, ce qui entraînera une incohérence des données.

Afin de résoudre ce problème, MySQL propose de nombreuses solutions. Expliquons-les une par une et analysons leurs avantages et inconvénients.

1. Base de données complète en lecture seule

Pour résoudre ce problème, le moyen le plus simple auquel nous pouvons penser est de définir la base de données en lecture seule pendant la sauvegarde de la base de données, et de cette façon, nous ne le faisons pas. Vous n'avez pas à vous soucier de l'incohérence des données. La méthode pour définir l'intégralité de la base de données en lecture seule est également très simple. Tout d'abord, nous exécutons le SQL suivant et examinons la valeur de la variable correspondante :

show variables like 'read_only';

Vous. Vous pouvez voir que par défaut, read_only est OFF, ce qui est l'état fermé. Changeons-le d'abord par ON, exécutez le SQL suivant : 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）

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

1 signifie ON, 0 signifie OFF, le résultat de l'exécution. est la suivante :

🎜🎜Ce read_only n'est pas valide pour le super utilisateur, donc une fois le réglage terminé, nous quitterons la session et créerons une nouvelle session. Les utilisateurs dotés de super autorisations se connecteront en tant que nouvel utilisateur. connectez-vous, exécutez un insert SQL et les résultats sont les suivants : 🎜🎜🎜🎜Comme vous pouvez le voir, ce message d'erreur indique que le MySQL actuel est en lecture seule (ne peut qu'interroger) et ne peut pas exécuter le SQL actuel. 🎜🎜Avec l'ajout d'un attribut en lecture seule, vous n'avez pas à vous soucier de l'incohérence des données lors de la sauvegarde. 🎜🎜Mais read_only est généralement utilisé pour identifier si une instance MySQL est la bibliothèque principale ou une bibliothèque esclave : 🎜🎜🎜🎜read_only=0, indiquant que l'instance est la bibliothèque principale. L'administrateur de base de données DBA peut écrire des données non commerciales sur l'instance de temps en temps pour déterminer si la bibliothèque principale est accessible en écriture et disponible. Il s'agit d'un moyen courant de détecter si l'instance de bibliothèque principale est active. 🎜🎜🎜🎜read_only=1, indiquant que l'instance est une bibliothèque esclave. Habituellement, lors de l'exploration régulière de la bibliothèque esclave, seules certaines opérations de lecture seront effectuées, telles que des instructions telles que "select 1;". 🎜🎜🎜🎜Ainsi, l'attribut read_only ne convient pas à la sauvegarde, et si l'attribut read_only est utilisé pour définir la bibliothèque entière en lecture seule, si le client échoue à l'exception, la base de données restera en lecture seule, ce qui rendra toute la bibliothèque dans un état non inscriptible pendant une longue période, et le risque est très élevé. 🎜🎜Cette solution n'est donc pas qualifiée. 🎜🎜2. Verrouillage global🎜🎜Le verrouillage global, comme son nom l'indique, verrouille l'intégralité de la bibliothèque. La bibliothèque verrouillée ne peut pas être ajoutée, supprimée ou modifiée, seulement en lecture. 🎜🎜Voyons ensuite comment utiliser le verrouillage global. MySQL fournit une méthode pour augmenter le verrou de lecture global. La commande consiste à vider les tables avec verrouillage en lecture (FTWRL). Lorsque vous devez mettre l'intégralité de la bibliothèque en lecture seule, vous pouvez utiliser cette commande, puis les opérations telles que les ajouts, les suppressions et les modifications par d'autres threads seront bloquées. 🎜🎜🎜🎜Du picture Comme vous pouvez le voir, la table peut être verrouillée à l'aide de la commande flush tables with read lock; ; l'opération de déverrouillage peut être complétée à l'aide de la commande unlock tables; (cela également être automatiquement déverrouillé lorsque la session est déconnectée). 🎜🎜Par rapport à la solution de la première section, FTWRL présente une légère amélioration, c'est-à-dire : si le client se déconnecte anormalement après avoir exécuté la commande FTWRL, MySQL libérera automatiquement le verrou global et toute la bibliothèque reviendra à un état où elle peut être mis à jour normalement et il ne restera pas en lecture seule. 🎜🎜Mais ! ! ! 🎜🎜L'ajout d'un verrou global signifie que l'ensemble de la base de données est en lecture seule pendant la période de sauvegarde, de sorte que l'activité ne peut être arrêtée que pendant la période de sauvegarde de la base de données. 🎜🎜Cette méthode n’est donc pas la meilleure solution. 🎜🎜3. Transactions🎜🎜Je ne sais pas si vous vous souvenez encore des niveaux d'isolement de la base de données que Brother Song a partagé avec vous auparavant. L'un des quatre niveaux d'isolement est REPEATABLE READ). est également le niveau d'isolement par défaut de MySQL. 🎜🎜Si un utilisateur exécute plusieurs fois la même instruction SELECT dans une autre transaction sous ce niveau d'isolement, le résultat sera toujours le même. (Parce que les modifications de données provoquées par la transaction en cours d'exécution ne peuvent pas être vues par le monde extérieur). 🎜

换言之，在 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 参数的日志，多了开启事务这一部分。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!