MySQL双机热备份配置与实现方案

MySQL 数据库双机热备份

在系统高可用架构中，数据库的热备份一直是绕不开的核心话题。也许你已经听过不少思路，但落实到具体操作时，还是会碰到不少细节问题。今天就从最经典的双机热备场景聊起，给出一套完整、可执行的步骤。

1. 环境准备

动手之前，先确认好基础环境：

1. 两台干净的 CentOS 6 Linux 系统——这是基础，系统毕竟是承载一切服务的根基。
2. 两台 Linux 虚拟机都装了 MySQL 数据库，版本保持一致。
3. 两台机器之间能够 ping 通——网络连通性是后续所有操作的前提，这一点容易被忽略。

先搞清楚几个关键概念

• 主服务器（master）：日常负责数据库的增、删、改、查，是整个集群的劳动力核心。
• 从服务器（sla ve）：负责热备份数据。一旦主服务器宕机或出现异常，可以暂时接替主服务器继续提供服务。
• 热备份（hot backup）：系统运行时的备份操作，整个过程不停服务，数据被实时或准实时地复制到备份存储中。这是真正意义上的高可用。
• 冷备份（cold backup）：系统关闭后进行的备份操作。备份期间服务必须停止，数据被完整复制。成本低，但停机时间不可接受。

2. 主从复制

环境描述

主服务器 master：192.168.111.136
从服务器 sla ve：192.168.111.137
MySQL 用户：replicate
MySQL 用户密码：root

两台机器同时操作 推荐使用 MobaXterm_CHS 连接虚拟机，操作会比较顺手。

关闭防火墙

service iptables stop

安装 MySQL

# 解压 MySQL 安装包，主从服务器的 MySQL 版本务必保持一致
cd /home
tar -xvf MySQL.rpm-bundle.tar

# 安装数据库
rpm -ivh MySQL-*rpm --nodeps --force

启动 MySQL，登录 MySQL

service mysql start

# 首次登录不需要密码，直接回车即可
mysql -uroot -p

# 授权 root 并修改密码
grant  all privileges on  *.* to 'root'@'localhost' identified by 'root';

# 仅修改密码：
alter user 'root'@'localhost' indentified by 'root';

flush privileges;

改静态 IP

cd /etc/sysconfig/network-scripts/
cat ifcfg-eth0

# 原始内容如下
DEVICE=eth0
HWADDR=00:0C:29:6D:7A:FD
TYPE=Ethernet
UUID=859d5289-7bf4-423f-aad4-dd2f4a0becbb
ONBOOT=no
NM_CONTROLLED=yes
BOOTPROTO=dhcp

vi ifcfg-eth0
# 改成以下内容
DEVICE=eth0
HWADDR=00:0C:29:6D:7A:FD
TYPE=Ethernet
UUID=859d5289-7bf4-423f-aad4-dd2f4a0becbb
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=static
IPADDR=192.168.111.137
NETMASK=255.255.255.0
GATEWAY=192.168.111.1
DNS1=8.8.8.8
DNS2=8.8.4.4

# 重启网络
sudo systemctl restart network

主服务器 master 配置 进入 192.168.111.136，登录 MySQL，创建同步用户 replicate，并授权复制（replication）从属（sla ve）权限，同时指定从服务器 IP 以及密码为 root：

# 主服务器创建用户
grant replication sla ve on *.* to  'replicate'@'192.168.111.137' identified by  'root';
flush privileges;

# 在从服务器 192.168.111.137 上测试连接
mysql -h192.168.111.136 -ureplicate -proot;

修改主服务器 MySQL 配置文件

vi /etc/my.cnf

# 添加如下内容
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql

重启 MySQL 服务

service mysql restart

查看主服务器状态

# 进入 MySQL 服务
mysql -uroot -proot

# 刷新表的读取锁
flush tables with read lock;

# 查看主服务器的状态
show master status\G;

# 解锁表
unlock tables;

注意记录 File（日志编号）和 Position（位置）这两个参数——后面配置从服务器时会用到。 从服务器 sla ve 配置 进入 192.168.111.137，修改从服务器 MySQL 配置文件：

vi /etc/my.cnf

