Mysql数据库主从搭建

机器准备

准备三台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