mysql主从复制宕机恢复

 

MySQL主从复制搭建

MySQL主从复制搭建

前言

一、用途及好处

主要用途

好处

二、原理

详解

三、搭建过程

 主服务器（Master）

 从服务器（Slave）

主服务器相关配置（基于bin-log的主从同步）

从服务器相关配置

总结

---

前言

因业务量慢慢变大，单机MySQL以无法满足现有需求，因此要增加服务器数量实现主从复制，读写分离，所以在这里记录一下搭建过程

模式：一主一从（默认的SBR方式）

服务器版本：Centos7

MySQL数据库版本：MySQL5.7.31

具体搭建过程可参考视频：https://www.bilibili.com/video/BV1g441137tt?from=search&seid=7537804346567657406

一、用途及好处

1.1主要用途

• 备份
• 读写分离
• 高可用和故障切换（需要做HA）
• MySQL升级测试

1.2好处

• 实现服务器负载均衡
• 通过复制实现数据的异地备份
• 提高数据库系统的可用性

 

二、原理

• MySQL 中有一种日志叫做 binlog日志（二进制日志），这个日志会记录下所有修改了数据库的SQL语句。主从复制的原理其实就是"从"服务器向"主"服务器请求这个日志文件，"主"服务器会把这个 bin 日志复制到"从"服务器上执行一遍，这样"从"服务器上的数据就和"主"服务器上的数据相同了。

详解

1. 主服务器必须启用二进制日志（log-bin），记录任何修改了数据库数据的事件；
2. 从服务器开启一个线程（I/O Thread)把自己扮演成 MySQL 的客户端，通过 MySQL 协议，请求主服务器的二进制日志文件中的事件；
3. 主服务器启动一个线程（Dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主服务器就会从第一个日志文件中的第一个事件一个一个发送给从服务器；
4. 从服务器接收到主服务器发送过来的数据把它放置到中继日志（relay log）文件中。并记录该次请求到主服务器的具体哪一个二进制日志文件内部的哪一个位置（主服务器中的二进制文件会有多个，其名结尾以6位数递增）；
5. 从服务器启动另外一个线程（SQL Thread ），把 relay log 中的事件读取出来，并在本地再执行一次。

三、搭建过程

 主服务器（Master）

• 启用二进制日志 log-bin；
• 设置一个全局唯一的 server_id；
• 提前准备好一个有复制权限（replication slave，replication client）的用户。

 从服务器（Slave）

• 启动中继日志 relay-log；
• 设置一个全局唯一的 server_id；
• 使用主服务器提供的有复制权限的用户连接至 Master；
• 启动复制线程。

主服务器相关配置（基于bin-log的主从同步）

 3.1：在主服务器MySQL配置文件/etc/my.cnf下新增

log-bin=master-a-bin
binlog-format=ROW
server-id=1

3.2：改完配置文件后需要MySQL服务

service mysqld restart

3.3：登录主库查看主库的状态

mysql -uroot -p
show master status;

3.4：在主库创建一个专门用来复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限 

新增一个用户为‘copy’
CREATE USER 'copy'@'192.168.48.29%' IDENTIFIED BY 'sdsi2014Ears.';

为该用户授权
GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.48.29%';

刷新权限
flush privileges;

3.5：从数据库验证远程连接主库是否正常，建议一定要检查一下

从库用在主库新增的用户远程登录
mysql -ucopy -sdsi2014Ears. -P 3306 -h 192.168.48.29

从服务器相关配置

 3.6：在从服务器MySQL配置文件/etc/my.cnf下新增

log-bin=master-a-bin   
binlog-format=ROW    
server-id=2

3.7：改完MySQL配置文件后重启服务

service mysqld restart

3.8：在从库上建立主从连接关系，即从库指定主库的日志信息和链接信息

登录mysql
mysql -uroot -p

连接主服务器
CHANGE MASTER TO MASTER_HOST='192.168.49.38', MASTER_PORT=3306, MASTER_USER='copy', MASTER_PASSWORD='sdsi2014Ears.', MASTER_LOG_FILE='master-a-bin.000002',MASTER_LOG_POS=613;
/*MASTER_LOG_FILE对应主库的file，MASTER_LOG_POS对应主库的Position*/

启动slave
start slave;

查看slave的状态
show slave status\G    (注意没有分号)

3.9：从库启动复制进程

START SLAVE;

观察Slave_IO_Running（IO）、 Slave_SQL_Running（SQL）进程是否为yes，如果为yes说明正常，如果长时间处于"Connecting"状态就检查从库指定的主库的链接信息是否正确，Seconds_Behind_Master为从库和主库的延迟时间，0表示当前从库和主库的数据是一致的

3.10：在主库中创建数据库验证同步情况

mysql -uroot -p

CREATE DATABASE cdh DEFAULT CHARACTER SET = utf8;

GRANT ALL PRIVILEGES ON cdh.* TO 'cdh'@'192.168.49.38%' IDENTIFIED BY 'sdsi2014Ears.' WITH GRANT OPTION;

SHOW DATABASES;

3.11：查看从库，发现主从数据库以保持一致

---

总结

如果绑定主从时报‘ERROR 3021(HY000):this operation cannot be performed with a running salve io thread’就需要先重置在重新绑定

--从库执行
1、停止已经启动的绑定
stop slave

2、重置绑定
reset master

3、执行复制主机命令
CHANGE MASTER TO MASTER_HOST='192.168.49.38', MASTER_PORT=3306, MASTER_USER='copy', MASTER_PASSWORD='Omni2014Ears.', MASTER_LOG_FILE='master-a-bin.000002',MASTER_LOG_POS=613;