环境:
# 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
0
86400
0
测试:
#在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:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| redhat |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from redhat.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
+----------+----------+
1 row in set (0.00 sec)
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)
mysql> select * from redhat.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
+----------+----------+
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
mysql> show processlist;
+----+-------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
| 4 | vaon | server3:34560 | NULL | Binlog Dump GTID | 2609 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 6 | proxy | server1:44775 | redhat | Sleep | 1004 | | NULL |
| 9 | proxy | server1:44782 | redhat | Sleep | 1398 | | NULL |
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
[root@server3 ~]# mysql -pVaon+123
mysql> show processlist;
+----+-------------+---------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+---------------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 1026 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 2632 | Waiting for master to send event | NULL |
| 8 | proxy | server1:55929 | NULL | Sleep | 1006 | | NULL |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+---------------+------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
基于库的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
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 10 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 12 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 20 | system user | | NULL | Connect | 12 | Waiting for an event from Coordinator | NULL |
| 22 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
19 rows in set (0.00 sec)
mysql> select * from mysql.slave_master_info;
+-----------------+------------------+----------------+--------------+-----------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+--------------+-------------+
| Number_of_lines | Master_log_name | Master_log_pos | Host | User_name | User_password | Port | Connect_retry | Enabled_ssl | Ssl_ca | Ssl_capath | Ssl_cert | Ssl_cipher | Ssl_key | Ssl_verify_server_cert | Heartbeat | Bind | Ignored_server_ids | Uuid | Retry_count | Ssl_crl | Ssl_crlpath | Enabled_auto_position | Channel_name | Tls_version |
+-----------------+------------------+----------------+--------------+-----------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+--------------+-------------+
| 25 | mysql-bin.000004 | 2098 | 172.25.254.2 | vaon | Vaon+123 | 3306 | 60 | 0 | | | | | | 0 | 30 | | 0 | aa585820-25f7-11e8-b9a4-5254006e32c4 | 86400 | | | 1 | | |
+-----------------+------------------+----------------+--------------+-----------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+--------------+-------------+
1 row in set (0.00 sec)
半同步复制
前面的复制方法都是异步复制,即master发送复制数据后不管slave是否接收到了这个数据
半同步复制是master发送复制数据后,slave返回一个已收到的ack信号后才会断开连接
#半同步复制mysql以插件形式提供,需要在master和slave上安装插件:
master(server2):
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> show plugins;
mysql> show variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| 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_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
#开启半同步复制:
mysql> set global rpl_semi_sync_master_enabled=on;
mysql> show variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON | #表示开启半同步复制
| rpl_semi_sync_master_timeout | 10000 | #默认1000毫秒,即10秒超时,将切换为异步复制,可修改(set global ...=毫秒)
| rpl_semi_sync_master_trace_level | 32 | #表示用于开启半同步复制时的调试级别,默认32
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON | #表示是否允许master每个事务都要等待slave接收确认,默认为ON
| rpl_semi_sync_master_wait_point | AFTER_SYNC | #有AFTER_SYNC和AFTER_COMMIT两种模式,这里采用AFTER_SYNC
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
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> select * from mysql.slave_relay_log_info;
+-----------------+----------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+
| Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name |
+-----------------+----------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+
| 7 | ./server3-relay-bin.000002 | 940 | mysql-bin.000005 | 1770 | 60 | 4 | 1 | |
+-----------------+----------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+
1 row in set (0.00 sec)master:
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 |
+----------+----------+组复制