浅谈MySQL8.0 异步复制的三种方式

本实验中分别针对空库、脱机、联机三种方式,配置一主两从的mysql标准异步复制。只做整服务器级别的复制,不考虑对个别库表或使用过滤复制的情况。

实验环境

[root@slave2 ~]# cat /etc/hosts
192.168.2.138 master
192.168.2.192 slave1
192.168.2.130 slave2
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16  |
+-----------+
1 row in set (0.00 sec)

一、空库

1.查看主库二进制信息

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

2.在主库上建立复制用户

mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication client,replication slave>
mysql> stop slave;
mysql> change master to master_host='192.168.2.138', master_port=3306, master_user='repl', master_password='wwwwww', master_log_file='mysql-bin.000004', master_log_pos=155;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G

刚才我们并没有在从库上建立repl用户,但由于create user语句是在起始位置点后执行的,因此可以正常复制到从库,查询mysql.user表即可确认。

sql> select * from mysql.user where user='repl'\G

二、脱机

如果数据库已经存在应用数据,但允许一个可接受的脱机时间窗口做复制,这种场景下常用的做法是先直接将主库的数据目录整体拷贝到从库,再启动复制。具体步骤如下。

1.在master节点创建测试库和测试表

CREATE DATABASE test;
Query OK, 1 row affected (0.04 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t(id int(10));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (111);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO t VALUES (222);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t VALUES (333);
Query OK, 1 row affected (0.00 sec)

2.在主库创建复制用户

mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication client,replication slave>
[root@master ~]# ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin
[root@master ~]# mysqladmin -hlocalhost -uroot -pwwwwww shutdown

4.复制数据至slave1、slave2

[root@master data]# cd /data
[root@master data]# scp -r mysql/ slave1:/data/
[root@master data]# scp -r mysql/ slave2:/data/

5.在slave1、slave2从库执行命令,删除auto.cnf文件

[root@slave1 mysql]# cd /data/mysql
[root@slave1 mysql]# rm -rf auto.cnf
[root@slave2 mysql]# cd /data/mysql
[root@slave2 mysql]# rm -rf auto.cnf
 

6.重启实例,在三个节点都需要执行

[root@master data]# service mysqld start
Starting MySQL.. SUCCESS!

7.在主库查看二进制日志

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

8.在slave1、slave2从库执行命令

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='192.168.2.138', master_port=3306, master_user='repl', master_password='wwwwww', master_log_file='mysql-bin.000005', master_log_pos=155;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G

9.在slave1、slave2从库执行命令查看库和表是否同步过来

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 |
+----------------+
| t       |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| id  |
+------+
| 111 |
| 222 |
| 333 |
+------+
3 rows in set (0.00 sec)

浅谈MySQL8.0 异步复制的三种方式

扫一扫手机访问