mysql主从复制

【MySQL】主从复制 MySQL是一款关系型数据库,被广泛应用于各种互联网应用中。随着业务的不断扩大,数据量不断增加,如何让MySQL具有更好的可用性和容灾能力成为了越来越重要的问题。本文将重点介绍MySQL主从复制和高可用性的实现,旨在为MySQL的使用者提供指导和帮助。本篇博客介绍了MySQL高可用性的常见解决方案,包括负载均衡、主从复制、主主复制以及结合MHA和Galera Cluster的整合方案。高可用性对于数据库系统至关重要,它可以确保用户的请求始终得以满足,并且降低系统停机时间的风险。 阅读详情

环境:

# 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       |
+----------+----------+组复制

MySQL主从复制 搭建MySQL主从复制经典架构 一、业务发展驱动数据发展   随着网站业务的不断发展,用户量的不断增加,数据量成倍地增长,数据库的访问量也呈线性地增长。特别是在用户访问高峰期间,并发访问量突然增大,数据库的负载压力也会增大,如果架构方案不够健壮,那么数据库服务器很有可能在高并发访问负载压力下宕机,造成数据访问服务的失效,从而导致网站的业务中断,给 阅读详情

相关推荐

秦时明月6.2魔改版_从零到一部署指南_含安卓客户端配置与GM后台直通

本文提供《秦时明月》6.2魔改版从零开始的完整部署指南。详细讲解CentOS服务器环境准备、服务端文件部署与安装、关键端口开放及进程验证。重点涵盖安卓客户端APK的IP修改与重签名方法,并介绍了GM后台工具的访问与使用,助你快速搭建并管理专属游戏服务器。

garlic的博客 316

MySQL——主从复制

从服务器上面启动一个I/O线程, 连接到主服务器上面请求读取二进制(Bin-log)日志,然后把读取到的二进制日志写到本地的Realy-log(中继日志)里面。主从复制(也称 AB 复制)允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。分析 - 可以在主服务器上创建实时数据,而信息分析可以在从服务器上进行,而不会影响主服务器的性能。执行此过程后,在主服务上操作的修改数据的操作都会在从服务器中执行一遍,这样就保证了数据的一致性。

xjm2001的博客 2131

TypeScript的安装与使用

TypeScript的安装与使用

qq_51589308的博客 601

【MySql】 搭建主从复制

主从复制是指将主库的数据变更同步到从库,从而保证主库和从库数据一致,可用于数据备份、失败迁移、读写分离、降低单库读写压力等场景。对于面向大企业的B端产品而言,主从复制技术尤为重要。

精益求精 2037

Cadence Allegro 17.4学习记录开始01-原理图Capture CIS 17.4

Orcad的学习记录

qq_31444421的博客 1万+

【Linux】常见指令的使用

Linux常用指令

qq_74897518的博客 1480

MySql主从复制原理

1、什么是主从复制?概念 主从复制是用来建立一个和 主数据库完全一样的数据库环境称为从数据库;主数据库一般是准实时的业务数据库。2、主从复制作用我们来思考如果在企业网站中,后端MYSQL数据库只有一台时候,会有以下问题:1、单点故障服务不可用2、无法处理大量的并发数据请求3、数据丢失所以通过主从复制后,它的优点就很明显1、如果主节点出现故障,那么我们就直接将服务切到从节点,来保证服务立马可用。2、如果并发请求特别大的时候,我们可用进行读写分离操作,让主库负责写,从库负责读。

qq_37436172的博客 3639

洛谷P5707 【深基2.例12】上学迟到(c语言)

第一篇题解 题目描述 yyy 的学校要求早上 8 点前到达。学校到 yyy 的家一共有 s(s\le 10000)s(s≤10000) 米,而 yyy 可以以 v(v<10000)v(v<10000) 米每分钟的速度匀速走到学校。此外在上学路上它还要额外花 10 分钟时间进行垃圾分类。请问为了避免迟到 yyy 最晚什么时候出门?输出 HH:MM 的时间格式,不足两位时补零。由于路途遥远, yyy 可能不得不提前一天出发,不过不可能提前超过一天。 输入格式 两个正整数 s,v,意思已经在题目中给定

cc_love_program的博客 1万+

MySQL-主从复制

MySQL-主从复制

我姓谭却弹不死你的博客 1418

让openclaw接入web端的deepseek或着qwen,使用无限token

