CentOS 7.4下MySQL 5.7.20主从环境搭建（M-S）

共计 15727 个字符，预计需要花费 40 分钟才能阅读完成。

MySQL 主从原理：

一，master 记录二进制日志，在每个事务更新数据完成之前，master 在二进制日志中记录这些改变、mysql 将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master 通知存储引擎提交事务。

二，slave 将 master 的 binary log 拷贝到它自己的中继日志。首先，slave 开始一个工作线程——I/ O 线程。I/ O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。Binlog dump process 从 master 的二进制日志中读取事件，如果已经执行完 master 产生的所有文件，它会睡眠并等待 master 产生新的事件。I/ O 线程将这些事件写入中继日志。

三，SQL slave thread（SQL 从线程）处理该过程的最后一步。SQL 线程从中继日志读取事件，并重新执行其中的事件而更新 slave 的数据，使其与 master 中的数据一致。

主从配置：

主机名 IP 系统版本 mysql 版本角色

mysqlmaster.cn 10.10.10.69 CentOS Linux release 7.4.1708 (Core) 5.7.20 master

mysqlslave.cn 10.10.10.72 CentOS Linux release 7.4.1708 (Core) 5.7.20 slave

步骤一、主从服务环境初始化

[root@mysqlmaster ~]# iptables -F
[root@mysqlmaster ~]# systemctl stop firewalld
[root@mysqlmaster ~]# systemctl disable firewalld
[root@mysqlmaster ~]# systemctl stop NetworkManager
[root@mysqlmaster ~]# systemctl disable NetworkManager

步骤二，安装 mysql

下载地址：https://downloads.mysql.com/archives/community/

下载相应软件包后解压并把安装包上传到服务器

安装以下软件包，顺序从上到下，注意顺序错误会报错！！！！

mysql-community-common-5.7.20-1.el7.x86_64.rpm
mysql-community-libs-5.7.20-1.el7.x86_64.rpm

mysql-community-client-5.7.20-1.el7.x86_64.rpm

mysql-community-server-5.7.20-1.el7.x86_64.rpm

安装

[root@mysqlmaster ~]# yum -y install mysql-community-common-5.7.20-1.el7.x86_64.rpm
已加载插件：fastestmirror, langpacks
正在检查 mysql-community-common-5.7.20-1.el7.x86_64.rpm: mysql-community-common-5.7.20-1.el7.x86_64
mysql-community-common-5.7.20-1.el7.x86_64.rpm 将被安装
正在解决依赖关系
–> 正在检查事务
—> 软件包 mysql-community-common.x86_64.0.5.7.20-1.el7 将被安装
–> 解决依赖关系完成

依赖关系解决

========================================================================================
Package 架构版本源大小
========================================================================================
正在安装:
mysql-community-common
x86_64 5.7.20-1.el7 /mysql-community-common-5.7.20-1.el7.x86_64 2.5 M

事务概要
========================================================================================
安装 1 软件包

总计：2.5 M
安装大小：2.5 M
Downloading packages:
Running transaction check
Running transaction test

Transaction check error:
file /usr/share/mysql/charsets/swe7.xml from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64

错误概要
————-

如上所示，出现了错误导致无法安装，但从错误信息可以看到，由于已经安装了 mariadb 数据库导致无法再安装 mysql 数据库，那就先把 mariadb 数据库卸载，

[root@mysqlmaster ~]# yum -y remove mariadb*（主从服务器均要卸载系统自带的 mariadb 数据库后才能安装 mysql）
已加载插件：fastestmirror, langpacks
正在解决依赖关系
–> 正在检查事务
—> 软件包 mariadb-libs.x86_64.1.5.5.56-2.el7 将被删除
–> 正在处理依赖关系 libmysqlclient.so.18()(64bit)，它被软件包 2:postfix-2.10.1-6.el7.x86_64 需要
–> 正在处理依赖关系 libmysqlclient.so.18(libmysqlclient_18)(64bit)，它被软件包 2:postfix-2.10.1-6.el7.x86_64 需要
–> 正在检查事务
—> 软件包 postfix.x86_64.2.2.10.1-6.el7 将被删除
–> 解决依赖关系完成

依赖关系解决

========================================================================================
Package 架构版本源大小
========================================================================================
正在删除:
mariadb-libs x86_64 1:5.5.56-2.el7 @anaconda 4.4 M
为依赖而移除:
postfix x86_64 2:2.10.1-6.el7 @anaconda 12 M

事务概要
========================================================================================
移除 1 软件包 (+1 依赖软件包)

安装大小：17 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在删除 : 2:postfix-2.10.1-6.el7.x86_64 1/2
正在删除 : 1:mariadb-libs-5.5.56-2.el7.x86_64 2/2
验证中 : 1:mariadb-libs-5.5.56-2.el7.x86_64 1/2
验证中 : 2:postfix-2.10.1-6.el7.x86_64 2/2

删除:
mariadb-libs.x86_64 1:5.5.56-2.el7

作为依赖被删除:
postfix.x86_64 2:2.10.1-6.el7

完毕！

