Maison >base de données >tutoriel mysql >Comment activer la réplication semi-synchrone dans MySQL 5.7
Comment activer la réplication semi-synchrone dans MySQL 5.7
- 一个新手original
- 2017-09-08 14:40:403187parcourir
1. Installer les plug-ins associés
show plugins; 查看模块 help --uninstall; 查看卸载模块
master: mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; --安装 semisync_master.so插件 Query OK, 0 rows affected (0.03 sec) slave: root@localhost [zw3306]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; --安装 semisync_slave.so插件 Query OK, 0 rows affected (0.00 sec) root@localhost [zw3306]>install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.00 sec)
2 . Paramètres modifiés :
set global rpl_semi_sync_master_enabled=1; set global rpl_semi_sync_master_timeout=1000; set global rpl_semi_sync_slave_enabled=1;
Vous pouvez également écrire directement dans le fichier de configuration [mysqld]
master: [mysqld] rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout = 1000 # 1 second slave： [mysqld] rpl_semi_sync_slave_enabled = 1
Modifié. paramètres Nécessité de redémarrer :
Afficher les paramètres modifiés
master: mysql> show global variables like '%rpl_semi%';
+-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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 | +-------------------------------------------+------------+ 6 rows in set (0.00 sec)
slave: root@localhost [(none)]>show global variables like '%rpl_semi%';
+-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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 | +-------------------------------------------+------------+ 8 rows in set (0.00 sec)
Si : Si la structure de copie originale est déjà construite, c'est très simple :
stop slave io_thread; start slave io_thread;
3. Synchroniser
change master to master_host='192.168.26.233', master_port=3306, master_user='repl',master_password='repl', master_auto_position=1;
root@localhost [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.26.233
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 194
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000002
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: 194
Relay_Log_Space: 861
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: 0
Master_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: 3306100
Master_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08
Master_Info_File: mysql.slave_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: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-9
Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-9,
ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified5. >
root@localhost [zw3306]>show tables;
+------------------+ | Tables_in_zw3306 | +------------------+ | t1 | +------------------+ 1 row in set (0.00 sec)
root@localhost [zw3306]>select * from t1;
+------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec)
6. Comment pour confirmer qu'il est synchrone ou semi-synchrone ?
show global variables like '%semi%'; show global status like '%semi%';
master: root@localhost [zw3306]>show global status like '%semi%';
+--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | 有多少个Semi-sync的备库 | Rpl_semi_sync_master_net_avg_wait_time | 0 | 事务提交后，等待备库响应的平均时间 | Rpl_semi_sync_master_net_wait_time | 0 | 等待网络响应的总次数 | Rpl_semi_sync_master_net_waits | 7 | 总的网络等待时间 | Rpl_semi_sync_master_no_times | 0 | 一共有几次从Semi-sync跌回普通状态 | Rpl_semi_sync_master_no_tx | 0 | 库未及时响应的事务数,如果这个值很大就有问题 | Rpl_semi_sync_master_status | ON | 主库上Semi-sync是否正常开启 | Rpl_semi_sync_master_timefunc_failures | 0 | 时间函数未正常工作的次数 | Rpl_semi_sync_master_tx_avg_wait_time | 410 | 开启Semi-sync，事务返回需要等待的平均时间 | Rpl_semi_sync_master_tx_wait_time | 2876 | 事务等待备库响应的总时间 | Rpl_semi_sync_master_tx_waits | 7 | 事务等待备库响应的总次数 | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | 改变当前等待最小二进制日志的次数 | Rpl_semi_sync_master_wait_sessions | 0 | 当前有几个线程在等备库响应 | Rpl_semi_sync_master_yes_tx | 7 | Semi-sync模式下，成功的事务数 +--------------------------------------------+-------+ 15 rows in set (0.00 sec)
root@localhost [zw3306]>show global variables like '%semi%';
+-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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 | +-------------------------------------------+------------+ 8 rows in set (0.00 sec)
Rpl_semi_sync_master_no_tx Si cette valeur est très grande, il y aura un problème
Il y a aussi d'autres raisons ;mysql> show global status like '%semi%';
+--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 17 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 10 | 不是用半同步复制的 | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 410 | | Rpl_semi_sync_master_tx_wait_time | 2876 | | Rpl_semi_sync_master_tx_waits | 7 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 7 | +--------------------------------------------+-------+ 14 rows in set (0.01 sec)
操作10个事物，可以发现都是 Rpl_semi_sync_master_no_tx master接收到N个slave的应答后，才commit事物，等待1s用户可以设置应道slave的数量。 rpl_semi_sync_master_wait_for_slave_count=1 默认是1 set global rpl_semi_sync_master_wait_for_slave_count=2;
signifie combien d'esclaves semi-synchrones ;
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!
Déclaration：
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn