目录
说明:
本文是基于Docker安装的mysql的主从同步,非这种方式在本文可能个别地方会有区别。
同时,MySQL版本是8.0.27。
1.创建主从同步用户
在master数据库上创建主从同步的用户repl:
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
alter user 'repl'@'%' IDENTIFIED with mysql_native_password by '123456';
在MySQL8以后默认是不使用密码的,所以我们添加mysql_native_password参数并设置密码。
2.授予主从同步权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
这里的%,可以指定一定网段的,比如192.168.200.%
3.刷新权限
FLUSH PRIVILEGES;
4.修改master配置文件
注意:
如果是rpm包安装的,mysql的配置所在位置一般是:/etc/my.cnf。
如果是docker安装的,一般是在指定挂载的目录下:/usr/local/mysql/conf/my.cnf
在[mysqld]条目中,添加:
[mysqld]
# server-id 服务器唯一标识
server_id=1
# log_bin 启动MySQL二进制日志,即数据同步语句,从数据库会一条一条的执行这些语句。
log_bin=master-bin
# binlog_do_db 指定记录二进制日志的数据库,即需要复制的数据库名.
# 如果复制多个数据库,重复设置这个选项即可。默认全部记录.
# 一般不特别指定
# binlog_do_db=test
# binlog_ignore_db 指定不记录二进制日志的数据库,即不需要复制的数据库名.
# 如果有多个数据库,重复设置这个选项即可。
binlog-ignore-db=mysql
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema
# 其中需要注意的是,binlog_do_db和binlog_ignore_db为互斥选项,一般只需要一个即可。
# 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
# 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
# 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
5.重启MySQL
如果是docker安装的mysql:
docker restart mysql
如果是rpm等二进制包安装的:
systemctl restart mysqld
注意:修改MySQL的配置是一定要重启MySQL的。
6.查看master的状态
show master status;
记住file和position的取值:binlog.000001和392
7.修改slave配置文件
# 注意,这里同4
# docker安装mysql挂载目录
vim /user/local/mysql/conf/my.cnf
# 二进制包安装mysql
vim /etc/my.cnf
#在[mysqld]中添加:
server-id=2
relay-log=slave-relay-bin
# relay-log-index=slave-relay-bin.index
# replicate-do-db=test
#备注:
# server-id 服务器唯一标识,如果有多个从服务器,每个服务器的server-id不能重复,
# 跟IP一样是唯一标识,如果你没设置server-id或者设置为0,则从服务器不会连接到主服务器。
# relay-log 启动MySQL二进制日志,可以用来做数据备份和崩溃恢复,
# 或主服务器挂掉了,将此从服务器作为其他从服务器的主服务器。
# replicate-do-db 指定同步的数据库,如果复制多个数据库,重复设置这个选项即可。
# 若在master端不指定binlog-do-db,则在slave端可用replication-do-db来过滤。
# replicate-ignore-db 不需要同步的数据库,如果有多个数据库,重复设置这个选项即可。
# 其中需要注意的是,replicate-do-db和replicate-ignore-db为互斥选项,一般只需要一个即可。
######## 一下配置不指定也可以 ########
# 指定不需要同步的数据库
binlog_ignore_db=mysql
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
# 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
# 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
# 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
# relay_log配置中继日志
relay_log=mall-mysql-relay-bin
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
# slave设置为只读(具有super权限的用户除外)
read_only=1
8.重启mysql
# 二进制安装
systemctl restart mysqld
# docker安装
docker restart mysql
9.构建主从连接信息
change master to master_host='192.168.222.128',master_port=3306,master_user='repl',
master_password='123456',master_log_file='binlog.000001',master_log_pos=156, master_connect_retry=30;
# master_host:对应主服务器的IP地址。
# master_port:对应主服务器的端口。
# master_user:在主数据库创建的用于同步数据的用户账号。
# master_password:在主数据库创建的用于同步数据的用户密码。
# master_log_file:指定从数据库要复制数据的日志文件,对应show master status显示。
# 的File字段的取值,我这里是master-bin.000001。
# master_log_pos:指定从数据库从哪个位置开始复制数据,对应show master status显示的Position字段# 的取值,我这里是154。
# master_connect_retry:连接失败重试的时间间隔,单位为秒。这一项可以不配置。
10.开始同步
#启动slave数据同步
start slave;
#停止slave数据同步(若有需要)
stop slave;
11.查看同步信息
show slave status;
也可以在mysql命令行中使用如下命令:
show slave status \G
Slave_IO_Running和Slave_SQL_Running都为yes,则表示同步成功。
Slave_IO_Running是slave和master进行二进制日志同步的线程。
Slave_SQL_Running是slave执行写入relay中继日志的SQL的线程。
12.同步失败
执行完同步命令后查看同步信息,会有一个字段用来显示报错信息:
Last_SQL_Error。
若在主从同步的过程中,出现其中一条语句同步失败报错了,则后面的语句也肯定不能同步成功了。例如,主库有一条数据,而从库并没有这一条数据,然而,在主库执行了删除这一条数据的操作,那么从库没有这么一条数据就肯定删除不了,从而报错了。在此时的从数据库的数据同步就失败了,因此后面的同步语句就无法继续执行。
这里提供的解决方法有两种:
(1)在从数据库中,使用SET全局sql_slave_skip_counter来跳过事件,跳过这一个错误,然后执行从下一个事件组开始。
-- 在从数据库上操作
stop slave;
set global sql_slave_skip_counter=1;
tart slave;
(2)在从数据库中,重新连上主数据库。这种操作会直接跳过中间的那些同步语句,可能会导致一些数据未同步过去的问题。最好就是等到从数据库与主数据库的数据结构和数据都一致了之后,再来恢复主从同步的操作。
-- 在slave上执行
stop slave;
reset slave all;
-- 在master上执行
reset master;
-- 在slave上执行,重新执行构建主从连接的SQL即可
change master to master_host='192.168.222.128',master_port=3306,master_user='repl',
master_password='123456',master_log_file='binlog.000001',master_log_pos=156;
start slave;
13.同步报错
这个过程中可能遇到的错误大体上有一下几种。
13.1 server_id冲突
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相同。
修改my.cnf配置文件的server_id即可。
注意:所有的数据库的server_id不能相同,可以自行修改。
修改之后记得重启mysql数据库。
13.2 initialize relay log失败
Fatal error: Slave failed to initialize relay log info structure from the repository
这个一般是之前有过数据同步,修改了之后重新进行同步报错的。
解决方案就是:
在进行同步之前先重置同步信息,具体就是:在start slave之前,先启用reset slave。
13.3 MySQL server UUIDs相同
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
这个发生的概率较大,一般是因为虚拟机是copy的,而不是单独生成的。
不管是二进制包安装还是docker方式安装,这个都可能会发生。
解决方案都是一样的,只是docker的多了一步而已。
为了确定主从库的uuids是一样的,我们在数据库查看UUIDs:
show variables like '%server_uuid%';
分别在主从库中执行这个命令,我们得到的都是:
4b729139-6320-11ed-80c4-0242ac110002
这说明确实是冲突了。
解决方案:
如果是二进制包等方式安装的直接执行如下命令:
find / -name auto.cnf
一般是这个目录:/var/lib/mysql/auto.cnf
如果是docker安装的,一般在挂载的mysql的数据目录下:/usr/local/mysql/data/auto.cnf
我们打开这个文件,发现这里面的UUIDs和我们使用show variables like '%server_uuid%'这个命令查看的是一致的,这说明我们找到的文件是正确的。
二进制包安装的方式直接删除主从库中其中一个的这个文件即可,
rm -f /var/lib/mysql/auto.cnf
然后重启数据库:
systemctl restart mysqld
重启之后,auto.cnf这个文件会重新生成,我们发现这个UUIDs已经变了:
bc502531-843d-11ed-a1d6-0242ac110002
网上有一些文章说这个id是随便生成的,我们随便修改就行。实际上不是的,随便修改的话会导致mysql启动失败的。
其实我们看看这两个UUIDs会发现,只有前2段变了,后面的3段其实没有变化的。如果要修改前2段是否可行,这个我不确定。但是修改后面的肯定会导致mysql启动失败的,这个是血的教训。
如果是docker安装方式,如果我们直接修改/usr/local/mysql/data/auto.cnf的话是不行的,我们要进入容器中进行操作:
docker exex -it mysql bash
同样执行命令:
find / -name auto.cnf
同样在这个目录中:
/var/lib/mysql/auto.cnf
我们删除这个文件即可。
rm -f /var/lib/mysql/auto.cnf
然后重启退出容器,重启mysql:
exit
docker restart mysql
最后重新执行同步命令即可:
start slave;
再查看同步信息就正常了:
show slave status;
一般地看到:Slave_IO_Running和Slave_SQL_Running的取值都是Yes就代表主从同步成功了。此时就可以在主库中创建数据库、修改表结构、新增数据等操作,然后在从库中去查看是否有同步了。如果正常同步说明主从同步已经成功了。