mysql主从复制、基于GTID的主从、半同步

最新推荐文章于 2025-08-06 11:07:19 发布

原创最新推荐文章于 2025-08-06 11:07:19 发布 · 282 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#linux #mysql

使用的mysql版本5.7.17

一、主从复制

原理：

主从复制一共有三个进程，从库生成两个线程，一个I/O线程，一个SQL线程；
i/o线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致

实验环境：

server1：master

server2：slave

使用pos号实现主从复制

实验操作：

server1
安装所需要的安装包

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

server2
安装所需要的安装包

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

配置master

[root@server1 ~]# vim /etc/my.cnf 
[root@server1 ~]# cat /etc/my.cnf | tail -n 2
server-id=1    
//这个server-id可以任意，但是必须是唯一的
//这里server1的设为1, sevrre2的设为2
log-bin=mysql-bin   //日志名称
[root@server1 ~]# /etc/init.d/mysqld start   //mysql的开启
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server1 ~]# grep password /var/log/mysqld.log   
//过滤查看mysql的初始密码(其他过滤出来的内容就不显示了)
2018-08-08T08:51:19.321424Z 1 [Note] A temporary password is generated for root@localhost: u/Klif/g3sFi
[root@server1 ~]# mysql_secure_installation  //进行初始化

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password:  //输入用户密码8位以上，并且是字母大小写+特殊字符+数字

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 
[root@server1 ~]# mysql -p
Enter password:   //新密码登陆即可
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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.54.%' identified by '5820hhXM!@#';  
//创建同步帐户,并给予权限
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      843 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

server2登陆测试

[root@server2 ~]# mysql -u repl -p -h 172.25.54.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

配置slave

[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# cat /etc/my.cnf | tail -n 1
server-id=2   //只需服务id即可
[root@server2 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@server2 ~]# grep password /var/log/mysqld.log 
2018-08-08T08:52:10.646512Z 1 [Note] A temporary password is generated for root@localhost: Ddp=y*gOr0uq
[root@server2 ~]# mysql_secure_installation //初始化过程同server1
[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> change master to master_host='172.25.54.1',master_user='repl',master_password='5820hhXM!@#',master_log_file='mysql-bin.000003',master_log_pos=843;
//与master建立认证, 所有信息需要与master的一样
//master主机ip,被授权的用户,密码,日志文件,pos号
Query OK, 0 rows affected, 2 warnings (0.99 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.54.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 843
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes  //yes代表I/O线程没问题
            Slave_SQL_Running: Yes  //yes代表SOL线程没问题
              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: 843
              Relay_Log_Space: 529
              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: 37ab8283-9ae8-11e8-ba69-525400d23507
             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)

mysql>

测试数据同步

在master上进行数据库操作

mysql> create database test;
Query OK, 1 row affected (0.14 sec)

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

mysql> use test;
Database changed
mysql> create table userlist (
    -> username varchar(15) not null,
    -> password varchar(25) not null);
Query OK, 0 rows affected (0.66 sec)

mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(25) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into userlist values ('user1','147');
Query OK, 1 row affected (0.07 sec)

mysql> insert into userlist values ('user2','258');
Query OK, 1 row affected (0.10 sec)

mysql> insert into userlist values ('user3','369');
Query OK, 1 row affected (0.06 sec)

mysql> quit
Bye
[root@server1 ~]#

在slave上边验证

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

mysql> use test;
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 userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
+----------+----------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@server2 ~]#

基于gtid实现主从复制
配置master

[root@server1 ~]# vim /etc/my.cnf
[root@server1 ~]# cat /etc/my.cnf | tail -n 4
server-id=1
log-bin=mysql-bin
gtid_mode=ON   //开启GTID
enforce-gtid-consistency=true
[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server1 ~]#

配置slave

[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# cat /etc/my.cnf | tail -n 3
server-id=2
gtid_mode=ON
enforce-gtid-consistency=true
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop slave;  //停止slave工作
Query OK, 0 rows affected (0.03 sec)

mysql> change master to master_host='172.25.54.1',master_user='repl',master_password='5820hhXM!@#',MASTER_AUTO_POSITION = 1;
//使用gtid重新与master建立认证
Query OK, 0 rows affected, 2 warnings (0.52 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: 172.25.54.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        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: 154
              Relay_Log_Space: 576
              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: 37ab8283-9ae8-11e8-ba69-525400d23507
             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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql>

测试

在master上进行数据库操作

mysql> use test;
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_test |
+----------------+
| userlist       |
+----------------+
1 row in set (0.00 sec)

mysql> delete from userlist where username='user3'; 
Query OK, 1 row affected (0.12 sec)

mysql> Bye
[root@server1 ~]#

在slave上边验证

mysql> use test;
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 userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> Bye
[root@server2 ~]#

二、半同步复制——基于GTID

简介

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接收完毕，这一过程，也就意味着有可能出现当主服务器或从服务器端发生故障的时候，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失
为了解决上述可能发生的错误，MySQL 5.5 引入了一种半同步复制模式。该模式可以确保从服务器接收完主服务器发送的binlog日志文件并写入到自己的中继日志relay log里，然后会给主服务器一个反馈，告诉主服务器已经接收完毕，这时主服务线程才返回给当前session告知操作完成。
当出现超时情况是，主服务器会暂时切换到异步复制模式，直到至少有一个从服务器从及时收到信息为止
中继日志的自我修复:
从MySQL 5.5.X 版本开始，增加了relay_log_recovery参数，这个参数的作用是：当slave从库宕机后，假如relay.log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log,并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为1时，可在slave从库上开启该功能，建议开启
半同步复制与异步复制的切换：
半同步复制的工作原理就是当slave从库IO_Thread线程将binlog日志接收完毕之后，要给master主库一个确认，如果rpl_semi_sync_master_timeout=10000 (10秒)超过10秒未收到slave从库的接受确认信号，那么就会自动切换为传统的异步复制模式

注意：
半同步复制模式必须在主服务器和从服务器端同时开启，否则主服务器默认使用异步复制模式

实验环境：

server1：master

server2：slave

实验在主从复制实现了的基础上进行

实验操作：

master、slave
mysql> show variables like ‘have_dynamic_loading’;
确保value为YES
master 安装插件

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.20 sec)

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

这里写图片描述

slave 安装插件

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; 
Query OK, 0 rows affected (0.05 sec)

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

这里写图片描述

测试

首先在master上查看以下参数：

这里写图片描述

然后在slave上关闭io_thread

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

接着在master上执行数据库操作，这里是对test库的表进行插入操作

此时插入数据会有一个10s的timeout，会有卡顿，查看参数，发现有些数值发生了变化

此时在slave上开启io_thread，并查看数据库，发现数据同步了

这里写图片描述

再进行一次测试

slave

这里写图片描述

master

这里写图片描述

slave

这里写图片描述

mysql主从复制、基于GTID的主从、半同步

使用的mysql版本5.7.17

一、主从复制

原理：

实验环境：

server1：master

server2：slave

使用pos号实现主从复制

实验操作：

server2登陆测试

测试数据同步

在master上进行数据库操作

在slave上边验证

基于gtid实现主从复制

测试

在master上进行数据库操作

在slave上边验证

二、半同步复制——基于GTID

简介

实验环境：

server1：master

server2：slave

实验在主从复制实现了的基础上进行

实验操作：

测试

首先在master上查看以下参数：

然后在slave上关闭io_thread

接着在master上执行数据库操作，这里是对test库的表进行插入操作

此时在slave上开启io_thread，并查看数据库，发现数据同步了

再进行一次测试

slave

master

slave