Cet article vous apporte des connaissances pertinentes sur mysql. Il présente principalement les problèmes liés à la technologie de réplication MySQL, notamment la réplication asynchrone, la réplication semi-synchrone, etc. Utile.
Apprentissage recommandé : Tutoriel vidéo mysql
Réplication asynchrone
Dans la réplication asynchrone (réplication asynchrone), le maître n'a pas besoin de se soucier de savoir si l'esclave reçoit le journal binaire, donc le maître ne le fait pas avoir des dépendances sur le Slave . Vous pouvez considérer le maître et l'esclave comme deux serveurs fonctionnant indépendamment, et les données finiront par être cohérentes via le journal binaire.
La réplication asynchrone offre les meilleures performances car elle n'entraîne presque aucune surcharge sur la base de données elle-même, à moins que le délai maître-esclave soit très important et que le Dump Thread doive lire un grand nombre de fichiers journaux binaires.
Si l'entreprise n'a pas d'exigences élevées en matière de cohérence des données et peut tolérer une perte de données, voire une perte importante, en cas de panne, il est recommandé d'utiliser la réplication asynchrone, qui offre les meilleures performances (telles que des entreprises comme Weibo, bien que les exigences de performances soient extrêmement élevées, mais la perte de données est généralement tolérée). Mais les systèmes de base de l'entreprise sont souvent plus préoccupés par la sécurité des données, comme les services de surveillance et les systèmes d'alarme.
Planification de l'environnement de réplication asynchrone :
master (docker), port 3310
slave (docker), port 3311
configuration principale
fichier de configuration my.cnf
rSta
$ sudo cat /home/mysql/docker-data/3311/conf/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/home/mysql/docker-data/3307/data
#socket=/home/mysql/docker-data/3307/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log-error=/home/mysql/docker-data/3307/logs/mysqld.log
#pid-file=/home/mysql/docker-data/3307/mysqld.pid
lower_case_table_names=1 # 表名是否小写
server-id=1403311
log-bin=mysql-bin # 开启binlog
binlog-format=ROW # binlog的格式
auto_increment_increment=1 # 自增的步长，适用于主主复制，为了避免id冲突，步长设置为master的个数
auto_increment_offset=1 # 自增的偏移，主主复制每个master的偏移需要不一致
# binlog-do-db=mstest # 要同步的数据库
# binlog-ignore-db=mysql # 要忽略的数据库
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000
Copier après la connexion
t mysql :
$ docker run --name mysql3310 -p 3310:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3310/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3310/data/:/var/lib/mysql -v /home/mysql/docker-data/3310/logs/:/var/log/mysql -d mysql:5.7
Copier après la connexion
Créer un utilisateur pour la synchronisation :
mysql> GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
Copier après la connexion
Voir le log binaire du maître sur le maître :
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1147 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Copier après la connexion
Voir la liste des processus sur le maître :
mysql> show processlist;
+----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
| 2 | root | localhost | order | Query | 0 | starting | show processlist |
| 6 | repluser | 172.17.0.1:48450 | NULL | Binlog Dump | 448 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
Copier après la connexion
Configuration de l'esclave
Le fichier de configuration my.cnf est cohérent avec la configuration du maître , sauf que le champ server-id doit être unique.
Démarrez MySQL :
$ docker run --name mysql3311 -p 3311:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3311/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3311/data/:/var/lib/mysql -v /home/mysql/docker-data/3311/logs/:/var/log/mysql -d mysql:5.7
Copier après la connexion
Définissez les informations du maître dans l'esclave :
# master_log_file和master_log_pos取上面show master status显示的值mysql> change master to master_host='172.23.252.98',master_port=3310,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1147;
Copier après la connexion
Démarrez l'esclave, démarrez les threads SQL et IO :
mysql> start slave;Query OK, 0 rows affected (0.00 sec)
Copier après la connexion
Vérifiez l'état de l'esclave :
mysql> show slave status\G;*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.54.214
Master_User: repluser
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1147
Relay_Log_File: 2da789531bf3-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1147
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1403311
Master_UUID: cd2eaa0a-7a59-11ec-b3b4-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:1 row in set (0.00 sec)Copier après la connexion
Seules les données nouvellement ajoutées seront copiées. Les données existantes doivent être synchronisées manuellement à l'aide d'outils (tels que mysqldump).
Affichez la liste des processus sur l'esclave :
mysql> show processlist;
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| 4 | root | localhost | order | Query | 0 | starting | show processlist |
| 7 | system user | | NULL | Connect | 533 | Waiting for master to send event | NULL |
| 8 | system user | | NULL | Connect | 127 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
Copier après la connexion
Réplication semi-synchrone
La réplication semi-synchrone est le mécanisme de réplication semi-synchrone avant MySQL 5.7.
La réplication semi-synchrone nécessite que pendant le processus de soumission de la transaction principale, au moins N esclaves reçoivent des journaux binaires. Cela garantit que lorsque le maître tombe en panne, les données d'au moins N serveurs esclaves sont complètes.
La réplication semi-synchrone n'est pas une fonction intégrée de MySQL. Au lieu de cela, vous devez installer le plug-in semi-synchrone, activer la fonction de réplication semi-synchrone et configurer N esclaves pour accepter avec succès les journaux binaires.
Inconvénients : Supposons que l'utilisateur 1 insère une donnée dans la base de données principale et attend que les données soient renvoyées. L'utilisateur 2 peut interroger ces données à ce moment-là. Si le maître raccroche à ce moment, l'utilisateur 2 ne peut pas retrouver ces données. résultant en un phénomène similaire à la lecture fantôme.
Réplication semi-synchrone améliorée
La réplication semi-synchrone améliorée résout les défauts de la réplication semi-synchrone WAIT et ACK se produisent avant la validation de la transaction, donc même si l'esclave ne reçoit pas le journal binaire, le maître l'est. en panne en raison de la dernière transaction. Elle n'a pas encore été soumise, donc les données elles-mêmes ne sont pas visibles du monde extérieur et il n'y a aucun problème de perte.
Ainsi, pour toute entreprise ayant des exigences de cohérence des données, comme les activités principales de commande du commerce électronique, de la banque, de l'assurance, des valeurs mobilières et d'autres activités étroitement liées aux fonds, vous devez utiliser une réplication semi-synchrone améliorée. De cette façon, les données sont sûres et garanties. Même en cas de temps d'arrêt, la machine esclave disposera d'une copie complète des données.
L'environnement de réplication semi-synchrone amélioré est basé sur la réplication asynchrone.
Installez le plug-in. Il est recommandé de l'installer à la fois sur le maître et sur l'esclave, car il y aura un scénario de commutation maître-esclave :
# master
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
# slave
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Copier après la connexion
Assurez-vous que le plug-in est installé avec succès :
mysql> show plugins;
... ...
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
46 rows in set (0.00 sec)
Copier après la connexion
Démarrez d'abord le plug-in. semi-synchronisation sur l'esclave :
mysql> set global rpl_semi_sync_slave_enabled = {0|1}; # 1：启用，0：禁止Copier après la connexion
Puis démarrez la semi-synchronisation sur le maître :
mysql> set global rpl_semi_sync_master_enabled = {0|1}; # 1：启用，0：禁止
# mysql> set global rpl_semi_sync_master_timeout = 10000; # 单位为ms，默认为10sCopier après la connexion
Redémarrez io_thread depuis la bibliothèque :
mysql> stop slave io_thread;
mysql> start slave io_thread;
Copier après la connexion
Pourquoi n'est-il pas recommandé d'écrire les paramètres de semi-synchronisation dans le fichier de configuration
Si les paramètres sont écrits dans le fichier de configuration, l'instance entrera en mode semi-synchronisation immédiatement après le démarrage. En cas de déconnexion à long terme, le redémarrage de l'instance peut entraîner la panne de la base de données principale.
Après avoir reconnecté la base de données esclave qui a été déconnectée pendant une longue période, vous devez attendre que toutes les transactions soient tracées puis activer manuellement le mode semi-synchrone au lieu de basculer directement après le démarrage pour éviter tout impact sur la base de données principale.
Requête des informations sur l'état de la base de données principale
mysql> show global status like "%semi%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
Copier après la connexion
Description du paramètre important :
- Rpl_semi_sync_master_clients：支持和注册半同步复制的已连Slave数
- Rpl_semi_sync_master_no_times：master关闭半同步复制的次数
- Rpl_semi_sync_master_no_tx：master没有收到slave的回复而提交的次数，可以理解为master等待超时的次数，即半同步模式不成功提交数量
- Rpl_semi_sync_master_status：ON是活动状态（半同步），OFF是非活动状态（异步），用于表示主服务器使用的是异步复制模式，还是半同步复制模式
- Rpl_semi_sync_master_tx_avg_wait_time：master花在每个事务上的平均等待时间
- Rpl_semi_sync_master_tx_waits：master等待成功的次数，即master没有等待超时的次数，也就是成功提交的次数
- Rpl_semi_sync_master_yes_tx：master成功接收到slave的回复的次数，即半同步模式成功提交数量。
查询主库参数信息
mysql> show global variables like '%sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| innodb_flush_sync | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| sync_binlog | 1 |
| sync_frm | ON |
| sync_master_info | 10000 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-------------------------------------------+------------+
18 rows in set (0.01 sec)
Copier après la connexion
重要参数说明：
- rpl_semi_sync_master_enabled：（主库）是否启动半同步
- rpl_semi_sync_master_timeout：等待多时毫秒后变成异步复制，默认是10000ms
- rpl_semi_sync_master_wait_point：5.7默认AFTER_SYNC（增强版半同步复制，无损复制模式），在得到slave的应答后再commit，可选值AFTER_COMMIT，在master提交后同步数据给slave，然后master等待slave应答，应答成功返回客户端。
可以在slave端执行stop slave，测试master端会发生什么情况？
在master上执行下面的sql：
mysql> insert into t_order values(3,"C");Query OK, 1 row affected (10.05 sec)
Copier après la connexion
会发现这条语句会阻塞10s（对应上面的参数rpl_semi_sync_master_timeout），然后执行成功，最后看看看master的日志描述：
2022-01-25T02:31:57.016430Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:31:57.016515Z 4 [Note] Start asynchronous binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:33:32.183819Z 2 [Note] Semi-sync replication initialized for transactions.
2022-01-25T02:33:32.183865Z 2 [Note] Semi-sync replication enabled on the master.
2022-01-25T02:33:32.184004Z 0 [Note] Starting ack receiver thread
2022-01-25T02:33:59.644444Z 5 [Note] While initializing dump thread for slave with UUID <aba2eb12-7cbc-11ec-9c1d-0242ac110003>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).
2022-01-25T02:33:59.644612Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:33:59.644632Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1403312)
2022-01-25T02:33:59.644727Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154)
2022-01-25T02:38:02.847879Z 0 [ERROR] mysqld: Got an error reading communication packets
2022-01-25T02:38:27.228952Z 2 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000005, pos: 684), semi-sync up to file mysql-bin.000005, position 419.
2022-01-25T02:38:27.229063Z 2 [Note] Semi-sync replication switched OFF.
2022-01-25T02:39:47.230189Z 5 [Note] Stop semi-sync binlog_dump to slave (server_id: 1403312)
Copier après la connexion
可以发现半同步关闭了，变成异步模式。
推荐学习：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!
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