# 添加如下内容
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=test
replicate-ignore-db = mysql,information_schema,performance_schema

重启 MySQL 服务

service mysql restart

登录 MySQL，用 change master 指定同步位置：

# 先停止 sla ve 服务线程
stop sla ve;

# 指定同步位置
change master to
master_host='192.168.111.136',
master_user='replicate',
master_password='root',
master_log_file='mysql-bin.000001',
master_log_pos=107;

# master_host : 主服务器 IP
# master_user : 同步用户
# master_log_file : File 日志编号
# master_log_pos : Position 位置

# 刷新权限
flush privileges;

查看从服务器状态

show sla ve status\G;

# 关键要看的是：
# Sla ve_IO_Running: Yes
# Sla ve_SQL_Running: Yes
# 两个 Yes 表示配置成功

SQL 语言不区分大小写，所以写命令时不必纠结大小写。 测试同步 在主服务器 MySQL 中，创建表并插入数据：

use test;
create table tb_mobile(mobile varchar(20), time timestamp default now());
insert into tb_mobile (mobile) values('13456789898');

在从服务器 MySQL 中，查看是否同步成功：

use test;
select * from tb_mobile;

现在从服务器可以同步主服务器，但主服务器无法同步从服务器的操作。接下来配置主主复制，也就是从单向复制变成双向复制。

3. 主主复制

环境描述 和主从复制时一致。完成主从复制后再做主主复制，两者不冲突，但为了稳妥，建议先打个快照。

服务器 A：192.168.111.136
服务器 B：192.168.111.137
MySQL 用户：replicate
MySQL 用户密码：root

服务器 A 配置（192.168.111.136） 编辑 MySQL 配置文件：

vi /etc/my.cnf

# 配置如下
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql,information_schema,performance_schema
# 添加以下配置以启用复制过滤器
replicate-do-db=test
replicate-ignore-db=mysql,information_schema,performance_schema

创建复制用户：

create user 'replicate'@'%' identified by 'root';
grant replication sla ve on *.* to 'replicate'@'%';
flush privileges;
# % 表示任何主机

记录二进制日志文件名和位置：

show master status;

服务器 B 配置（192.168.111.137） 编辑 MySQL 配置文件：

vi /etc/my.cnf

# 配置如下
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql,information_schema,performance_schema
# 添加以下配置以启用复制过滤器
replicate-do-db=test
replicate-ignore-db=mysql,information_schema,performance_schema

创建复制用户：

create user 'replicate'@'%' identified by 'root';
grant replication sla ve on *.* to 'replicate'@'%';
flush privileges;

记录二进制日志文件名和位置：

show master status;

配置双向复制 在服务器 A（192.168.111.136）上设置从服务器（指向服务器 B）：从服务器 B 获取二进制日志文件名和位置，然后在服务器 A 上执行以下命令：

change master to
master_host='192.168.111.137',
master_user='replicate',
master_password='root',
master_log_file='mysql-bin.000001',  -- 请使用服务器 B 实际的日志文件名
master_log_pos=107;  -- 请使用服务器 B 实际的日志位置

start sla ve;

在服务器 B（192.168.111.137）上设置从服务器（指向服务器 A）：从服务器 A 获取二进制日志文件名和位置，然后在服务器 B 上执行以下命令：

change master to
master_host='192.168.111.136',
master_user='replicate',
master_password='root',
master_log_file='mysql-bin.000001',  -- 请使用服务器 A 实际的日志文件名
master_log_pos=107;  -- 请使用服务器 A 实际的日志位置

start sla ve;

验证复制状态 在服务器 A 和 B 上分别验证复制状态：

show sla ve status\G;

检查 Sla ve_IO_Running 和 Sla ve_SQL_Running 是否均为 Yes，同时确认没有错误信息。 最后，分别在服务器 A 和服务器 B 中插入数据，看看数据库是否互相同步：

use test;
insert into tb_mobile (mobile) values('13456789898');
select * from tb_mobile;

总结

以上分享的是一套完整的 MySQL 双机热备方案，涵盖了主从复制和主主复制的全部步骤。实际操作时，关键是每一步都要确认状态正确，尤其是日志文件和位置参数，很容易被忽略。希望这篇文章能对你的实际部署有所帮助。