[实验] MariaDB & MySQL 主从同步的搭建 (互为主从)

纪念:站主于 2019 年 11 月完成了此开源实验,并将过程中的所有命令经过整理和注释以后,形成以下教程

步骤一:规划拓扑

1.1 服务器列表

数据库服务器 21 IP 地址:192.168.1.21
数据库服务器 22 IP 地址:192.168.1.22

1.2 服务器列表简介

数据库服务器 21 和 数据库服务器 22 相互同步对方的数据

步骤二:系统环境要求

1) 所有服务器的系统都需要是 CentOS 7 版本
2) 所有服务器都要关闭防火墙
3) 所有服务器都要关闭 SELinux
4) 所有服务器系统都要配置好可用的软件源
5) 需要按照拓扑图给对应的服务器配置好 IP 地址和主机名
6) 所有服务器都要可以相互 ping 通自己和对方的 IP 地址和主机名

步骤三:所有数据库服务器安装 MariaDB 或 MySQL 数据库

3.1 所有数据库服务器安装 MariaDB 或 MySQL

(分别在数据库服务器 21 和数据库服务器 22 上执行以下步骤)

# yum -y install mariadb-server

(补充:这里以安装 MariaDB 数据库为例)

3.2 设置所有数据库服务器开机自启 MariaDB 或 MySQL

(分别在数据库服务器 21 和数据库服务器 22 上执行以下步骤)

# systemctl enable mariadb

(补充:这里以开机自启 MariaDB 数据库为例)

步骤四:配置 MairaDB & MySQL 互为主从结构

4.1 将数据库服务器 22 设置为数据库服务器 21 的从库

4.1.1 开启数据库服务器 21 的 server-id 和 binlog 日志

(只在数据库服务器 21 上执行以下步骤)

# vi /etc/my.cnf

将部分内容修改如下:

[mysqld]
server-id=1
log-bin=mariadb-bin
......


补充:这里以
1) 将 server-id 设置为 1
2) 启动 binlog 日志,并将 binlog 日志的前缀设置为 mariadb-bin
为例

(注意: 集群里的各个数据库的 server id 不能一样)

4.1.2 重启数据库服务器 21 的数据库

(只在数据库服务器 21 上执行以下步骤)

# systemctl restart mariadb

(补充:这里以重启 MariaDB 数据库为例)

4.1.3 在数据库服务器 21 的数据库中创建用于同步的用户
4.1.3.1 进入数据库

(只在数据库服务器 21 上执行以下步骤)

# mysql -p
4.1.3.2 创建数据库服务器 21 用于被数据库服务器 22 同步的 MariaDB 用户

(只在数据库服务器 21 上执行以下步骤)

> grant replication slave on *.* to 'backup'@'192.168.1.22' identified by 'backup';
4.1.3.3 刷新数据库服务器 21 里所有用户的权限

(只在数据库服务器 21 上执行以下步骤)

> flush privileges;
4.1.3.4 显示数据库服务器 21 的 MariaDB 的主库参数

(只在数据库服务器 21 上执行以下步骤)

> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000003 |      475 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

(补充:这里显示的 master_log_file 和 master_log_pos 的参数会在后面配置从库中使用)

4.1.4 让数据库服务器 22 同步数据库服务器 21
4.1.4.1 启动数据库服务器 22

(只在数据库服务器 22 上执行以下步骤)

# systemctl start mariadb

(补充:这里以启动 MariaDB 数据库为例)

4.1.4.2 进入数据库服务器 22 的数据库

(只在数据库服务器 22 上执行以下步骤)

# mysql -p
4.1.4.3 同步主库

(只在数据库服务器 22 上执行以下步骤)

> change master to master_host="192.168.1.21",master_user='backup',master_password='backup',master_log_file='mariadb-bin.000003',master_log_pos=475;
4.1.4.4 启动从库状态

(只在数据库服务器 22 上执行以下步骤)

> start slave;
4.1.4.5 显示从库状态

(只在数据库服务器 22 上执行以下步骤)

> show slave status\G;
          Master_Host: 192.168.1.21
              ......
          Slave_IO_Running: Yes  
          Last_IO_Error: ......
              ......
          Slave_SQL_Running: Yes
          Last_SQL_Error: ......
              ......

(补充:这里显示它的主服务器是 192.168.1.21)

