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ù)或者對(duì)實(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ù)之前先對(duì)主數(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í)記錄的寫了下
希望本文所述對(duì)大家CentOS服務(wù)器設(shè)置有所幫助。
相關(guān)文章
這篇文章主要介紹了10大HBase常見運(yùn)維工具整理小結(jié)，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2020-07-07
這篇文章主要給大家介紹了關(guān)于如何在Linux中設(shè)置快捷方式圖標(biāo)的相關(guān)資料，文中通過示例代碼的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
這篇文章主要介紹了linux中安裝deb軟件包實(shí)現(xiàn)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
Linux配置SSH和Xshell連接服務(wù)器的教程(圖解)
這篇文章主要介紹了Linux配置SSH和Xshell連接服務(wù)器的教程，本文圖文并茂給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-11-11
這篇文章主要介紹了幾個(gè)好用的 Unix/Linux 命令技巧,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-07-07
linux下修改文件權(quán)限chmod命令詳細(xì)解析
大家好，本篇文章主要講的是linux下修改文件權(quán)限chmod命令詳細(xì)解析，感興趣的同學(xué)趕快來看一看吧，對(duì)你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
Centos 6.8編譯安裝LNMP環(huán)境（Nginx+MySQL+PHP）教程
這篇文章主要介紹了關(guān)于CentOS 6.8中編譯安裝LNMP環(huán)境的相關(guān)資料，LNMP即Linux，Nginx，MySQL，PHP，文中通過一步步的步驟介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面來一起看看吧。2017-03-03
這篇文章主要介紹了Linux環(huán)境下nodejs的安裝圖文教程,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-12-12
ubuntu下用dpkg命令行運(yùn)行deb安裝包的方法
在Ubuntu系統(tǒng)中，除了通過桌面啟動(dòng)deb安裝包外，還能通過命令行來運(yùn)行deb安裝包，需要使用到dpkg命令，下面這篇文中就給大家介紹下Ubuntu下使用dpkg命令運(yùn)行deb安裝包的方法，需要的朋友可以參考借鑒，一起來學(xué)習(xí)下吧。2017-01-01
阿里云 CentOS7.4 安裝 Python3.6的方法講解
下面小編就為大家分享一篇阿里云 CentOS7.4 安裝 Python3.6的方法講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2018-01-01