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
-> t;
+------+-----------+
| 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)
###此配置保证其它服务器能够连接到主服务器，否则后面的从服务器配置复制时会失败！！！