CentOs7中mysql5.7如何實現主從復制配置

發布時間：2021-11-06 11:48:19 來源：億速云閱讀：172 作者：小新欄目：MySQL數據庫

這篇文章主要介紹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)
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)
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)
###此配置保證其它服務器能夠連接到主服務器，否則后面的從服務器配置復制時會失敗！！！

以上是“CentOs7中mysql5.7如何實現主從復制配置”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！

向AI問一下細節

91超碰碰碰碰久久久久久综合_超碰av人澡人澡人澡人澡人掠_国产黄大片在线观看画质优化_txt小说免费全本

CentOs7中mysql5.7如何實現主從復制配置

猜你喜歡

91超碰碰碰碰久久久久久综合_超碰av人澡人澡人澡人澡人掠_国产黄大片在线观看画质优化_txt小说免费全本

CentOs7中mysql5.7如何實現主從復制配置

猜你喜歡

最新資訊

相關推薦

相關標簽