CentOS服務器平臺搭建mysql主從復制與讀寫分離的方法

2024-09-01 13:54:40

字體：大中小

來源：轉載

供稿：網友

本文實例講述了CentOS服務器搭建mysql主從復制與讀寫分離的方法。分享給大家供大家參考，具體如下：

mysql 主從復制的優點:

① 如果主服務器出現問題，可以快速切換到從服務器提供的服務，保證高可用性
② 可以在從服務器上執行查詢操作，降低主服務器的訪問壓力
③ 可以在從服務器上執行備份，以避免備份期間影響主服務器的服務

注意事項：

① server-id必須唯一，一般使用ip的后三位
② 從庫Slave_IO_Running:NO 可能原因:帳號無權限操作
③ Can't execute the query because you have a conflicting read lock，解鎖下即可 unlock tables;
④ 一般只有更新不頻繁的數據或者對實時性要求不高的數據可以通過從服務器查詢，實時性要求高的數據仍然需要從主數據庫獲得
⑤ 修改完主從服務器的配置需要重啟mysql：service mysqld restart

主機A: 192.168.10.111
從機B: 192.168.10.124

請先分別安裝mysql，版本需一致，裝了即可跳過

復制代碼代碼如下:

yum install mysql mysql-server #輸入y即可自動安裝,直到安裝完成

1、先登錄主機 A，在主服務器上，設置一個從數據庫的賬戶，使用REPLICATION SLAVE（從復制）賦予權限，如：

復制代碼代碼如下:

	mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.10.124' IDENTIFIED BY '123456'

賦予從機權限，有多臺從機，就執行多次。

mysql>flush privileges;

2、打開主機A的my.cnf，輸入如下：（修改主數據庫的配置文件my.cnf，開啟BINLOG，并設置server-id的值，修改之后必須重啟mysql服務）

server-id        = 1  #主機標示，整數 log_bin         = /var/log/mysql/mysql-bin.log  #確保此文件可寫，開啟bin-log read-only       =0 #主機，讀寫都可以 binlog-do-db     =test  #需要備份數據，多個寫多行 binlog-ignore-db  =mysql #不需要備份的數據庫，多個寫多行

可以通過mysql>show variables like 'log_%'; 驗證二進制日志是否已經啟動。

3、現在可以停止主數據的的更新操作，并生成主數據庫的備份，我們可以通過mysqldump到處數據到從數據庫，當然了，你也可以直接用cp命令將數據文件復制到從數據庫去，注意在導出數據之前先對主數據庫進行READ LOCK，以保證數據的一致性

mysql> flush tables with read lock;Query OK, 0 rows affected (0.19 sec)

然后mysqldump導出數據：

復制代碼代碼如下:

mysqldump -h127.0.0.1 -p3306 -uroot -p test > /data/backup/test.sql

4、得到主服務器當前二進制日志名和偏移量，這個操作的目的是為了在從數據庫啟動后，從這個點開始進行數據的恢復。

mysql> show master status;+------------------+----------+--------------+------------------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000007 |   517 | test     | mysql      |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

最好在主數據庫備份完畢，恢復寫操作。

mysql> unlock tables;Query OK, 0 rows affected (0.28 sec)

5、將剛才主數據備份的test.sql復制到從數據庫（navicat、phpmyadmin、命令行都可以），進行導入。

6、修改從數據庫的my.cnf，增加server-id參數，指定復制使用的用戶，主數據庫服務器的ip，端口以及開始執行復制日志的文件和位置。打開從機B的my.cnf，輸入（修改之后必須重啟mysql服務）

server-id    = 2 log_bin     = /var/log/mysql/mysql-bin.log master-host   =192.168.10.111 master-user   =backup master-pass   =123456 master-port   =3306 master-connect-retry=60 #如果從服務器發現主服務器斷掉，重新連接的時間差(秒) replicate-do-db =test #只復制某個庫 replicate-ignore-db=mysql #不復制某個庫

7、在從服務器上,啟動slave進程

mysql> start slave;

8、在從服務器進行show salve status驗證

mysql> SHOW SLAVE STATUS/G *************************** 1. row ***************************         Slave_IO_State: Waiting for master to send event          Master_Host: 192.168.10.111          Master_User: root          Master_Port: 3306         Connect_Retry: 5        Master_Log_File: mysql-bin.000007      Read_Master_Log_Pos: 263         Relay_Log_File: mysqld-relay-bin.000002         Relay_Log_Pos: 408     Relay_Master_Log_File: mysql-bin.000007        Slave_IO_Running: Yes       Slave_SQL_Running: Yes        Replicate_Do_DB: test      Replicate_Ignore_DB: mysql       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: 263        Relay_Log_Space: 564        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: 1 row in set (0.00 sec)

提示

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

說明配置成功了

9、測試主從服務器是否能同步

插入修改刪除增加字段修改字段增加表自己測試都可以

[xjp@server22 ~]$mysql -uroot -p123456 mysql> create database test; mysql> create table user(id int); mysql> insert into user values(1),(2),(3),(4),(5),(6); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | +----+ 2rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> update user set id=11 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+ | id | +----+ | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> delete from user where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 11 | | 3 | | 4 | | 5 | | 6 | +----+ 5 rows in set (0.00 sec) mysql> alter table user add name varchar(50); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+------+ | id | name | +----+------+ | 11 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | +----+------+ 5 rows in set (0.00 sec) mysql> ALTER TABLE user MODIFY COLUMN name VARCHAR(200); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+--------------+------+-----+---------+-------+ | Field | Type     | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id  | int(11)   | NO  |   | NULL  |    | | name | varchar(200) | YES |   | NULL  |    | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create table user2(id int); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test      | | user      | | user2     | +----------------+ 3 rows in set (0.00 sec) mysql>

在從服務器查看是否同步過來如果一致說明成功

mysql> use test; Database changed mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 2 rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 11 | | 3 | | 4 | | 5 | | 6 | +----+ 5 rows in set (0.00 sec) mysql> select * from user; +----+------+ | id | name | +----+------+ | 11 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | +----+------+ 5 rows in set (0.00 sec) mysql> desc user; +-------+--------------+------+-----+---------+-------+ | Field | Type     | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id  | int(11)   | NO  |   | NULL  |    | | name | varchar(200) | YES |   | NULL  |    | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test      | | user      | | user2     | +----------------+ 3 rows in set (0.00 sec) mysql> mysql binlog日志查看：  show binlog events/G; *************************** 12. row ***************************   Log_name: mysql-bin.000007     Pos: 985  Event_type: Query  Server_id: 1 End_log_pos: 1075     Info: use `test`; delete from user where id=2 *************************** 13. row ***************************   Log_name: mysql-bin.000007     Pos: 1075  Event_type: Query  Server_id: 1 End_log_pos: 1175     Info: use `test`; alter table user add name varchar(50) *************************** 14. row ***************************   Log_name: mysql-bin.000007     Pos: 1175  Event_type: Query  Server_id: 1 End_log_pos: 1287     Info: use `test`; ALTER TABLE user MODIFY COLUMN name VARCHAR(200) *************************** 15. row ***************************   Log_name: mysql-bin.000007     Pos: 1287  Event_type: Query  Server_id: 1 End_log_pos: 1376     Info: use `test`; create table user2(id int) 15 rows in set (0.00 sec)

很早的時候搭建的了，走不少彎路，根據當時記錄的寫了下

希望本文所述對大家CentOS服務器設置有所幫助。

注：相關教程知識閱讀請移步到服務器教程頻道。

上一篇：詳解Linux監控重要進程的實現方法

下一篇：CentOS 6.8 安裝vsftpd的方法步驟