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地址，测试应用连接是否正常**