[root@xuegodslave ~]# yum -y install mysql-community-common-5.7.20-1.el7.x86_64.rpm
已加载插件：fastestmirror, langpacks
正在检查 mysql-community-common-5.7.20-1.el7.x86_64.rpm: mysql-community-common-5.7.20-1.el7.x86_64
mysql-community-common-5.7.20-1.el7.x86_64.rpm 将被安装
正在解决依赖关系
–> 正在检查事务
—> 软件包 mysql-community-common.x86_64.0.5.7.20-1.el7 将被安装
–> 解决依赖关系完成

依赖关系解决

事务概要
========================================================================================
安装 1 软件包

总计：2.5 M
安装大小：2.5 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : mysql-community-common-5.7.20-1.el7.x86_64 1/1
验证中 : mysql-community-common-5.7.20-1.el7.x86_64 1/1

已安装:
mysql-community-common.x86_64 0:5.7.20-1.el7

完毕！

如上所示已经安装成功第一个包，同样的操作把剩下的三个包安装完毕！（主从服务器均进行同样操作）

步骤三，启动 MySQL 服务，并进行 MySQL 服务初始化（主从服务器均进行同样操作）

[root@mysqlmaster ~]# systemctl start mysqld

[root@mysqlmaster ~]# grep “password” /var/log/mysqld.log 查询 mysql 初始密码，
2018-05-12T14:50:13.167604Z 1 [Note] A temporary password is generated for root@localhost: Lil=#:VjS8*Z （初始密码）

[root@mysqlmaster ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 这里输入：Lil=#:VjS8*Z

The existing password for the user account root has expired. Please set a new password.

New password: 输入新密码 zX@987Weqqrd1

Re-enter new password: 重复一遍确认 zX@987Weqqrd1
The ‘validate_password’ plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y 修改密码确认

New password: 再次输入 zX@987Weqqrd1

Re-enter new password: 再次输入 zX@987Weqqrd1

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y 确认修改
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y 移除匿名用户
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y 不允许 root 远程登录
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y 删除测试数据库
– Dropping test database…
Success.

– Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y 刷新权限表
Success.

All done!

说明：MySQL 服务要求密码强度及复杂的都十分严格，如果需要使用简单密码可以修改系统配置文件 /etc/my.cnf 加入 validate_password=off 然后重启 mysql 服务进行修改就可以使用简单密码（以上步骤主从服务器均要执行）

步骤四、登录 MySQL 主服务器，创建一个测试数据库及表，并创建一个授权账号进行主从数据同步

[root@mysqlmaster ~]# mysql -uroot -p’zX@987Weqqrd1’ 使用新密码登录 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 MySQL Community Server (GPL)

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>

mysql> create database test; 创建一个名为 test 的数据库
Query OK, 1 row affected (0.00 sec)

mysql> use test; 切换到 test 数据库

Database changed
mysql> show tables; 查询当前数据库表
Empty set (0.00 sec)

mysql> create table test1(id int,name varchar(20)); 创建一个测试表
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| test1 |
+—————-+
1 row in set (0.00 sec)

创建授权账号：slave 指定从服务器 IP：10.10.10.72 密码：A@*qw92!derS

mysql> grant replication slave on *.* to slave@10.10.10.72 identified by “A@*qw92!derS”;
Query OK, 0 rows affected, 1 warning (0.00 sec)

步骤五、主从服务器配置：

修改 master 系统配置文件 /etc/my.cnf 末尾加入一下内容

log-bin=mysql-bin-master 启用二进制日志

server-id=1 本机数据库 ID 标示

binlog-do-db=test 可以被从服务器复制的库, 二进制需要同步的数据库名（创建的测试数据库）

binlog-ignore-db=mysql 不可以被从服务器复制的库

[root@mysqlmaster ~]# systemctl restart mysqld 重启数据库服务器

[root@mysqlmaster ~]# mysql -uroot -p’zX@987Weqqrd1’ 登录数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log MySQL Community Server (GPL)

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>

mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: mysql-bin-master.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.20-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin-master.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
2 rows in set (0.00 sec)

如上所示，master 服务器已配置成功

mysqldump -uroot -p’zX@987Weqqrd1′ test >test.sql 导出 master 测试数据库 test

[root@mysqlmaster ~]# mysqldump -uroot -p’zX@987Weqqrd1′ test >test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysqlmaster ~]# ll test.sql
-rw-r–r– 1 root root 1790 5 月 13 00:01 test.sql

将导出的数据库传到 slave 服务器上

[root@mysqlmaster ~]# scp test.sql 10.10.10.72:/root
The authenticity of host ‘10.10.10.72 (10.10.10.72)’ can’t be established.
ECDSA key fingerprint is SHA256:cHQticA8/IMXFPFXspEnN0h4FLG7LaXnT8Zpr7ricrA.
ECDSA key fingerprint is MD5:1a:70:c5:60:05:47:6a:75:8a:47:db:85:51:1c:32:2c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘10.10.10.72’ (ECDSA) to the list of known hosts.
root@10.10.10.72’s password:
test.sql 100% 1790 1.9MB/s 00:00

配置从服务器

[root@xuegodslave ~]# mysql -uslave -p’A@*qw92!derS’ -h 10.10.10.69 在 slave 服务器上使用授权账号 slave 登录 master mysql 测试授权账号能否登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.20-log MySQL Community Server (GPL)

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>

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
+——————–+
1 row in set (0.00 sec)

如上所示查询不到 test 测试数据库

mysql> exit; 退出 master 数据库
Bye

[root@xuegodslave ~]# mysql -uroot -p’zX@987Weqqrd1’ 登录 slave 服务器，密码初始化与 master 相同
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.20 MySQL Community Server (GPL)

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>

mysql> create database test; 创建测试数据库
Query OK, 1 row affected (0.01 sec)

mysql> exit; 退出数据库
Bye
[root@xuegodslave ~]# mysql -uroot -p’zX@987Weqqrd1′ test<test.sql 将 master 导出的数据库内容导入 slave 创建的 test 数据库内
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@xuegodslave ~]# mysql -uroot -p’zX@987Weqqrd1′ 登录 slave 服务器
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.20 MySQL Community Server (GPL)

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> use test; 切换到 test 数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables; 查看数据库内容
+—————-+
| Tables_in_test |
+—————-+
| test1 | 已存在 master 服务器创建的表 test1，导入成功
+—————-+
1 row in set (0.00 sec)

