CentOS服務(wù)器平臺(tái)搭建mysql主從復(fù)制與讀寫分離的方法
本文實(shí)例講述了CentOS服務(wù)器搭建mysql主從復(fù)制與讀寫分離的方法。分享給大家供大家參考，具體如下：
mysql 主從復(fù)制的優(yōu)點(diǎn):
① 如果主服務(wù)器出現(xiàn)問題， 可以快速切換到從服務(wù)器提供的服務(wù)，保證高可用性
② 可以在從服務(wù)器上執(zhí)行查詢操作， 降低主服務(wù)器的訪問壓力
③ 可以在從服務(wù)器上執(zhí)行備份， 以避免備份期間影響主服務(wù)器的服務(wù)
注意事項(xiàng)：
① server-id必須唯一，一般使用ip的后三位
② 從庫Slave_IO_Running:NO 可能原因:帳號(hào)無權(quán)限操作
③ Can't execute the query because you have a conflicting read lock，解鎖下即可 unlock tables;
④ 一般只有更新不頻繁的數(shù)據(jù)或者對實(shí)時(shí)性要求不高的數(shù)據(jù)可以通過從服務(wù)器查詢， 實(shí)時(shí)性要求高的數(shù)據(jù)仍然需要從主數(shù)據(jù)庫獲得
⑤ 修改完主從服務(wù)器的配置需要重啟mysql：service mysqld restart
主機(jī)A: 192.168.10.111
從機(jī)B: 192.168.10.124
請先分別安裝mysql，版本需一致，裝了即可跳過
1、先登錄主機(jī) A，在主服務(wù)器上，設(shè)置一個(gè)從數(shù)據(jù)庫的賬戶，使用REPLICATION SLAVE（從復(fù)制）賦予權(quán)限，如：
賦予從機(jī)權(quán)限，有多臺(tái)從機(jī)，就執(zhí)行多次。
mysql>flush privileges;
2、 打開主機(jī)A的my.cnf，輸入如下：（修改主數(shù)據(jù)庫的配置文件my.cnf，開啟BINLOG，并設(shè)置server-id的值，修改之后必須重啟mysql服務(wù)）
server-id = 1 #主機(jī)標(biāo)示，整數(shù) log_bin = /var/log/mysql/mysql-bin.log #確保此文件可寫，開啟bin-log read-only =0 #主機(jī)，讀寫都可以 binlog-do-db =test #需要備份數(shù)據(jù)，多個(gè)寫多行 binlog-ignore-db =mysql #不需要備份的數(shù)據(jù)庫，多個(gè)寫多行
可以通過mysql>show variables like 'log_%'; 驗(yàn)證二進(jìn)制日志是否已經(jīng)啟動(dòng)。
3、現(xiàn)在可以停止主數(shù)據(jù)的的更新操作，并生成主數(shù)據(jù)庫的備份，我們可以通過mysqldump到處數(shù)據(jù)到從數(shù)據(jù)庫，當(dāng)然了，你也可以直接用cp命令將數(shù)據(jù)文件復(fù)制到從數(shù)據(jù)庫去，注意在導(dǎo)出數(shù)據(jù)之前先對主數(shù)據(jù)庫進(jìn)行READ LOCK，以保證數(shù)據(jù)的一致性
mysql> flush tables with read lock; Query OK, 0 rows affected (0.19 sec)
然后mysqldump導(dǎo)出數(shù)據(jù)：
4、得到主服務(wù)器當(dāng)前二進(jìn)制日志名和偏移量，這個(gè)操作的目的是為了在從數(shù)據(jù)庫啟動(dòng)后，從這個(gè)點(diǎn)開始進(jìn)行數(shù)據(jù)的恢復(fù)。
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)
最好在主數(shù)據(jù)庫備份完畢，恢復(fù)寫操作。
mysql> unlock tables; Query OK, 0 rows affected (0.28 sec)
5、將剛才主數(shù)據(jù)備份的test.sql復(fù)制到從數(shù)據(jù)庫（navicat、phpmyadmin、命令行都可以），進(jìn)行導(dǎo)入。
6、修改從數(shù)據(jù)庫的my.cnf，增加server-id參數(shù)，指定復(fù)制使用的用戶，主數(shù)據(jù)庫服務(wù)器的ip，端口以及開始執(zhí)行復(fù)制日志的文件和位置。打開從機(jī)B的my.cnf，輸入（修改之后必須重啟mysql服務(wù)）
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 #如果從服務(wù)器發(fā)現(xiàn)主服務(wù)器斷掉，重新連接的時(shí)間差(秒) replicate-do-db =test #只復(fù)制某個(gè)庫 replicate-ignore-db=mysql #不復(fù)制某個(gè)庫
7、在從服務(wù)器上,啟動(dòng)slave進(jìn)程
mysql> start slave;
8、在從服務(wù)器進(jìn)行show salve status驗(yàn)證
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、測試主從服務(wù)器是否能同步
插入 修改 刪除 增加字段 修改字段 增加表自己測試都可以
[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>
在從服務(wù)器查看是否同步過來 如果一致說明成功
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)
很早的時(shí)候搭建的了，走不少彎路，根據(jù)當(dāng)時(shí)記錄的寫了下
希望本文所述對大家CentOS服務(wù)器設(shè)置有所幫助。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信