mysql主从复制

weixin_30411997

于 2018-03-12 23:36:00 发布

阅读量43

点赞数

原文链接：http://www.cnblogs.com/vaon/p/8552834.html

版权

环境：

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
# mysql --version
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper

主：server2（172.25.254.2）

从：server3（172.25.254.3）

server-id方式主从同步

server2：

[root@server2 ~]# yum localinstall mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y

[root@server2 ~]# /etc/init.d/mysqld start

Initializing MySQL database: [ OK ] #初始化
Installing validate password plugin: [ OK ] #密码插件
Starting mysqld: [ OK ]

[root@server2 ~]# grep password /var/log/mysqld.log #查看生成的初始密码

2018-03-12T13:17:08.403316Z 1 [Note] A temporary password is generated for root@localhost: Eq,NuoCyb7,j
2018-03-12T13:21:14.337002Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.dkut6U.sql' started.
2018-03-12T13:21:14.630112Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.dkut6U.sql' ended.
2018-03-12T13:21:16.864796Z 0 [Note] Shutting down plugin 'sha256_password'
2018-03-12T13:21:16.864800Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-03-12T13:21:22.120741Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)

[root@server2 ~]# mysql_secure_installation #运行安全配置向导

Enter password for user root:

#输入初始密码Eq,NuoCyb7,j

New password:

Re-enter new password:

#创建新的root密码Vaon+123，再输入一次root新密码

#密码要求：不能少于8位，且要包含数字、大小写字母、特殊符号至少各一个

Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

#是否再次修改root密码no

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

#是否删除匿名用户y

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

#是否禁止root远程登陆y

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

#是否删除test数据库y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

#是否重新加载权限表y

[root@server2 ~]# vim /etc/my.cnf

server-id=1
log-bin=mysql-bin

[root@server2 ~]# /etc/init.d/mysqld restart

#查看二进制日志，可以由这些二进制日志恢复数据：

[root@server2 ~]# cd /var/lib/mysql

[root@server2 mysql]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180312 21:40:53 server id 1 end_log_pos 123 CRC32 0x4f8d039d Start: binlog v 4, server v 5.7.17-log created 180312 21:40:53 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ZYOmWg8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABlg6ZaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZ0DjU8=
'/*!*/;
# at 123
#180312 21:40:53 server id 1 end_log_pos 154 CRC32 0xe5363506 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

#root登陆数据库：

mysql> grant replication slave on *.* to vaon@'172.25.254.3' identified by 'Vaon+123';

#给vaon用户做备份的权限，密码Vaon+123

mysql> flush privileges; #刷新

mysql> show master status; #查看主节点master状态，记住这两个数据，备机同步会用到
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 600 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

server3：

与server2一方法样，首先安装软件，开启数据库，修改root密码为Vaon+123

[root@server3 ~]# vim /etc/my.cnf

server-id=2
[root@server3 ~]# /etc/init.d/mysqld restart

#测试一下vaon用户远程登陆server2主机数据库：

[root@server3 ~]# mysql -uvaon -p -h 172.25.254.2
Enter password: #输入Vaon+123

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

#root用户登陆本地数据库：

[root@server3 ~]# mysql -p
Enter password: #输入root密码Vaon+123

mysql> change master to MASTER_HOST='172.25.254.2',MASTER_USER='vaon',MASTER_PASSWORD='Vaon+123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=600;

mysql> flush privileges;

mysql> show slave status\G; #查看备机状态

Slave_IO_Running: No
Slave_SQL_Running: No

mysql> start slave; #开启备机服务

mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#查看相应的二进制文件：

[root@server3 ~]# cd /var/lib/mysql
[root@server3 mysql]# ls
auto.cnf ibdata1 mysqld_safe.pid relay-log.info
ca-key.pem ib_logfile0 mysql.sock server3-relay-bin.000001
ca.pem ib_logfile1 mysql.sock.lock server3-relay-bin.index
client-cert.pem ibtmp1 performance_schema server-cert.pem
client-key.pem master.info private_key.pem server-key.pem
ib_buffer_pool mysql public_key.pem sys
[root@server3 mysql]# cat master.info
25
mysql-bin.000001
600
172.25.254.2
vaon
Vaon+123
3306
60
0

0
30.000

86400

测试：

#在server2上创建数据库redhat，表usertb后，server3上查询有同样的表产生。主从复制成功

#无论在server2上增、删、改数据，server3上都会同步

server2：

mysql> create database redhat;

mysql> use redhat

mysql> create table usertb (username varchar(20) not null,password varchar(20) not null);

mysql> insert into usertb values ('user1','111');

server3：

gtid方式主从同步

相对与server-id方式，企业中更多会用gtid方式

GTID(GlobalTransaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

[root@server2 mysql]# cd /var/lib/mysql

[root@server2 mysql]# mysqlbinlog -v mysql-bin.000001 #-v可以将增删改库、表过程中的命令都显示出来

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180312 21:40:53 server id 1 end_log_pos 123 CRC32 0x4f8d039d Start: binlog v 4, server v 5.7.17-log created 180312 21:40:53 at startup

　　　　　　...

[root@server2 mysql]# mysqlbinlog --start-datetime="2018-03-12 21:40:53" --stop-datetime="2018-03-12 23:34:15" mysql-bin.000001 > test.sql

#将2018-03-12 21:40:53至2018-03-12 23:34:15之间产生的二进制日志倒到test.sql文件中

[root@server2 mysql]# mysql -p < test.sql
Enter password: #输入密码Vaon+123

#将2018-03-12 21:40:53至2018-03-12 23:34:15之间的数据恢复到数据库中

#以上只是测试，可以进一步理解主从复制的原理，恢复数据时要保证server2上的redhat库已经被drop掉，否则导入test.sql中的数据时会提示redhat库已存在，恢复失败

#在整个过程中server3一直在同步server2数据，做完这个实验两个主机的数据库还是一样的

server2:

[root@server2 mysql]# vim /etc/my.cnf

server-id=1
log-bin=mysql-bin

gtid_mode=ON
enforce-gtid-consistency=true

[root@server2 mysql]# /etc/init.d/mysqld restart

server3:

[root@server3 mysql]# vim /etc/my.cnf

server-id=2

gtid_mode=ON
enforce-gtid-consistency=true

[root@server3 mysql]# /etc/init.d/mysqld restart

mysql> stop slave;

#重新制定同步策略：

mysql> change master to MASTER_HOST='172.25.254.2',MASTER_USER='vaon',MASTER_PASSWORD='Vaon+123',MASTER_AUTO_POSITION=1;

mysql> start slave;

测试：

#在server2添加一条数据：

mysql> insert into redhat.usertb values ('user2','222');

#查看master状态，与前一种方法相比多出了这个gtid

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 | 420 | | | aa585820-25f7-11e8-b9a4-5254006e32c4:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

#在server3查看slave状态：

mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Retrieved_Gtid_Set: aa585820-25f7-11e8-b9a4-5254006e32c4:1
Executed_Gtid_Set: aa585820-25f7-11e8-b9a4-5254006e32c4:1

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| aa585820-25f7-11e8-b9a4-5254006e32c4 | 1 | 1 |
| aa585820-25f7-11e8-b9a4-5254006e32c4 | 2 | 2 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)

读写分离

调度端：server1（172.25.254.1）

读写端：server2（172.25.254.2）

只读端：server3（172.25.254.3）

server1本身不能安装数据库服务端软件，server1只时作为一个调度器使用，客户访问server1，server1将请求分发给server2或server3

server1：

tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/

mv /usr/local/mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

40 min_idle_connections = 1, #最少有一个客户端的时候执行读写分离
41 max_idle_connections = 2, #最多有两个客户端的时候执行读写分离

#编写调度器配置文件

mkdir /usr/local/mysql-proxy/etc

mkdir /usr/local/mysql-proxy/log

vim /usr/local/mysql-proxy/etc/mysql-proxy.conf

[mysql-proxy]
proxy-address=172.25.254.1:3306
proxy-read-only-backend-addresses=172.25.254.3:3306 #server3只读
proxy-backend-addresses=172.25.254.2:3306 #server2读写
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
daemon=true
user=root
log-level=debug
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
keepalive=true
admin-username=admin
admin-password=admin
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua

chmod 660 /usr/local/mysql-proxy/etc/mysql-proxy.conf

#启动服务：

/usr/local/mysql-proxy/bin/mysql-proxy --plugins=admin --plugins=proxy --defaults-file=/usr/local/mysql-proxy/etc/mysql-proxy.conf

#使用netstat -antlp查看到4041和3306端口表示服务已经启动：

server2:

在server2上创建proxy用户并给相应权限，由于server2和server3是主从复制，所以server3上会自动同步这些信息

mysql> grant select,update,insert on *.* to proxy@'172.25.254.%' identified by 'Vaon+123';

mysql> flush privileges;

#验证一下：

[root@server1 ~]# mysql -uadmin -padmin -h 172.25.254.1 -P 4041

#安装一个抓包工具：

[root@server1 ~]# yum install -y tcpdump

[root@server1 ~]# tcpdump -i eth0 port 3306 #监控eth0上的数据情况

#现在在宿主机上安装mysql客户端，用三个用户同时登陆数据库服务器，做写操作，看到tcpdump监控到是server2主机在起作用，然后在另外一个用户处做读操作，看到tcpdump监控到是server3主机起作用

