共计 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 行业资讯频道！
