MySQL导入冲突保留两者_面试被问MySQL 主从复制，怎么破？
一、前言
隨著應用業(yè)務數(shù)據(jù)不斷的增大，應用的響應速度不斷下降，在檢測過程中我們不難發(fā)現(xiàn)大多數(shù)的請求都是查詢操作。
此時，我們可以將數(shù)據(jù)庫擴展成主從復制模式，將讀操作和寫操作分離開來，多臺數(shù)據(jù)庫分攤請求，從而減少單庫的訪問壓力，進而應用得到優(yōu)化。整理了一份328頁MySQLPDF文檔
本次測試使用兩個虛擬機：ip：192.168.2.21(主) ip：192.168.2.22(從)
二、主從復制原理
同步操作通過 3 個線程實現(xiàn)，其基本步驟如下：
主服務器將數(shù)據(jù)的更新記錄到二進制日志中(記錄被稱作二進制日志事件)-- 主庫線程；
從庫將主庫的二進制日志復制到本地的中繼日志(relay log)-- 從庫 I/O 線程；
從庫讀取中繼日志中的事件，將其重放到數(shù)據(jù)中 -- 從庫 SQL 線程。
三、配置主庫
# 3.1 創(chuàng)建用戶
為了安全起見，準備創(chuàng)建一個新用戶用于從庫連接主庫。
# 創(chuàng)建用戶
create user 'repl'@'%' identified by 'repl';
# 授權(quán)，只授予復制和客戶端訪問權(quán)限
grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl';
# 3.2 修改配置文件
1)vim /etc/my.cnf 在[mysqld]下添加：
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format = mixed
server-id = 21
sync-binlog = 1
character-set-server = utf8
2)保存文件并重啟主庫：
service mysqld restart
配置說明：
log-bin：設置二進制日志文件的基本名；
log-bin-index：設置二進制日志索引文件名；
binlog_format：控制二進制日志格式，進而控制了復制類型，三個可選值
-STATEMENT：語句復制
-ROW：行復制
-MIXED：混和復制，默認選項
server-id：服務器設置唯一ID，默認為1，推薦取IP最后部分；
sync-binlog：默認為0，為保證不會丟失數(shù)據(jù)，需設置為1，用于強制每次提交事務時，同步二進制日志到磁盤上。
# 3.3 備份主數(shù)據(jù)庫數(shù)據(jù)
若主從數(shù)據(jù)庫都是剛剛裝好且數(shù)據(jù)都是一致的，直接執(zhí)行 show master status 查看日志坐標。
若主庫可以停機，則直接拷貝所有數(shù)據(jù)庫文件。
若主庫是在線生產(chǎn)庫，可采用 mysqldump 備份數(shù)據(jù)，因為它對所有存儲引擎均可使用。
1)為了獲取一個一致性的快照，需對所有表設置讀鎖：
flush tables with read lock;
2)獲取二進制日志的坐標：
show master status;
返回結(jié)果：
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3)備份數(shù)據(jù)：
# 針對事務性引擎
mysqldump -uroot -ptiger --all-database -e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql
# 針對 MyISAM 引擎，或多引擎混合的數(shù)據(jù)庫
mysqldump -uroot --all-database -e -l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql
1 row in set (0.00 sec)
4)恢復主庫的寫操作：
unlock tables;
四、配置從庫
# 4.1 修改配置文件
1)vim /etc/my.cnf 在[mysqld]下添加：
log-bin = mysql-bin
binlog_format = mixed
log-slave-updates = 0
server-id = 22
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
read-only = 1
slave_net_timeout = 10
2)保存文件并重啟從庫：
service mysqld restart
配置說明：
log-slave-updates：控制 slave 上的更新是否寫入二進制日志，默認為0；若 slave 只作為從服務器，則不必啟用；若 slave 作為其他服務器的 master，則需啟用，啟用時需和 log-bin、binlog-format 一起使用，這樣 slave 從主庫讀取日志并重做，然后記錄到自己的二進制日志中；
relay-log：設置中繼日志文件基本名；
relay-log-index：設置中繼日志索引文件名；
read-only：設置 slave 為只讀，但具有super權(quán)限的用戶仍然可寫；
slave_net_timeout：設置網(wǎng)絡超時時間，即多長時間測試一下主從是否連接，默認為3600秒，即1小時，這個值在生產(chǎn)環(huán)境過大，我們將其修改為10秒，即若主從中斷10秒，則觸發(fā)重新連接動作。
# 4.2 導入備份數(shù)據(jù)
如果 3.3 步驟中沒進行備份，忽略此步驟。
mysql -uroot -p < /data/all_db.sql
# 4.3 統(tǒng)一二進制日志的坐標
根據(jù) 3.3 步驟獲取的坐標，統(tǒng)一到從庫中：
change master to
master_host='192.168.2.21',
master_user='repl',
master_password='repl',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=120;
注意：此處使用的是新創(chuàng)建的賬戶。
# 4.4 啟動主從復制
1)啟動從庫 slave 線程：
start slave;
2)查看從服務器復制功能狀態(tài)：
show slave status\G;
返回結(jié)果：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
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: 120
此處只張貼部分返回結(jié)果。
結(jié)果說明：
Slave_IO_Running：此進程負責 slave 從 master 上讀取 binlog 日志，并寫入 slave 上的中繼日志。
Slave_SQL_Running：此進程負責讀取并執(zhí)行中繼日志中的 binlog 日志。
這兩個進程的狀態(tài)需全部為 YES，只要有一個為 NO，則復制就會停止。
當 Relay_Master_Log_File = Master_Log_File 且 Read_Master_Log_Pos = Exec_Master_Log_Pos 時，則表明 slave 和 master 處于完全同步的狀態(tài)。
五、驗證
使用一個簡單的例子：
在主庫創(chuàng)建名為 mysql_test 的數(shù)據(jù)庫，如果同步成功，那么在從庫中也能查詢出名為 mysql_test 數(shù)據(jù)庫。
六、參考資料
創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎總結(jié)
以上是生活随笔為你收集整理的MySQL导入冲突保留两者_面试被问MySQL 主从复制，怎么破？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 华为m6如何隐藏应用
- 下一篇： 苹果手机有指纹锁吗