Mysql8.0.23+ 主从复制

1、软件环境

环境名称	主机名称	Mysql版本	IP	OS 版本
主库	master	Mysql8.0.30	172.16.134.24	Centos 7.2
从库	Slave01	Mysql8.0.30	172.16.134.25	Centos 7.2
级联从库	Slave02	Mysql8.0.30	172.16.134.26	Centos 7.2

2、主库安装配置

2.1主库配置文件

   [root@master ~]# vi /etc/mysql3308/my.cnf

[mysqld] port = 3308 mysqlx_port=33080 socket = /u01/mysql3308/data/mysql.sock mysqlx_socket = /u01/mysql3308/data/mysqlx.sock basedir = /u01/mysql3308/mysql datadir = /u01/mysql3308/data pid-file = /u01/mysql3308/data/mysql.pid tmpdir = /u01/mysql3308/tmp ### log server_id=1 log_error = /u01/mysql3308/logs/error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /u01/mysql3308/logs/slow.log log_timestamps = SYSTEM log_bin = /u01/mysql3308/binlog/mysql-bin binlog_format = row binlog_cache_size = 16M binlog_row_image = full binlog_rows_query_log_events = 1

2.2初始化及启动

初始化:如果不是默认的路径，请使用初始参数 [root@master ~]# mysqld --defaults-file=/u01/mysql3308/my.cnf  --initialize --user=mysql [root@master ~]# mysqld_safe --defaults-file=/u01/mysql3308/my.cnf &

2.3创建复制用户

mysql8 授权用户必须先创建，创建和授权不能使用同一语句 mysql> create user repl@'%' identified with mysql_native_password by 'rep1123'; mysql> grant replication slave on *.* to repl@'%'; mysql> flush privileges;

3、从库搭建

从库搭建方法有以下多种方法，可自由选择：

1. 使用mydumper导出主库数据，然后进行导入到从库
2. 使用FTWL锁库/停库，获得主库位点，然后copy数据文件至从库
3. 使用xtrabackup进行数据库备份，然后在从库还原
4. 使用MySQL 8.0的clone特性将实例克隆到从库

 这里采用xtrabackup进行数据库的备份及恢复

3.1主库执行备份

[root@master ~]# xtrabackup --defaults-file=/u01/mysql3308/my.cnf    --user=xtrabk --password=xtrabk  --socket=/u01/mysql3308/data/mysql.sock --backup --target-dir=/u01/mysql3308/backup/full/

3.2从库基础环境搭建

3.2.1 mysql soft 复制

打包mysql软件并复制到从库解压，删除数据目录、binglog、logs文件

3.2.2从库配置文件

 [root@slave01 ~]# vi /etc/mysql3309/my.cnf

编辑从库my.cnf 文件，替换为从库的路径 server_id=2 relay_log=/u01/mysql3309/relaylog/mysql-relay-bin

3.2.3 从库恢复

准备：应用redo，类似于之前的apply [root@slave01 ~]# xtrabackup --defaults-file=/u01/mysql3309/my.cnf --prepare --target-dir=/u01/mysql3308/backup/full_xtra 恢复： [root@slave01 ~]# xtrabackup --defaults-file=/u01/mysql3309/my.cnf --copy-back --target-dir=/u01/mysql3308/backup/full_xtra 恢复完成后，更改data、binlog目录权限

3.3 启动从库

[root@slave01 ~]# Mysqld_safe –defaults-file=/u01/mysql3309/my.cnf &

3.4 获取二进制文件及位置

[root@master ~]# cat xtrabackup_info binlog_pos = filename 'mysql-bin.000009', position '157'

3.5 添加指向主库相关配置信息

Mysql>change replication source to   source_host='172.16.134.24',   source_port=3308,   source_user='repl',   source_password='rep1123',   source_log_file='mysql-bin.000009',   source_log_pos=157;

3.6 开启从库复制

Mysql>start replica;

