建站
咨詢
售后
建站咨詢
新聞中心
mysql主從復(fù)制的優(yōu)點(diǎn):
1、 如果主服務(wù)器出現(xiàn)問題， 可以快速切換到從服務(wù)器提供的服務(wù)，保證高可用性
2、 可以在從服務(wù)器上執(zhí)行查詢操作， 降低主服務(wù)器的訪問壓力
3、 可以在從服務(wù)器上執(zhí)行備份， 以避免備份期間影響主服務(wù)器的服務(wù)
創(chuàng)新互聯(lián)主營新樂網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,APP應(yīng)用開發(fā),新樂h5重慶小程序開發(fā)搭建,新樂網(wǎng)站營銷推廣歡迎新樂等地區(qū)企業(yè)咨詢
注意事項：
1、server-id必須唯一，一般使用ip的后三位
2、從庫Slave_IO_Running:NO 可能原因:帳號無權(quán)限操作
3、Can't execute the query because you have a conflicting read lock，解鎖下即可 unlock tables;
4、一般只有更新不頻繁的數(shù)據(jù)或者對實時性要求不高的數(shù)據(jù)可以通過從服務(wù)器查詢， 實時性要求高的數(shù)據(jù)仍然需要從主數(shù)據(jù)庫獲得
5、修改完主從服務(wù)器的配置需要重啟mysql：service mysqld restart
主機(jī)A: 192.168.10.111
從機(jī)B: 192.168.10.124
請先分別安裝mysql，版本需一致，裝了即可跳過
yum install mysql mysql-server #輸入y即可自動安裝,直到安裝完成
1、先登錄主機(jī) A，在主服務(wù)器上，設(shè)置一個從數(shù)據(jù)庫的賬戶，使用REPLICATION SLAVE（從復(fù)制）賦予權(quán)限，如：
mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.10.124' IDENTIFIED BY '123456'
賦予從機(jī)權(quán)限，有多臺從機(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ù)，多個寫多行 binlog-ignore-db =mysql #不需要備份的數(shù)據(jù)庫，多個寫多行 可以通過mysql>show variables like 'log_%'; 驗證二進(jìn)制日志是否已經(jī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ù)：
mysqldump -h127.0.0.1 -p3306 -uroot -p test > /data/backup/test.sql
4、得到主服務(wù)器當(dāng)前二進(jìn)制日志名和偏移量，這個操作的目的是為了在從數(shù)據(jù)庫啟動后，從這個點(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ù)器斷掉，重新連接的時間差(秒) replicate-do-db =test #只復(fù)制某個庫 replicate-ignore-db=mysql #不復(fù)制某個庫
7、在從服務(wù)器上,啟動slave進(jìn)程
mysql> start slave;
8、在從服務(wù)器進(jìn)行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、測試主從服務(wù)器是否能同步
插入 修改 刪除 增加字段 修改字段 增加表自己測試都可以
[linuxidc@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) 新聞標(biāo)題：CentOS搭建MySQL主從復(fù)制，讀寫分離
文章URL：http://www.5511xx.com/article/cccieig.html
