7 MySQL 集群Cluster
7.1 主从复制
主从架构属于负载均衡集群
可以实现读写分离, 将读操作调度到多个节点
而负责写操作的主服务器是存在单点失败的
高可用集群是为了解决单点失败问题
7.1.1 主从复制原理
image.png
主从复制过程:
- 主节点收到用户请求, 进行数据更新
- 主节点将更新的操作写入二进制日志, bin log
- 主节点开启专门的dump线程, 负责把新生成的二进制日志, 发给从节点
- 从节点的io thread负责接收主节点dump线程发过来的数据
- 接收过来的数据写入中继日志relay log
- SQL thread把中继日志中的新的内容, 写入从节点数据库, 实现主从数据的同步
主从复制相关线程:
主节点:
- dump thread: 为每个Slave的IO thread启动一个dump线程, 用于向其发送二进制日志事件
从节点:
- IO thread: 向master请求二进制日志事件, 并保存于中继日志中, relay log
- SQL thread: 从中继日志中读取日志事件, 在本地完成重放
- dump线程与io thread通信走的是mysql协议, 主节点开启3306, 从节点会打开随机端口接收数据
7.1.2 主从复制相关配置
主从复制配置要求:
-
主从节点分别启用二进制日志
log-bin
主节点必须启用二进制, 因为要记录日志, 然后同步给从节点
从节点看情况启用, 不过一般都是要直接启用的, 因为一旦主节点故障, 从节点需要提升为主节点
此外, 如果需要利用从节点进行备份和还原, 那么从节点必须启用二进制, 并且添加log-slave-updates选项, 把从主节点接收的二进制写到本地二进制日志里
-
为当前所有节点设置一个全局唯一的server id号, 在二进制日志中用来表示当前的日志事件是来自哪个主机的, 以免混淆不同的日志来源, 因此, 要给每个节点, 配置一个与众不同的server id
server-id=# 默认为1, 只需要确保不同节点, id不同即可, 一般用服务器ip的最后一位即可 -
主节点给每个从节点创建一个具有复制权限的账号, 专门用于从节点从主节点复制二进制日志
grant replication slave on *.* to 'repluser'@'HOST' identified by 'replpass'; -
从节点启用中继日志
[mysqld] server_id=# #为当前节点设置一个全局唯一的ID号, 推荐用ip地址最后一位, 可以保证不冲突 log-bin=/data/mysql/mysql-bin #从节点也要启用二进制日志, 首先, 从节点以后有提升为主节点的可能, 同时, 数据备份时, 也可以利用从节点备份, 减少主节点压力 read_only=ON #设置数据库只读, 针对supper user无效, 非必须选项. 用来防止普通用户连接到从数据库进行修改， 但是从节点的更新是不会复制到主节点的，就会造成数据不一致. 不过， 该选项对root无效. relay_log=relay-log #relay log的文件路径, 默认值HOSTNAME-relay-bin, 非必须选项 relay_log_index=relay-log.index #默认值HOSTNAME-relay-bin.index, 非必须选项 -
从节点命令
change master to master_host='MASETHOSTIP', #在从节点指定主节点的ip master_user='repluser', # 从节点复制用户 master_password='replpass', # 从节点复制密码 master_log_file='mysql-bin.xxxxxx', #指明从主节点的哪个二进制日志开始复制 masterlog_pos=#; #指明二进制日志文件的位置 -
从节点开启io thread和sql thread线程
start slave; #默认是不开启的 -
如何确定从主节点哪个日志哪个位置开始复制
show master logs;
7.1.3 主从复制案例-1
从0搭建主从, 主从服务器都是新的服务器, 之前没有任何数据
环境:
图片.png
CentOS 7
MySQL 5.7.31
master: 10.0.0.237
slave: 10.0.0.227
配置过程:
- 修改主节点配置文件
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
log-bin=/data/mysql/mysql-bin
server-id=237
[client]
socket=/data/mysql/mysql.sock
- 创建二进制日志存放路径, 和权限
mkdir /data/mysql -pv
[root@master ~]#chown -R mysql.mysql /data/mysql
service mysqld restart
- 记录当前二进制日志的位置, 此步骤要在创建复制账号之前记录, 因为创建账号后, 二进制会被修改. 先记录二进制位置, 再创建账号, 这样创建的复制账号信息也会被记录到二进制日志里, 一同复制给从节点, 以防之后主机点down了, 将从节点提升为主节点时还要手动创建账号
[14:07:14 root@master ~]#ll /data/mysql/mysql-bin*
-rw-r----- 1 mysql mysql 154 Nov 25 14:07 /data/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql 29 Nov 25 14:07 /data/mysql/mysql-bin.index
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 | #从00001文件的154开始后, 都是需要复制的信息
+------------------+-----------+
1 row in set (0.00 sec)
- 主节点创建复制授权账号
#授权账号的host信息要写成一个网段, 因为, 从节点有可能有多个, 不能只写单个ip
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (0.01 sec)
- 从节点配置文件修改
[mysqld]
server-id=227
log-bin=/data/mysql/mysql-bin
read-only=ON
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
chown -R mysql.mysql /data/mysql
service mysqld restart
- 从节点配置复制命令
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.237',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
- 从节点执行了change master to后会在MySQL数据目录生成新的文件
-rw-r----- 1 mysql mysql 154 Nov 25 14:23 slave-relay-bin.000001 #从节点中继日志
-rw-r----- 1 mysql mysql 25 Nov 25 14:23 slave-relay-bin.index #从节点中继日志文件信息
-rw-r----- 1 mysql mysql 57 Nov 25 14:23 relay-log.info #中继日志信息, 存放中继日志和主节点二进制日志的对应关系
-rw-r----- 1 mysql mysql 90 Nov 25 14:23 master.info #复制信息, 存放chang master to命令的参数信息,
[03:38:41 root@slave ~]#cat /data/mysql/master.info
25
mysql-bin.000001
154
10.0.0.237
repluser
000000
3306
60
0
0
30.000
0
86400
0
[03:39:44 root@slave ~]#cat /data/mysql/relay-log.info
7
./slave-relay-bin.000001 # 中继日志的POS=4对应主节点二进制日志的POS=154
4
mysql-bin.000001
154
0
0
1
- 观察从节点的信息
#执行change master to命令后, 可以在从节点观察状态, 尤其要注意Slave_IO_Running: No
和Slave_SQL_Running: No, 两个值, 如果出现NO, 就说明主从复制出现问题, 该值需要做监控, 这里为NO, 是因为暂时还没有开启两个线程
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.237
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
...
Seconds_Behind_Master: NULL #这里表示了主从复制的延迟时间, 也需要进行监控
- 从节点启动SQL和IO线程
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
- 开启两个线程后, 会在主从节点生成用来进行复制同步的tcp连接
#主节点
[03:38:35 root@master ~]#ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
ESTAB 0 0 [::ffff:10.0.0.237]:3306 [::ffff:10.0.0.227]:50336
#从节点
[03:40:13 root@slave ~]#ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
ESTAB 0 0 10.0.0.227:50336 10.0.0.237:3306
- 开启后, 主节点会生成dump线程, 从节点生成sql和io线程
mysql> show processlist;
+----+----------+------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 4 | repluser | 10.0.0.227:43156 | NULL | Binlog Dump(主节点dump线程) | 34 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+----------+------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 3 | system user | | NULL | Connect(从节点io线程) | 58 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect(从节点sql线程) | 716 | Slave has read all relay log; waiting for more updates | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS ' |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.237
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 448
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 614
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试同步是否成功:
- 检查从节点是否有repluser账号
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
- 观察slave状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 448 #主节点最新的二进制位置, 目前和主节点的show master logs值是对应的
Seconds_Behind_Master: 0 #主从复制延迟为0
- 主节点导入hellodb数据, 在从节点测试
[14:43:27 root@master ~]#mysql < hellodb_innodb.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | #数据库成功复制
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
7.1.4 主从复制案例-2
单独添加新的从节点
部署思路:
- 先在主节点做一次完全备份, 把现有数据都导出来
- 找到主节点完全备份时的二进制位置
- 从节点导入完全备份数据
- 从节点开启二进制, 位置指向主节点备份文件中的change master to位置
环境:
主节点还是10.0.0.237
新的从节点10.0.0.217
步骤:
- 主节点做完全备份
[14:43:38 root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/allbackup_`date +%F_%T`.sql
[03:49:10 root@master ~]#ll /data/allbackup_2021-06-16_03\:49\:05.sql
-rw-r--r-- 1 root root 869712 Jun 16 03:49 /data/allbackup_2021-06-16_03:49:05.sql
- 将完全备份拷贝到从节点
[13:53:56 root@mysql-master ~]#scp /data/allbackup_2021-06-14_13\:52\:10.sql 10.0.0.217:/root
- 新的从节点配置MySQL
[mysqld]
server-id=217
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
service mysqld restart
- 开启从节点复制
#可以直接从完全备份文件中获取主节点二进制文件和记录位置, 直接把change master to命令写到完全备份文件里, 统一导入到从节点
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
# 因为在master节点执行了备份，所以会刷新一次日志， 生成新的mysql-bin.000002
CHANGE MASTER TO
MASTER_HOST='10.0.0.237',
MASTER_USER='repluser',
MASTER_PASSWORD='000000',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;
mysql> set sql_log_bin=off;
mysql> source /root/allbackup_2020-11-25_14\:52\:27.sql
mysql> set sql_log_bin=on;
- 导入完全备份文件后, 查看从节点状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.237
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: slave-2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No #此时线程还是关闭的, 不过没关系, 此时即使主节点还在写数据, 从节点也是从change mater to定义好的主节点二进制文件和位置开始同步
Slave_SQL_Running: No
- 主节点修改数据
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert teachers (name,age,gender) value('zhao',18,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers (name,age,gender) value('qian',18,'M');
Query OK, 1 row affected (0.01 sec)
- 验证从节点开始线程后, 修改的数据能正常同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhao | 18 | M | #主节点在从节点开始线程前添加的数据也能正常复制
| 6 | qian | 18 | M |
+-----+---------------+-----+--------+
- 观察主从节点各种的线程
#由于有两个从节点, 因此主节点会为每个从节点开启单独的dump线程
mysql> show processlist;
+----+----------+-----------------+---------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------------+---------+-------------+------+---------------------------------------------------------------+------------------+
| 3 | repluser | 10.0.0.52:33918 | NULL | Binlog Dump | 8026 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 7 | root | localhost | hellodb | Query | 0 | starting | show processlist |
| 8 | repluser | 10.0.0.53:57096 | NULL | Binlog Dump | 147 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+----------+-----------------+---------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
#新添加的从节点也正常开启了复制线程
mysql> show processlist;
+----+-------------+-----------+--------------------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+--------------------+---------+------+--------------------------------------------------------+------------------+
| 2 | root | localhost | #mysql50#mysql-bin | Query | 0 | starting | show processlist |
| 3 | system user | | NULL | Connect | 197 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 277 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+--------------------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
7.1.5 主从复制案例-3
主节点宕机, 如何提升从节点为主节点
- 确定哪个从节点对应的主节点二进制位置是更新的, 提升数据较新的那个从节点为主节点. 因为从节点同步到的主节点的二进制位置, 就决定了数据的新旧
方法1. 从节点使用show slave status;查看
方法2. 从节点查看relay-log.info
方法1：show slave status, 查看Master_Log_File和Read_Log_Pos
- 10.0.0.227 slave-1
图片.png
-
10.0.0.217 slave-2
图片.png
方法2： 从节点查看relay-log.info文件
10.0.0.227 slave-1
[04:08:25 root@slave ~]#cat /data/mysql/relay-log.info
7
./slave-relay-bin.000004
923
mysql-bin.000002
710
0
0
1
1
10.0.0.217 slave-2
[04:08:47 root@slave-2 ~]#cat /data/mysql/relay-log.info
7
./slave-2-relay-bin.000002
876
mysql-bin.000002
710
0
0
1
- 实际生产中, 可以借助软件, 实现对从节点的监控，自动监控哪个从节点的日志更新， 然后提升为主节点
案例: 假设10.0.0.227-slave-1的从节点的二进制是更新的，关闭主节点服务器， 提升slave-1为主节点
- 先关闭主节点服务器， 或者停止mysql服务也可， 之后从节点会立即报错
Last_IO_Error: error reconnecting to master 'repluser@10.0.0.237:3306' - retry-time: 60 retries: 2
- 提升10.0.0.227-slave-1为主节点， 修改配置文件， 关闭read-only配置
需要先记录新的主节点复制到了旧的主节点的那个二进制位置， 之后基于这个位置， 从旧的主节点利用mysqlbinlog把未导入的数据同步给新的主节点
因为主节点宕机时, 可以还存在一部分未同步给从节点的数据
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 10.0.0.237
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 710 # 当前复制到了主节点的710位置
[04:08:30 root@slave ~]#vim /etc/my.cnf
[mysqld]
server-id=227
log-bin=/data/mysql/mysql-bin
read-only=OFF
[04:11:19 root@slave ~]#service mysqld restart
# 也可以临时关闭read_only
set global read_only=off;
- 清除旧的主节点信息
10.0.0.227-slave-1
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)
# reset slave all后, 从节点原本的master.info, relay-log.info和已经产生的中继日志文件都会被清除, 只留一个slave-relay-bin.000001, 表示开始新的relay-log
- 在新的主节点进行完全备份
[04:14:47 root@slave ~]#mysqldump -A --single-transaction --master-data=1 -F > backup.sql
-
分析旧的master日志， 根据新的master此前记录的同步到的二进制位置， 利用mysqlbinlog把未同步的数据导出来
-
将导出来的数据， 导入给新的master， 由于新的master是开启了二进制的， 因此， 导入过程会被记录到二进制
-
此时， 新的master理想情况下， 就有了全部的数据， 之后修改新的master的完全备份文件， 添加change master to， 然后导入到其他从节点， 这样其他从节点就可以有全部的数据
-
首先， 其余从节点因为复制的内容少于新的master， 因此， 需要利用新的master的完全备份， 同步到和新的master一样的数据， 然后因为新的master会导入旧的master未同步的数据， 并且记录到本地二进制，这样在完全备份中指定二进制位置是新的master还未导入旧的master未同步的数据的时刻，因此剩下的slave也会把未同步的信息同步
-
修改backup.sql，指定新的master节点
CHANGE MASTER TO
MASTER_HOST='10.0.0.227',
MASTER_USER='repluser',
MASTER_PASSWORD='000000',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
[04:17:12 root@slave ~]#scp backup.sql 10.0.0.217:/root
- 其余slave重新指定master
mysql> stop slave;
mysql> reset slave all;
mysql> set sql_log_bin=off;
mysql> source backup.sql;
mysql> set sql_log_bin=on;
mysql> start slave;
- 验证主从同步
10.0.0.227-新的master
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
10.0.0.217-slave-2
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.227
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: slave-2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> create database master_slave;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| master_slave |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb2 |
+--------------------+
8 rows in set (0.01 sec)
- 之后故障的master需要重新添加到主从，需要新的master做完全备份， 然后修改change master to. 然后，故障主节点清除主从信息， 重新添加主从
主从总结:
1. 主从架构， 从节点即使开启了二进制，默认也是不会记录新的， 数据从主节点同步到中继日志，然后直接写入到数据库
2. 如果需要利用从节点做备份的话， 那么可以在从节点做完全备份， 至于二进制日志，需要在从节点配置文件指定log-slave-updates, 这样从节点也会记录二进制日志到本地， 之后就可以直接在从节点做备份了
3. 主从故障， 会造成主节点无法写入数据，业务中断， 读业务暂时不受影响
4. 不过， 如果是手动切换，因为要耗费时间去判断故障主节点还有那些数据没有同步给提升的主节点， 之后导入数据， 因此会有业务中断. 并且程序代码也要切换主从节点
5. 因此，主从需要配合程序完成自动切换，否则手动切换不建议使用
7.1.6 主从复制相关
7.1.6.1 清除从节点主从复制信息
当主服务器宕机, 提升了一个从节点为主节点后, 其他从节点需要重新指到新的主节点, 此时需要先停止其他从节点的slave, 然后清除slave信息.
清除slave信息有两个方法, 不过都要先停止slave
mysql> stop slave;
方法1:
mysql> reset slave all; #清除所有从服务器上设置的主服务器同步信息, 如: HOST, PORT, USER和PASSWORD等, 这种方式清除的更彻底
方法2:
mysql> reset slave; #从服务器清除master.info, relay-log.info, relay log, 开始新的relay log
#执行reset slave all后, 所有关于主从复制的文件都被删除, 生成新的relay-log文件slave-relay-bin.000001
[17:17:41 root@slave ~]#ll /data/mysql
total 123008
-rw-r----- 1 mysql mysql 56 Nov 25 00:05 auto.cnf
-rw------- 1 mysql mysql 1680 Nov 25 00:05 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 25 00:05 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 25 00:05 client-cert.pem
-rw------- 1 mysql mysql 1676 Nov 25 00:05 client-key.pem
drwxr-x--- 2 mysql mysql 272 Nov 25 14:43 hellodb
-rw-r----- 1 mysql mysql 293 Nov 25 14:18 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Nov 25 16:45 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov 25 16:45 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov 25 00:05 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Nov 25 14:18 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Nov 25 00:05 mysql
-rw-r----- 1 mysql mysql 154 Nov 25 14:18 mysql-bin.000001
-rw-r----- 1 mysql mysql 29 Nov 25 14:18 mysql-bin.index
-rw-r----- 1 mysql mysql 36039 Nov 25 17:15 mysql.log
-rw-r----- 1 mysql mysql 5 Nov 25 14:18 mysql.pid
srwxrwxrwx 1 mysql mysql 0 Nov 25 14:18 mysql.sock
-rw------- 1 mysql mysql 5 Nov 25 14:18 mysql.sock.lock
drwxr-x--- 2 mysql mysql 8192 Nov 25 00:05 performance_schema
-rw------- 1 mysql mysql 1676 Nov 25 00:05 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Nov 25 00:05 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Nov 25 00:05 server-cert.pem
-rw------- 1 mysql mysql 1680 Nov 25 00:05 server-key.pem
-rw-r----- 1 mysql mysql 177 Nov 25 17:15 slave-relay-bin.000001
-rw-r----- 1 mysql mysql 25 Nov 25 17:15 slave-relay-bin.index
drwxr-x--- 2 mysql mysql 8192 Nov 25 00:05 sys
#slave信息也被删除
mysql> show slave status \G
Empty set (0.00 sec)
7.1.6.2 主从复制冲突, 造成数据无法同步的问题和解决方法
由于从节点没有设置read_only=on, 只读, 造成从节点也可以修改数据, 此时比如从节点增加了一行数据, 然后如果主节点也增了一行数据, 并且存在主键冲突时, 那么这时数据同步就会失败
案例演示
- 从节点增加一行数据, 10.0.0.217
mysql> use hellodb;
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhao | 18 | M |
| 6 | qian | 18 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> insert teachers (name,age,gender) value ('haha',19,'M');
Query OK, 1 row affected (0.01 sec)
- 主节点增加数据, 10.0.0.227
mysql> use hellodb;
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhao | 18 | M |
| 6 | qian | 18 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> insert teachers (name,age,gender) value ('lala',19,'M');
Query OK, 1 row affected (0.00 sec)
此时, 虽然主从节点增加的数据不同, 但是从节点增加的数据主键位为7, 主节点增加的数据主键位也为7, 那么主节点向从节点同步自己增加的主键为7的数据时就会冲突, 因为主键7在从节点已经有了
#从节点观察show slave status;信息
Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '7' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 1513
#这时产生的错误是SQL线程错误, 也就是把数据从中继日志写到从节点数据库时产生的错误
- 一旦发生数据冲突, 往后的所有数据都不会同步了
解决方法
- 可以在从服务器忽略主服务器的复制事件个数, 此为global变量, 或指定跳过事件的ID
#系统变量, 指定跳过复制事件的个数
#在出现问题的从节点上执行
mysql> set global sql_slave_skip_counter=1; # 出现几个sql错误， 就跳过几次
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave; #关闭slave
mysql> start slave; #再开启slave
#验证SQL错误消失即可
此时, 观察从节点复制到的位置id
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 615
验证主节点二进制位置, 两个id相等, 就说明同步正常了, 此时在出现冲突后主节点写入的数据, 都会同步给从节点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 615 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
但是, 这种情况下, 那条冲突的数据, 由于在从节点已经被忽略, 因此主节点不会再向从节点同步那条数据, 这时主从节点是不一样的, 如果只有一条, 那么只需在从节点修改, 按照主节点数据修改即可, 如果多的话就要再分析了
对于错误冲突数据很多的情况下, 可以直接在从节点配置文件中指定, 略过所有的冲突, 之后重启服务
[mysqld]
slave_skip_errors=ALL #也可以单独指定SQL ERROR ID, 该ID就是从show slave status;报错得来的， Last_SQL_ERROR值
7.1.6.3 从节点服务器如果重启, 那么从节点的两个复制线程会默认自动开启
从节点服务器选项
skip-slave-start=ON #服务重启后不自动开启两个IO和SQL线程
7.1.6.4 保证主从复制的事务安全
- 在master节点启用参数
sync_binlog=1 #每次写数据， 立即同步二进制日志到磁盘， 性能差， 但是安全
# InnoDB引擎参数
innodb_flush_log_at_trx_commit=1 # 每次提交事务， 立即同步日志写磁盘
sync_master_info=# # 多少次事件后master.info同步到磁盘
- 在slave节点启用参数
sync_relay_log=# 多少次写后同步relay log到磁盘
sync_relay_log_info=# 多少次事务后，同步relay-log.info到磁盘
7.2 级联复制
级联复制可以用来减少主服务器的压力, 正常的一主多从, 要求主服务器负责写数据, 还要根据后端不同数量的从服务器开启不同的复制线程, 每个线程都会消耗资源,
有了中间节点, 主节点只需要开启一个dump线程把数据同步给中间节点, 其余从节点由中间节点去负责同步
7.2.1 实现级联复制要求
- 中间节点开启二进制, 这样另一个从节点才能从中间节点复制数据
- 中间节点开启log-slave-updates 中间节点必配置, 启用后从节点会把从主节点接收到的数据也写入自己的二进制日志中, 这样才能把数据再复制给其他从节点
MySQL工作时,每个二进制日志只记录当前自己这台服务器的操作,从节点从主节点获得的二进制会写到数据库里, 但是不会写到二进制日志, 但是其他从节点是要从中间节点的二进制日志获取数据的, 这就要求中间节点从主节点获取的二进制信息也要写入自己的二进制日志里, 同时自身发生的操作也写到二进制日志里
实现级联复制
10.0.0.237-主节点
10.0.0.227-作为中间节点
10.0.0.217作为从节点, 建议找一个干净的mysql环境. 因为从节点一旦有多于中间节点和主节点的数据， 那么会保留， 造成数据不一致
步骤:
- 三台服务器安装mysql
- 配置主节点
server-id=237
log-bin=/data/mysql/mysql-bin
service mysqld restart
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by '000000';
3 . 配置中间节点
[mysqld]
log-slave-updates
log-bin=/data/mysql/mysql-bin
server-id=227
service mysqld restart
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.237',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
- 确保中间节点有复制账号
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
- 主节点导入hellodb数据库， 确保能同步给中间节点， 并且中间节点会记录二进制日志到本地
[12:02:48 root@master ~]#mysql < hellodb_innodb.sql
- 中间节点
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 查看中间节点二进制
[12:03:51 root@mid ~]#ll /data/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql 10147 Jun 15 12:02 /data/mysql/mysql-bin.000001 # 已经更新
- 在中间节点做数据完全备份, 需要先确保中间节点和主节点是同步的, 查看POS ID
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 10219 |
+------------------+-----------+
1 row in set (0.00 sec)
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 10219
[12:05:01 root@mid ~]#mysqldump -A -F --master-data=1 --single-transaction > /data/new_all.sql
[12:05:13 root@mid ~]#ll /data/
total 8
-rw-r--r-- 1 root root 203 Jun 15 12:05 new_all.sql
- 复制到从节点
[12:05:16 root@mid ~]#scp /data/new_all.sql 10.0.0.217:/root
- 从节点配置文件
[mysqld]
server-id=217 #server-id不能冲突
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
- 创建二进制路径
[18:21:56 root@slave ~]#mkdir -pv /data/mysql/
[18:22:01 root@slave ~]#chown -R mysql.mysql /data/mysql
[12:13:15 root@slave ~]#service mysqld restart
- 导入完全备份
CHANGE MASTER TO
MASTER_HOST='10.0.0.227',
MASTER_USER='repluser',
MASTER_PASSWORD='000000',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
[12:18:55 root@slave ~]#mysql < new_all.sql
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
- 确保三台服务器主从复制位置一致， 数据一致
从服务器的二进制位置要和中间节点一致
中间节点要和主服务器一致
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
- 总结
单纯的主从， 需要根据从节点的个数建立对应的dump线程，对主节点压力很大
级联复制可以减轻主节点压力, 但从节点的复制效率不如单纯的主从
如果是多机房情况下，可以考虑级联复制， 每个机房找一个节点，和主服务器进行同步
7.3 双主复制
双主复制: 两个节点, 都可以更新数据, 并且互为主从
容易产生的问题: 数据不一致, 因此慎用, 如果两台服务器同时写入数据, 并且数据的主键相等, 就会出现冲突
双主的好处: 配合读写分离器, 实现写操作只调度到一个主节点, 读操作调度到另一节点, 类似主从. 不过, 因为本身配的都是主节点, 因此, 一旦负责写操作的主节点宕机, 可以直接把请求调度到另一个主节点, 省去了主从手动切换的过程
考虑要点: 自动增长id
解决方法:
-
搭建双主, 通过前端分离器实现写操作往一个服务器调度, 读操作往另一个服务器调度, 这样就不会出现同时写操作的情况, 可以避免主键id冲突
-
两个节点分别使用不同的主键id增长策略, 一个节点配置奇数增长, 一个节点配置偶数增长
节点1:
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度, 实现奇数增长
节点2:
auto_increment_offset=2 #开始点
auto_increment_increment2 #增长幅度, 实现偶数增长
7.3.1 双主复制的配置步骤
各节点使用一个唯一的server-id
都启用binary log和relay log
创建拥有复制权限的用户账号
定义自动增长id字段的数字范围各为奇偶
均把对方指定为主节点, 并启动复制线程
7.3.2 配置案例
双主:
CentOS 7
MySQL 5.7.31
10.0.0.237
10.0.0.227
10.0.0.237配置
[12:44:37 root@master-1 ~]#vim /etc/my.cnf
[mysqld]
server-id=237
log-bin=/data/mysql/mysql-bin
auto_increment_offset=1
auto_increment_increment=2
10.0.0.227配置
[12:40:36 root@master-2 ~]#vim /etc/my.cnf
server-id=227
log-bin=/data/mysql/mysql-bin
auto_increment_offset=2
auto_increment_increment=2
10.0.0.237
配置后重启服务, 生成二进制日志
[12:44:37 root@master-1 ~]#service mysqld restart
[12:48:20 root@master-1 ~]#ll /data/mysql/mysql-bin*
-rw-r----- 1 mysql mysql 154 Jun 15 12:48 /data/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql 29 Jun 15 12:48 /data/mysql/mysql-bin.index
查看二进制日志位置
[12:48:41 root@master-1 ~]#mysql -e 'show master logs;'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
创建复制账号
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (0.00 sec)
10.0.0.227
配置后重启服务, 生成二进制日志
[12:49:24 root@master-2 ~]#service mysqld restart
[12:49:31 root@master-2 ~]#ll /data/mysql/mysql-bin*
-rw-r----- 1 mysql mysql 154 Jun 15 12:49 /data/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql 29 Jun 15 12:49 /data/mysql/mysql-bin.index
查看二进制日志位置
[12:49:44 root@master-2 ~]#mysql -e 'show master logs'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
先把10.0.0.227作为10.0.0.237的从节点
10.0.0.227
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.237',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
由于复制的位置是主节点创建账号之前的位置, 因此从节点无需再创建复制账号
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repluser | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
此时从节点同步数据, 但是不会生成二进制日志. 因为没有开启log-slave-updates
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
再把10.0.0.237作为10.0.0.227的从节点
10.0.0.237
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.227',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
测试双向同步
10.0.0.237 创建数据库 db1
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
查看10.0.0.227同步情况
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
10.0.0.227 创建数据库 db2
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
查看10.0.0.237同步情况
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
- 测试两个节点同时创建数据库db3是否冲突
利用Xshell同时向多个窗口发送指令, create database db3;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
可以看到同时创建同名的数据库不会冲突, 不过会产生复制错误, 导致后续创建的数据不会同步
master-1
Last_Errno: 1007
Last_Error: Error 'Can't create database 'db3'; database exists' on query. Default database: 'db3'. Query: 'create database db3'
master-2
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'db3'; database exists' on query. Default database: 'db3'. Query: 'create database db3'
跳过冲突:
[mysqld]
slave_skip_errors=ALL
[05:32:02 root@master-1 ~]#service mysqld restart
[mysqld]
slave_skip_errors=ALL
[05:32:02 root@master-2 ~]#service mysqld restart
- 测试同时在两个节点创建相同的表是否冲突
在db3创建table1表, use db3;
mysql> create table table1(id int auto_increment primary key, name char(10));
Query OK, 0 rows affected (0.02 sec)
mysql> create table table1(id int auto_increment primary key, name char(10));
Query OK, 0 rows affected (0.02 sec)
结果显示建表可以创建, 但是还会出现冲突
Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 'table1' already exists' on query. Default database: 'db3'. Query: 'create table table1(id int auto_increment primary key, name char(10))'
Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 'table1' already exists' on query. Default database: 'db3'. Query: 'create table table1(id int auto_increment primary key, name char(10))'
按照上面相同步骤, 跳过冲突
测试在db3.table1表创建记录是否冲突
mysql> insert table1(name) value ('b');
Query OK, 1 row affected (0.10 sec)
mysql> insert table1(name) value ('b');
Query OK, 1 row affected (0.11 sec)
mysql> select * from table1;
+----+------+
| id | name |
+----+------+
| 1 | b |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
mysql> select * from table1;
+----+------+
| id | name |
+----+------+
| 1 | b |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
结果显示创建数据不会冲突, 因为我们针对两个节点设定了不同的主键id自动增长
补充:
-
即使数据库没配置成不同的自动增长步长, 只要是表开启了自动增长, 那么即使同时插入相同的数据, 也不会冲突, MySQL会自动调整表的自增步长
-
如果插入数据时, 人为指定了主键, 那么同时插入数据就会冲突
建议:
- 可以搭双主架构, 但是按照一主一从使用, 利用分离器实现写数据往一个主节点调度, 读数据往另一个主节点调度, 数据由一个主节点复制到另一个主节点, 这样就不会冲突, 同时, 一旦负责写输入的主节点down了, 把写数据往另一个主节点调度即可, 因为另一个主节点都是配置好了的, 可以直接写数据
跳过复制错误方法-指定具体编号
构建错误
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'db3'; database exists' on query. Default database: 'db3'. Query: 'create database db3'
分别修改两个节点的配置文件
10.0.0.237
[23:09:43 root@master-1 ~]#vim /etc/my.cnf
[mysqld]
slave_skip_errors=1007 # 该选项无法动态修改,而slave_skip_counter是可以动态修改的
重启mysql
[23:11:26 root@master-1 ~]#service mysqld restart
Shutting down MySQL............ [ OK ]
Starting MySQL. [ OK ]
观察错误消失即可, 10.0.0.227上也做相同的操作就可以恢复数据同步了
7.4 半同步复制
默认情况下, MySQL的主从复制是异步复制, 用户发送DML请求到达MySQL读写分离器, 读写分离器会把请求调度到主节点进行写操作, 主节点完成数据库更新会立即返回更新成功结果给分离器, 分离器会返回用户更新成功消息, 主节点同时将二进制更新同步给从节点
如果主从复制延迟较大时, 主节点的二进制更新还没有复制给从节点时, 一旦主节点故障, 二进制日志也无法恢复, 那么这时用户更新的数据就会丢失, 因为从节点还没来得及更新自己的数据. 但是分离器已经返回给用户更新成功通知.
同步复制:
主节点更新数据后, 必须要确保全部从节点已经完全接收了二进制的更新, 更新成功后, 才会返回给分离器更新成功了. 否则用户看不到更新成功消息. 这就会造成用户等待, 因为所有从节点完成同步会有延迟
解决方法: 半同步复制
必须确保任意一个从节点同步成功, 返回同步成功结果给主节点, 主节点才会通知分离器更新成功, 否则不会返回
这样半同步就确保了即使主节点宕机, 至少有一个从节点是有更新的数据的
然而, 如果所有节点的复制都很慢, 主节点需要设置超时时长, 规定时间内如果无法同步到任何一个从节点, 就会返回更新失败. 虽然此时也有风险, 比如等待时间设置过长, 如果等待期间内主服务器宕机, 仍然会丢失数据. 半同步只是尽肯能照顾了数据安全, 也保证了效率.
主从和半同步复制需要搭配使用
实验环境:
10.0.0.237 主节点
10.0.0.227 从节点
步骤1: 两个节点部署mysql
步骤2: 主节点10.0.0.237开启半同步
#MySQL 5.7版本需要在mysql里安装半同步插件, 不同版本配置不同, 高本版的MySQL和MariaDB是在配置文件里直接加载
#install这一步需要在修改配置文件, 重启mysqld之前做, 要不然会报错, 系统未知的变量"rpl_semi_sync_master_enabled"
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
vim /etc/my.cnf
rpl_semi_sync_master_enabled=ON #开启半同步复制功能
rpl_semi_sync_master_timeout=3000 #设置超时时间, 3s内无法同步, 会返回更新失败消息, 默认是10s
server-id=237
log-bin=/data/mysql/mysql-bin
[09:54:03 root@master ~]#service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (3.00 sec)
步骤3: 修改从节点配置文件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
rpl_semi_sync_slave_enabled=ON #从节点启用半同步复制
server-id=227
log-bin=/data/mysql/mysql-bin
[09:54:03 root@slave ~]#service mysqld restart
从节点指定复制位置
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.237',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='000000',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
查看主节点半同步状态
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 | #这里可以看到已经有了一个从节点
mysql> show 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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
步骤4: 验证同步情况
主节点导入测试存储过程和hellodb数据库
[09:54:03 root@master ~]#cat testlog.sql
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 100000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;
[09:56:19 root@master ~]#mysql < hellodb_innodb.sql
[09:56:26 root@master ~]#mysql hellodb < testlog.sql
mysql> use hellodb;
mysql> call sp_testlog;
同时观察从节点同步情况, 确保同步正常
mysql> select count(*) from hellodb.testlog;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
步骤5: 再搭建一个从节点10.0.0.217实现一主两从架构
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
[14:31:46 root@slave-2 ~]#vim /etc/my.cnf
[mysqld]
server-id=217
log-bin=/data/mysql/mysql-log
rpl_semi_sync_slave_enabled=ON
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[10:46:01 root@slave-2 ~]#service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
将主节点数据做完全备份
[10:47:29 root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/allbackup.sql
将完全备份拷贝到10.0.0.53第二个从节点
[10:47:30 root@master ~]#scp /data/allbackup.sql 10.0.0.217:/root
从节点设置change master to
#查看完全备份日志位置, 修改完全备份文件
CHANGE MASTER TO
MASTER_HOST='10.0.0.237',
MASTER_USER='repluser',
MASTER_PASSWORD='000000',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
从节点导入完全备份
mysql> set sql_log_bin=off;
mysql> source /root/allbackup.sql
mysql> set sql_log_bin=on;
从节点开启slave
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
验证同步
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
步骤6: 验证主节点上半同步客户端为2
mysql> show global status like '%semi%';
+--------------------------------------------+----------+
| Variable_name | Value |
+--------------------------------------------+----------+
| Rpl_semi_sync_master_clients | 2 |
步骤7: 关闭10.0.0.217上的slave功能, 验证主节点可以正常写数据
10.0.0.217
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
主节点创建数据库
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
可以看到此时主节点还是可以正常修改, 因为挂了一个从节点, 另一个从节点还是正常工作可以同步的, 而且因为环境简单, 同步时间没有超过3秒, 所以同步成功
验证10.0.0.227上同步成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
步骤8: 关掉10.0.0.227上的从节点服务, 构建无法同步的环境, 测试主节点能否修改数据
10.0.0.52
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
此时主节点的mysql日志会提示同步关闭
2020-11-25T17:03:48.034172Z 0 [ERROR] /usr/local/mysql/bin/mysqld: Got an error reading communication packets
2020-11-25T17:04:30.781849Z 13 [ERROR] Semi-sync master failed on net_flush() before waiting for slave reply
2020-11-25T17:04:30.781888Z 13 [Note] Stop semi-sync binlog_dump to slave (server_id: 53)
2020-11-25T17:04:30.782041Z 13 [Note] Aborted connection 13 to db: 'unconnected' user: 'repluser' host: '10.0.0.53' (Found net error)
2020-11-25T17:08:37.501641Z 0 [ERROR] /usr/local/mysql/bin/mysqld: Got an error reading communication packets
2020-11-25T17:09:30.803252Z 2 [Note] Stop semi-sync binlog_dump to slave (server_id: 52)
2020-11-25T17:09:30.803377Z 2 [Note] Aborted connection 2 to db: 'unconnected' user: 'repluser' host: '10.0.0.52' (failed on flush_net())
2020-11-25T17:10:35.383468Z 14 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000003, pos: 487), semi-sync up to file mysql-bin.000003, position 319.
2020-11-25T17:10:35.383562Z 14 [Note] Semi-sync replication switched OFF.
确保主节点和从节点复制关系消失后, 主节点创建数据库
mysql> show global status like '%semi%';
+--------------------------------------------+----------+
| Variable_name | Value |
+--------------------------------------------+----------+
| Rpl_semi_sync_master_clients | 0 | #确保client为0
#此时主节点会等待超时时间到期后再返回信息, 这里显示成功是因为即使在主节点成功了, 但是信息也没有复制到从节点上. 根据半同步原理, 此时会返回给用户端更新失败消息.
mysql> create database testdb2;
Query OK, 1 row affected (3.00 sec)
这时, 需要恢复主从同步关系用户才能正常写数据并且得到更新成功的消息
两个从节点启动复制, 为下个实验做准备, 开启复制后, 之前创建的testdb2会继续复制到两个从节点
start slave;
7.5 复制过滤器
让从节点仅复制指定的数据库, 或指定数据库的指定表
复制过滤器的两种实现方式
(1) 服务器选项: 在主服务器配置文件中指定哪些数据库会复制, 哪些不会复制
此方法问题: 基于二进制还原将无法实现, 不建议使用
vim /etc/my.cnf
binlog-do-db=DB_NAME #数据库白名单, 不支持同时指定多个值, 如果想实现多个数据库需要写多行
binlog-ignore-db=DB_NAME #数据库黑名单, 不支持同时指定多个值, 如果想实现多个数据库需要写多行
案例:
- 先查看主服务器上数据库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb2 |
+--------------------+
7 rows in set (0.01 sec)
- 修改主节点配置文件, 添加复制规则
[15:08:20 root@master-1 ~]#vim /etc/my.cnf
[mysqld]
binlog-do-db=testdb2
binlog-do-db=hellodb
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
server-id=237
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
~
[10:54:37 root@master ~]#service mysqld restart
- 验证配置
# Binlog_Do_DB表示只同步哪个数据库给从节点
mysql> show master status;
+------------------+----------+-----------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-----------------+------------------+-------------------+
| mysql-bin.000003 | 154 | testdb2,hellodb | | |
+------------------+----------+-----------------+------------------+-------------------+
1 row in set (0.00 sec)
# 主节点在testdb2创建表, 验证可以同步到从节点
mysql> use testdb2;
Database changed
mysql> create table test (id int);
Query OK, 0 rows affected (0.01 sec)
# 验证从节点可以同步
mysql> show tables from testdb2;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
# 主节点在testdb创建t1表, 验证不会同步
mysql> use testdb;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
从节点
mysql> show tables from testdb;
Empty set (0.00 sec)
- 使用服务器选项方法, 在跨越数据库创建数据时, 是不会同步的. 创建数据, 必须在会被同步的库里创建
主节点
mysql> use testdb;
mysql> create table testdb2.db_test(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables from testdb2;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| db_test |
| test |
+-------------------+
2 rows in set (0.00 sec)
从节点不会同步db_test表, 因为表是主节点在testdb里创建的
mysql> show tables from testdb2;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
- 此外, 使用服务器选项方式, 对于那些不会同步的数据库, 主节点即使开启了二进制日志, 也不会记录数据, 这样之后就无法利用二进制对于那些没有同步的数据库进行数据恢复了, 因此该方法不推荐使用
(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=
- MySQL中, 只能在命令行或者配置文件定义服务器选项. 这些变量在MySQL中不存在
案例:
- 取消主节点的复制过滤配置
[11:03:01 root@master ~]#vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
server-id=237
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[11:03:28 root@master ~]#service mysqld restart
- 两个从节点配置复制过滤
[17:03:43 root@slave-1 ~]#vim /etc/my.cnf
replicate-do-db=testdb2
replicate-do-db=hellodb
[17:04:07 root@slave-1 ~]#service mysqld restart
[17:28:05 root@slave-2 ~]#vim /etc/my.cnf
replicate-do-db=testdb2
replicate-do-db=hellodb
# 这里如果重启失败, 需要先把半同步复制注释, 重启mysql, 然后进到mysql安装半同步复制插件, 之后启用半同步, 重启mysql即可
[17:28:05 root@slave-2 ~]#service mysqld restart
- 主节点在db1和hellodb做修改, 验证可以正常同步给从节点即可
- 总结: 定义了复制过滤后, 主节点新创建的数据库是不会复制给从节点的, 需要从节点把新的数据库添加到配置文件指定. 这种方法主节点会对所有数据库记录二进制, 可用来做还原
7.6 GTID复制
GTID复制: (Global Transaction ID全局事务标识符), MySQL5.6版本开始支持, GTID复制不像传统的复制方式(异步复制, 半同步复制)需要找到binlog文件名和POS点
GTID复制只需要知道master节点的ip, 端口, 账号, 密码即可.
开始GTID后, 执行change master to master_auto_position=1即可, 它会自动寻找到相应的位置开始同步
GTID = server_uuid:transaction_id, 在一组复制中, 全局唯一
server_uuid 来源于 /var/lib/mysql/auto.cnf
GTID服务器相关选项
gtid_mode # gitd模式
enforce_gtid_consistency # 保证GTID安全的参数
配置案例
- 主服务器 - 10.0.0.237
[02:05:17 root@master-1 ~]#vim /etc/my.cnf
[mysqld]
server-id=237
log-bin=/data/mysql/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
[02:17:04 root@master-1 ~]#service mysqld restart
- 从服务器 - 10.0.0.227
取消slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
[02:19:54 root@slave-2 ~]#vim /etc/my.cnf
[mysqld]
server-id=217
log-bin=/data/mysql/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
[02:20:44 root@slave-2 ~]#service mysqld restart
mysql> change master to master_host='10.0.0.237',
-> master_user="repluser",
-> master_password="000000",
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
7.7 复制的监控和维护
(1): 清理日志
purge { binary | master } logs { to 'log_name' | before datetime_expr }
reset slave [all]
(2): 复制监控
show master status
show binary logs
show binglog events
show slave status
show processlist
(3): 从服务器是否落后于主服务
Seconds_Behind_Master: 0
(4): 如果确定主从节点数据是否一致
percona-toolkit
pt-table-checksum #检查主从数据是否一致，
pt-table-sync #把主库数据同步到从库
(5): 数据不一致如何修复
删除从数据库, 重新复制
7.8 复制的问题和解决方案
7.8.1 数据损坏或丢失
1. master损坏: MHA+半同步复制,
mha会自动提升一个较新的从节点为主节点, 无需手动切换
配合半同步复制, 确保至少有一个从节点已经同步了数据, 一旦主节点宕机, 会提升该同步了数据的从节点为主节点, 保证数据不丢失
2: slave损坏: 删除数据, 主节点做完全备份, 重新复制
7.8.2 不唯一的server id
重新复制
7.8.3 复制延迟
需要额外的监控工具的辅助
一从多主: MariaDB10版本后支持
多线程复制: 对多个数据库并行复制, MySQL支持客户端并行访问, 但是复制是串行的. 5.7以后可以根据数据库的数量开启对应线程进行复制.
7.8.4 MySQL主从数据不一致
- 造成主从不一致的原因
主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
从节点未设置只读，误操作写入数据
主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
MySQL自身bug导致
- 主从不一致修复方法
- 将从库重新实现
虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。
- 使用percona-toolkit工具辅助
PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。
这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
- 手动重建不一致的表
在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致
这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的
范例：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
从库开启只读，不允许人为写入
定期进行主从一致性检验
网友评论