(注意:这里要确保 Slave_IO_Running: 和 Slave_SQL_Running: 后面没有报错信息)

4.2 将数据库服务器 21 设置为数据库服务器 22 的从库

4.2.1 关闭数据库服务器 22 的数据库

(只在数据库服务器 22 上执行以下步骤)

# systemctl stop mariadb

(补充:这里以停止 MariaDB 数据库为例)

4.2.2 开启数据库服务器 21 的 server-id 和 binlog 日志

(只在数据库服务器 22 上执行以下步骤)

# vi /etc/my.cnf

(将部分内容修改如下)

[mysqld]
server-id=2
log-bin=mariadb-bin
......


补充:这里以
1) 将 server-id 设置为 2
2) 启动 binlog 日志,并将 binlog 日志的前缀设置为 mariadb-bin
为例

(注意: 集群里的各个数据库的 server id 不能一样)

4.2.3 启动数据库服务器 22 的数据库

(只在数据库服务器 22 上执行以下步骤)

# systemctl start mariadb

(补充:这里以重启 MariaDB 数据库为例)

4.2.4 在数据库服务器 22 的数据库中创建用于同步的用户
4.2.4.1 进入数据库

(只在数据库服务器 22 上执行以下步骤)

# mysql -p
4.2.4.2 创建数据库服务器 21 用于被数据库服务器 22 同步的 MariaDB 用户

(只在数据库服务器 22 上执行以下步骤)

> grant replication slave on *.* to 'backup'@'192.168.1.21' identified by 'backup';
4.2.4.3 刷新数据库服务器 22 数据库里所有用户的权限

(只在数据库服务器 22 上执行以下步骤)

> flush privileges;
4.2.4.4 显示数据库服务器 22 的 MariaDB 的主库参数

(只在数据库服务器 22 上执行以下步骤)

> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000003 |      475 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

(补充:这里显示的 master_log_file 和 master_log_pos 的参数会在后面配置从库中使用)

4.2.5 让数据库服务器 21 同步数据库服务器 22
4.2.5.1 进入数据库服务器 21 的数据库

(只在数据库服务器 21 上执行以下步骤)

# mysql -p
4.2.5.2 同步主库

(只在数据库服务器 21 上执行以下步骤)

> change master to master_host="192.168.1.22",master_user='backup',master_password='backup',master_log_file='mariadb-bin.000003',master_log_pos=475;
4.2.5.3 启动从库状态

(只在数据库服务器 21 上执行以下步骤)

> start slave;
4.2.5.4 显示从库状态

(只在数据库服务器 21 上执行以下步骤)

> show slave status\G;
          Master_Host: 192.168.1.22
              ......
          Slave_IO_Running: Yes  
          Last_IO_Error: ......
              ......
          Slave_SQL_Running: Yes
          Last_SQL_Error: ......
              ......

(补充:这里显示它的主服务器是 192.168.1.21)

(注意:这里要确保 Slave_IO_Running: 和 Slave_SQL_Running: 后面没有报错信息)

步骤五:测试 MariaDB & MySQL 互为主从集群

5.1 进入数据库

(分别在数据库服务器 21 和数据库服务器 22 上执行以下步骤)

# mysql -uroot -p

5.2 创建测试库

(只在数据库服务器 21 上执行以下步骤)

> create database test1;

5.3 进入测试库

(分别在数据库服务器 21 和数据库服务器 22 上执行以下步骤)

> use test1;

5.4 创建测试表

(只在数据库服务器 21 上执行以下步骤)

> create table test1a(id int(10),name char(100),age int(10));

(补充:这里随意创建了一张表格)

5.5 在数据库服务器 21 上插入测试数据

(只在数据库服务器 21 上执行以下步骤)

> insert into test1a(id,name,age) values('1','zmy','10');

(补充:这里随意插入了一条数据)

5.6 在数据库服务器 22 上插入测试数据

(只在数据库服务器 22 上执行以下步骤)

> insert into test1a(id,name,age) values('2','ming','20');

(补充:这里随意插入了一条数据)

5.7 在两个数据库里都可以看到对方插入的测试数据

(分别在数据库服务器 21 和数据库服务器 22 上执行以下步骤)

> select * from test1a;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zmy  |   10 |
|    2 | ming |   20 |
+------+------+------+
2 rows in set (0.00 sec)