Mysql主从复制
理论知识
主从复制必要前提
- 主从复制必要的条件：
- 主库开启binlog日志（设置log-bin参数）
- 主从server-id不同
- 从库服务器能连同主库
实现原理
原理：实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成；要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己本地（slave端）按照执行日志中所记录的顺序，全部操作一遍。
- ---在主库上把有数据更改的（DDL DML DCL）sql语句都记录到二进制日志（Binary Log）中。
- ---备库的I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
- ---备库的SQL线程读取中继日志中的事件，将其重放到备库数据库之上。
binlog的三种类型
binlog有三种模式：statement模式、mixed模式和row模式。
- statement模式记录的是SQL语句
- row模式记录的是每一行数据的变化
- mixed模式是自动组合 STATEMENT 和 ROW 模式，按照最优方式来记录日志。
- Binlog日志的开启和关闭可以通过设置MySQL的配置文件实现。
配置步骤
- 两台机器时间一致
- 对主库已有的数据库不会进行自动同步。
- 主从同步之前，主库上已有数据库备份，需要在从库上手动导入同步
| 操作系统 |
IP |
mysql版本 |
主从类型 |
| Anolis OS8.9 |
192.168.200.81 |
mysql8.4 |
主 |
| Anolis OS8.9 |
192.168.200.83 |
mysql8.4 |
从 |
需要注意：mysql 版本不同，有些命令是不同的
|
1
|
show master status; 不能用了
|
查看主节点binlog的命令
|
1
2
3
4
5
|
# mysql 8.4版本前使用这条命令查看
show master status;
# MySQL 8.4版本后使用这条命令查看
SHOW BINARY LOG STATUS;
|
change master to不能用了
从节点配置主节点信息的命令
|
1
2
3
4
|
# MSQL 8.23前
CHANGE MASTER TO MASTER_HOST='192.168.200.81', MASTER_USER='nomax', MASTER_PASSWORD='nomax', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=158;
# MSQL 8.23后
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.81', SOURCE_USER='nomax', SOURCE_PASSWORD='nomax', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=158;
|
start slave不能用了
|
1
2
3
4
5
6
7
8
9
10
11
|
# 开启同步
start replica ; #8.0.22之后
start slave ; #8.0.22之前
#停止同步
stop replica ; #8.0.22之后
stop slave ; #8.0.22之前
#清空之前的主从复制配置信息
reset replica ; #8.0.22之后
reset slave ; #8.0.22之前
|
show slave status不能用了
查看从节点状态的
|
1
2
3
|
# 查看状态,\G表示行转列，便于查看
show replica status\G ; #8.0.22之后
show slave status\G ; #8.0.22之前
|
一、主节点配置(在192.168.200.81操作)
主从节点配置的差异：由于后续需要演示主从切换，所以无论是主从节点，都需要提前开启binlog和relaylog。故而这里主从配置基本一致，具体配置选项差异只有：server_id、read-only选项
编辑/etc/my.cnf
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=1
#指定binlog和binglog index的文件名
log_bin=/data/log/mysql/mysql-bin
log_bin_index=/data/log/mysql/mysql-bin.index
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#[可选]启用中继日志
relay-log=/data/log/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字
|
- relay-log中继日志可以看主从复制之间的细节
- log_bin_index，用来索引，主从复制时提供日志文件顺序
- binlog_format=row
- row哪里更改都会记录下来，不会出现无法正确复制的问题。很详细，缺点是日志量大
- read-only=0 主的设置0读写，从的设置1只读
修改配置后重启数据库:
|
1
2
3
|
systemctl restart mysql
or
service mysql restart
|
二、从节点配置(在192.168.200.83操作)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
##节点id编号，各个mysql的server_id需要唯一
server_id=2
#指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可选]启用中继日志
relay-log=/data/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=1
# #[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
# #binlog_expire_logs_seconds=6000
# #[可选]设置不要复制的数据库
# #binlog-ignore-db=test
# #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
# #binlog-do-db=需要复制的主数据库名字
|
主从节点配置的差异：开启binlog和relaylog,具体配置选项差异只有：server_id、read-only选项
在从节点登录主节点服务器获取服务器公钥
防止后续出现从节点连接主节点服务器报无公钥的错误
|
1
|
mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key
|
|
1
2
3
|
systemctl restart mysql
or
service mysql restart
|
三、创建用于主从同步的用户(主从都要创建)
root 用户也可以但是不安全
- MySQL8新版本
|
1
2
3
4
5
6
|
#创建nomax用户
CREATE USER'nomax'@'%' IDENTIFIED BY'nomax';
#给nomax用户授予数据同步的权限
GRANT replication slave on *.* to 'nomax'@'%';
#刷新权限
flush privileges;
|
- mysql5 老版本
|
1
2
|
GRANT REPLICATION SLAVE ON *.* TO 'nomax'@'%' identified by 'nomax';
flush privileges;
|
参数解析
新版本需要先创建用户
老版本权限用户可一起生成
- GRANT replication slave：授予改用户从库中进行复制的权限
- ON *.*：第一个 * 代表数据库名，第二个 * 代表表名，*.* 意味着所有数据库中的所有表。
- TO 'nomax' ：用户名字
- '@'%' ：表示用户可以在任何主机连接数据库
- identified by 'nomax' ：为用户设置nomax的密码
- 查询确认创建的用户
|
1
|
SELECT User FROM mysql.user;
|
四、开启主从复制
- 查看主节点binlog执行位置（主节点192.168.200.81来执行以下命令）
|
1
2
3
4
|
# mysql8.4的
SHOW BINARY LOG STATUS;
# mysql8及其以前的
SHOW BINARY STATUS;
|
|
1
2
3
4
5
6
7
|
mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 158 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
|
- 从节点开启主节点同步操作（从节点192.168.200.83来执行以下命令）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
#从节点配置连接主的信息
CHANGE REPLICATION SOURCE TO
#主节点的IP
SOURCE_HOST='192.168.200.81',
#主节点的端口号
SOURCE_PORT=3306,
#主节点的用户
SOURCE_USER='nomax',
#主节点的密码
SOURCE_PASSWORD='nomax',
#通过 SHOW BINARY LOG STATUS;查看
SOURCE_LOG_FILE='mysql-bin.000013',
SOURCE_LOG_POS=158;
#开启从节点备份
start replica;
#查看从节点的状态
show replica status \G;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
方便复制版本
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.81',
SOURCE_PORT=3306,
SOURCE_USER='nomax',
SOURCE_PASSWORD='nomax',
SOURCE_LOG_FILE='mysql-bin.000013',
SOURCE_LOG_POS=158;
start replica;
show replica status \G;
|
CHANGE REPLICATION SOURCE TO SOURCE_HOST=‘192.168.200.81’, SOURCE_LOG_FILE=‘mysql-bin.000013’, SOURCE_LOG_POS=158, SOURCE_PORT=3306, SOURCE_USER=‘nomax’, SOURCE_PASSWORD=‘nomax’;
确保下面四项参数正确
Replica_IO_Running: YesReplica_SQL_Running: YesLast_IO_Error:Last_SQL_Error:
报错：
Last_IO_Error: Error connecting to source 'nomax@192.168.200.81:3306'. This was attempt 10/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
- 原因：根据上一张密码传输方式的第3条，该插件发现连接未加密，因此需要使用RSA加密来传输密码。但是，服务器不会将公用密钥发送给客户端，并且客户端未提供公用密钥，因此它无法加密密码并且连接失败
解决方法：
- 使用复制用户请求服务器公钥
|
1
|
mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key
|
在这种情况下，服务器将RSA公钥发送给客户端，后者使用它来加密密码并将结果返回给服务器。插件使用服务器端的RSA私钥解密密码，并根据密码是否正确来接受或拒绝连接。
重新在从库配置change masrer to并且start slave，复制可以正常启动：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
#停止主从复制
#清空之前的主从复制配置信息
stop replica;
reset replica;
#从新配置主从复制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.81',
SOURCE_PORT=3306,
SOURCE_USER='nomax',
SOURCE_PASSWORD='nomax',
SOURCE_LOG_FILE='mysql-bin.000013',
SOURCE_LOG_POS=158;
start replica;
show replica status \G;
|
五、主从同步验证
- 也可以用navicat去试试，俩IP的数据库都连上进行创建库表啥的操作
- 主节点上进行建库、建表、插入表数据操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
#创建数据库
mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)
#查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.06 sec)
#切换数据库
mysql> use test_db;
Database changed
#创建表
mysql> CREATE TABLE `t_test` (
-> `id` int(11) NOT NULL,
-> `age` int(11) DEFAULT NULL,
-> `score` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.10 sec)
#插入表数据
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
#插入表数据
mysql> INSERT INTO `t_test` VALUES (222, 22, 19);
Query OK, 1 row affected (0.01 sec)
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test |
+-------------------+
1 row in set (0.00 sec)
#查看表数据
mysql> select * from t_test;
+-----+------+-------+
| id | age | score |
+-----+------+-------+
| 1 | 2 | 1 |
| 222 | 22 | 19 |
+-----+------+-------+
2 rows in set (0.00 sec)
|
检查从节点192.168.200.83是否也都同步成功：
如下所示，从节点也都自动完成了主节点上所进行的相关操作~
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.00 sec)
#切换数据库
mysql> use test_db;
Database changed
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test |
+-------------------+
1 row in set (0.00 sec)
#查询表数据
mysql> select * from t_test;
+-----+------+-------+
| id | age | score |
+-----+------+-------+
| 1 | 2 | 1 |
| 222 | 22 | 19 |
+-----+------+-------+
2 rows in set (0.00 sec)
|
至此，主从同步部署完成
六、故障切换
mysql主从，主节点宕机，如何进行切换
- 在从节点执行（版本以8.4为例，之前的命令不同参考本文理论知识模块命令注意）
|
1
2
|
mysql> stop replica;
mysql> reset replica;
|
- 查看是否是只读模式
|
1
2
3
4
5
6
7
|
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
|
只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。
或者不重启使用命令临时关闭只读，但下次重启后失效：set global read_only=off;
- 查看
|
1
|
mysql> show replica status\G
|
- 在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常
stop replica;mysql> reset replica;
查看是否是只读模式
|
1
2
3
4
5
6
7
|
mysql> show variables like ‘read_only';
±--------------±------+
| Variable_name | Value |
±--------------±------+
| read_only | ON |
±--------------±------+
1 row in set (0.00 sec)
|
**只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。**
**或者不重启使用命令临时关闭只读，但下次重启后失效：`set global read_only=off;`**
查看
|
1
|
mysql> show replica status\G
|
**在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常**
|
