MySQL study之主从复制--半同步复制
MySQL半同步复制和异步复制的差别如上述架构图所示：在mysql异步复制的情况下，Mysql Master Server将自己的Binary Log通过复制线程传输出去以后，Mysql MasterSever就自动返回数据给客户端，而不管slave上是否接受到了这个二进制日志。在半同步复制的架构下，当master在将自己binlog发给slave上的时候，要确保slave已经接受到了这个二进制日志以后，才会返回数据给客户端。对比两种架构：异步复制对于用户来说，可以确保得到快速的响应结构，但是不能确保二进制日志确实到达了slave上；半同步复制对于客户的请求响应稍微慢点，但是他可以保证二进制日志的完整性。
一、配置主从同步
主服务器my.cnf：
[root@rh6 ~]# cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
server_id = 1
# rpl_semi_sync_master_enabled = 1
# rpl_semi_sync_master_timeout = 1000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
在主服务器上授权从服务器用户访问：
master 授权：
mysql> GRANT REPLICATION SLAVE ON *.* to 'tom'@'%' identified by'oracle';
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| tom | % | *2447D497B9A6A15F2776055CB2D1E9F86758182F|
+------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)
查看master服务器状态：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 317| | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
配置从服务器：
从服务器my.cnf：
[root@mysrv01 ~]# cat /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
relay-log = relay-log
relay-log-index = relay-log.index
skip-slave-start=1
log-bin=mysql-bin
binlog_format=mixed
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
innodb_buffer_pool_size=1GB
innodb_buffer_pool_instances=2
symbolic-links=0
init_connect='set autocommit=0'
server-id=2
# rpl_semi_sync_slave_enabled = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
登陆从服务器验证：
[root@mysrv01 ~]# mysql -u root -p
Enter password:
mysql> show variables like '%relay%';
+---------------------------+---------------------------------------+
| Variable_name |Value |
+---------------------------+---------------------------------------+
| max_relay_log_size |0 |
| relay_log |relay-log |
| relay_log_basename |/usr/local/mysql/data/relay-log |
| relay_log_index |/usr/local/mysql/data/relay-log.index |
| relay_log_info_file |relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery |OFF |
| relay_log_space_limit |0 |
| sync_relay_log |10000 |
| sync_relay_log_info |10000 |
+---------------------------+---------------------------------------+
11 rows in set (0.02 sec)
配置从服务器连接主服务器：
mysql> change master tomaster_host='192.168.8.200',master_user='tom',master_password='oracle',master_log_file='mysql-bin.000003',master_log_pos=317;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:Waiting for master to send event
Master_Host:192.168.8.200
Master_User:tom
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000003
Read_Master_Log_Pos:317
Relay_Log_File:relay-log.000002
Relay_Log_Pos:283
Relay_Master_Log_File:mysql-bin.000003
Slave_IO_Running:Yes ；；从服务器IO thread启动，用于接收relay log并写入本地relay log files
Slave_SQL_Running:Yes ；； 从服务器SQL thread启动，对relay log应用并执行SQL
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:317
Relay_Log_Space:450
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:1
Master_UUID:0954ca39-ac09-11e6-b4ab-08002786d9b2
Master_Info_File:/usr/local/mysql/data/master.info
SQL_Delay:0
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
二、配置半同步：
主服务器配置：
mysql> install plugin rpl_semi_sync_masterSONAME 'semisync_master.so'; ；；安装半同步插件
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like "%semi%";
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
半同步相关参数：
| rpl_semi_sync_master_enabled | OFF | 是否启动半同步复制，默认关闭
| rpl_semi_sync_master_timeout | 10000 | 等待从服务器告诉接受到的超时时间，如果时间到了，还没接受到，自动降级为异步
| rpl_semi_sync_master_trace_level | 32 | 运行级别
| rpl_semi_sync_master_wait_no_slave | ON | 没有slave的时候是否也需要等待，默认为也需要等待
激活半同步：
mysql> set global rpl_semi_sync_master_enabled = 1; ；；将这两个参数写入到my.cnf
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show 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_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
从服务器配置：
mysql> install plugin rpl_semi_sync_slave SONAME'semisync_slave.so'; ；；安装客户端半同步插件
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like "%semi%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
激活半同步：
mysql> set global rpl_semi_sync_slave_enabled = 1; ；；将参数写入到my.cnf
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%semi%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
重新启动slave service：
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
三、将半同步配置参数写入到my.cnf
在主服务器my.cnf文件中添加两行信息：
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
然后重启server：
mysql> show 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_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
在从服务器my.cnf文件中添加：
rpl_semi_sync_slave_enabled = 1
重启从服务器server：
mysql> show variables like "%semi%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:Waiting for master to send event
Master_Host:192.168.8.200
Master_User:tom
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000004
Read_Master_Log_Pos:120
Relay_Log_File:relay-log.000007
Relay_Log_Pos:283
Relay_Master_Log_File:mysql-bin.000004
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:120
Relay_Log_Space:450
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:1
Master_UUID:0954ca39-ac09-11e6-b4ab-08002786d9b2
Master_Info_File:/usr/local/mysql/data/master.info
SQL_Delay:0
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
四、主从复制同步测试：
主服务器做事务处理：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.02 sec)
mysql> create database prod;
Query OK, 1 row affected (0.00 sec)
mysql> use prod;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values (10),(20),(30);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
查看数据：
mysql> select * from t1;
+------+
| id |
+------+
| 10 |
| 20 |
| 30 |
+------+
3 rows in set (0.00 sec)
从服务器查看同步信息：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prod |
| test |
+--------------------+
5 rows in set (0.05 sec)
mysql> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
五、查看半同步相关参数
mysql> show status like "%semi%";
+-------------------------------------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------------------------------------+----------+
| Rpl_semi_sync_master_clients | 1 | 半同步复制客户端的个数
| Rpl_semi_sync_master_net_avg_wait_time | 555 | 平均等待时间（默认毫秒）
| Rpl_semi_sync_master_net_wait_time | 1665 | 总共等待时间
| Rpl_semi_sync_master_net_waits | 3 | 等待次数
| Rpl_semi_sync_master_no_times | 0 | 关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx | 0 | 表示没有成功接收slave提交的次数
| Rpl_semi_sync_master_status | ON | 表示当前是异步模式还是半同步模式，on为半同步
| Rpl_semi_sync_master_timefunc_failures | 0 | 调用时间函数失败的次数
| Rpl_semi_sync_master_tx_avg_wait_time | 575 | 事物的平均传输时间
| Rpl_semi_sync_master_tx_wait_time | 1725 | 事物的总共传输时间
| Rpl_semi_sync_master_tx_waits | 3 | 事物等待次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 | 当前有多少个session因为slave的回复而造成等待
| Rpl_semi_sync_master_yes_tx | 3 | 成功接受到slave事物回复的次数
+-------------------------------------------------------------------+---------+
取消半同步复制的插件：
主服务器：
# mysql > uninstall plugin rpl_semi_sync_master;
# mysql > show status like "%semi%"
从服务器：
# mysql > uninstall plugin rpl_semi_sync_slave;
# mysql > show status like "%semi%"
======== 至此@主从复制半同步配置完成 ！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