🪟 Windows: 提供原生的 PowerShell 一键安装支持!配置过程和官方版本是相同的,唯一的区别就是再选则model时要选择带Browser字样的deepseek或者qwen。安装之前需要先清理官方版本的安装痕迹,否则openclaw版本跳转不到这个中文版本。因为此版本与官方版本存在代差,在配置飞书时可能会出现无法安装飞书的情况。剩下的配置保持和官方版本一致操作即可。选中之后需要进行一次网页端的登录。登录后会出现可选择的model。安装完成后会跳到这个中文界面。涉及到接入飞书机器人的操作。

z1872385的博客 303

【WebRTC】【Unity】Unity Web RTC1-Unity中简单实现远程画面

平台:基本常用平台包括安卓,苹果(max和IOS),Windows64,Linux都行。10. 注意,如果从移动设备浏览器连接服务,信令服务器的SSL证书一定要先设好。想要在Unity中实现实时远程桌面,找到了Render Streaming这个手段,本篇介绍相应的使用方法和实例。8. 打开电脑浏览器,地址栏输入http://127.0.0.1,点击Receiver Sample。新建Unity项目,模板选Third Person,Project Name随意。启动App,控制台出现如下信息表示成功。

每日出拳老爷子的博客 2518

MySQL的主从复制原理

一、主从复制概念 二、读写分离的概念 三、主库和从库 1. 主库 2. 从库 四、主从复制的流程 五、主从复制效果展示

qq_41721746的博客 2654

多角度模板匹配的模板创建技巧

基于形状的模板匹配多角度创建技巧

manuoo的专栏 3403

实现 MySQL 主从复制配置

主从复制主要操作是让一台服务器 Master 的数据和其他服务器 Slave 保持同步。一台 Master 数据可以同步到多台备库 Slave 上,同时一台 Slave 本身也可以配置另外一台服务器的 Master,Master 库和 Slave 库可以有多种不同的组合。在企业实际的开发中,后端 MySQL 数据库只有一台的时候,会有以下问题:主从复制流程图: 执行流程说明:192.168.2.125 和 192.168.2.126 安装 MySQL 的步骤都是一样的,以安装在 192.168.2.

养歌的博客 2801

大中型超市收银系统(可直接运行)

光步前台收银系统系统功能:商品销售、商品退货、挂单、收银员权限、会员销售、单品取消、整单取消、商品查询、脱机销售、脱机数据上传,基本资料下载,开钱箱、小票打印、收款结算,支持多币种付款方式(现金、储值卡、礼券、微信等),支持多种促销。运行环境:.net 4.0登陆信息用户名:1001 密码:123456内置商品信息,商品编码可输入00001000 - 00010000,测试效果相关功能键F5 结算 F9挂单 F11商品查询 F8退货 Insert单品取消 Del整单取消 F12开钱箱F2 会员 Ctrl+Z退出系统 Home 锁屏 相关源码待公开

win10 跳过U8环境检测的IE WebControls.rar

win10 安装u8的时候 提示IE WebControls 不通过,这个工具可以欺骗u8的检查。 跳过U8环境检测的IE WebControls。提示导入失败后,重新检测通过。

mysql8 主从复制

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 文章目录mysql主从复制常用命令创建用户相关权限相关环境介绍原理实现一、创建数据库一、主库操作从库配置常见错误处理可能用到的命令总结 mysql主从复制常用命令 创建用户相关 创建新用户 host: '%'=所有人 'localhost'=本服务器才能访问 '指定ip'=指定ip才能访问 格式:create user '新用户的名字'@'host地址' identified by '新用户的登录密码'; #创建用户并

特别剑的博客 2568

jdk-8u144-linux-x64.tar.gz.

该资源为搭建Linux系统Java环境用,资源的后缀为tar.gz格式的压缩包

mysql主从复制种类_MYSQL主从复制

relay 传递Slave 复数、奴隶replication 复制privileges 特权主从复制,只能有一个主节点,可以用n多个从节点一、配置文件在执行这个主从复制之前,首先必须打开 Master 端的Binary Log(MySQL-bin.xxxxxx)功能,否则无法实现。从服务器也需要开启二进制日志和 relay 日志功能 .这样可以从主服务器读取 binlog, 并产生 relaylo...

weixin_33122947的博客 283
上一篇: hadoop
下一篇: 知识点记录
ToryCode
博客等级 码龄3年 44粉丝 130原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值