CentOS 7.4 MySQL 5.7.20主從環境搭建（M-S）

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 將被安裝
--> 解決依賴關系完成

依賴關系解決

========================================================================================
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服務初始化（主從服務器均進行同樣操作）

[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備份。

posted @ 2018-05-13 01:19 daemonlu 閱讀(1052) 評論(0) 收藏舉報

刷新頁面返回頂部

daemonlu

CentOS 7.4 MySQL 5.7.20主從環境搭建（M-S）

公告