Linux安装mysql配置双机热备（主/主复制）

1. 概述

Mysql双机热备，双机热备主要强调的是数据备份，而不是故障转移；部署两台mysql服务，本文使用的两台服务器（10.11.1.28和10.11.1.29），下文可能会直接称之为28服务器和29服务器；本文实现的是主/主复制关系，也就是两台mysql服务都可以进行完整的读写操作；主要思路就是先配置28为主机，29为从机；然后将配置过程逆过来在进行配置即可；本文实现的复制方式为：二进制日志文件位置复制
双机热备方案的主要两种组建方式：
(1)第一种，双机热备它的工作原理是使用两台服务器，一台作为主服务器（Active），运行应用系统来提供服务。另一台作为备机，安装完全一样的应用系统，但处于待机状态（Standby）。当Active服务器出现故障时，通过软件诊测将Standby机器激活，保证应用在短时间内完成恢复正常使用。
(2)第二种，双机互备方式则是在双机热备的基础上，两个相对独立的应用在两台机器同时运行，但彼此均设为备机，当某一台服务器出现故障时，另一台服务器可以在短时间内将故障服务器的应用接管过来，从而保证了应用的持续性，这种方式实际上是双机热备方案的一种应用。

本文使用的是第二种。

2. 配置28主-->29从的主从复制

根据上面Linux安装单机mysql的步骤，在两台服务器分别安装mysql服务，然后确保服务全部正常可用；

操作28服务器（主服务器）

第一步：在28服务上创建专门用于与29服务备份用户；在主机上创建与从机备份使用的用户
CREATE USER 'cp_user'@'10.11.1.29' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'cp_user'@'10.11.1.29';
（ps：这里的cp_user 和123456 是一会儿备份服务器配置是需要用到的master服务器的用户名和密码，需要记下来）
第二步：修改 MySQL配置文件： /etc/my.cnf，添加如下内容(基于原有配置在添加如下配置):

log-bin=mysql-bin
binlog_format=mixed
#服务器唯一性标识符，每台服务器配置必须保存不一样
server-id=1
read-only=0
#需要备份的那个数据库名叫“test_db”（可选）如果不指定数据库，默认同步所有的数据库
binlog-do-db=test_db
#这里设置用来台服务器来做备份，按个人情况定 我们这里是主主复制，所以是两台
auto-increment-increment=2
#表示这台服务器序号，从1开始，不超auto-increment-increment
#配置完该数据库中插入第一个数据id=1,第二条数据id=3而不是2，避免的数据库集群中id冲突
auto-increment-offset=1

image.png

第三步：修改配置完成后，重启mysql服务
service mysqld restart

第四步：登录mysql，查看master状态信息
mysql –u root –p 然后输入对应密码即可登录
show master status\G; 查看master状态，如下图：

image.png

完成上述操作，master节点就已经配置完成了；

操作29服务器（从服务器）

第一步：修改mysql配置文件 /etc/my.cnf
vi /etc/my.cnf
配置内容如下(基于原有配置在添加如下配置)：

log-bin=mysql-bin
binlog_format=mixed
#服务器唯一性标识符，每台服务器配置必须保存不一样
server-id=2
#要同步的数据库名 如果不指定数据库，默认同步所有的数据库
replicate-do-db=test_db
relay-log=mysql.relay.bin
log-slave-updates=ON

第二步：修改完配置保存后，重启mysql服务
service mysqld restart
第三步：登录29服务器的mysql服务器：执行下面命令（配置同步的主服务器）
mysql –u root –p 输入对应密码即可

change master to master_host='10.11.1.28',master_port=3306,master_user='cp_user',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=156;

ps：上面命令中10.11.1.28为主机ip； 3306主机端口；cp_user/123456 同步使用的用户/密码 mysql-bin.000001同步使用的文件；156同步使用的标识码
启动从节点start slave;

这样从节点就已经完成配置了

第四步：重新启动29的mysql服务 ，登录mysql看一下从节点的状态
重启服务：service mysql restart
登录mysql服务：mysql –u root –p 输入对应的密码
查看从节点状态：show slave status\G;

image.png

