本实验中分别针对空库、脱机、联机三种方式,配置一主两从的mysql标准异步复制。只做整服务器级别的复制,不考虑对个别库表或使用过滤复制的情况。
1.实验环境
[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)
一、空库
- 查看主库二进制信息
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 on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.在从库创建主库信息。
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表即可确认。
mysql> select * from mysql.user where user='repl'/G
二.脱机
如果数据库已经存在应用数据,但允许一个可接受的脱机时间窗口做复制,这种场景下常用的做法是先直接将主库的数据目录整体拷贝到从库,再启动复制。具体步骤如下。
1.在master节点创建测试库和测试表
mysql> 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 on *.* to 'repl'@