宿主机：

[root@station Desktop]# mysql -h 172.25.254.1 -uproxy -pVaon+123

MySQL [redhat]> insert into redhat.usertb values('user5','555');

[vaon@station Desktop]# mysql -h 172.25.254.1 -uproxy -pVaon+123

MySQL [redhat]> select * from redhat.usertb;

[root@server1 ~]# tcpdump -i eth0 port 3306

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
10:41:32.994274 IP 172.25.254.100.37325 > server1.mysql: Flags [P.], seq 315167658:315167690, ack 993165180, win 123, options [nop,nop,TS val 1578429 ecr 1315487], length 32
10:41:32.994429 IP server1.55929 > server3.mysql: Flags [P.], seq 3396649331:3396649363, ack 4236304878, win 490, options [nop,nop,TS val 1326159 ecr 1307211], length 32
10:41:32.994826 IP server3.mysql > server1.55929: Flags [P.], seq 1:284, ack 32, win 453, options [nop,nop,TS val 1317884 ecr 1326159], length 283
10:41:32.994834 IP server1.55929 > server3.mysql: Flags [.], ack 284, win 524, options [nop,nop,TS val 1326159 ecr 1317884], length 0
10:41:32.994904 IP server1.mysql > 172.25.254.100.37325: Flags [P.], seq 1:284, ack 32, win 453, options [nop,nop,TS val 1326159 ecr 1578429], length 283
10:41:32.994995 IP 172.25.254.100.37325 > server1.mysql: Flags [.], ack 284, win 131, options [nop,nop,TS val 1578429 ecr 1326159], length 0
10:41:55.746089 IP 172.25.254.100.37323 > server1.mysql: Flags [P.], seq 1395554888:1395554933, ack 3989032060, win 182, options [nop,nop,TS val 1601180 ecr 933070], length 45
10:41:55.746111 IP server1.mysql > 172.25.254.100.37323: Flags [.], ack 45, win 453, options [nop,nop,TS val 1348911 ecr 1601180], length 0
10:41:55.746257 IP server1.44775 > server2.mysql: Flags [P.], seq 2503411261:2503411306, ack 3398932149, win 758, options [nop,nop,TS val 1348911 ecr 938581], length 45
10:41:55.785634 IP server2.mysql > server1.44775: Flags [.], ack 45, win 453, options [nop,nop,TS val 1343331 ecr 1348911], length 0
10:41:55.936492 IP server2.mysql > server1.44775: Flags [P.], seq 1:12, ack 45, win 453, options [nop,nop,TS val 1343481 ecr 1348911], length 11
10:41:55.936509 IP server1.44775 > server2.mysql: Flags [.], ack 12, win 758, options [nop,nop,TS val 1349101 ecr 1343481], length 0
10:41:55.936637 IP server1.mysql > 172.25.254.100.37323: Flags [P.], seq 1:12, ack 45, win 453, options [nop,nop,TS val 1349101 ecr 1601180], length 11
10:41:55.937144 IP 172.25.254.100.37323 > server1.mysql: Flags [.], ack 12, win 182, options [nop,nop,TS val 1601371 ecr 1349101], length 0
10:42:16.274042 IP 172.25.254.100.37325 > server1.mysql: Flags [P.], seq 32:64, ack 284, win 131, options [nop,nop,TS val 1621708 ecr 1326159], length 32
10:42:16.274206 IP server1.55929 > server3.mysql: Flags [P.], seq 32:64, ack 284, win 524, options [nop,nop,TS val 1369439 ecr 1317884], length 32
10:42:16.274557 IP server3.mysql > server1.55929: Flags [P.], seq 284:581, ack 64, win 453, options [nop,nop,TS val 1361163 ecr 1369439], length 297
10:42:16.274567 IP server1.55929 > server3.mysql: Flags [.], ack 581, win 557, options [nop,nop,TS val 1369439 ecr 1361163], length 0
10:42:16.274644 IP server1.mysql > 172.25.254.100.37325: Flags [P.], seq 284:581, ack 64, win 453, options [nop,nop,TS val 1369439 ecr 1621708], length 297
10:42:16.274706 IP 172.25.254.100.37325 > server1.mysql: Flags [.], ack 581, win 140, options [nop,nop,TS val 1621709 ecr 1369439], length 0
^C
20 packets captured
22 packets received by filter
0 packets dropped by kernel

#使用lsof工具查看：

yum install lsof -y

