Linux配置MySQL多实例主从同步

注: 本文档主从同步配置是基于多实例的Mysql

本文档内容基于以下两篇博客:

linux下安装MySQL5.7及遇到的问题总结

linux下配置mysql多实例

文档目录:

一. 主从同步简介

二. 配置MySQL多实例主从同步

• 2.1 机器配置
• 2.2 修改主服务器配置
• 2.3 修改从服务器配置
• 2.4 开启主从同步并验证

三. Linux配置MySQL多实例主从同步遇到的问题

• 3.1 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids
• 3.2 error connecting to master ‘slave@192.168.226.136:3308’

一. 主从同步简介

主从同步定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上.
在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）.
复制是异步进行的.
通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

主从同步优点

1. 提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
2. 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
3. 提高主服务器的性能(读写分离降低了主服务器的压力)

二. 配置MySQL多实例主从同步

2.1 机器配置

服务器	操作系统	ip地址	实例对应端口号	MySQL版本
服务器1(主)	CentOS7	192.168.226.136	3308	5.7.24
服务器2(从)	CentOS7	192.168.226.137	3308	5.7.24

注意: 主服务器需要和从服务器建立网络通信

2.2 修改主服务器配置

修改配置文件

MySQL默认配置: /etc/my.cnf

我的多实例配置: /etc/mysql/3308/my.cnf

在[mysqld]下增加下面几行.

server-id=3308
#log-bin设置此参数表示启用binlog功能，并指定路径名称
log-bin=/var/lib/mysql/3308/mysql-bin
sync_binlog=0
#设置日志的过期天数
expire_logs_days=7
#binlog_cache_size此参数表示binlog使用的内存大小
binlog_cache_size=1M

重启MySQL数据库

配置修改完毕后，需要重启MySQL数据库以使配置生效。

单实例重启方式:

service mysql restart

多实例重启方式:

# 停止实例  端口号不是3306需要指定socket文件
/opt/modules/mysql/bin/mysqladmin -uroot -p123456 -S /var/lib/mysql/3308/mysql.sock shutdown  

# 启动实例 多实例启动时需要指定my.cnf文件
nohup /bin/sh /opt/modules/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/3308/my.cnf --user=mysql &

创建同步数据账号,并赋权

登录主服务器的mysql

# MySQL多实例需要使用-S命令来指定socket文件
mysql -uroot -p123456 -S /var/lib/mysql/3308/mysql.sock

创建用于从服务器同步数据使用的账号

# 注REPLICATION代表用户的操作权限为复制 
# *.*表示任意数据库中所有表
# %匹配所有的host地址
# 123456指的是slave用户的密码
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by '123456';
FLUSH PRIVILEGES;

获取主服务器的二进制日志信息

show master status;

注意: File为使用的日志文件名字，Position为使用的文件位置，这两个参数须记下，配置从服务器时会用到

2.3 修改从服务器配置

服务器配置修改

MySQL默认配置: /etc/my.cnf

我的多实例配置: /etc/mysql/3308/my.cnf

在[mysqld]下面增加以下几行

# server-id给数据库服务的唯一标识 
# 注意: 这里必须保证唯一性,即和master的server-id不一致,否则开启同步时会报错
server-id=3309
#read_only设置数据库为只读，防止从库数据修改后，主从数据不一致，但是有Super权限的账号还是有写的权限，所以要某个账号只读的话，可以去掉账号的Super权限
read_only=1

#binlog_cache_size此参数表示binlog使用的内存大小
binlog_cache_size=1M
expire_logs_days=10
max_binlog_size=100M

重启MySQL数据库使配置生效

# 停止实例  端口号不是3306需要指定socket文件
/opt/modules/mysql/bin/mysqladmin -uroot -p123456 -S /var/lib/mysql/3308/mysql.sock shutdown  

# 启动实例 多实例需要指定my.cnf文件
nohup /bin/sh /opt/modules/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/3308/my.cnf --user=mysql &

2.4 开启主从同步并验证

连接主库

# 注意:默认连接端口3306,如果端口不是3306,需要使用MASTER_PORT指定端口号
# MASTER_LOG_FILE为主服务器使用的日志文件名
# MASTER_LOG_POS为使用的文件位置
 CHANGE MASTER TO MASTER_HOST='192.168.226.136',MASTER_PORT=3308, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=2478;

启动同步

mysql> start slave;

查看从库状态

mysql> show slave status \G;

红框标出的两个两个参数状态都是YES,证明同步配置正常,否则要根据报错提示做出相应的处理

完成后,需要刷新同步配置

# 首先停止同步
mysql> stop slave;

# 清理掉之前的配置,防止同步已经同步了的数据
mysql> reset slave all;

最后重新连接主库,进行同步

同步验证

如下图: 主服务器test库–>user表下面插入一条记录

从服务器,首先会生成对应的库表(如果没有的话),然后插入对应记录

主从同步正常!

三. Linux配置MySQL多实例主从同步遇到的问题

3.1 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

执行命令 show slave status \G; 查看从服务器同步状态时,报错:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; 
please check the manual before using it).

报错原因: 主服务器和从服务器的server-id一致,我这里是两台服务器,使用的都是3308端口,所以配置的时候server-id都配置的3308导致冲突

解决方案:
修改其中一台服务器MySQL配置文件,默认是 /etc/my.cnf,修改server-id,保证主从不同即可

3.2 error connecting to master ‘slave@192.168.226.136:3308’

开启主从同步后报错:

Last_IO_Error: error connecting to master 'slave@192.168.226.136:3308' - retry-time: 60  retries: 1

报错原因:

slave账户连接到主服务器异常

可能原因:

1. 主服务器 3308端口对应的实例挂掉
2. slave用户权限设置问题,没有相应权限

解决方案:

如果实例挂掉, 需要重启主服务器对应实例:

nohup /bin/sh /opt/modules/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/3308/my.cnf --user=mysql &

如果用户不具有相应权限,登录MySQL执行如下命令:

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by '123456';
FLUSH PRIVILEGES;