mysql 数据库双机热备份

mysql 数据库双机热备份

1. 环境准备

1. 两台纯净的centos6 linux系统
2. 两台linux虚拟机都安装mysql数据库
3. 前提条件，两台机器可以ping通

名词解释

• 主服务器（master）：负责日常服务器的增、删、改、查任务，是数据库集群的主力者。
• 从服务器（slave）：负责服务器上热备份数据，若主服务器崩溃或人为不可抗拒的灾难，可暂时替代主服务器继续提供数据。
• 热备份（hot backup）：指系统运行时进行的备份操作，在备份过程中，系统不会停止服务，数据被实时或准实时地复制到备份存储中。
• 冷备份（cold backup）：指系统关闭或不运行时进行的备份操作，在备份过程中，系统服务被停止，数据被复制到备份存储中。成本较低。

2. 主从复制

环境描述

主服务器master：192.168.111.136
从服务器slave：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

# 首次登录不需要密码，直接enter
mysql -uroot -p

# 授权root并修改密码为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）从属（slave）权限，并指定从服务器ip以及密码为root

# 主服务器创建用户
grant replication slave 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（位置）两个参数，用于从服务器配置主从关系时用到。

从服务器slave配置

进入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指定同步位置

# 先停止slave服务线程
stop slave;

# 指定同步位置
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 slave status\G;

# Slave_IO_Running: Yes
# Slave_SQL_Running: 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 slave 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 slave 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 slave;

在服务器 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 slave;

验证复制状态

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

show slave status\G;

检查 Slave_IO_Running 和 Slave_SQL_Running 是否均为 Yes，以及是否存在任何错误。

分别在服务器A和服务器B中，插入输入，查看数据库是否同步

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