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

jianjian

2018-05-12

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 [email protected] 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.
[email protected]'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备份。

推荐阅读：

mysql mysql主从配置 centos 线程

安科网

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

jianjian

jianjian

相关推荐

MySQL主从复制原理以及需要注意的地方

MySQL 8.0.15配置MGR单主多从的方法

mysql主从复制配置过程

Golang操作MySql数据库的完整步骤记录

Mysql联表update数据的示例详解

MySQL数据类型优化原则

专业级的MySQL开发设计规范及SQL编写规范

Mysql 查询JSON结果的相关函数汇总

Mysql 实现字段拼接的三个函数

浅谈MySQL中的自增主键用完了怎么办

MySql索引使用策略分析

mysql 如何动态修改复制过滤器

MySQL ddl语句的使用

MySQL中使用binlog时格式该如何选择

mysql 8.0.22 安装配置图文教程

解决Navicat Premium 连接 MySQL 8.0 报错\"1251\"的问题分析

MySQL数据操作-DML语句的使用

详解 MySQL中count函数的正确使用方法

MySQL 基于时间点的快速恢复方案

MySQL外键约束的实例讲解

jianjian