机器准备
准备三台centos7机器,已安装mysql5.7
三台机器的ip分别为:
- 192.168.39.129 主
- 192.168.39.130 从
- 192.168.39.131 从
安装可参考文章https://blog.csdn.net/hzau_itdog/article/details/107560301
在三台机器上分别创建1个数据库readwrite,字符编码为utf8:
CREATE DATABASE `readwrite` CHARACTER SET utf8 COLLATE utf8_general_ci;
master 节点配置
在192.168.39.129 上配置
vim /etc/my.cnf
#server-id给数据库服务的唯一标识
server-id=1
##log-bin设置此参数表示启用binlog功能,并指定路径名称
log-bin=/var/lib/mysql/mysql-bin
sync_binlog=0
#设置日志的过期天数
expire_logs_days=7
binlog-do-db=readwrite
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
- 这里的server-id用于标识唯一的数据库,在从库必须设置为不同的值。
- binlog-ignore-db:表示同步的时候忽略的数据库
- binlog-do-db:指定需要同步的数据库
重启mysql,配置生效,执行以下的命令:systemctl restart mysqld
重启成功后,登录mysql。
赋予从库权限账号,允许用户在主库上读取日志,赋予192.168.39.130和192.168.39.131也就是Slave机器有File权限, 只赋予Slave机器有File权限还不行,还要给它REPLICATION SLAVE的权限才可以。
mysql> grant FILE on *.* to 'hzau'@'192.168.39.130' identified by 'Hzau@2020';
mysql> grant replication slave on *.* to 'hzau'@'192.168.39.130' identified by 'Hzau@2020';
mysql> flush privileges;
mysql> grant FILE on *.* to 'hzau'@'192.168.39.131' identified by 'Hzau@2020';
mysql> grant replication slave on *.* to 'hzau'@'192.168.39.131' identified by 'Hzau@2020';
mysql> flush privileges;
重启mysql(systemctl restart mysqld),登录mysql,查看主库信息
File是同步会使用到的binlog文件,Position是同步的时候也要用到的。
配置从库
1、修改/etc/my.conf
log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
replicate-do-db=readwrite
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
注意:两个从库的server-id不一样,需要唯一。
2、修改完/etc/my.cnf后,重启一下mysql(systemctl restart mysqld)
3、进入Slave的mysql控制台,执行下面操作:
stop slave;
change master to master_host='192.168.39.129',master_user='hzau',master_password='Hzau@2020',master_log_file='mysql-bin.000002', master_log_pos=154;
start slave;
4、查看从库状态
show slave status;
上面的信息有Slave_IO_Running: No和Slave_SQL_Running: Yes,证明主从同步失败。
5、查看错误日志
cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
replicate-do-db=readwrite
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
错误日志在 /var/log/mysqld.log
查看错误日志
2020-07-27T01:19:20.559519Z 24 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000003' at position 154, relay log './localhost-relay-bin.000001' position: 4
2020-07-27T01:19:20.567347Z 23 [Note] Slave I/O thread for channel '': connected to master 'hzau@192.168.39.129:3306',replication started in log 'mysql-bin.000003' at position 154
2020-07-27T01:19:20.570177Z 23 [ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
提示 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
他们的auto.cnf中的server-uuid是一样的
主库
从库
问题解决
停止从库的mysqld服务,删除他的 /var/lib/mysql/auto.cnf文件,再启动数据库服务即可:
[root@localhost mysql]# systemctl stop mysqld.service
[root@localhost mysql]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
[root@localhost mysql]# systemctl start mysqld.service
重新执行下面命令
Slave_IO_Running: Yes和Slave_SQL_Running: Yes,证明主从同步成功。
在主库中建表插入数据可以看到,从库中也有这条数据。
查考资料: https://www.fangzhipeng.com/db/2019/06/25/mysql-install-ms.html