mysql數據庫replication 主從(master

主從(master-slave)同步對於我們很多的朋友來講並不陌生了，下面 們一起來看看mysql數據庫replication 主從(master-slave)同步例子，希望文章對各位有幫助。

這篇文章如果能加上auto-increment-increment,auto-increment-offset，這二個配置會更好一點。
重新配置mysql replication，是為了研究MHA作准備。

一，服務器說明

192.168.10.103 master
192.168.10.209 slave1
192.168.10.219 slave2
一主二從

二，mysql配置

1,master配置

[mysqld] 
 
server-id=1 
log-bin=mysql-bin 
read-only=0 
binlog-do-db=test 

2,slave1配置

[mysqld] 
 
server-id=2 
log-bin=mysql-bin 
read-only=0    //可讀寫 
replicate-do-db=test 
log-slave-updates 

3,slave2配置

[mysqld] 
 
server-id=3 
log-bin=mysql-bin 
read-only=1   //只讀 
replicate-do-db=test 
log-slave-updates 
我用的是mysql自帶的test數據庫做測試的。改完配置重啟數據庫
# /etc/init.d/mysqld restart 

三，創建mysql同步賬號

1,創建賬號

mysql> GRANT SELECT,REPLICATION SLAVE ON *.* TO  test@'192.168.%' IDENTIFIED BY 'test'; 
三台mysql都添加該用戶

2,測試賬號

# mysql -u test -p -h 192.168.10.103 
# mysql -u test -p -h 192.168.10.209 
# mysql -u test -p -h 192.168.10.219 

在三台mysql任何一台，進行上面操作，如果都成功，說明登錄賬號沒問題

四，將slave加入到master

1,查看master的file和postion

mysql> show master status\G; 
*************************** 1. row *************************** 
 File: mysql-bin.000004 
 Position: 2850 
 Binlog_Do_DB: test 
Binlog_Ignore_DB: 
1 row in set (0.00 sec) 
 
ERROR: No query specified 

2,在二台slave配置

 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.103',  //master ip 
MASTER_USER='test', MASTER_PASSWORD='test',  //同步用戶名和密碼 
MASTER_LOG_FILE='mysql-bin.000004', //master的file文件 
MASTER_LOG_POS=2850;   //master的postion 

3,slave端啟動，並查看狀態

mysql> slave start; 
mysql replication 一主二從
mysql replication 一主二從
上圖中紅色區域是要注意的地方，
Slave_IO_Running為No,Slave_SQL_Running為No,Slave_IO_State如果不是Waiting for master to send event，都不可能同步成功
還有一點就是file和position必須根master一至。下面找了些錯誤信息以及命令說明

五，測試

 

CREATE TABLE IF NOT EXISTS `test` ( 
  `big_test` bigint(20) DEFAULT '0', 
  `int_test` int(11) DEFAULT '0', 
  `medium_test` mediumint(8) DEFAULT '0', 
  `small_test` smallint(6) DEFAULT '0', 
  `tiny_test` tinyint(10) DEFAULT '0' 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

在master上面，創建一張表，然後在slave1和slave2看看有沒有這張表，就可以確定配置有沒有成功了。

六，錯誤信息的處理，主從服務器上的命令，及狀態信息

1,在從服務器上使用show slave status\G
Slave_IO_Running為No,則說明IO_THREAD沒有啟動，請執行mysql> start slave io_thread
Slave_SQL_Running為No,則復制出錯,查看Last_error字段排除錯誤後執行mysql> start slave sql_thread
Slave_IO_State字段空, #復制沒有啟動
Connecting to master #沒有連接上master
Waiting for master to send event #已經連上

2,主服務器上的相關命令

show master status
show slave hosts
show logs
show binlog events
purge logs to 'log_name'
purge logs before 'date'
reset master(老版本flush master)
set sql_log_bin=

3,從服務器上的相關命令

slave start
slave stop
SLAVE STOP IO_THREAD #此線程把master段的日志寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD #此線程把寫到本地的日志應用於數據庫
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= #動態改變master信息
PURGE MASTER [before 'date'] 刪除master端已同步過的日志
4,Master 同步線程狀態

以下列出了master的 Binlog Dump 線程 State 字段中最常見的幾種狀態。如果在master上沒有 Binlog Dump 線程，那麼同步就沒有在運行。
也就是說，沒有slave連接上來。
Sending binlog event to slave
事件是由二進制日志構成，一個事件通常由更新語句加上其他信息。線程讀取到一個事件並正發送到slave上。
Finished reading one binlog; switching to next binlog
讀取完了一個二進制日志，正切換到下一個。
Has sent all binlog to slave; waiting for binlog to be updated
已經讀取完全部未完成更新日志，並且全部都發送到slave了。它處於空閒狀態，正等待在master上執行新的更新操作以在二進制日志中產生新
的事件，然後讀取它們。
Waiting to finalize termination
當前線程停止了，這個時間很短。
5,Slave的I/O線程狀態
以下列出了slave的I/O線程 State 字段中最常見的幾種狀態。從MySQL 4.1.1開始，這個狀態在執行 SHOW SLAVE STATUS 語句結果的
Slave_IO_State 字段也會出現。這意味著可以只執行 SHOW SLAVE STATUS 語句就能了解到更多的信息。
Connecting to master
該線程證嘗試連接到master上。
Checking master version
確定連接到master後出現的一個短暫的狀態。
Registering slave on master
確定連接到master後出現的一個短暫的狀態。
Requesting binlog dump
確定連接到master後出現的一個短暫的狀態。該線程向master發送一個請求，告訴它要請求的二進制文件以及開始位置。

Waiting to reconnect after a failed binlog dump request
如果二進制日志轉儲(binary log dump)請求失敗了(由於連接斷開)，該線程在休眠時進入這個狀態，並定期重連。重連的時間間隔由 --
master-connect-retry 選項來指定。
Reconnecting after a failed binlog dump request
該線程正嘗試重連到master。
Waiting for master to send event
已經連接到master，正等待它發送二進制日志。如果master閒置時，這個狀態可能會持續較長時間，如果它等待超過 slave_read_timeout 秒
，就會發生超時。這時，它就會考慮斷開連接，然後嘗試重連。
Queueing master event to the relay log
已經讀取到一個事件，正把它拷貝到中繼日志中以備SQL線程處理。
Waiting to reconnect after a failed master event read
讀日志時發生錯誤(由於連接斷開)。該線程在重連之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正嘗試重連到master。當連接確定後，狀態就變成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中繼日志的大小總和超過這個值了。I/O線程等待SQL線程先處理中繼日志然後刪除它們以釋放足夠的空間
。
Waiting for slave mutex on exit
當前線程停止了，這個時間很短。

6,Slave的SQL線程狀態

以下列出了slave的SQL線程 State 字段中最常見的幾種狀態：
Reading event from the relay log
從中繼日志裡讀到一個事件以備執行。
Has read all relay log; waiting for the slave I/O thread to update it
已經處理完中繼日志中的全部事件了，正等待I/O線程寫入更新的日志。
Waiting for slave mutex on exit
當前線程停止了，這個時間很短。