mysql 5.7主从配置

1、MySQL主从复制简介

MySQL主从复制（也称A/B复制、Replication）,简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致分为三个步骤：
1）主将更改操作记录到binlog里；
2）从将主的binlog事件（SQL语句）同步到从本机上，并记录在relaylog里；
3）从根据relaylog里面的SQL语句按顺序执行；
主上有一个log dump线程，用来和从的I/O线程传递binlog
从上有两个线程，其中I/O线程，用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。

主从配置需要注意的点

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

2、准备工作

2.1 系统及软件准备

操作系统: CentOS7.8
MySQL版本：5.7.30
Master服务器：192.168.10.243
Slave服务器：192.168.10.143

2.2、MySQL服务器操作系统配置

# 关闭主、从服务器防火墙
systemctl stop firewalld && systemctl disable firewalld

# 关闭主备服务器SELinux
# 关闭SELinux
# 查看SELinux状态
sestatus –v
getenforce

# 临时关闭SELinux
setenforce 0
# 永久关闭SELinux（需要重启操作系统）
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

# 优化文件句柄
# 打开文件的限制
vim /etc/security/limits.conf 
*     soft    nofile  65536
*     hard    nofile  131072
*     -       nproc   unlimited
*     soft    memlock unlimited
*     hard    memlock unlimited

# 优化内核参数
修改/etc/sysctl.conf文件
# 增加tcp支持的队列数
# 减少断开连接时 ，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.tcp_max_syn_backlog = 1024000

2.3、安装 MySQL-5.7.30　　

Master、Slave节点安装MySQL

# MySQL软件下载
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-client-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-common-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-devel-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-server-5.7.30-1.el7.x86_64.rpm

# 安装MySQL：
yum -y localinstall *.rpm 

或者配置yum源，直接使用yum命令安装MySQL

vim /etc/yum.repo.d/mysql.repo
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0

# 保存退出，执行
yum clean all
yum makecache
# 安装 mysql-community-server
yum -y install mysql-community-server

2.4、启动MySQL服务，并将服务加入开机启动

systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld

2.5、登录mysql并初始化root密码

# 登录mysql
说明：mysql在安装完成，启动mysqld服务后，MySQL会自动生成一个随机的root用户密码，可以到日志里面查看。
vim /var/log/mysqld.log

# 可以看到类似如下信息：
2020-03-03T06:24:40.573789Z 1 [Note] A temporary password is generated for root@localhost: p5+yU>E*q8hh

# 说明：用该临时密码登录mysql后，必须要先修改密码，如下：
[root@aliy-prod-pubser-server001 opt]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.7.26

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = PASSWORD('6ps?3n2vPcXjhUfz');

# 如果从服务器是克隆的主服务器，则修改 auto.cnf（vi /var/lib/mysql/auto.cnf） 文件中 server-uuid 值
否则主从复制会报 1593 错误，修改完记得重启MySQL

3、MySQL主从复制配置

3.1、master服务器配置

1）用户添加及授权

在master 服务器授权一个账户，拥有slave权限

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.10.143' IDENTIFIED BY 'GrantT^Slave143';
mysql> FLUSH PRIVILEGES;

说明: 192.168.10.143为备库IP，GrantT^Slave143为账号repl的密码，配置时修改成实际环境的信息。

3.2. my.cnf配置修改

修改mysql配置文件，my.ini （windows）或 my.cnf（Linux）找到[mysqld] 标记，下方添加

server-id=1
log-bin=master-bin
binlog-format=ROW
# 以下两个为可选选项，如果要保证数据不丢失最好在清理binlog之前把要清理的日志备份，日志文件的大小根据磁盘的性能做适当的调整
expire_logs_days = 7 // binlog过期清理时间
max_binlog_size = 1G // binlog每个日志文件大小

max_connections = 1000
character-set-server = utf8mb4 // 配置字符集
collation-server = utf8mb4_unicode_ci // 配置字符集
// 配置不同步的库
binlog-ignore-db = mysql
binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

3.3 重启master 的mysql服务

systemctl restart mysqld

4、备库配置

在另一个mysql上配置从服务器

4.1 备库my.cnf修改

找到my.ini （windows） 或 my.cnf （linux），找到[mysqld]标记，更改如下配置

[mysqld]
max_connections = 1000
log_bin = slave-bin
server_id = 2
relay_log = slave-relay-bin
log_slave_updates = 1
read_only = 1
#以下两个为可选选项，如果要保证数据不丢失最好在清理binlog之前把要清理的日志备份，日志文件的大小根据磁盘的性能做适当的调整
expire_logs_days = 7 //binlog过期清理时间
max_binlog_size = 100m //binlog每个日志文件大小

replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys

重启mysql使配置生效

systemctl restart mysqld

5、开启复制

5.1、查看主库binlog信息

登陆主库执行

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

执行之后可以获取目前主库binlog使用的文件及pos点　　

5.2、在从库上设置复制信息

根据上一步获取到的信息（Position, File）配置从库的复制信息，在从库上执行如下命令

CHANGE MASTER TO MASTER_HOST='192.168.10.243',MASTER_USER='repl',MASTER_PASSWORD='GrantT^Slave143',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=154;

注意：执行从库配置, 每次重启mysql均需要执行（如果没有开启自动启动主从）

参数解释：

解释：
// master的ip地址
mysql> change master to master_host='192.168.10.243',
// master授权的用户
master_user='repl',
// master的授权用户密码
master_password='GrantT^Slave143',

// master的binlog日志名称，这里使用上述命令搜索出来的为准
master_log_file='edu-mysql-bin.00000x',
// master的日志位置 这里使用上述“1.3”命令搜索出来的为准，不能带引号，必须是整型，否则会报错
master_log_pos=xx,
// 重试时间、单位秒，默认重试时间为 60s
master_connect_retry=30;

5.3、在从库上开启复制

从库复制信息配置完成后执行命令开启复制：
mysql> start slave;

5.4、查看复制状态

启动之后如果不报错即可执行如下命令查看复制的状态：
show slave status G;

主要查看下面两个参数状态，只要都是yes，表示主从通信正常。
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果均为yes则正常，否则需根据last_error信息进行调试

6、主从复制测试

# 创建数据库
create database mydb;

# 创建表
use mydb;
create table mytab1(id int(10),name varchar(50),depid int(11));

# 插入数据
insert into mytab1 (id, name, depid) values (1,'mcb', 2),(2,'zhangsan',3);

查看主库数据库，表，数据

查看从库数据库，表，数据

7、主从同步可能遇到的问题

基于局域网的Master/Slave机制在通常情况下已经可以满足“实时”备份的要求了。如果延迟比较大，可以从以下几个因素进行排查：
(1) 网络延迟；
(2) Master负载过高；
(3) Slave负载过高；

如果出现同步失败，可以根据提示处理错误，处理完成后，需要刷新同步配置：
先停止同步
mysql> stop slave;

清理掉之前的配置，防止同步已经同步了的数据
mysql> reset slave all;

然后重新连接主库，进行同步。