mysql> exit;
Bye
[root@xuegodslave ~]# systemctl stop mysqld 停掉 slave 数据库服务
[root@xuegodslave ~]# vim /etc/my.cnf 编辑 slave 服务器数据库配置文件末尾添加一行 server-id=2（从服务器 ID 号，不要和主 ID 相同，如果设置多个从服务器，每个从服务器必须有一个唯一的 server-id 值，必须与主服务器的以及其它从服务器的不相同。可以认为 server-id 值类似于 IP 地址：这些 ID 值能唯一识别复制服务器群集中的每个服务器实例。）

[root@xuegodslave ~]# systemctl start mysqld 启动 mysql 服务
[root@xuegodslave ~]# mysql -uroot -p’zX@987Weqqrd1′ 登录 mysql 服务器
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

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> stop slave; 停止 slave
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host=’10.10.10.69′,master_user=’slave’,master_password=’change master to master_host=’10.10.10.69′,master_user=’slave’,master_password=’A@*qw92!derS’;（授权 slave 服务器同步 master，指定账号和密码）
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave; 启动 slave
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G 查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.69
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000001
Read_Master_Log_Pos: 448
Relay_Log_File: xuegodslave-relay-bin.000002
Relay_Log_Pos: 675
Relay_Master_Log_File: mysql-bin-master.000001
Slave_IO_Running: Yes 一个负责与主机的 io 通信

Slave_SQL_Running: Yes 负责自己的 slave mysql 进程

如上图所示，主从服务器均运行并连接成功

再到主服务器上查看状态：

最后在 master 服务器中的 test 数据库中插入数据进行同步测试：

测试成功，主从服务器已成功同步数据！

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

如果遇到主从不同步，看一下主从 bin-log 的位置，然后再同步。

在主服务器上看二进制日志事件列表

mysql> show binlog events \G

从服务器执行 MySQL 命令下：

mysql> stop slave；#先停止 slave 服务

mysql> change master to master_log_file=’mysql-bin-master.000001′,master_log_pos=1164;

#根据上面主服务器的 show master status 的结果，进行从服务器的二进制数据库记录回归，达到同步的效果

mysql>slave start; #启动从服务器同步服务

mysql> show slave status\G; #用 show slave status\G; 看一下从服务器的同步情况

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果都是 yes，那代表已经在同步

重启从服务器，再查看状态：

停止从服务器 slave stop;

开启从服务器 slave start;

排错思路：

1、二进制日志没有开启

2、IPTABLES 没有放开端口

3、对应的主机 IP 地址写错了

SQL线程出错

1、主从服务器数据库结构不统一

出错后，数据少，可以手动解决创建插入，再更新 slave 状态。

注：如果主上误删除了。那么从上也就误删除了。#因此主上要定期做 mysqldump 备份。

推荐阅读：

Ubuntu 16.04 下实现 MySQL 主从复制 http://www.linuxidc.com/Linux/2017-08/146624.htm
MySQL 5.6.26 主从复制报错 1050 http://www.linuxidc.com/Linux/2017-08/146203.htm
MySQL 主从复制详解 http://www.linuxidc.com/Linux/2017-10/147550.htm
MySQL 基于 GTID 主从复制之半同步复制 https://www.linuxidc.com/Linux/2018-05/152175tm
MySQL 主从复制读写分离与高可用配置 https://www.linuxidc.com/Linux/2018-04/151893.htm
mysql-proxy 代理加 MySQL 主从实现读写分离 https://www.linuxidc.com/Linux/2017-05/143453.htm
CentOS 7.4 下 MySQL+Amoeba 实现主从同步读写分离 https://www.linuxidc.com/Linux/2018-03/151648.htm