Mysql数据库主从搭建

深碍√TFBOYSˉ_ 2023-03-01 11:40 2阅读 0赞

### 机器准备 ###

准备三台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][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，查看主库信息

![20200724164357518.png][]

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;

![20200727094533586.png][]

上面的信息有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是一样的

主库

![2020072709541461.png][]

从库

![20200727095509848.png][]

**问题解决**

停止从库的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

重新执行下面命令

![2020072709592213.png][]

![20200727095859809.png][]

Slave\_IO\_Running: Yes和Slave\_SQL\_Running: Yes，证明主从同步成功。

在主库中建表插入数据可以看到，从库中也有这条数据。

![20200727100155782.png][]

查考资料： [https://www.fangzhipeng.com/db/2019/06/25/mysql-install-ms.html][https_www.fangzhipeng.com_db_2019_06_25_mysql-install-ms.html]

[https_blog.csdn.net_hzau_itdog_article_details_107560301]: https://blog.csdn.net/hzau_itdog/article/details/107560301
[20200724164357518.png]: /images/20230208/7093a54ab6ba4f40b3e14802bc35d614.png
[20200727094533586.png]: /images/20230208/720f531cf34844738f9132896141a92b.png
[2020072709541461.png]: /images/20230208/af952966fea04cd5a9809316d39cc340.png
[20200727095509848.png]: /images/20230208/d576cfe2ee1f4581be8643090ed3b069.png
[2020072709592213.png]: /images/20230208/bd1926070ef64d439caeca13c4368ad0.png
[20200727095859809.png]: /images/20230208/d37f4faa5b2140fb9e5b11bbbc484f22.png
[20200727100155782.png]: /images/20230208/8f3d75801439465fada60195520879e1.png
[https_www.fangzhipeng.com_db_2019_06_25_mysql-install-ms.html]: https://www.fangzhipeng.com/db/2019/06/25/mysql-install-ms.html