一、 需求

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

3.环境准备

二、异步复制

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