MySQL：1.基于日志（binlog）主从复制（Master-

---

title: MySQL：1.基于日志（binlog）主从复制（Master-Slave）
categories: 数据库
tags:
- MySQL
timezone: Asia/Shanghai
date: 2019-03-10

---

环境

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

[root@centos181002 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

第零步：关闭系统默认防火墙（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

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

1.安装MySQL

cat <<EOF >/etc/yum.repos.d/mysql-community.repo 
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
EOF

yum install -y mysql-community-server

2.启动和初始化

systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld

grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation

第三步：Master节点设置

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

注意修改server_id、binlog-do-db、binlog-ignore-db、log-bin、expire_logs_days

# 编辑并在[mysqld]中增加以下内容
cp /etc/my.cnf /etc/my.cnf.bak
vi /etc/my.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 mysqld

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

# 创建用户
grant replication slave, replication client on *.* to 'admin'@'11.11.11.62' identified by 'Xiaoliu123!';

# 刷新权限
flush privileges;

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

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

mysql -uroot -pXiaoliu123!
create database test;
use test; 
create table linux(username varchar(15) not null,password varchar(15) not null);
insert into linux values ('XiaoMing', 'xiaoliu');
insert into linux values ('XiaoHong', '12346'), ('HongHong', '12346');
commit;
select * from linux;
exit

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

# 1.先临时锁表
mysql -uroot -pXiaoliu123!
flush tables with read lock;

# 2.记录主节点File和Position
show master status;

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

# 4.解锁表
mysql -uroot -pXiaoliu123!
unlock tables; 

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

第四步：Slave 节点设置

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

注意修改：、server_id、binlog-do-db、binlog-ignore-db、log-bin、relay_log

# 新建并编辑并在[mysqld]中增加以下内容
cp /etc/my.cnf /etc/my.cnf.bak
vi /etc/my.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.重启Slave并恢复主节点备份的数据库

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

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

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

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

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

注意修改：、master_host、master_log_file、master_log_pos

mysql -uroot -pXiaoliu123!
change master to master_host='11.11.11.61',
master_user='admin',
master_password='Xiaoliu123!',
master_port=3306,
master_log_file='test-mysql-bin.000001',
master_log_pos=1612,
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 -pXiaoliu123!
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 -pXiaoliu123!
select * from test.linux;

附录：增加新的从节点

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

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

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

FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态

show master status;

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

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

(4)取消主数据库锁定

UNLOCK TABLES;