Sélection du format binlog lors de l'utilisation de binlog dans MySQL

La colonne

tutoriel mysql présente le choix du format binlog lors de l'utilisation de binlog.

1. Trois modes de binlog

1. Mode niveau instruction

Chaque SQL qui modifie les données sera enregistré dans le maître dans le journal bin. Lorsque l'esclave est copié, le processus SQL l'analysera dans le même SQL qui a été exécuté du côté maître d'origine et l'exécutera à nouveau. Avantages : Les avantages du niveau instruction sont qu'il résout d'abord les lacunes du niveau ligne. Il n'a pas besoin d'enregistrer les modifications dans chaque ligne de données, réduit la quantité de journaux de journaux, enregistre les E/S et améliore les performances. Parce qu'il n'a besoin d'enregistrer que les détails des instructions exécutées sur le maître et les informations contextuelles lorsque les instructions sont exécutées. Inconvénients : Puisqu'il s'agit d'une instruction d'exécution enregistrée, pour que ces instructions soient exécutées correctement du côté esclave, elle doit également enregistrer certaines informations pertinentes lorsque chaque instruction est exécutée, c'est-à-dire des informations contextuelles, pour garantir que toutes les instructions sont exécutées. correctement lorsqu'il est exécuté du côté esclave, le même résultat peut être obtenu que lorsqu'il est exécuté du côté maître. De plus, comme MySQL se développe rapidement, de nombreuses nouvelles fonctions ont été ajoutées, ce qui rend la réplication de MySQL confrontée à de nombreux défis. Naturellement, plus le contenu est complexe dans la réplication, plus il est facile pour les bogues d'apparaître. Au niveau des instructions, il a été découvert que de nombreuses situations provoqueraient des problèmes de réplication MySQL, principalement lorsque certaines fonctions ou fonctions sont utilisées lors de la modification des données. Par exemple, sleep() ne peut pas être copié correctement.

2. Mode niveau de ligne

Le journal enregistrera la forme modifiée de chaque ligne de données, puis modifiera les mêmes données du côté esclave. Avantages : Le journal bin n'a pas besoin d'enregistrer les informations liées au contexte de l'instruction SQL exécutée. Il doit uniquement enregistrer quel enregistrement a été modifié et quelle a été la modification. Par conséquent, le contenu du journal au niveau des lignes enregistrera clairement les détails de chaque ligne de modification de données. Et il n'y aura aucun problème si les procédures stockées, les fonctions et les appels et déclencheurs de déclencheurs ne peuvent pas être copiés correctement dans certaines circonstances. Inconvénients : au niveau de la ligne, lorsque toutes les instructions exécutées sont enregistrées dans le journal, elles seront enregistrées sous forme de modifications enregistrées dans chaque ligne, ce qui peut générer une grande quantité de contenu du journal. Par exemple, il existe une telle instruction de mise à jour : mettre à jour l'ensemble de produits. Owner_member_id= 'd' oùowner_member_id='a', après exécution, ce qui est enregistré dans le journal n'est pas l'événement correspondant à cette instruction de mise à jour (mysql enregistre le journal bin-log sous forme d'événements), mais chaque événement mis à jour par cette instruction. Les modifications d'un enregistrement sont enregistrées comme autant d'événements dans lesquels de nombreux enregistrements sont mis à jour. Naturellement, la quantité de journaux bin-log sera importante.

3. Le mode mixte

est en fait une combinaison des deux premiers modes. En mode mixte, mysql distinguera le formulaire de journal à enregistrer en fonction de chaque instruction SQL spécifique exécutée. choisissez entre l'instruction et la ligne. Le niveau des instructions dans la nouvelle version est toujours le même qu'auparavant, seules les instructions exécutées sont enregistrées. Le mode au niveau des lignes a été optimisé dans la nouvelle version de mysql. Toutes les modifications ne seront pas enregistrées au niveau des lignes. Par exemple, lorsque la structure de la table change, elle sera enregistrée en mode instruction si l'instruction sql est bien update ou For. des instructions telles que delete qui modifient les données, toutes les modifications de ligne seront toujours enregistrées.

2. Quel format devons-nous choisir lors de l'utilisation de binlog

Grâce à l'introduction ci-dessus, nous savons que binlog_format peut économiser les IO et accélérer la synchronisation dans certains scénarios, mais pour InnoDB, le moteur de transaction , lorsque le niveau d'isolement READ-COMMITTED, READ-UNCOMMITTED ou le paramètre innodb_locks_unsafe_for_binlog est activé, interdit l'écriture sous binlog_format=statement. En même temps, binlog_format=mixed est un mode qui utilise par défaut le format d'instruction pour les moteurs non transactionnels et autres. niveaux d’isolement. Seul le format des lignes sera enregistré.

> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

> create table t(c1 int) engine=innodb;

> set binlog_format=statement;

> insert into t values(1);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

> set binlog_format='mixed';

> show binlog events in 'mysql-bin.000004'\G
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
        Pos: 287
 Event_type: Gtid
  Server_id: 3258621899
End_log_pos: 335
       Info: SET @@SESSION.GTID_NEXT= 'ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375'
*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 335
 Event_type: Query
  Server_id: 3258621899
End_log_pos: 407
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 407
 Event_type: Table_map
  Server_id: 3258621899
End_log_pos: 452
       Info: table_id: 124 (test.t)
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 452
 Event_type: Write_rows_v1
  Server_id: 3258621899
End_log_pos: 498
       Info: table_id: 124 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: mysql-bin.000002
        Pos: 498
 Event_type: Xid
  Server_id: 3258621899
End_log_pos: 529
       Info: COMMIT /* xid=18422 */复制代码

Pourquoi ne puis-je pas utiliser le format de déclaration binlog sous READ-COMMITTED(RC) et READ-UNCOMMITTED ? En effet, lorsqu'une instruction est exécutée dans une transaction, elle peut voir les données soumises ou en cours d'écriture par d'autres transactions. Une fois la transaction validée, le binlog est écrit puis lu à partir de la bibliothèque esclave. Les données que vous verrez ne correspondront pas aux données écrites dans la bibliothèque principale. Par exemple: Il y a une table :

+------+------+
| a    | b    |
+------+------+
|   10 |    2 |
|   20 |    1 |
+------+------+复制代码

On fait ce qui suit :

1. mises à jour de session1 dans la transaction, UPDATE t1 SET a=11 where b=2; Il y a un enregistrement de ligne (10,2) qui remplit les conditions , et il n'y a pas de soumission.
2. session2 effectue également une opération de mise à jour, met à jour la ligne (20,1) en (20,2) et la soumet.
3. Ensuite, la session précédente1 valide la mise à jour sur la ligne (10,2).

Si le binlog utilise l'enregistrement au format Statement, pendant la lecture esclave, la mise à jour de la session2 sera lue en premier car elle a été soumise en premier, et la ligne (20,1) sera mise à jour en (20, 2). Ensuite, l'instruction UPDATE t1 SET a=11 where b=2; de session1 sera lue et les deux lignes (10,2) et (20,2) seront mises à jour en (11,2). Cela se traduit par le comportement de la bibliothèque principale (11, 2), (20,2) et le côté esclave est (11,2), (11, 2).

三、问题分析

上面是通过一个具体的例子说明。本质原因是RC事务隔离级别并不满足事务串行化执行要求，没有解决不可重复和幻象读。

对于Repetable-Read和Serializable隔离级别就没关系，Statement格式记录。这是因为对于RR和Serializable，会保证可重复读，在执行更新时候除了锁定对应行还会在可能插入满足条件行的时候加GAP Lock。上述case更新时，session1更新b =2的行时，会把所有行和范围都锁住，这样session2在更新的时候就需要等待。从隔离级别的角度看Serializable满足事务的串行化，因此binlog串行记录事务statement格式是可以的。同时InnoDB的RR隔离级别实际已经解决了不可重复读和幻象读，满足了ANSI SQL标准的事务隔离性要求。

READ-COMMITTED、READ-UNCOMMITTED的binlog_format限制可以说对于所有事务引擎都适用。

四、拓展内容

对于InnoDB RR和Serializable隔离级别下就一定能保证binlog记录Statement格式么？也不一定。在Innodb中存在参数innodb_locks_unsafe_for_binlog控制GAP Lock，该参数默认为OFF：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.01 sec)复制代码

即RR级别及以上除了行锁还会加GAP Lock。但如果该参数设置为ON，对于当前读就不会加GAP Lock，即在RR隔离级别下需要加Next-key lock的当前读蜕化为READ-COMMITTED。所以如果此参数设置为ON时即便使用的事务隔离级别为Repetable-Read也不能保证从库数据的正确性。

五、总结

对于线上业务，如果使用InnoDB等事务引擎，除非保证RR及以上隔离级别的写入，一定不要设置为binlog_format为STATEMENT，否则业务就无法写入了。而对于binlog_format为Mixed模式，RR隔离级别以下这些事务引擎也一定写入的是ROW event。

更多相关免费学习推荐：mysql教程(视频)

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!