Linux系统下使用MySql双机热备功能

概述

试验目的

验证、测试Linux系统下的MySQL双机热备功能。

试验环境

准备两台服务器，分别安装好操作系统和数据库，数据库的版本要一样。

环境项目	环境配置	备注
硬件环境	华为 2288H V5服务器	服务器1:192.168.200.1 服务器2:192.168.200.2
操作系统	Ubuntu20.04 LTS
MySQL	8.0.25-0-ubuntu0.20.04.1

准备数据库

在两台服务器上分别新建测试用的数据库：

CREATE DATABASE `mytest` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

准备账号

在服务器1上建立一个只能从服务器2登录的账号

示例中的账号为syn,密码为:123456

# 新建用户
CREATE USER `syn`@`192.168.200.2` IDENTIFIED WITH mysql_native_password BY '123456';
# 查看验证
SELECT user,host,plugin FROM mysql.user;
# 授予数据复制的权限
GRANT Replication Slave ON *.* TO `syn`@`192.168.200.2`;

在服务器2上建立一个只能从服务器1登录的账号

示例中的账号为syn,密码为:123456

# 新建用户
CREATE USER `syn`@`192.168.200.1` IDENTIFIED WITH mysql_native_password BY '123456';
# 查看验证
SELECT user,host,plugin FROM mysql.user;
# 授予数据复制的权限
GRANT Replication Slave ON *.* TO `syn`@`192.168.200.1`;

测试账号

• 在服务器1上，用服务器2的账号，登录服务器2上的数据库

mysql -h 192.168.200.2 -u syn -p

• 在服务器2上，用服务器1的账号，登录服务器1上的数据库

mysql -h 192.168.200.1 -u syn -p

• 测试账号时的问题
  如果测试账号时出现类似如下的问题：
  

1. 请检查配置文件/etc/mysql/mysql.conf.d/mysqld.cnf中的bind_address参数是否允许了访问的IP地址。
2. 检查被访问服务器的防火墙是否开启了3306端口。
3. 检查账号的host是否设置正确。

这是因为用户名或者密码错误:

修改测试配置

修改配置文件

两台服务器分别修改配置文件/etc/mysql/my.cnf

sudo vim /etc/mysql/my.cnf

在文件的最后添加如下代码：

[mysqld]
#其中的mytest是数据库名
# 主服务器唯一ID,服务器之间不能一样
server-id=1
# 启用二进制日志
log-bin=backuplog
sync_binlog=1
binlog_format=row
auto_increment_increment = 1
auto_increment_offset = 1 
max_binlog_size=512m
expire_logs_days=1
# 设置需要记录二进制日志的数据库(需要同步复制的都要记录,可设置多个，如果不配置，则全部记录)
binlog_do_db=mytest
# 设置不要复制的数据库(可设置多个)
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
# 设置需要复制的数据库(可设置多个，如果不配置，则全部同步)
replicate_do_db=mytest
replicate_wild_ignore_table=mytest.log
slave_skip_errors=all

如果只是想要主从复制，比如服务器1是主服务器，服务器2是从服务器,只从主服务器复制数据到从服务器，那么从服务器只需要填写server-id一项即可。

重启MySQL服务

修改好之后，记得重启MySQL服务

sudo service mysql restart

正常重启：

重启时候有问题，需要检查配置：

检查master服务状态

sudo mysql
mysql> show master status;

要分别检查服务器1和服务器2的master状态，并记住File和Position的值，下面指定同步位置的时候，master_log_file要填写File的值，master_log_pos要填写Postition的值。

配置主从

配置master参数

如果只是想要主从复制，比如服务器1是主服务器，服务器2是从服务器,只从主服务器复制数据到从服务器，那么此处只需要指定服务器2的同步位置。

• 服务器1

mysql> stop slave;
mysql> change master to master_host='192.168.200.2',master_user='syn',master_password='123456', master_log_file='backuplog.000001',master_log_pos=156;
mysql> start slave;

• 服务器2

mysql> stop slave;
mysql> change master to master_host='192.168.200.1',master_user='syn',master_password='123456', master_log_file='backuplog.000001',master_log_pos=156;
mysql> start slave;

检查slave状态

分别检查两台服务器的slave状态：

查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no或其他，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

show slave status\G;

同步测试

创建表

• 在服务器1创建表

不要复制这个代码

mysql> use mytest;
mysql> CREATE TABLE tb_by_1(
	->id INT NOT NULL AUTO_INCREMENT,
	->dtext VARCHAR(20) NOT NULL,
	->PRIMARY KEY (id)
	->)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在服务器2上查看是否有这个表了：

	mysql> use mytest;
	mysql> show tables;

已经有了。

• 在服务器2上创建表
  
  在服务器1上查询：
  
  都能查询到了。

数据测试

• 在服务器1上往表1插入数据

INSERT INTO tb_by_1 (dtext) VALUES ('input by s1');

在服务器2上查询：

也可以查到了。

• 其他测试略

参考文献

Linux安装Mysql8.0.20并配置主从复制（一主一从，双主双从)
Windows配置MySQL双机热备