MySQL主从库配置和主库宕机解决方案

r囧r小猫 2022-04-14 01:29 371阅读 0赞

原理：MySQL从库上有一个IO线程负责从主库取binlog写到本地，另外有一个SQL线程负责执行这些本地值日，实现命令重做

一、准备工作

系统：CentOS

软件包：mysql-5.6.22.tar.gz

master服务器：192.168.1.112

slave服务器1:192.168.1.102

slave服务器2:192.168.1.104

二、修改master服务器配置

vi /etc/my.cnf

\[mysqld\]

log-bin=mysql-bin \#必选项，启用二进制日志，值为日志文件名前缀，该日志文件存储位置为mysql数据库目录

server-id=112 \#必选项，服务器唯一ID，默认是1，一般取IP最后一段

binlog-do-db=mydb \#可选项，需要同步的数据库，如果没有本行，即表示同步所有的数据库

binlog-ignore-db=mysql \#可选项，被忽略的数据库

三、修改slave服务器1配置

vi /etc/my.cnf

\[mysqld\]

log-bin=mysql-bin \#可选项，启用二进制日志，可是主库宕机后升级从库为主库时仍然需要设置，所以现在一起启用也可以

server-id=102 \#必选项，服务器唯一ID

replicate-do-db=mydb \#可选项，需要同步的数据库名，不写本行表示同步所有的数据库

四、重启所有服务器上的mysql服务

建议先手动同步一下主从服务器中要备份的数据库

service mysqld restart \#如果mysql没有配置为系统服务，命令为/etc/init.d/mysql restart

五、在master服务器上建立用于同步的账户并授权slave

mysql -u root -p

Enter password:root

mysql>create user 'mymaster'@'%' identified by 'mymaster';

mysql>grant replication slave on \*.\* to 'mymaster'@'%' identified by 'mymaster';\#一般不用root账号，%表示所有从库都可以连，也可以指定从库IP增强安全性

六、登录master服务器的mysql，查看master状态

mysql>show master status;

+------------------+----------+--------------+------------------+  
   | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
   +------------------+----------+--------------+------------------+  
   | mysql-bin.000001 |      120 |              |                  |  
   +------------------+----------+--------------+------------------+

七、配置两台slave服务器

mysql>change master to master\_host='192.168.1.112',master\_user='mymaster',master\_password='mymaster',master\_log\_file='mysql-bin.000001',master\_log\_pos=120;\#连接master

mysql>start slave; \#启动slave的复制功能

八、登录slave的mysql，查看从库状态

mysql>show slave status\\G; \#以下是部分结果

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event\#从库状态，已连上主库

Master\_Host: 192.168.1.112\#master地址

Master\_User: mymaster\#master用户

Master\_Port: 3306\#master端口

Connect\_Retry: 60\#重试时间，默认是60秒

Master\_Log\_File: mysql-bin.000001\#master的binlog文件名

Read\_Master\_Log\_Pos: 1556\#同步读取二进制日志的位置，大于等于Exec\_Master\_Log\_Pos

Slave\_IO\_Running: Yes\#必须为Yes，此线程把master段的日志写到本地

Slave\_SQL\_Running: Yes\#必须为Yes，此线程把写到本地的日志应用于数据库

Replicate\_Do\_DB:\#同步数据库名（如果有）

Replicate\_Ignore\_DB: \#忽略数据库名（如果有）

Exec\_Master\_Log\_Pos: 1556\#执行同步命令的位置

Last\_IO\_Errno: 0\#最近IO出错位置

Last\_IO\_Error: \#最近IO出错信息

Last\_SQL\_Errno: 0\#最近SQL出错位置

Last\_SQL\_Error: \#最近SQL出错信息

Master\_Server\_Id: 112\#master服务器ID

Master\_Info\_File: /data/mysqldb/master.info\#master.info文件位置，位于该slave中

1 row in set (0.00 sec)

如果Slave\_IO\_Running和Slave\_SQL\_Running均为Yes，则slave配置完成

九、主从服务器测试

在master中创建数据库，创建表，进行增删改操作都会被同步到所有slave中

十、主库宕机解决方案

假设发生了突发事件，master宕机，现在的需求是要将192.168.1.102提升为主库，另外一个为从库

步骤：

1.确保所有的relay log全部更新完毕，在每个从库上执行stop slave io\_thread; show processlist;直到看到Has read all relay log,则表

示从库更新都执行完毕了

2.登陆所有从库，查看master.info文件，对比选择pos最大的作为新的主库，这里我们选择192.168.1.102为新的主库

3.登陆192.168.1.102，执行stop slave; 并进入数据库目录，删除master.info和relay-log.info文件, 配置my.cnf文件，开启log-bin,如果有

log-slaves-updates和read-only则要注释掉，执行reset master

4.创建用于同步的用户并授权slave，同第五大步骤

5.登录另外一台从库，执行stop slave停止同步

6.根据第七大步骤连接到新的主库

7.执行start slave;

8.修改新的master数据，测试slave是否同步更新

十一、其他

1.如果修改了主服务器的配置，记得删除从服务器上的master.info文件。否则从服务器使用的还是老配置，可能会导致错误。

2.如果你想使用复制数据文件的方式来备份数据库，只要在从服务器上的mysql命令行先键入stop slave;然后复制数据库文件，复制

好了，再 在mysql命令行键入start slave;启动从服务器，这样就即备份了数据有保证了数据完整性，而且整个过程中主服务器的

mysql无需停止。并且如果在此期间master的数据有改动，在下次start slave时数据也会同步更新过来

3.Slave\_IO\_State的取值

空 //复制没有启动

Connecting to master//没有连接上master

Waiting for master to send event//已经连上

4.双机互备则是两个mysql同时配置为master及slave

5.主服务器上的相关命令：

show master status

show slave hosts

show \{master|binary\} logs

show binlog events

purge \{master|binary\} logs to 'log\_name'

purge \{master|binary\} logs before 'date'

reset master(老版本flush master)

set sql\_log\_bin=\{0|1\}

6.从服务器上的相关命令

start slave

stop slave

stop slave io\_thread

start slave is\_thread

stop slave sql\_thread

start slave sql\_thread

reset slave

SET GLOBAL SQL\_SLAVE\_SKIP\_COUNTER

load data from master

show slave status(SUPER,REPLICATION CLIENT)

CHANGE MASTER TO MASTER\_HOST=, MASTER\_PORT=,MASTER\_USER=, MASTER\_PASSWORD= \#动态改变master信息  
PURGE MASTER \[before 'date'\] \#删除master端已同步过的日志

MySQL主从库配置和主库宕机解决方案

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql 互为主备宕机数据丢失_mysql 主从复制、读写分离、模拟宕机、备份恢复方案生产环境实战...

相关 mysql主从库配置_mysql主从库配置

相关 mysql主从库配置

相关 Redis 主库宕机如何快速恢复？面试必问！

相关 Redis 主库宕机如何快速恢复？面试必问！

相关恢复Redis中主、从库宕机

相关 Redis中主、从库宕机如何恢复？

相关 MySQL Replication, 主从和双主配置

相关 MySQL主从库配置和主库宕机解决方案

相关 MariaDB/Mysql主从复制(Docker)——主库已有数据的解决方案——MariaDB/Mysql数据备份