mysql5.7主从复制原理及基本配置

最新推荐文章于 2024-05-20 13:22:58 发布

话多必失丶

最新推荐文章于 2024-05-20 13:22:58 发布

阅读量4k

点赞数 2

本文链接：https://blog.csdn.net/u010489158/article/details/81489651

版权

mysql主从复制原理:

传统的mysql复制就是主-从复制,它会有一个主,一个或多个从,在主节点提交与执行完事物之后,通过bin-log(2进制日志),将其(异步的)通过从节点上的IO线程发送到从节点上，并将bin-log日志存到从节点的readly-log(中继日志)中，通过sql线程以重新执行(在基于语句的复制中),或应用(在基于行的复制中),
当从节点连上主节点后,会向主节点发送上次同步的位置,主节点将日志文件发给从节点

mysql主从复制 (二进制方式,异步复制)

mysql的安装

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server1 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server1 ~]# ls
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar          mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-client-5.7.17-1.el6.x86_64.rpm    mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm    mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm     mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-5.7.17-1.el6.x86_64.rpm  mysql-community-test-5.7.17-1.el6.x86_64.rpm

[root@server1 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y

进行初始化设置

[root@server1 ~]# /etc/init.d/mysqld restart
[root@server1 mnt]# cat /var/log/mysqld.log |grep password
2018-10-18T09:11:31.491475Z 1 [Note] A temporary password is generated for root@localhost: pNa+fASiH2TI         
 #在日志中可以看到mysql初始化密码
2018-10-18T09:11:39.126053Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.lzg4k7.sql' started.
2018-10-18T09:11:39.152990Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.lzg4k7.sql' ended.

修改mysql密码

方式1
[root@server1 ~]# mysql_secure_installation
方式2
ALTER USER root@localhost identified by 'Redhat123...'; #进入数据库修改密码

登陆测试

server2作同样的配置！！！

异步复制的配置（pos方式）

master

配置my.cnf

binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步 test1库,再添加一行“binlog-do-db=test1”,以此类推
binlog-ignore-db=mysql #禁止同步 mysql 数据库

[root@server1 ~]# vim /etc/my.cnf
server-id=1                      ###2的32次方减1，唯一标识符，不能一样
##可以认为 server-id 值类似于 IP 地址:这些 ID 值能唯一识别复制服务器群集中的每个服务器实例。
log-bin=master-bin                ###启动二进制日志

slave

配置my.cnf

replicate-do-db=redhat   #从服务器同步redhat数据库
replicate-ignore-db=redhat   #从服务器不同步redhat数据库
replicate-do-table=redhat.test   #备份redhat库中的test表
replicate-ignore-table=redhat.test   #不备份redhat库中的test表
replicate-wild-do-table=redhat.test%   
replicate-wild-ignore-table=redhat.test%

server-id=2
server2不需要去发送2进制日志给别的服务器，因此不需要打开log-bin

进入数据库

master

1：创建可以进行复制的slave用户repl
mysql> grant replication slave on *.* to 'repl'@'172.25.1.%' identified by 'Redhat123...';
Query OK, 0 rows affected, 0 warning (0.04 sec)
2：刷新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)
3：查看master状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| master-logbin.000001 |      446 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

slave

连接
mysql> change master to master_host='172.25.1.1',master_user='repl',master_password='Redhat123...',master_log_file='mysql-bin.000001',master_log_pos=446;
Query OK, 0 rows affected, 2 warnings (1.70 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.25.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-logbin.000001
          Read_Master_Log_Pos: 446
               Relay_Log_File: server2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-logbin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 446
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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)

ERROR: 
No query specified

#可以看到当没有启动slave时，io线程和sql线程是没有启用的
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-logbin.000001
          Read_Master_Log_Pos: 446
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: master-logbin.000001
             Slave_IO_Running: Yes    #当IO线程和SQL线程都是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: 446
              Relay_Log_Space: 533
              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: cd947c57-d2b5-11e8-91ae-525400d8c130
             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)

ERROR: 
No query specified

在异步复制中，因为从服务器是和主进行同步的，从服务器可以将写的事件同步到从服务器中，但是从服务器无法将二进制日志同步到主服务器中，因此不能在从服务器的mysql中进行写入操作，否则复制会出现问题，一般会在从服务器中添加read-only=yes来限制从服务器写入,但是read-only不对super用户生效

测试

在主节点上插入数据

在从服务器上进行查询

数据已经进行了同步

主服务器上master的状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| master-logbin.000001 |     1360 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从服务器上slave的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-logbin.000001
          Read_Master_Log_Pos: 1360
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 1238
        Relay_Master_Log_File: master-logbin.000001
             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: 1360
              Relay_Log_Space: 1447
              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: cd947c57-d2b5-11e8-91ae-525400d8c130
             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)

