黄兵的个人博客

MySQL主备模式设置

573x314 由于原来主服务器经常出问题，所以需要再设置一台备机，下面是设置备机的过程：

既然作为主备模式，那必须至少有两台服务器。

A服务器： 192.168.67.124     主服务器master

B服务器： 192.168.67.125     副服务器slave

首先安装数据库，可以参考这篇文章：ubuntu安装MySql遇到的一些问题

主数据库设置：

安装好MySQL数据库之后，登录数据库服务器，新建用户账号：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'replicant'@'192.168.67.125' IDENTIFIED BY 'my_pwd';

其中replicant是数据库用户名，192.168.67.125是数据库IP地址，my_pwd是数据库密码。

在主库和备库都创建该账号。

之后修改主库MySQL配置文件，在Ubuntu 18.04默认路径是：/etc/mysql/mysql.conf.d

server-id		= 10
log_bin			= /var/log/mysql/mysql-bin.log

之后重启数据库，命令是：

sudo systemctl restart mysql.service

之后执行如下SQL命令，为了确认二进制日志文件已经在主库创建：

 mysql> SHOW MASTER STATUS;

结果如下图所示：

开始将主库的所有数据备份并复制到备库，具体操作过程如下：

将所有数据导出：

mysqldump -u root -p --all-databases > alldb.sql

之后通过SCP命令将数据库复制到备库上面，具体命令如下：

scp -i ~/.ssh/id_rsa -r /root/alldb.sql root@192.168.67.125:/root/

这里使用的是密钥的登录方式。

备库设置：

之后修改备库MySQL配置文件：

修改/etc/mysql/mysql.conf.d文件，增加如下内容，之后重启MySQL服务：

server-id		= 2
log_bin			= /var/log/mysql/mysql-bin.log
relay_log		= /var/log/mysql/mysql-relay-bin
log_slave_updates	= 1
read_only		= 1

在备库上设置如何连接到主库，并重放其二进制日志，具体命令如下：

 mysql>  CHANGE MASTER TO MASTER_HOST='192.168.67.124', 
            MASTER_USER='replicant', 
            MASTER_PASSWORD='my_pwd', 
            MASTER_LOG_FILE='mysql-bin.000001', 
            MASTER_LOG_POS=1480;

注：

MASTER_LOG_FILE和MASTER_LOG_POS是在主库上通过执行SQL：SHOW MASTER STATUS;得到的，可以与上图的执行结果对比一下。

之后开始启动复制：

mysql> START SLAVE;

如果上面命令没有错误，用SHOW SLAVE STATUS命令检查：

mysql> 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，应该是刚才设置错误，直接修改一下备机的server_id，重启MySQL数据库。

可以通过执行：

mysql> SHOW PROCESSLIST\G;

在主库上我们可以从线程列表中看到复制线程，在主库上可以看到由备库I/O线程向主库发起的连接：

我们还可以从线程列表中看到复制线程，下面是通过备库看一个I/O线程，一个SQL线程：

更多资料可以参考这里：mysql数据同步备份（同步整个数据库) ZT

参考资料：

1、《高性能MySQL》第三版 电子工业出版社 2013.5

2、O'REILLY - The Replication User Account

3、mysql数据同步备份（同步整个数据库) ZT

4、Export and Import all MySQL databases at one time

黄兵个人博客原创。

转载请注明出处： 黄兵个人博客 - MySQL主备模式设置