MYSQL的主从复制、gtid的主从复制和半同步复制

一、mysql的主从复制

• 配置主数据库：
  1.在主库中解压mysql安装包

[root@server1 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@server1 ~]# ls

2.删除没有用的包

[root@server1 ~]# rm -fr mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server1 ~]# rm -fr mysql-community-devel-5.7.24-1.el7.x86_64.rpm mysql-community-embedded-5.7.24-1.el7.x86_64.rpm mysql-community-embedded-compat-5.7.24-1.el7.x86_64.rpm mysql-community-embedded-devel-5.7.24-1.el7.x86_64.rpm mysql-community-minimal-debuginfo-5.7.24-1.el7.x86_64.rpm mysql-community-server-minimal-5.7.24-1.el7.x86_64.rpm mysql-community-test-5.7.24-1.el7.x86_64.rpm
[root@server1 ~]# ls

3.在主库中安装mysql

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

4.把主库中安装mysql的包发送给从库

[root@server1 ~]# scp mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm root@172.25.21.2：/root/

5.开启主数据库

[root@server1 ~]# systemctl start mysqld

6.查看数据库密码

[root@server1 ~]# grep password /var/log/mysqld.log

7.进行安全初始化，修改密码
注意：密码必须由数字，字母大小写和特殊字符构成，三者不可缺一

[root@server1 ~]# mysql_secure_installation

8.编辑配置文件

[root@server1 ~]# vim /etc/my.cnf
 28 log-bin=mysql-bin
 29 server-id=1

9.重启数据库服务

[root@server1 ~]# systemctl restart mysqld

10.创建用户并授权

查看二进制日至是否开启

查看主库的状态

• 配置从数据库：
  1.安装mysql

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

2.编辑配置文件

[root@server2 ~]# vim /etc/my.cnf
server-id=2

3.开启数据库服务

[root@server2 ~]# systemctl start mysqld

4.查看数据库密码

[root@server2 ~]# grep password /var/log/mysqld.log

5.进行安全初始化，修改密码
注意：密码必须由数字，字母大小写和特殊字符构成，三者不可缺一

[root@server2 ~]# mysql_secure_installation

6.查看是否能够登陆，登陆成功表示授权成功

[root@server2 ~]# mysql -h 172.25.21.1 -uyun -pLYly12345@

7.设定从设备并开启从设备

注意master_log_file和master_log_pos的值要依照主设备状态的值来设定，因为会变化

开启slave

查看slave状态

8.测试：
在主数据库中创建库，创建表，并插入数据

在从库中查看是否复制到主库新建的库和信息，可以查看代表主从复制成功


二、GTDI的主从复制
1.编辑主数据库的配置文件

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

gtid_mode=ON
enforce-gtid-consistency=true

2.重启主数据库服务

[root@server1 ~]# systemctl restart mysqld

3.编辑从数据库的配置文件

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

4.重启从数据库服务

[root@server2 ~]# systemctl restart mysqld

5.在主数据库中给表新插入数据

从数据库可以查看到gtid


在gtid的表中可以查看到刚刚新建的用户信息

6.在从库中先停掉slave，重新创建链接，再开启slave

7.测试：在主库表中添加新的信息

从库中查看是否复制成功

三、半同步复制

1.在主库中安装服务插件，并且开启半同步复制

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

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

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> show variables like '%rpl%';
+-------------------------------------------+------------+
| 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_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)

3.在从库也安装服务插件，并且开启半同步复制

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

4.重启从库上的IO线程，如果没有重启，依然是默认的异步复制，重启后，slave会在master上注册为半同步复制slave角色

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

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

5.在从库中查看是否开启半同步复制

mysql> show status like '%rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
3 rows in set (0.01 sec)

测试一：半同步复制失败
1.先关闭从端的IO线程

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

2.在主库表中插入数据，会等待从库回应，从库没有回应，会等待10秒，10秒后会变成异步复制

mysql> use westos
Database changed
mysql> insert into usertb values('user4','123');
Query OK, 1 row affected (10.00 sec)

3.在主库发现半同步复制失败次数为1

（1）Rpl_semi_sync_master_no_tx表示没有成功接收slave的次数，即半同步失败的次数
（2）Rpl_semi_sync_master_yes_tx表示半成功复制成功的次数

mysql> show status like '%rpl%';                                         +--------------------------------------------+-------+
| 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              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | OFF   |
| 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.00 sec)

4.在从数据库查看没有同步过来

mysql> use westos;
Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
+----------+----------+
3 rows in set (0.00 sec)

实验二、半同步复制成功

1.从数据库打开IO

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

2.主数据表插入新的数据

mysql> insert into usertb values('user5','123');
Query OK, 1 row affected (0.01 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
| user5    | 123      |
+----------+----------+
5 rows in set (0.00 sec)

3.从数据库可以查看到user5的信息，即半同步复制成功

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
| user5    | 123      |
+----------+----------+
5 rows in set (0.00 sec)

4.查看进程信息

mysql> show processlist;
+----+------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host          | db     | Command          | Time | State                                                         | Info             |
+----+------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
|  5 | root | localhost     | westos | Query            |    0 | starting                                                      | show processlist |
|  7 | yun  | server2:42320 | NULL   | Binlog Dump GTID |   38 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)