ERROR: 
No query specified

通过GTID实现主从复制

GITD是唯一标示符是由服务器的UUID(全局唯一标示对于任何一个服务器都是128位随机符)并结合事务ID号来组合成一个唯一的标示，某一个主机上某一个事务的标识码 就叫做**GTID**

自从引入GTID之后 MySQL5.6的binlog在每一个事务的首部都会写上GTID的标示，在二进制文件中，每一个事务的语句记录下来的时候在这个事件首部会把相关联的GTID给记录下来。

因此GTID使得追踪和比较复制事务变得非常简单，而且能够实现从崩溃中快速进行恢复。

配置GTID

server1中对my.cnf添加的配置

[root@server1 ~]# vim /etc/my.cnf
server-id=1
log-bin=master-logbin
gtid_mode=ON
enforce-gtid-consistency=true


[root@server1 ~]# /etc/init.d/mysqld restart

server2中对my.cnf添加的配置

[root@server2 ~]# vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=true

[root@server2 ~]# /etc/init.d/mysqld restart

配置基于GTID的异步复制（server2）

（1）关闭之前的主从复制在配置GTID主从复制

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='172.25.1.1',master_user='repl',master_password='Redhat123...',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

（2）查看从服务器的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-logbin.000004
          Read_Master_Log_Pos: 459
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 426
        Relay_Master_Log_File: master-logbin.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: 459
              Relay_Log_Space: 675
              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: cd947c57-d2b5-11e8-91ae-525400d8c130
             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: cd947c57-d2b5-11e8-91ae-525400d8c130:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

(3)查看server1的uuid及日志

[root@server1 mysql]# cat auto.cnf 
[auto]
server-uuid=cd947c57-d2b5-11e8-91ae-525400d8c130

[root@server1 mysql]# ls
auto.cnf         ib_buffer_pool  master-logbin.000001  mysql               private_key.pem  test
ca-key.pem       ibdata1         master-logbin.000002  mysqld_safe.pid     public_key.pem
ca.pem           ib_logfile0     master-logbin.000003  mysql.sock          server-cert.pem
client-cert.pem  ib_logfile1     master-logbin.000004  mysql.sock.lock     server-key.pem
client-key.pem   ibtmp1          master-logbin.index   performance_schema  sys

[root@server1 mysql]# mysqlbinlog master-logbin.000002

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181018 18:45:39 server id 1  end_log_pos 123 CRC32 0x5f47a665 	Start: binlog v 4, server v 5.7.17-log created 181018 18:45:39 at startup
ROLLBACK/*!*/;
BINLOG '
U2TIWw8BAAAAdwAAAHsAAAAAAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABTZMhbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AWWmR18=
'/*!*/;
# at 123
#181018 18:45:39 server id 1  end_log_pos 154 CRC32 0xb70dbe9e 	Previous-GTIDs
# [empty]
# at 154
#181018 18:50:49 server id 1  end_log_pos 177 CRC32 0x96a5f640 	Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysql启动多线程

MySQL的多线程复制不是一个新的东西，在5,7之前就已经有了，在5.7上进行了增强；

多线程复制可以减轻主从复制的延迟；

从服务器中一个线程只能针对一个数据库进行复制，显得有些鸡肋；但是大部分操作有可能都是对一个库的操作；

在MySQL5.7中的多线程复制可以看做是基于table的，这样就可以提高效率；

配置my.cnf（server2）