3.7 查看复制状态

mysql> show replica status \G *************************** 1. row ***************************              Replica_IO_State: Waiting for source to send event                   Source_Host: 172.16.134.24                   Source_User: repl                   Source_Port: 3308                 Connect_Retry: 60               Source_Log_File: mysql-bin.000009           Read_Source_Log_Pos: 2909                Relay_Log_File: mysql-relay-bin.000002                 Relay_Log_Pos: 3078         Relay_Source_Log_File: mysql-bin.000009            Replica_IO_Running: Yes           Replica_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_Source_Log_Pos: 2909               Relay_Log_Space: 3288               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Source_SSL_Allowed: No            Source_SSL_CA_File:            Source_SSL_CA_Path:               Source_SSL_Cert:             Source_SSL_Cipher:                Source_SSL_Key:         Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Source_Server_Id: 1                   Source_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0              Source_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL     Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates            Source_Retry_Count: 86400                   Source_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Source_SSL_Crl:            Source_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0          Replicate_Rewrite_DB:                  Channel_Name:            Source_TLS_Version:        Source_public_key_path:         Get_Source_public_key: 0             Network_Namespace: 1 row in set (0.00 sec)

3.8 验证复制状态

主库创建及更新表

mysql> select * from test; +------+------+ | id   | name | +------+------+ |    1 | wang | |    2 | lei  | +------+------+ 2 rows in set (0.00 sec) mysql> create table test_01 select * from test; Query OK, 2 rows affected (0.48 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> select * from test_01; +------+------+ | id   | name | +------+------+ |    1 | wang | |    2 | lei  | +------+------+ 2 rows in set (0.00 sec) mysql> insert into test_01 values(3,'jk'); Query OK, 1 row affected (0.15 sec) mysql> select * from test_01; +------+------+ | id   | name | +------+------+ |    1 | wang | |    2 | lei  | |    3 | jk   | +------+------+ 3 rows in set (0.00 sec) mysql>

从库查询并验证

mysql> select * from test_01; +------+------+ | id   | name | +------+------+ |    1 | wang | |    2 | lei  | |    3 | jk   | +------+------+ 3 rows in set (0.00 sec) mysql>

至此，一个简单的主从环境搭建完成，需要注意的时MySQL 8.0.23前后复制命令的差别。

4、级联从库搭建

在上面的基础上搭建级联从库

4.1 从库配置文件

[root@slave01 ~]# vi /etc/mysql3309/my.cnf

log_slave_updates = 1

重启从库

停止SQL_THREAD 线程(继续接受二进制文件，但不应用)

Mysql>stop slave sql_thread;

4.2 级联库恢复

准备：应用redo，类似于之前的apply [root@slave02 ~]# xtrabackup --defaults-file=/u01/mysql3309/my.cnf --prepare --target-dir=/u01/mysql3309/backup/full_xtra 恢复： [root@slave02 ~]# xtrabackup --defaults-file=/u01/mysql3309/my.cnf --copy-back --target-dir=/u01/mysql3309/backup/full_xtra 恢复完成后，更改data、binlog目录权限

4.3 启动从库

[root@slave02 ~]# Mysqld_safe –defaults-file=/u01/mysql3309/my.cnf &

4.4 获取二进制文件及位置

[root@slave01 ~]#cat xtrabackup_info binlog_pos = filename 'mysql-bin.000013', position '157'

4.5 添加指向主库相关配置信息

在slave02上配置指向slave01

Mysql>change replication source to   source_host='172.16.134.25',   source_port=3309,   source_user='repl',   source_password='rep1123',   source_log_file='mysql-bin.000013',   source_log_pos=157;

4.6 开启从库SQL应用

在slave01上开启sql应用

Mysql>start slave sql_thread;

4.7 开启级联从库复制

在slave02上开启复制

Mysql>start replica;

5、级联半同步搭建

在上面的基础上改造半同步建级联

半同步的两种模式

1. after_commit

提交流程:client-->execute sql-->wrtie redolog-->write binlog-->innodb storage commit-->wait ACK-->client receive OK

2.after_sync

提交流程:client-->execute sql-->wrtie redolog-->write binlog-->wait ACK-->innodb storage commit-->client receive OK。

从提交流程可以看出，两个模式的区别就是提交到存储引擎与等待从库的ACK的顺序。

after_commit模式先提交到存储引擎，那么主库上事务相当于已经完成了，虽然没有等到从库的ACK，没有给客户端以事务成功的反馈，但是在连接主库的其他客户端上是能查询到主库修改的数据，此时发生故障，从库切主库时相当于丢失了数据。也就是说after_commit无法保证主从数据的强一致性。

after_sync模式是主库先要等到从库的ACK，然后再提交到存储引擎，在提交到存储引擎前，主从上都查不到事务的修改，此时发生故障切换，不算丢失数据，因为故障前的事务尚未完成。也就是说after_sync模式可以保证主从的强一致性。

因此推荐使用after_sync模式。

5.1 安装半同步插件

Master

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

Slave01

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

Slave02

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

5.2 主库配置半同步

临时开启 set global rpl_semi_sync_master_enabled=1; set global rpl_semi_sync_master_timeout=3000;  3秒 永久开启 修改master my.cnf rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=3000

mysql> show global variables like '%semi%';

+-------------------------------------------+------------+

| Variable_name                             | Value      |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled              | ON         |

| rpl_semi_sync_master_timeout              | 3000       |

| 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 |

+-------------------------------------------+------------+

6 rows in set (0.02 sec)

5.3 从库salve01配置半同步

注意：salve01 作为级联的中转库，需要开启半同步的主、从参数

临时开启 set global rpl_semi_sync_master_enabled=1; set global rpl_semi_sync_master_timeout=3000;  3秒 set global rpl_semi_sync_slave_enabled =1; 永久开启 修改salve01 my.cnf rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=3000 rpl_semi_sync_slave_enabled=1

mysql> show global variables like '%semi%';

+-------------------------------------------+------------+

| Variable_name                             | Value      |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled              | ON         |

| rpl_semi_sync_master_timeout              | 3000       |

| 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_semi_sync_slave_enabled               | ON         |

| rpl_semi_sync_slave_trace_level             | 32         |

+-------------------------------------------+------------+

8 rows in set (0.01 sec)

5.4 从库salve02配置半同步

临时开启 set global rpl_semi_sync_slave_enabled =1; 永久开启 修改salve21 my.cnf rpl_semi_sync_slave_enabled=1

mysql> show global variables like '%semi%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled      | ON    |

| rpl_semi_sync_slave_trace_level   | 32    |

+---------------------------------+-------+

2 rows in set (0.00 sec)

5.5 从库开启半同步

Salve01:

Mysql>stop replica; Mysql>start replica;

Salve02：

Mysql>stop replica; Mysql>start replica;:

查看半同步状态：

Master：

mysql> show global  status like '%semi%'; +--------------------------------------------+-------+ | 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             | 2     | | 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      | 203   | | Rpl_semi_sync_master_tx_wait_time          | 406   | | 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)

Slave01：

mysql> show global  status like '%semi%'; +--------------------------------------------+-------+ | 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             | 1     | | Rpl_semi_sync_master_no_times              | 0     | | Rpl_semi_sync_master_no_tx                 | 0     | | Rpl_semi_sync_master_status                | ON    | | Rpl_semi_sync_master_timefunc_failures     | 0     | | Rpl_semi_sync_master_tx_avg_wait_time      | 233   | | Rpl_semi_sync_master_tx_wait_time          | 233   | | 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     | | Rpl_semi_sync_slave_status                 | ON    | +--------------------------------------------+-------+

Slave02:

mysql> show global  status like '%semi%'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON    |

6、GTID主从复制