共計 5490 個字符，預計需要花費 14 分鐘才能閱讀完成。
這篇文章主要介紹 CentOs7 中 mysql5.7 如何實現主從復制配置，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
MySQL5.7 主、從配置具體步驟：
點擊 (此處) 折疊或打開
1. 兩臺 MySQL 服務器
主：192.168.253.138 端口：1221
從：192.168.253.139 端口：1221
2. 修改主從配置參數
2.1 主服務器
## 添加入下內容
vi /etc/my.cnf
[mysqld]
log-bin = mysql-bin ## 必須啟用二進制格式日志
server-id=138 ##server-id 必須唯一，一般為 IP 的尾數
2.2 從服務器
## 添加入下內容
vi /etc/my.cnf
[mysqld]
log-bin = mysql-bin ## 啟用二進制格式日志，可選配置
server-id=139 ##server-id 必須唯一，一般為 IP 的尾數
3. 重啟主、從服務器使修改生效
主服務器（138）
[root@my01 ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
從服務器（139）
[root@my02 ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
4. 在主服務器上創建復制賬號并授權 slave
[root@my01 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19-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 grant replication slave on *.* to mysync @ % identified by mysql123
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql
5. 查看主服務器狀態
## 記錄 File 與 Position 的值，配置從服務器時使用，之后主服務器就不要做任何操作了，避免值的改變使得從服務器配置失敗
mysql show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 1306 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
6. 配置、啟動從服務器
[root@my02 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19-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 CHANGE MASTER TO MASTER_HOST= 192.168.253.138 , MASTER_PORT=1221, MASTER_USER= mysync , MASTER_PASSWORD= mysql123 , MASTER_LOG_FILE= mysql-bin.000001 , MASTER_LOG_POS=1306;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql start slave;
Query OK, 0 rows affected (0.01 sec)
7. 查看從服務器復制狀態
## 最主要的是查看 Slave_IO_Running: Yes，Slave_SQL_Running: Yes 這兩項是不是 YES，是則代表主從配置成功，否則失敗
mysql show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.253.138
Master_User: mysync
Master_Port: 1221
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1306
Relay_Log_File: my02-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1306
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 138
Master_UUID: dfb01359-857f-11e7-8ed4-000c2997411c
Master_Info_File: /data/db/mysql/1221/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql
8. 測試主、從情況
## 主服務器
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.02 sec)
mysql show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+——————–+
5 rows in set (0.00 sec)
mysql use test;
Database changed
mysql show tables;
Empty set (0.00 sec)
mysql create table t(id int,name varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql insert into t values (1, zhang san
Query OK, 1 row affected (0.07 sec)
mysql show tables;
+—————-+
| Tables_in_test |
+—————-+
| t |
+—————-+
1 row in set (0.00 sec)
mysql select * from
–
+——+———–+
| id | name |
+——+———–+
| 1 | zhang san |
+——+———–+
1 row in set (0.00 sec)
mysql insert into t values (2, li si
Query OK, 1 row affected (0.01 sec)
mysql
### 從服務器
mysql show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+——————–+
5 rows in set (0.00 sec)
mysql use 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 |
+—————-+
| t |
+—————-+
1 row in set (0.00 sec)
mysql select * from t;
+——+———–+
| id | name |
+——+———–+
| 1 | zhang san |
| 2 | li si |
+——+———–+
2 rows in set (0.00 sec)
mysql
#### 恭喜成功了！！！
## 注意：主服務器權限配置，如下：
mysql update user set user.Host= % where user.User= root
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql flush privileges;
Query OK, 0 rows affected (0.01 sec)
### 此配置保證其它服務器能夠連接到主服務器，否則后面的從服務器配置復制時會失敗！！！
以上是“CentOs7 中 mysql5.7 如何實現主從復制配置”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！