[root@server2 ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16       # ###打开16个线程，原先的进程变成调度器
###下面是优化参数
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
symbolic-links=0

[root@server2 ~]# /etc/init.d/mysqld restart

查看（server2）

（1）未修改配置

mysql> use mysql;
Database changed

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select * from slave_relay_log_info\G;
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./server2-relay-bin.000005
    Relay_log_pos: 4
  Master_log_name: master-logbin.000004
   Master_log_pos: 459
        Sql_delay: 0
Number_of_workers: 0
               Id: 1
     Channel_name: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   13 | Waiting for master to send event                       | NULL             |
|  2 | system user |           | NULL | Connect |   13 | Slave has read all relay log; waiting for more updates | NULL             |
|  6 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

（2）修改配置

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

mysql> use mysql;
Database changed

mysql> select * from slave_relay_log_info\G;
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./server2-relay-bin.000007
    Relay_log_pos: 4
  Master_log_name: master-logbin.000004
   Master_log_pos: 459
        Sql_delay: 0
Number_of_workers: 16
               Id: 1
     Channel_name: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   52 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |           | NULL | Connect |   52 | Waiting for master to send event                       | NULL             |
|  3 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 20 | system user |           | NULL | Connect |   52 | Waiting for an event from Coordinator                  | NULL             |
| 22 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
19 rows in set (0.00 sec)

半同步复制配置

几种不同复制的概念

异步复制：其为Mysql的默认模式，Master在执行完Client所提交的事务会将结果立即返回给Client，但是并不关心Slave是否接收并处理，如果此时Master宕机，主上已经提交的事务可能并没有传到Slave上，Slave提升为Master，数据也会可能不完整。

全同步复制：当Master接受到Client所提交的事务，会等Slave执行了该事务在把结果返回给Client，因此必定有大量的性能损失。

半同步复制（版本5.7）：介于异步复制和全同步复制之间，Master在执行了Client所提交的事务后不会立即返回给 Client，而是等待至少有一个Salve的relay log接受到bin log才返回给Client，大大提高了的数据的安全性。如果Slave响应超时（默认为10s）以后，半同步复制会切换为异步复制，在Slave恢复以后，会切换到半同步复制，并且数据会进行同步

配置

我们可以通过在SQL中配置，也可以通过配置my.cnf文件实现永久生效！！！

（1）通过SQL配置（临时生效）

<1> 加载Master插件（server1）

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

UNINSTALL PLUGIN rpl_semi_sync_master;
#取消加载插件

mysql> set global rpl_semi_sync_master_enabled=ON;
mysql> show global variables like '%semi%';
+-------------------------------------------+------------+
| 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 |
+-------------------------------------------+------------+

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

<2> 从节点配置

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

UNINSTALL PLUGIN rpl_semi_sync_slave;
#取消加载插件

mysql> set global rpl_semi_sync_slave_enabled=ON;
mysql> stop slave io_thread;     
mysql> start slave io_thread;
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

<2> 测试

###主服务器###
mysql> show global status like 'rpl_se%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |   #半同步连接数,如果Value=1证明主从半同步复制链接成功
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |       ###失败次数
| Rpl_semi_sync_master_status                | ON    |     ###连接状态
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 349   |
| Rpl_semi_sync_master_tx_wait_time          | 349   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |      ###成功次数
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

###从服务器###
mysql> show global status like 'rpl_semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

###从服务器###
mysql> stop slave io_thread;   #停掉slave的io线程
Query OK, 0 rows affected (0.01 sec)

###主服务器###
mysql> insert into user values (8,'xxxx');   #在主服务插入数据
Query OK, 1 row affected (10.00 sec)    #延迟10s以后插入成功


###从服务器###
mysql> select * from user;   从服务器中没有添加成功
+------+--------+
| id   | name   |
+------+--------+
|    1 | redhat |
+------+--------+
6 rows in set (0.00 sec)
mysql> show global status like '%rpl_se%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |  #连接数为0
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 3     |
| Rpl_semi_sync_master_no_times              | 3     |
| Rpl_semi_sync_master_no_tx                 | 3     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 628   |
| Rpl_semi_sync_master_tx_wait_time          | 1257  |
| 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     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

###从服务器###
mysql>  show global status like 'rpl_semi%'；
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |    ##半同步已经关闭
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> start slave io_thread;   #打开slave的io线程
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;   #查看数据添加成功
+------+--------+
| id   | name   |
+------+--------+
|    1 | redhat |
|    8 | xxxx   |
+------+--------+
7 rows in set (0.00 sec)

###主服务器###
mysql> show global status like '%rpl_se%';
+--------------------------------------------+-------+
| 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             | 5     |
| Rpl_semi_sync_master_no_times              | 3     |
| Rpl_semi_sync_master_no_tx                 | 3     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 538   |
| Rpl_semi_sync_master_tx_wait_time          | 1615  |
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

###从服务器###
mysql> show global status like 'rpl_se%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |    #半同步已经打开
+----------------------------+-------+
1 row in set (0.00 sec)

超时时间查询（Master）

mysql> show variables  like 'rpl_semi_sync_master_timeout';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.01 sec)

话多必失丶

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
mysql5.7主从复制原理及基本配置

mysql主从复制原理:传统的mysql复制就是主-从复制,它会有一个主,一个或多个从,在主节点提交与执行完事物之后,通过bin-log(2进制日志),将其(异步的)通过从节点上的IO线程发送到从节点上，并将bin-log日志存到从节点的readly-log(中继日志)中，通过sql线程以重新执行(在基于语句的复制中),或应用(在基于行的复制中),当从节点连上主节点后,会向主节点发送上次同步...
复制链接

扫一扫