MariaDB系列之二：基于日志（binlog）主从复制（Master-Slave）

---

title: MariaDB系列之二：基于日志（binlog）主从复制（Master-Slave）
categories: Linux
tags:
- MariaDB
- MySQL
timezone: Asia/Shanghai
date: 2019-02-01

简介

MySQL5.6 开始主从复制有两种方式：基于日志（binlog）、基于 GTID（全局事务标示符）。 本文主要讲基于日志（binlog）的复制。

MySQL 主从复制（也称 A/B 复制）的原理

(1) Master 将数据改变记录到二进制日志(binary log)中，也就是配置文件 log-bin 指定的文件， 这些记录叫做二进制日志事件(binary log events)；
(2) Slave 通过 I/O 线程读取 Master 中的 binary log events 并写入到它的中继日志(relay log)； 
(3) Slave 重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完 成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)。

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致； 
(2) Master 和 Slave 数据库的版本要一致； 
(3) Master 和 Slave 数据库中的数据要一致；  
(4) Master 开启二进制日志，Master 和 Slave 的 server_id 在局域网内必须唯一； 

注意：实际生产环境中大数据量（超 2G 数据）的备份，建议不要使用 mysqldump 进行 比分，因为会非常慢。此时推荐使用 XtraBackup 进行备份。

环境

[root@centos181001 ~]# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

主节点：11.11.11.61
从节点：11.11.11.62

第一步：关闭系统默认防火墙（by all）

setenforce 0
sed -i -r "/^SELINUX=/c SELINUX=disabled" /etc/selinux/config
which systemctl && systemctl stop firewalld
which systemctl && systemctl disable firewalld
which systemctl && systemctl stop iptables || service iptables stop
which systemctl && systemctl disable iptables || chkconfig iptables off

第二步：安装MariaDB并设置开机自动启动（by all）

# 1.安装
yum install mariadb mariadb-server mariadb-libs mariadb-devel -y

# 2.启动MariaDB并设置开机自动启动
systemctl start mariadb
systemctl status mariadb
systemctl enable mariadb

# 3.初始化数据库
mysql_secure_installation
    Enter current password for root (enter for none):       # 输入密码，默认为空
    Set root password? [Y/n] y                              # 是否设置root密码
    Remove anonymous users? [Y/n] y                         # 是否移除anonymous用户
    Disallow root login remotely? [Y/n] n                   # 是否禁止远程登录
    Remove test database and access to it? [Y/n] y          # 是否移除默认的演示数据库
    Reload privilege tables now? [Y/n] y                    # 是否重新加载权限表？

第三步：Master节点设置

1.修改 Master 的配置文件/etc/my.cnf.d/server.cnf

# 编辑并在[mysqld]中增加以下内容
vim /etc/my.cnf.d/server.cnf

# 设置 server_id，一般设置为 IP
server_id=61
# 复制过滤：需要备份的数据库，输出 binlog
binlog-do-db=test
# 复制过滤：不需要备份的数据库，不输出（mysql 库一般不同步）
binlog-ignore-db=mysql
# 开启二进制日志功能，可以随便取，最好有含义
log-bin=test-mysql-bin
## 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
# 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062

2.重启 Master 数据库服务，创建数据同步用户并授予相应的权限

# 1.重启数据库服务
systemctl restart mariadb

# 2.创建同步用户并授权
# 登录数据库
mysql -uroot -pxiaoliu

# 创建用户
MariaDB [(none)]> grant replication slave, replication client on *.* to 'admin'@'11.11.11.62' identified by 'xiaoliu';

# 刷新权限
MariaDB [(none)]> flush privileges;

# 查看主节点状态
show master status;

3.创建测试数据库、表并写入一定数据，用于模拟现有的业务系统数据库

mysql -uroot -pxiaoliu
MariaDB [(none)]> create database test;
MariaDB [(none)]> use test; 
MariaDB [(none)]> create table linux(username varchar(15) not null,password varchar(15) not null);
MariaDB [(none)]> insert into linux values ('XiaoMing', 'xiaoliu');
MariaDB [(none)]> insert into linux values ('XiaoHong', '12346'), ('HongHong', '12346');
MariaDB [(none)]> commit;
MariaDB [(none)]> select * from linux;