[root@server1 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 960 root 11u IPv4 8538 0t0 TCP server1:mysql (LISTEN)
mysql-pro 960 root 13u IPv4 8584 0t0 TCP server1:44775->server2:mysql (ESTABLISHED)
mysql-pro 960 root 14u IPv4 8879 0t0 TCP server1:mysql->172.25.254.100:37325 (ESTABLISHED)
mysql-pro 960 root 15u IPv4 8825 0t0 TCP server1:44782->server2:mysql (ESTABLISHED)
mysql-pro 960 root 16u IPv4 8880 0t0 TCP server1:55929->server3:mysql (ESTABLISHED)

[root@server1 ~]# mysql -uadmin -padmin -h 172.25.254.1 -P 4041

mysql> SELECT * FROM backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 172.25.254.2:3306 | up | rw | NULL | 0 |
| 2 | 172.25.254.3:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)

[root@server2 ~]# mysql -pVaon+123

[root@server3 ~]# mysql -pVaon+123

基于库的I/O多线程复制

1.多线程，增加SLAVE数据回滚能力

2.异步，I/O传输快，不需要SLAVE回复确认信号

半同步，增强数据一致性，可设置接收确认信号秒数，超过秒数恢复异步传输

server3：

[root@server3 ~]# vim /etc/my.cnf

server-id=2

gtid_mode=ON
enforce-gtid-consistency=true

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@server3 ~]# /etc/init.d/mysqld restart

[root@server3 ~]# mysql -pVaon+123

半同步复制

前面的复制方法都是异步复制，即master发送复制数据后不管slave是否接收到了这个数据

半同步复制是master发送复制数据后，slave返回一个已收到的ack信号后才会断开连接

#半同步复制mysql以插件形式提供，需要在master和slave上安装插件：

master（server2）：

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> show plugins;

slave（server3）：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> show plugins;

mysql> set global rpl_semi_sync_slave_enabled=on;
mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
测试：

slave：

mysql> stop slave; #关闭主从复制
master：

mysql> insert into db1.usertb values('chaoshi','chaoshi');

#这里会卡10s，10s过后主从复制会从半同步模式回退到异步模式

slave：

mysql> start slave; #开启slave，数据开始同步，并切换为半同步复制

master：

mysql> show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| 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              | 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      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
#Rpl_semi_sync_master_status表示主服务器使用是异步还是半同步复制

#Rpl_semi_sync_master_client表示从服务器有多少个配置成半同步复制

#Rpl_semi_sync_master_yes_tx表示从服务器确认成功提交的数量

#Rpl_semi_sync_master_no_tx表示从服务器确认失败提交的数量

slave：

mysql> select * from db1.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1        |
| user2    | 2        |
| user3    | 3        |
| user4    | 4        |
| user5    | 5        |
| user6    | 6        |
| user7    | 7        |
| chaoshi | chaoshi |
+----------+----------+

总结：

1.异步复制效率最高，技术最成熟，但数据完整性最差

2.半同步复制由于slave的确认机制保证了数据的完整性，但牺牲了部分传输效率

3.某些情况半同步复制依然不能保证数据完整性

延时复制

测试库：db1

测试表：usertb

slave:

mysql> stop slave;

mysql> change master to MASTER_DELAY=60; #将slave设置为60秒之后再复制master的数据

mysql> show slave status\G;

SQL_Delay: 60

#查看slave的二进制文件，可以看到二进制文件中已经记录了master的操作，60秒之后才会回滚到slave的数据库中：

mysql> insert into db1.usertb values('user14','14');
Query OK, 1 row affected (0.10 sec)
slave：

[root@server3 mysql]# pwd
/var/lib/mysql

[root@server3 mysql]# mysqlbinlog -v server3-relay-bin.000002
　　　　　　　　　　...

BINLOG '
jx2uWhMBAAAANQAAAJ0GAAAAAGwAAAAAAAEAA2RiMQAGdXNlcnRiAAIPDwQUABQAAz1DzjE=
jx2uWh4BAAAALgAAAMsGAAAAAGwAAAAAAAEAAgAC//wGdXNlcjE0AjE0dt4gTA==
'/*!*/;
### INSERT INTO `db1`.`usertb`
### SET
###   @1='user14'
###   @2='14'
# at 909
#180318 16:04:31 server id 1 end_log_pos 1770 CRC32 0x75f020f1    Xid = 42
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

#60秒之后查看slave数据库：

[root@server3 ~]# mysql -pVaon+123 -e "select * from db1.usertb;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1        |
| user2    | 2        |
| user3    | 3        |
| user4    | 4        |
| user5    | 5        |
| user6    | 6        |
| user7    | 7        |
| chaoshi | chaoshi |
| user8    | 8        |
| user9    | 9        |
| user10   | 10       |
| user11   | 11       |
| user12   | 12       |
| user13   | 13       |
| user14   | 14       |
+----------+----------+
组复制