Mysql数据库主从配置

2021-11-26 18:39:50

Mysql主从同步

Mysql数据库进行主从配置后，可以实现数据库的备份、同时应用也可以实现读写分离，提高应用的并发量。

主从同步原理

主从原理大致有三个步骤：

在主库上把数据更改记录到二进制日志中（Binary Log）中，这些记录称为二进制日志事件。
从库通过IO线程将主库上的日志复制到自己的中继日志（Relay Log）中。
从库通过SQL线程读取中继日志中的事件，将其重放到自己数据上。

二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

主从配置

基本环境

两台服务器安装相同版本的mysql数据库
可以先手动将主库的数据复制一份到从库中（统一初态）
应保证在配置过程中主库的数据不会改变，可以通过加锁实现

配置主库

修改my.cnf文件，在[mysqld]加入下面的内容：

# 服务的唯一编号
server-id = 1
# 开启mysql binlog功能
log-bin = mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名为db_test
# 如果备份多个数据库，重复设置这个选项即可
binlog-do-db = db_test

# 不需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可（未测试）
# binlog-ignore-db=mysql2
# 这个参数要加上，否则不会给更新的记录些到二进制文件里（未测试）
# log-slave-updates=1
# 跳过错误，继续执行复制操作(可选)
# slave-skip-errors=1

保存配置文件，重启mysql服务

// Windows直接在服务中重启，Linux：
service mysqld restart

创建提供给从库，用于同步数据的账号（是主库的账号）

create user 'slave'@'127.0.0.2' identified by 'slave';
# 赋予replication slave权限，但在使用从库的数据时，还是要使用有read权限的账号
grant replication slave on *.* to 'slave'@'127.0.0.2';

flush privileges;

网上介绍的方法大都是使用grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ identified by ‘密码’;这种方式，但实测会出现……near 'identified by 密码' at line 1这个错误！
经分析，出错的原因是新版的的mysql版本已经将创建账户和赋予权限的方式分开了，所以应该使用上面的方法。

查看主库的状态

- 加“\G”可以使展示更友好
show master status\G;

记下主库状态信息中的File和Position，后面会用到

配置从库

修改my.cnf文件，在[mysqld]加入下面的内容：

# 服务的唯一编号
server-id = 2
# 开启mysql binlog功能
log-bin = mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名为db_test
binlog-do-db = db_test

保存配置文件，重启mysql服务

service mysqld restart

执行同步命令

# 设置主服务器ip，同步账号密码，同步位置
change master to master_host='127.0.0.1',
                 master_user='slave',
                 master_password='slave',
                 master_log_file='mysql-bin.000100',
                 master_log_pos=2411;
# 开启同步功能
start slave;

注意此处设置的是主库对应的IP，以及主库配置中创建的主库账号，以及主库状态信息中的File和Position

查看从库的状态

show slave status\G;

主要关注Slave_IO_Running和Slave_SQL_Running的状态是否都为Yes，如果是说明从库配置成功。

测试

在主库中新增一行记录，看从库是否将该数据同步了；
在主库创建新表，看从库是否将新表同步了。

可能的问题

从库未能同步主库数据

可以通过show slave status\G;检查Slave_IO_Running和Slave_SQL_Running的状态是否都为Yes

如果Slave_IO_Running的状态为Connecting，一般是从库数据被修改导致的Position失效导致的（当然原因也可能是因为网络不通、密码不对）：

程序可能在slave上进行了写操作
也可能是slave机器重启后，事务回滚造成的.

所以不要手动修改从表数据，否则数据冲突会导致主从同步失败（超过了一定的重试次数，从库不再进行同步）。
如果Slave_IO_Running的状态已经为Connecting了，可以通过手动同步数据库，然后修改Position的方法解决：

停掉Slave服务：slave stop;
到主服务器上查看主机状态，记录File和Position对应的值
然后到slave服务器上执行手动同步：change master to master_log_pos=6352;

如果发生的错误比较少（只有一处），也可以在从库通过下面的方法解决（未测试）：

stop slave ;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave ;

参考

https://www.cnblogs.com/atcloud/p/10773855.html

码农公寓