4.备份新建立的test数据库并复制到从服务器

# 1.先临时锁表
MariaDB [test]> flush tables with read lock;

# 2.备份数据库
[root@centos181001 ~]# mysqldump -u root -pxiaoliu test >/home/test.sql

# 3.解锁表
mysql -uroot -pxiaoliu
MariaDB [(none)]> unlock tables; 

# 4.将 Master 上备份的数据远程传送到 Slave 上，以用于 Slave 配置时恢复数据
[root@centos181001 ~]# scp /home/test.sql root@11.11.11.62:/home/

第四步：Slave 节点设置

1.修改 Slave 的配置文件/etc/my.cnf.d/server.cnf

# 编辑并在[mysqld]中增加以下内容
vim /etc/my.cnf.d/server.cnf

# 设置 server_id，一般设置为 IP
server_id=62
# 复制过滤：需要备份的数据库，输出binlog
binlog-do-db=test
# 复制过滤：不需要备份的数据库，不输出（mysql 库一般不同步）
binlog-ignore-db=mysql
# 开启二进制日志，以备 Slave 作为其它 Slave 的 Master 时使用
log-bin=mysql-slave1-bin
# 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存 binlog_cache_size = 1M
# 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
# 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# relay_log 配置中继日志
relay_log=lyz-mysql-relay-bin
## log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 防止改变数据(除了特殊的线程)
read_only=1

2.重启MariaDB并恢复主节点备份的数据库

# 1.重启数据库服务
systemctl restart mariadb

# 2.创建相同名字的数据库
mysql -uroot -pxiaoliu
create database test;

# 3.恢复数据
mysql -uroot -pxiaoliu test </home/test.sql

# 4.登录数据库查看是否恢复成功
mysql -uroot -pxiaoliu
select * from test.linux;

3.登录Slave数据库,添加相关参数

mysql -uroot -pxiaoliu
MariaDB [(none)]> change master to master_host='11.11.11.61',
master_user='admin',
master_password='xiaoliu',
master_port=3306,
master_log_file='test-mysql-bin.000001',
master_log_pos=1152,
master_connect_retry=30;

    # 参数说明
    master_host='11.11.11.61'：主节点的IP地址
    master_user='admin'：刚才在Master建立的用于同步数据库的用户
    master_password='xiaoliu'：主节点同步用户的密码
    master_port=3306：Master节点数据库的端口
    master_log_file='lyz-mysql-bin.000001'：指定Slave从哪个日志文件开始读取复制文件(可在Master上使用show master status查看到日志文件名)
    master_log_pos=1312：从哪个POSITION号开始读(可在Master上使用show master status查看）
    master_connect_retry=30：当重新建立主从连接时，如果连接建立失败，间隔多久后重试,单位为秒，默认设置为60秒，同步延迟调优参数。

4.查看同步状态

# 1.查看同步状态
show slave status\G;

    # 可看到Slave_IO_State为空
    # Slave_IO_Runngin和Slave_SQL_Running是No
    # 表示Slave还是没有开始复制过程。

# 2.开启主从同步
start slave;

# 3.再次查看状态
show slave status\G;

    # 主要查看以下3个状态
       Slave_IO_State: Waiting for master to send event
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

# 4.可以分别在主从节点查看I/O线程创建的连接
show processlist\G;

    1.row为处理slave的I/O线程的连接。
    2.row为处理MySQL客户连接线程。
    3.row为处理本地命令行的线程

第五步：测试

1. 在主节点写入数据

mysql -uroot -pxiaoliu
use test;
insert into linux values ('aabbcc', '12346');
insert into linux values ('aabbcc', '12346');
insert into linux values ('aabbcc', '12346');
insert into linux values ('aabbcc', '12346');
insert into linux values ('aabbcc', '12346');
commit;

2.到从节点查看是否有同步过来

mysql -uroot -pxiaoliu
select * from test.linux;

附录：增加新的从节点

只需要按第三步里的从节点设置并在主节点设置同步账户允许此节点访问即可。

附录：如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：

(1)主数据库进行锁表操作，不让数据再进行写入动作

FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态

show master status;

(3)记录下 FILE 及 Position 的值。

将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
UNLOCK TABLES;