MYSQL半同步

于 2018-08-11 01:41:40 发布
阅读量723 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wwy0324/article/details/81572455
版权

server2：

[root@server2 ~]# vim /etc/my.cnf

 29 server-id=2
 30 log-bin=mysql-bin
 31 log-slave-updates
 32 
 33 gtid_mode=ON
 34 enforce-gtid-consistency=true

这里写图片描述

mysql> show slave status\G;

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

这里写图片描述
server3：

[root@server3 ~]# vim /etc/my.cnf

 31 gtid_mode=ON
 32 enforce-gtid-consistency=true

这里写图片描述

[root@server3 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server3 ~]# cd /var/log/
[root@server3 log]# grep password mysqld.log
[root@server3 log]# mysql_secure_installation
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

这里写图片描述

mysql> grant replication slave on *.* to repl@'172.25.8.%' identified by 'Westos+123';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.16 sec)

测试

[root@server3 log]# mysql -u repl -p -h 172.25.8.2    #可以登录

这里写图片描述
server2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use westos;
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> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| usertb           |
+------------------+
1 row in set (0.00 sec)

root@server2 ~]# mysqldump -p westos > westos.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@server2 ~]# scp westos.sql root@172.25.8.3:

server3：

[root@server3 ~]# vim westos.sql

 31 create database westos;
 32 
 33 use westos

这里写图片描述

[root@server3 ~]# mysql -p < westos.sql
Enter password: 


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use westos;
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> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| usertb           |
+------------------+
1 row in set (0.00 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| zmy      | 123      |
| lty      | 456      |
| jcl      | 789      |
+----------+----------+
3 rows in set (0.00 sec)

server2

mysql> grant replication slave on *.* to repl@'172.25.8.%' identified by 'Westos+123';
Query OK, 0 rows affected, 1 warning (0.12 sec)

server3


mysql> change master to master_host='172.25.8.2',master_user='repl',master_password='Westos+123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.85 sec)

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G;

这里写图片描述

server1

mysql> use westos;
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> insert into westos.usertb values ('user1','333');
Query OK, 1 row affected (0.20 sec)

server3

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| zmy      | 123      |
| lty      | 456      |
| jcl      | 789      |
| user1    | 333      |
+----------+----------+
4 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User        | Host          | db   | Command          | Time | State                                                         | Info             |
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
|  1 | system user |               | NULL | Connect          |  890 | Slave has read all relay log; waiting for more updates        | NULL             |
|  2 | system user |               | NULL | Connect          | 5048 | Waiting for master to send event                              | NULL             |
| 10 | repl        | server3:36830 | NULL | Binlog Dump GTID | 1079 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 11 | root        | localhost     | NULL | Query            |    0 | starting                                                      | show processlist |
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
[root@server2 ~]# vim /etc/my.cnf

 36 slave-parallel-type=LOGICAL_CLOCK             #组提交（并行复制）
 37 slave-parallel-workers=16                     #slave 开多少个线程
 38 master_info_repository=TABLE                  #保存master.info
 39 relay_log_info_repository=TABLE               #保存relay-log.info
 40 relay_log_recovery=ON                         #自动打开恢复选项

这里写图片描述

[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
mysql> show processlist;

这里写图片描述

server3

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.8.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 234
               Relay_Log_File: server3-relay-bin.000003
                Relay_Log_Pos: 447
        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: 234
              Relay_Log_Space: 1469
              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: 2
                  Master_UUID: 6965f416-9c49-11e8-a5cd-525400fd9fef
             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: 6965f416-9c49-11e8-a5cd-525400fd9fef:3,
c49edbab-9c49-11e8-a600-525400cfed2c:2
            Executed_Gtid_Set: 6965f416-9c49-11e8-a5cd-525400fd9fef:1-3,
c49edbab-9c49-11e8-a600-525400cfed2c:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

server1

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  #安装半同步复制的插件
Query OK, 0 rows affected (0.58 sec)

server2

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.41 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.12 sec)

server3

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.45 sec)

server3

mysql> select plugin_name,plugin_status
    -> from information_schema.plugins
    -> where plugin_name like '%semi%';
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)


mysql> show plugins;

这里写图片描述
server1

mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

server2

mysql> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.42 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

server3

mysql> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.07 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

server1

mysql> show variables like 'rpl_semi_sync%';                  #查看参数有没有生效
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |    #延迟10s
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |    # 收到1个slave就可以提交
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync%';                     #查看状态
+--------------------------------------------+-------+
| 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             | 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     |
+--------------------------------------------+-------+
14 rows in set (0.07 sec)

server2

mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| 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         |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| 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)

server3

mysql> show variables like 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

server1

mysql> use westos
Database changed
mysql> insert into usertb values ('user2','222');
Query OK, 1 row affected (0.13 sec)

mysql> insert into usertb values ('user3','234');
Query OK, 1 row affected (0.12 sec)

server2

mysql> show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| 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             | 2     |
| 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      | 426   |
| Rpl_semi_sync_master_tx_wait_time          | 853   |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     |  # 变成2了
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

server2

mysql> stop slave io_thread;          #如果server2关闭IO线程，server3将无法更新到server1的数据
Query OK, 0 rows affected (0.16 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

server3

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| zmy      | 123      |
| lty      | 456      |
| jcl      | 789      |
| user1    | 333      |
| user2    | 222      |
| user3    | 234      |
+----------+----------+
6 rows in set (0.00 sec)
加油，你可以的！
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值