一、 需求

1.准备6台数据库
2.一台主库，一台从库，一台延时复制，一台半同步复制，一台过滤复制，一台基于GTID的主从复制

3.环境准备

主机	外网ip	内网ip	主从
db01	10.0.0.51	172.16.1.51	mysql主库
db02	10.0.0.52	172.16.1.52	mysql从库
db03	10.0.0.53	172.16.1.53	mysql延时复制
db04	10.0.0.54	172.16.1.54	mysql半同步复制
db05	10.0.0.55	172.16.1.55	mysql过滤复制
db06	10.0.0.56	172.16.1.56	GTID主从复制

二、异步复制

1.mysql主库配置

#编辑主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin

#查看binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+-------------------------------+
| mysql-bin.000008 |      120 |              |                  | 0e2277b0-1e92-11eb-b4de-000c29577624:1-2 |
+------------------+----------+--------------+------------------+-------------------------------+
1 row in set (0.00 sec)

#授权用户
mysql> grant replication slave on *.* to zzc@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (1.00 sec)

2.从库配置

#从库配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

[root@db02 ~]# mysql
mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='zzc',
    -> master_password='123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000008',
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.09 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: 172.16.1.51
                  Master_User: zzc
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 120
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

三、延时复制

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=3

[root@db03 ~]# systemctl restart mysqld

[root@db03 ~]# mysql
mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='zzc',
    -> master_password='123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000008',
    -> master_log_pos=120,
    -> master_delay=3600;      #延时辅助只需多加这条内容

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: zzc
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 120
               Relay_Log_File: db03-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
------------------------------- 省略 ---------------------------------
					SQL_Delay: 3600
          SQL_Remaining_Delay: NULL

四、半同步复制

#半同步复制基于异步复制而来

#修改从库配置文件
[root@db04 ~]# vim /etc/my.cnf
[mysqld]
server_id=4
rpl_semi_sync_slave_enabled =1

#先确认主从的MySQL服务器是否支持动态增加插件
mysql> show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)

#分别在主从库上安装对用插件
-- 主库安装插件
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 = 1;
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> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.20 sec)

#编辑主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
rpl_semi_sync_master_enabled=1         
rpl_semi_sync_master_timeout=1000

#从库开启半同步复制
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.07 sec)
#重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;

#查看binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+-------------------------------+
| mysql-bin.000009 |      120 |              |                  | 0e2277b0-1e92-11eb-b4de-000c29577624:1-2 |
+------------------+----------+--------------+------------------+-------------------------------+
1 row in set (0.00 sec)


[root@db04 ~]# mysql
mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='zzc',
    -> master_password='123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=120;

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


-- 主库查看
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

-- 从库查看
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
#这两个变量常用来监控主从是否运行在半同步复制模式下。

五、GTID的主从复制

#主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin

rpl_semi_sync_master_enabled=1         
rpl_semi_sync_master_timeout=1000

log-slave-updates
gtid_mode=on
enforce_gtid_consistency=on

#从库1
[root@db56 ~]# vim /etc/my.cnf
[mysqld]
server_id=6
log_bin=mysql-bin
log-slave-updates
gtid_mode=on
enforce_gtid_consistency=on


mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='zzc',
    -> master_password='123',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=120;
    -> master_auto_position=1;

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 151
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes