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

相关推荐

pentaho-kettle依赖拉取失败?试试这个Maven仓库配置的终极解决方案

本文详细解析了Pentaho Kettle依赖拉取失败的常见原因,并提供了三种Maven仓库配置方案,包括基础pom.xml配置、全局settings.xml设置以及企业级私有镜像仓库搭建。通过实战验证的方法和常见问题排查指南,帮助开发者彻底解决pentaho-kettle依赖问题,提升项目构建效率。

weixin_30275415的博客 205

MySQL——主从复制

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

xjm2001的博客 2131

水文预报新手别怕!用Matlab 18小时搞定马斯京根法参数K和x(附完整代码)

本文详细介绍了如何使用Matlab在18小时内快速掌握水文预报中的马斯京根法参数K和x的求解方法。通过完整的代码示例和分步解析,帮助新手从理论到实践快速跨越,解决传统手工试算耗时耗力的问题,提升水文预报的效率和精度。

weixin_42573113的博客 259

【MySql】 搭建主从复制

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

精益求精 2037

INCA_ProF脚本教程

一、首先INCA软件是当前主流标定开发软件之一,尤其在动力系统控制器的标定中应用非常广泛;与此同时ProF编程脚本是INCA软件提供各种实用功能之一,使用者可根据需求编写ProF脚本语言对ECU闪存编程等各种自定义操作。 二、然而ProF脚本语言在ETAS的官网或其他网络上并没有专门的使用教程,通常只有ETAS技术支持人员提供的ProF Documentation文档或少量可参考的Demo案例,然而ProF Documentation文档仅涉及ProF语言的函数的操作说明,没有从全局的框架上介绍Pro

weixin_29389627的博客 1万+

windchill安装搭建(保姆式)

保姆式搭建windchill 材料准备 Vmware工具,用来创建和打开虚拟机的工具 安装系统的镜像文件 数据库安装文件,这里是Oracle数据库 Windchill的安装包 Vmware工具,用来安装和打开虚拟机的软件 Windowsserver2008的镜像工具,用来安装虚拟机环境的系统,如果你有win2007或win2010的也可以,在做虚拟机时,需要先安装系统。 Oracle数据库安装包 Windchill11M030的安装包 (安装包如有需要可以私信邮箱发送) 虚拟机安装 安装虚拟机VMware

tr1294937662的博客 6219

MySql主从复制原理

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

qq_37436172的博客 3639

大语言模型与ROS集成的自然语言机器人控制:开源框架与应用实例

近年来,大语言模型(LLM)与机器人操作系统(ROS)的集成逐步成熟,使得自然语言驱动的机器人控制成为可能。用户输入“捡起绿色方块”或“把微波炉里的饭热好端过来”这类日常语句,机器人能够自动拆解步骤、规划动作并执行。本文梳理2026年前后发布的三个代表性开源框架——ROS-LLM、ROSClaw以及LLM+Nav2基准测试系统,介绍其技术路径与工程落地情况。

Studying…… 636

MySQL-主从复制

MySQL-主从复制

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

Apollo EM Planner中路径-速度解耦优化的数学建模与仿真分析

摘要 Apollo EM Planner采用路径-速度解耦优化策略解决自动驾驶运动规划问题。该算法将时空轨迹规划分解为路径规划和速度规划两个子问题:首先在静态环境下生成几何最优路径,再优化沿路径的速度曲线。通过五次多项式表示路径和速度,构建包含平滑性、安全性、舒适性的多目标优化函数,并引入弹性模型处理动态障碍物约束。算法具有解耦优化、弹性适应、实时求解等特点,适用于高速公路、城市交叉口等多种场景。数学建模包括路径几何优化、速度动态优化和弹性势能函数,最终生成满足车辆动力学和环境约束的安全轨迹。

走向CTO的路上... 936

Cesium.Ray 知识详解,示例代码

Cesium.Ray 是 CesiumJS 中用于表示三维空间中无限射线的核心类,广泛应用于地理信息系统(GIS)、三维可视化和碰撞检测等场景。射线由起点(origin)和方向向量(direction)定义,数学表达式为 ( P(t) = \text{origin} + t \times \text{direction} )。核心属性包括起点和方向向量,常用方法包括射线与平面的交点计算、根据距离获取射线上的点等。在 Cesium 中,Cesium.Ray 常用于地形碰撞检测、鼠标点选对象和雷达探测模拟等场景

大剑师兰特的GIS世界 426

MySQL的主从复制原理

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

qq_41721746的博客 2654

海洋捕食者算法(MPA)详解:从海洋生态智慧到高性能优化

摘要: 海洋捕食者算法(MPA)是一种基于海洋生态觅食行为的元启发式优化算法,模拟捕食者(如鲨鱼)的布朗运动和莱维飞行策略进行全局与局部搜索。算法分为三阶段:高速比阶段(全局探索)、单位速度比阶段(平衡搜索)和低速比阶段(局部开发),通过精英矩阵和FADs效应(概率扰动)避免早熟收敛。MPA具有参数少、收敛快、寻优能力强等特点,适用于复杂优化问题,其数学模型将生物行为转化为随机游走与自适应策略,并通过收敛因子动态调整探索-开发平衡。实验表明,MPA在多个基准问题上表现优异。

qq_42212808的博客 275

实现 MySQL 主从复制配置

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

养歌的博客 2801

怀旧版电脑GBC模拟器

再现原版GBC掌机的界面,电脑运行连游戏界面都一样大小,很怀旧,很真实,(英文版)强烈推荐,很小的文件,很难才找到的。

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

mysql8 主从复制

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

特别剑的博客 2568

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

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、付费专栏及课程。

余额充值