如上图所示，从节点就配置完成了，接下来就可以测试数据同步问题了
分别登录28mysql服务和29mysql服务，创建test_db数据库，创建表，添加数据测试如下图：
创建数据库sql：
CREATE DATABASE IF NOT EXISTS test_db default charset utf8 COLLATE utf8_general_ci;
切换数据库sql
USE test_db;
创建表sql
CREATE TABLE user(id int not null auto_increment,user_name VARCHAR(50),password VARCHAR(10) ,name VARCHAR(50),status VARCHAR(10) ,constraint pk__person primary key(id));
添加数据sql
INSERT INTO user (user_name,password,name,status) VALUES('admin','admin','admin','1');

image.png
image.png

以上操作，从28到29的主从复制就搞定了！

接下来完成从29到28的主从复制，这样就能保证28和29两台mysql服务都能进行互相数据同步了。

---

3. 配置29主-->28从的主从复制

实际就是上面主从的逆向操作。将29作为主服务器，28作为从服务器。步骤基本和上面一样。 其中 28和29服务器的/etc/my.cnf配置文件 继续追加 主从配置内容即可。

1. 第一步：在29服务中创建备份用户
   CREATE USER 'cp_user'@'10.11.1.28' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'cp_user'@'10.11.1.28';
2. 第二步：打开/etc/my.cnf文件，开启29服务的binarylog
   新增配置如下：

# 主机使用配置 如果不指定数据库，默认同步所有的数据库
read-only=0
binlog-do-db=test_db
auto-increment-increment=2
auto-increment-offset=2
到现在为止，29服务的my.cnf完整配置放到文章末尾了（仅供参考）

3. 第三步：不需要导出29服务的初态同步到28服务上了，因为28服务和29服务的初态是一样的，登录mysql查看master日志状态。
   show master status\G;

image.png

4. 第四步：登录到28服务器开启中继relay_log
   打开28服务的/etc/my.cnf文件添加如下配置：

#从机使用配置 如果不指定数据库，默认同步所有的数据库
replicate-do-db=test_db
relay-log=mysql.relay.bin
log-slave-updates=ON
到现在为止，28服务的my.cnf完整配置放到文章末尾了（仅供参考）

5. 第五步：在28服务器上开启同步：
   change master to master_host='10.11.1.29',master_port=3306,master_user='cp_user',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=156;
   host为29服务的IP地址，user、password是在29服务上创建的备份用户，log_file、log_pos是在29服务上看到的master状态信息。
   启动从节点start slave;

6. 第六步：在28服务上查看slave status;

image.png

如上图所示，就搞定了！

接下来就可以测试了，在28服务和29服务任意一台上面添加数据，数据都会相应的同步到另一台上面！

至此mysql的双机热备就完成了！

注意：如果重启mysql后需要重新建立主从关系！
如果两台mysql服务数据不一致，在一台上面删除另一条不存在的数据时，则主从关系就会失效！！！

问题总结：如果查看从节点状态时出现下图情况,需要停止从节点,然后重新启动从节点即可：

image.png

解决办法：

mysql> stop slave ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> reset slave all;
Query OK, 0 rows affected (0.15 sec)
 
mysql> change master to master_host='10.11.1.28',master_port=3306,master_user='cp_user',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=881;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
 
mysql> start slave;

经过上述操作，就完成了mysql的双机热备服务安装，这可是生产环境实际应用过的呦！

28服务完整my.cnf文件内容：

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysqldb
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#主机使用配置
log-bin=mysql-bin
binlog_format=mixed
server-id=1
read-only=0 
binlog-do-db=test_db
auto-increment-increment=2
auto-increment-offset=1
#从机使用配置
replicate-do-db=test_db
relay-log=mysql.relay.bin
log-slave-updates=ON
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

29服务完整my.cnf文件内容：

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysqldb
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#从机使用配置
log-bin=mysql-bin
binlog_format=mixed
server-id=2 
replicate-do-db=test_db
relay-log=mysql.relay.bin
log-slave-updates=ON
# 主机使用配置
read-only=0 
binlog-do-db=test_db
auto-increment-increment=2
auto-increment-offset=2
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

本文为原著，如有问题，欢迎各位大佬指正，共同努力！