MySQL主从复制
6.1.1 主从复制架构和原理
6.1.1.1 服务性能扩展方式
- Scale Up,向上扩展,垂直扩展
- Scale Out,向外扩展,横向扩展
6.1.1.2 MySQL的扩展
- 读写分离
- 复制:每个节点都有相同的数据集,向外扩展,基于二进制日志的单向复制
6.1.1.3 复制的功用
- 数据分布
- 负载均衡读
- 备份
- 高可用和故障切换
- MySQL升级测试
6.1.1.4 复制架构
一主一从复制架构
一主多从复制架构
6.1.1.5 主从复制原理
主从复制相关线程
主节点: dump Thread:为每个Slave的I/O Thread启动一个dump线程,用于向其发送binary log events 从节点: I/O Thread:向Master请求二进制日志事件,并保存于中继日志中 SQL Thread:从中继日志中读取日志事件,在本地完成重放
跟复制功能相关的文件:
- master.info:用于保存slave连接至master时的相关信息,例如账号、密码、服务器地址等
- relay-log.info:保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
6.1.1.6 主从复制特点
- 异步复制
- 主从数据不一致比较常见
6.1.1.7 各种复制架构
- 一Master/一Slave
- 一主多从
- 从服务器还可以再有从服务器
- Master/Master
- 一从多主:适用于多个不同数据库
- 环状复制
复制需要考虑二进制日志事件记录格式
- STATEMENT(5.0之前)
- ROW(5.1之后,推荐)
- MIXED
6.1.2 实现主从复制配置
参考官网 https://mariadb.com/kb/en/library/setting-up-replication/ https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
主节点配置: (1) 启用二进制日志
[mysqld]
log_bin
(2) 为当前节点设置一个全局惟一的ID号
[mysqld]
server-id=#
log-basename=master #可选项,设置datadir中日志名称,确保不依赖主机名
说明:
server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2),默认值为1
0 to 4294967295 (<= MariaDB 10.2.1),默认值为0,如果从节点为0,所有master都将拒绝此slave的连接
(3) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
从节点配置: (1) 启动中继日志
[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON #设置数据库只读,针对supper user无效
relay_log=relay-log #relay log的文件路径,默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
(2) 使用有复制权限的用户账号连接至主服务器,并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mariadb-bin.xxxxxx', MASTER_LOG_POS=#; START SLAVE [IO_THREAD|SQL_THREAD]; SHOW SLAVE STATUS;
范例:新建主从复制
#主节点 [root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin [root@master ~]#systemctl restart mariadb [root@master ~]#mysql MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.39.%' identified by 'magedu'; #查看二进制文件和位置 MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28052 | | mariadb-bin.000002 | 545 | +--------------------+-----------+ 2 rows in set (0.001 sec) #从节点 [root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 [root@slave ~]#systemctl restart mariadb [root@slave1 ~]#mysql MariaDB [(none)]> help change master to MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.39.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=545; MariaDB [(none)]> start slave; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.39.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 26987890 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 26987902 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 26987890 Relay_Log_Space: 26988213 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 #复制的延迟时间 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 8 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 34 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 100006 1 row in set (0.000 sec)
主服务器非新建时
如果主节点已经运行了一段时间,且有大量数据时,如何配置并启动slave节点
- 通过备份恢复数据至从服务器
- 复制起始位置为备份时,二进制日志文件及其POS
范例:主服务器运行一段时间后,新增从节点服务器
#在主服务器完全备份
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_date +%F_%T
.sql
[root@master ~]#ll /backup/
total 2988
-rw-r--r-- 1 root root 3055918 Nov 27 17:41 fullbackup_2019-11-27_17:41:17.sql
[root@master ~]#scp /backup/fullbackup_2019-11-27_17\:41\:17.sql 192.168.8.11:/data/
#优化主服务器的性能
#global innodb_flush_log_at_trx_commit=2
#sync_binlog=0
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| sync_binlog | 0 |
|---------------------+-------+
5 rows in set (0.001 sec)
#将完全备份还原到新的从节点
[root@slave ~]#dnf -y install mariadb-server
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=11
read-only
[root@slave ~]#systemctl restart mariadb
#配置从节点,从完全备份的位置之后开始复制
[root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
[root@slave ~]#vim /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.10',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
[root@slave ~]#mysql < /data/fullbackup_2019-11-27_17\:41\:17.sql
[root@slave ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.8.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 389
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> start slave;
6.1.3 主从复制相关
-
限制从服务器为只读
read_only=ON #注意:此限制对拥有SUPER权限的用户均无效
注意:以下命令会阻止所有用户, 包括主服务器复制的更新
FLUSH TABLES WITH READ LOCK;
-
在从节点清除信息
注意:以下都需要先 STOP SLAVE
RESET SLAVE #从服务器清除master.info ,relay-log.info, relay log ,开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息,如HOST,PORT, USER和 PASSWORD 等
-
复制错误解决方法
可以在从服务器忽略几个主服务器的复制事件,此为global变量,或指定跳过事件的ID #系统变量,指定跳过复制事件的个数 SET GLOBAL sql_slave_skip_counter = N #服务器选项,只读系统变量,指定跳过事件的ID [mysqld] slave_skip_errors=1007|ALL
-
START SLAVE 语句,指定执到特定的点
START SLAVE [thread_types] START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos thread_types: [thread_type [, thread_type] ... ] thread_type: IO_THREAD | SQL_THREAD
范例:复制冲突的解决
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.39.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 26988271 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 557 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4' Skip_Counter: 0 Exec_Master_Log_Pos: 26988144 Relay_Log_Space: 26988895 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1007 #错误编码 Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4' Replicate_Ignore_Server_Ids: Master_Server_Id: 8 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 37 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 100006 1 row in set (0.000 sec) #方法1 MariaDB [(none)]> stop slave; MariaDB [(none)]> set global sql_slave_skip_counter=1; MariaDB [(none)]> start slave; #方法2 [root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] slave_skip_errors=1007|ALL [root@slave1 ~]#systemctl restart mariadb
-
保证主从复制的事务安全 参看https://mariadb.com/kb/en/library/server-system-variables/ 在master节点启用参数:
sync_binlog=1 每次写后立即同步二进制日志到磁盘,性能差 #如果用到的为InnoDB存储引擎: innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘 innodb_support_xa=ON #分布式事务MariaDB10.3.0废除 sync_master_info=# #次事件后master.info同步到磁盘
在slave节点启用服务器选项:
skip-slave-start=ON #不自动启动slave
在slave节点启用参数:
sync_relay_log=# #次写后同步relay log到磁盘 sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
范例:当master服务器宕机,提升一个slave成为新的master
#找到哪个从节点的数据库是最新,让它成为新master [root@centos8 ~]#cat /var/lib/mysql/relay-log.info 5 ./mariadb-relay-bin.000002 1180 mysql-bin.000002 996 0 #新master修改配置文件,关闭read-only配置 [root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 read-only=OFF log-bin=/data/mysql/logbin/mysql-bin #清除旧的master复制信息 MariaDB [hellodb]>set global read_only=off; MariaDB [hellodb]>stop slave; MariaDB [hellodb]>reset slave all; #在新master上完全备份 [root@slave1 ~]#mysqldump -A --single-transaction --master-data=1 -F > backup.sql [root@slave1 ~]#scp backup.sql 10.0.0.28: #分析旧的master 的二进制日志,将未同步到至新master的二进制日志导出来,恢复到新master,尽可能恢复数据 #其它所有 slave 重新还原数据库,指向新的master [root@slave2 ~]#vim backup.sql CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371; MariaDB [hellodb]>stop slave; MariaDB [hellodb]>reset slave all; MariaDB [hellodb]>set sql_log_bin=off; MariaDB [hellodb]>source backup.sql; MariaDB [hellodb]>set sql_log_bin=on; MariaDB [hellodb]>start slave;
6.1.4 实现级联复制
需要在中间的从服务器启用以下配置 ,实现中间slave节点能将master的二进制日志在本机进行数据库更新,并且也同时更新本机的二进制,从而实现级联复制
[mysqld]
server-id=18
log_bin
log_slave_updates
read-only
案例:三台主机实现级联复制
#在192.168.100.8充当master #在192.168.100.18充当级联slave #在192.168.100.28充当slave #在master实现 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin [root@centos8 ~]#systemctl restart mariadb [root@centos8 ~]#mysql MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' identified by 'magedu'; [root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql [root@centos8 ~]#scp /data/all.sql 192.168.100.18:/data [root@centos8 ~]#scp /data/all.sql 192.168.100.28:/data #在中间级联slave实现 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin read-only log_slave_updates #级联复制中间节点的必选项 [root@centos8 ~]#systemctl restart mariadb #还原数据库 [root@centos8 ~]#vim /data/all.sql CHANGE MASTER TO MASTER_HOST='master节点的iP', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=523; [root@centos8 ~]#mysql MariaDB [(none)]> set sql_log_bin=0; MariaDB [(none)]> source /data/all.sql MariaDB [(none)]> show master logs; #记录二进制位置,给第三个节点使用 MariaDB [(none)]> set sql_log_bin=0; MariaDB [(none)]> start slave; #在第三个节点slave上实现 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 read-only [root@centos8 ~]#systemctl restart mariadb [root@centos8 ~]#vim /data/all.sql CHANGE MASTER TO MASTER_HOST='中间节点的IP', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344; [root@centos8 ~]#mysql < /data/all.sql [root@centos8 ~]#mysql -e 'start slave;'
6.1.5 主主复制
主主复制:两个节点,都可以更新数据,并且互为主从 容易产生的问题:数据不一致;因此慎用 考虑要点:自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤: (1) 各节点使用一个惟一server_id (2) 都启动binary log和relay log (3) 创建拥有复制权限的用户账号 (4) 定义自动增长id字段的数值范围各为奇偶 (5) 均把对方指定为主节点,并启动复制线程
范例:实现两个节点的主主复制模型
#在第一个master节点上实现 [root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin auto_increment_offset=1 #开始点 auto_increment_increment=2 #增长幅度 [root@master1 ~]#systemctl start mariadb [root@master1 ~]#mysql MariaDB [(none)]>show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28303 | | mariadb-bin.000002 | 386 | +--------------------+-----------+ 2 rows in set (0.000 sec) MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' identified by 'magedu'; #在第二个master节点上实现 [rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin auto_increment_offset=2 #开始点 auto_increment_increment=2 #增长幅度 [root@master2 ~]#systemctl start mariadb [root@master2 ~]#mysql MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.100.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000002', -> MASTER_LOG_POS=386; Query OK, 0 rows affected (0.019 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> show master logs; #查看二进制位置 +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28303 | | mariadb-bin.000002 | 344 | +--------------------+-----------+ 2 rows in set (0.001 sec) #在第一个master节点上实现 MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.100.18', -> MASTER_USER='repluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000002', -> MASTER_LOG_POS=344; Query OK, 0 rows affected (0.007 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.002 sec) MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10)); #两个节点分别插入数据 #在第一个节点上执行 MariaDB [db1]> create database db1; MariaDB [db1]> insert t1 (name) values('user1'); #在第二个节点上执行 MariaDB [db1]> insert t1 (name) values('user2'); #两个节点同时插入数据 MariaDB [db1]> insert t1 (name) values('userX'); MariaDB [db1]> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | user1 | | 2 | user2 | | 3 | userX | | 4 | userX | +----+-------+ 4 rows in set (0.001 sec) #两个节点同时创建数据库,发生复制冲突 MariaDB [db1]> create database db2; MariaDB [db1]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.18 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 1029 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 1110 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2' Skip_Counter: 0 Exec_Master_Log_Pos: 897 Relay_Log_Space: 1553 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2' Replicate_Ignore_Server_Ids: Master_Server_Id: 18 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 2 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 2 1 row in set (0.003 sec)
6.1.6 半同步复制
默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失
半同步复制实现:
官方文档: https://mariadb.com/kb/en/library/semisynchronous-replication/
范例:CentOS 7 实现半同步复制
#主服务器配置: INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; UNINSTALL PLUGIN rpl_semi_sync_master ; SHOW PLUGINS; #查看插件 SET GLOBAL rpl_semi_sync_master_enabled=1; SET GLOBAL rpl_semi_sync_master_timeout = 1000; #超时长1s,默认值为10s SHOW GLOBAL VARIABLES LIKE '%semi%'; SHOW GLOBAL STATUS LIKE '%semi%'; #从服务器配置: INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled=1; #mariadb-10.3版以后 #主服务器配置: [mysqld] plugin_load_add = semisync_master #从服务器配置: [mysqld] plugin_load_add = semisync_slave
范例:CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制
#在master实现,启用半同步功能 [root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin plugin-load-add = semisync_master rpl_semi_sync_master_enabled=ON rpl_semi_sync_master_timeout=3000 #设置3s内无法同步,也将返回成功信息给客户端 [root@centos8 ~]#systemctl restart mariadb MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------------+--------------+ | Variable_name | Value | +---------------------------------------+--------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 3000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ 9 rows in set (0.002 sec) MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_request_ack | 0 | | 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_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 18 rows in set (0.001 sec) #在其它所有slave节点上都实现,启用半同步功能 [root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 plugin_load_add = semisync_slave rpl_semi_sync_slave_enabled=ON [root@slave ~]#systemctl restart mariadb [root@slave ~]#mysql MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------------+--------------+ | 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_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ 9 rows in set (0.001 sec) MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_request_ack | 0 | | Rpl_semi_sync_master_status | OFF | | 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_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | ON | +--------------------------------------------+-------+ 18 rows in set (0.001 sec) MariaDB [(none)]> #在master上实现 MariaDB [db1]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | #两个从节点 | Rpl_semi_sync_master_get_ack | 4 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 4 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 1 | | Rpl_semi_sync_master_request_ack | 3 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 1177 | | Rpl_semi_sync_master_tx_wait_time | 2355 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 2 | | Rpl_semi_sync_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 18 rows in set (0.001 sec) #测试 #在master实现,创建数据库,立即成功 MariaDB [db1]> create database db2; Query OK, 1 row affected (0.004 sec) #在所有slave节点实现,停止复制线程 MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.011 sec) #在master实现,创建数据库,等待3s才能成功 MariaDB [db1]> create database db3; Query OK, 1 row affected (3.003 sec) #在任意一个slave节点实现,恢复复制线程 MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.006 sec) #在master实现,创建数据库,立即成功 MariaDB [db1]> create database db4; Query OK, 1 row affected (0.002 sec)
6.1.7 复制过滤器
让从节点仅复制指定的数据库,或指定数据库的指定表 复制过滤器两种实现方式: (1) 服务器选项:主服务器仅向二进制日志中记录与特定数据库相关的事件
此方法存在的问题:基于二进制还原将无法实现;不建议使用
注意:此项和binlog_format相关 参看:https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
vim /etc/my.cnf
binlog-do-db = #数据库白名单列表,不支持同时指定多个值,如果想实现多个数据库需多行实现
binlog-ignore-db = #数据库黑名单列表
注意:
This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information. This option can not be set dynamically. When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.
(2) 从服务器SQL_THREAD在relay log中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地 此方法存在的问题:会造成网络及磁盘IO浪费
从服务器上的复制过滤器相关变量
replicate_do_db=db1,db2,db3 #指定复制库的白名单,变量可以指定逗号分隔的多个值,选项不支持多值
replicate_ignore_db= #指定复制库黑名单
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar% #支持通配符
replicate_wild_ignore_table=
When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters. When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
注意:跨库的更新将无法同步
MariaDB [db1]> create table db2.t1(id int);
Query OK, 0 rows affected (0.010 sec)
6.1.8 主从复制加密
基于SSL复制:在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的,外网里访问数据或则复制,存在安全隐患。通过SSL/TLS加密的方式进行复制的方法,来进一步提高数据的安全性 官网文档:https://mariadb.com/kb/en/library/replication-with-secure-connections/
实现MySQL复制加密
-
生成 CA 及 master 和 slave 的证书
[root@centos8 ~]#mkdir /etc/my.cnf.d/ssl/ [root@centos8 ~]#cd /etc/my.cnf.d/ssl/ [root@centos8 ssl]#openssl genrsa 2048 > cakey.pem Generating RSA private key, 2048 bit long modulus (2 primes) ...+++++ ..........................+++++ e is 65537 (0x010001) [root@centos8 ssl]#openssl req -new -x509 -key cakey.pem --out cacert.pem -days 3650 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:magedu Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:ca.magedu.org Email Address []: [root@centos8 ssl]#ls cacert.pem cakey.pem [root@centos8 ssl]#openssl req -newkey rsa:2048 -nodes -keyout master.key > master.csr Generating a RSA private key ..+++++ .................................................................................................+++++ writing new private key to 'master.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:magedu Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:master.magedu.org Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@centos8 data]#ls cacert.pem cakey.pem master.csr master.key [root@centos8 ssl]#openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem --set_serial 01 > master.crt Signature ok subject=C = CN, ST = beijing, L = beijing, O = magedu, OU = opt, CN = master.magedu.org Getting CA Private Key [root@centos8 ssl]#ls cacert.pem cakey.pem master.crt master.csr master.key [root@centos8 ssl]#openssl req -newkey rsa:2048 -nodes -keyout slave.key > slave.csr Generating a RSA private key .................................+++++ ...............................................+++++ writing new private key to 'slave.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:magedu Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:slave.magedu.org Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@centos8 ssl]# [root@centos8 ssl]#openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem --set_serial 02 > slave.crt Signature ok subject=C = CN, ST = beijing, L = beijing, O = magedu, OU = opt, CN = slave.magedu.org Getting CA Private Key [root@centos8 ssl]#ls cacert.pem cakey.pem master.crt master.csr master.key slave.crt slave.csr slave.key [root@centos8 ~]#chown -R mysql.mysql /etc/my.cnf.d/ssl/
-
主服务器开启 SSL,配置证书和私钥路径
[mysqld]
log-bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
-
创建一个要求必须使用 SSL 连接的复制账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.100.%' IDENTIFIED BY 'magedu' REQUIRE SSL;
-
从服务器slave上使用CHANGER MASTER TO 命令时指明ssl相关选项
[root@centos8 ~]#mysql -urepluser -pmagedu -h192.168.100.8 ERROR 1045 (28000): Access denied for user 'repluser'@'192.168.100.18' (using password: YES) [root@centos8 ~]#mysql -urepluser -pmagedu -h192.168.100.8 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key MariaDB [(none)]> [root@centos8 ~]#chown -R mysql.mysql /etc/my.cnf.d/ssl/ mysql> CHANGE MASTER TO MASTER_HOST='MASTERIP', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245, MASTER_SSL=1, MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
6.1.9 GTID复制
GTID复制:(Global Transaction ID 全局事务标识符) MySQL 5.6 版本开始支持,GTID复制不像传统的复制方式(异步复制、半同步复制)需要找到binlog文件名和POS点,只需知道master的IP、端口、账号、密码即可。开启GTID后,执行change master to master_auto_postion=1即可,它会自动寻找到相应的位置开始同步
GTID 架构
GTID = server_uuid:transaction_id,在一组复制中,全局唯一 server_uuid 来源于 /var/lib/mysql/auto.cnf
GTID服务器相关选项
gtid_mode #gtid模式
enforce_gtid_consistency #保证GTID安全的参数
GTID配置范例
-
主服务器
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
mysql> grant replication slave on *.* to 'repluser'@'192.168.100.%' identified by 'magedu';
-
从服务器
vim /etc/my.cnf server-id=2 gtid_mode=ON enforce_gtid_consistency mysql>CHANGE MASTER TO MASTER_HOST='192.168.100.100', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; mysql>start slave;
6.1.10 复制的监控和维护
(1) 清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]
(2) 复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST
(3) 从服务器是否落后于主服务
Seconds_Behind_Master:0
(4) 如何确定主从节点数据是否一致 percona-toolkit (5) 数据不一致如何修复 删除从数据库,重新复制
6.1.11 复制的问题和解决方案
6.1.11.1 数据损坏或丢失
- Master:MHA + semisync replication
- Slave: 重新复制
6.1.11.2 不惟一的server id
重新复制
6.1.11.3 复制延迟
- 需要额外的监控工具的辅助
- 一从多主:mariadb10 版后支持
- 多线程复制:对多个数据库复制
6.1.11.4 MySQL主从数据不一致
-
造成主从不一致的原因
- 主库binlog格式为Statement,同步到从库执行后可能造成主从不一致。
- 主库执行更改前有执行set sql_log_bin=0,会使主库不记录binlog,从库也无法变更这部分数据。
- 从节点未设置只读,误操作写入数据
- 主库或从库意外宕机,宕机可能会造成binlog或者relaylog文件出现损坏,导致主从不一致
- 主从实例版本不一致,特别是高版本是主,低版本为从的情况下,主数据库上面支持的功能,从数据库上面可能不支持该功能
- MySQL自身bug导致
-
主从不一致修复方法
- 将从库重新实现
虽然这也是一种解决方法,但是这个方案恢复时间比较慢,而且有时候从库也是承担一部分的查询操作的,不能贸然重建。
- 使用percona-toolkit工具辅助
PT工具包中包含pt-table-checksum和pt-table-sync两个工具,主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快,不需要停止主从辅助,缺点是需要知识积累,需要时间去学习,去测试,特别是在生产环境,还是要小心使用
关于使用方法,可以参考下面链接:https://www.cnblogs.com/feiren/p/7777218.html
- 手动重建不一致的表
在从库发现某几张表与主库数据不一致,而这几张表数据量也比较大,手工比对数据不现实,并且重做整个库也比较慢,这个时候可以只重做这几张表来修复主从不一致
这种方案缺点是在执行导入期间需要暂时停止从库复制,不过也是可以接受的
范例:A,B,C这三张表主从数据不一致
1、从库停止Slave复制 mysql>stop slave; 2、在主库上dump这三张表,并记录下同步的binlog和POS点 mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql 3、查看A_B_C.sql文件,找出记录的binlog和POS点 head A_B_C.sql 例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=66666666; 4、把A_B_C.sql拷贝到Slave机器上,并做Change master to指向新位置 mysql>start slave until MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=66666666; #以上指令是为了保障其他表的数据不丢失,一直同步,直到同步完那个点结束,A,B,C表的数据在之前的备份已经生成了一份快照,只需要导入进入,然后开启同步即可 5、在Slave机器上导入A_B_C.sql mysql -uroot -pmagedu testdb mysql>set sql_log_bin=0; mysql>source /backup/A_B_C.sql mysql>set sql_log_bin=1; 6、导入完毕后,从库开启同步即可。 mysql>start slave;
-
如何避免主从不一致
- 主库binlog采用ROW格式
- 主从实例数据库版本保持一致
- 主库做好账号权限把控,不可以执行set sql_log_bin=0
- 从库开启只读,不允许人为写入
- 定期进行主从一致性检验
本文链接:https://www.yunweipai.com/34252.html
网友评论comments