MySQL主从数据库配置和常见问题


本篇主要介绍MySQL的主从数据配置方法。

MySQL主从数据库配置和常见问题

公司运维能力不是太好,数据库最近出了一次问题,导致丢失了一天的数据,并且某个服务宕机一晚上。为了避免再次出现类似问题,我决定添加一个Slave服务器,以避免数据丢失和服务宕机的问题。

MySQL本身就提供了主从备份方式,下面介绍配置方法。

1. 环境说明

两台服务器,系统均为CentOS7系统,MySQL版本均为5.7及以上,保证从库版本与主库版本相同,或者高于主库版本。

2. 配置方法

2.1 主库配置

主库主要是修改一下MySQL的配置文件,让主库记录binlog,并且提供一个从库用来连接主库的用户。下面是具体的配置及解释。

vim /path/to/my.cnf

# 在my.cnf中的[mysqld]下面,添加以下配置。
server-id=1
## 以下是可选配置
# binlog文件前缀
lob-bin=mysql-bin
# 忽略以下数据库
binlog-ignore-db=information_schema,mysql
# 备份以下数据库
binlog-do-db=test

# 添加后,执行以下语句,重启MySQL服务。
systectl restart mysqld

之后,登录MySQL命令行使用以下语句给从库连接主库用的用户授权。注意根据个人情况修改其中的ip和用户信息。

grant FILE on *.* to 'root'@'111.111.111' identified by '123456';
grant replication slave on *.* to 'root'@'111.111.111.111' identified by '123456';
flush privileges;

2.2 从库配置

从库需要配置需要同步的数据库以及与主库的连接方式。

vim /path/to/my.cnf

# 将以下配置根据自身情况加入其中
server-id=3
## 以下是可选配置
# 备份那些数据库
replicate-do-db=database1
# 不备份的数据库
replicate-ignore-db=mysql
# 忽略备份导致的错误
slave-skip-errors=all

# 添加配置以后,执行下面的语句重启MySQL服务。
systectl restart mysqld

之后,登录MySQL命令行,执行以下语句,设置连接相关的配置。 其中的master_log_file和master_log_pos可以通过在主库中执行show master status查看。

stop slave;
change master to master_host='111.111.111.111',master_user='root',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=2391;
start slave

2.3 查看主从库状态

以下命令均在MySQL命令行执行。

# 主库状态
show master status
# 从库状态
show slave status

# 开始、停止备份(从库服务器执行)
start slave
stop slave

3. 常见问题

3.1 Could not find first log file name in binary log index file

如果查看从库状态发现此问题,请查看主库状态,将其中的File和Position字段通过在从库中执行以下SQL语句写入从库配置中。

change master to master_log_file='mysql-bin.000001',master_log_pos=3726

3.2 ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

如果启动slave时出现此错误,主要可能是因为保存着以前slave用的relay-log,可以执行以下语句来启动slave。

reset slave;
start slave;