CentOS 7.4 MySQL 5.7.20主从环境搭建(M-S)

内容预览:
  • 在事件写入二进制日志完成后,master通知存储引擎提交事务~
  • 二,slave将master的binary log拷贝到它自己的中继日志~
  •   #因此主上要定期做mysqldump备份~

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

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

[[email protected] ~]# iptables -F
[[email protected] ~]# systemctl stop firewalld
[[email protected] ~]# systemctl disable firewalld
[[email protected] ~]# systemctl stop NetworkManager
[[email protected] ~]# 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 

安装

[[email protected] ~]# 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数据库卸载,

[[email protected] ~]# 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

完毕!

[[email protected] ~]# 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 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服务初始化(主从服务器均进行同样操作)

[[email protected] ~]# systemctl start mysqld

[[email protected] ~]# grep “password” /var/log/mysqld.log   查询mysql初始密码,
2018-05-12T14:50:13.167604Z 1 [Note] A temporary password is generated for [email protected]: Lil=#:VjS8*Z  (初始密码)

[[email protected] ~]# 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:    输入新密码  [email protected]

Re-enter new password:   重复一遍确认 [email protected]
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:  再次输入  [email protected]

Re-enter new password:  再次输入  [email protected]

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主服务器,创建一个测试数据库及表,并创建一个授权账号进行主从数据同步

[[email protected] ~]# mysql -uroot -p’[email protected]’  使用新密码登录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)

Copyright (c) 2000, 2017, 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>

mysql> show databases;  查看当前数据库
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)

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

mysql> show databases; 
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |  新建数据库
+——————–+
5 rows in set (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  密码:[email protected]*qw92!derS   

mysql> grant replication slave on *.* to [email protected] identified by “[email protected]*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    不可以被从服务器复制的库

[[email protected] ~]# systemctl restart mysqld  重启数据库服务器

[[email protected] ~]# mysql -uroot -p’[email protected]’  登录数据库
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)

Copyright (c) 2000, 2017, 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>

mysql> show master status;  查看数据库状态信息
+————————-+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+————————-+———-+————–+——————+——————-+
| mysql-bin-master.000001 | 154 | test | mysql | |
+————————-+———-+————–+——————+——————-+
1 row in set (0.00 sec)

mysql> show binlog eventsG
*************************** 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’[email protected]′ test >test.sql  导出master测试数据库test

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

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

[[email protected] ~]# 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

 

配置从服务器

[[email protected] ~]# mysql -uslave -p’[email protected]*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)

Copyright (c) 2000, 2017, 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>

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

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

mysql> exit;  退出master数据库
Bye

 

[[email protected] ~]# mysql -uroot -p’[email protected]’  登录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)

Copyright (c) 2000, 2017, 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>

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.01 sec)

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

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

[[email protected] ~]# mysql -uroot -p’[email protected]   登录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)

Copyright (c) 2000, 2017, 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;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+——————–+
5 rows in set (0.00 sec)

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
[[email protected] ~]# systemctl stop mysqld  停掉slave数据库服务
[[email protected] ~]# vim /etc/my.cnf  编辑slave服务器数据库配置文件末尾添加一行 server-id=2 (从服务器ID号,不要和主ID相同 ,如果设置多个从服务器,每个从服务器必须有一个唯一的server-id值,必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址:这些ID值能唯一识别复制服务器群集中的每个服务器实例。)

[[email protected] ~]# systemctl start mysqld  启动mysql服务
[[email protected] ~]# mysql -uroot -p’[email protected]   登录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)

Copyright (c) 2000, 2017, 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> 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=’[email protected]*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 statusG;          #用show slave statusG;看一下从服务器的同步情况

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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

 

重启从服务器,再查看状态:

停止从服务器slave stop;

开启从服务器slave start;

排错思路:

1、二进制日志没有开启

2、IPTABLES 没有放开端口

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

SQL线程出错

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

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

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

以上就是:CentOS 7.4 MySQL 5.7.20主从环境搭建(M-S) 的全部内容。

本站部分内容来源于互联网和用户投稿,如有侵权请联系我们删除,谢谢。
Email:[email protected]


0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论