本文的主要内容有mysql複制原理，mysql一主多從、雙主架構的示例解讀，以及mysql在主從複制架構實踐中的常見錯誤問題和解決方法。
一 mysql複制原理
1 原了解讀
mysql的複制(replication)是異步複制,即從一個mysql實列或端口(Master)複制到另一個mysql實列的或端口(slave)；複制操作由3個程序完成,其中2個(SQL程序和I/O程序)在Slave上，另一個在Master上；要實作複制，必須打開Master端的二進制日志(log-bin)，log-bin記錄着整個資料對的操作資訊，所有slave從master端擷取該更新的日志，将其傳送到本地并寫到本地檔案中，然後在讀取本地檔案内容執行日志中記錄的更新操作；slave上已經完整拷貝master資料後，就可以連接配接到master上然後等待處理更新了。如果master當機或者slave連接配接斷開，slave會定期嘗試連接配接到master上直到能重連并且等待更新.重試的時間間隔由--master-connect-retry選項來控制,它的預設值是60秒。每個slave都記錄了它關閉時的日志位置，msater也不知道有多少個slave連接配接上來或者哪個slave從什麼時候開始更新。
2 資料同步實作步驟
（1）mysql的同步功能由三個線程來實作，master上一個，slave上兩個；
（2）slave啟動時，I/O線程連接配接到mater上，請求master發送二進制日志中的語句；
（3）mater建立一個I/O線程，把日志内容發送到salve上；
（4）slave上的I/O線程讀取master上的binlog dump發送的語句，并且記錄到中繼日志relay logs；
（5）slave開啟SQL線程，讀取中繼日志，然後執行這些語句來更新資料
slave上開啟兩個線程很有用：把讀日志和執行日志分作兩個線程任務處理，執行任務慢的話，讀日志的任務不會跟着慢下來，而且讀日志任務常常很快就完成，執行任務的sql線程往往需要很久。
二 一主多從架構部署示例及常見故障解決
1 實驗環境配置設定
192.168.1.2 master.test.com
192.168.1.3 slave1.test.com
192.168.1.4 slave2.test.com
2 建立主從複制賬号，限定賬戶在指定伺服器登入
在master上建立使用者
> grant replication salve on *.* to 'slave1'@'192.168.1.3' identified by '123456';
>grant replication salve on *.* to 'slave2'@'192.168.1.4' identified by '123456';
>flush privileges;
3 備份已有資料，并複制資料到從節點（如果都是新資料庫，可以略去此步）
>flush tables with read lock;
>show matser status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB
| mysql-bin.000001| 106 | test |
tar zcvf /tmp/mysql.tar.gz /var/lib/mysql/
rsync -zrvz --delete /tmp/mysql.tar.gz 192.168.1.3:/tmp/
rsync -zrvz --delete /tmp/mysql.tar.gz 192.168.1.4:/tmp/
3 解鎖
>unlock tables;
Query OK, 0 rows affected (0.00 sec)
4 配置master伺服器，修改mysql的主配置檔案
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
#### Master ####
server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
relay-log = mysql-relay
relay-log-index = mysql-relay.index
expire-logs-days = 10
max-binlog-size = 100M
log-slave-updates = 1
binlog-do-db = test
replicate-do-db = test
binlog-ignore-db = mysql
replicate-ignore-db = mysql
如果需要備份多個資料庫,那麼應該寫多行
binlog-do-db=test1
binlog-do-db=test2
replicate-do-db=test1
replicate-do-db=test2
修改完成後重新開機mysql
/etc/init.d/mysqld restart
5 配置slave伺服器，修改兩個從伺服器的配置檔案，并重新開機服務(注意修改server-id)
[mysqld]
server-id = 2
6 在兩個slave上配置開啟同步
> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.2',
-> MASTER_USER='salve1', # 在104 上面改成slave2
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=106,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
.............
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7 檢視主資料庫的狀态
>show matser status; ##檢視主資料庫狀态
>show processlist; ##檢視複制程序狀态
8 測試驗證
驗證方法：在主資料庫上建立資料庫和在test下建立表，到從資料檢視資料是否已經同步。
注意：建立的資料庫不會同步，因為資料庫的更新隻設定了test資料庫。
9 故障排除
從伺服器上show slave status\G;檢視slave狀态
Slave_IO_Running,為No,則說明IO_THREAD沒有啟動,請執行: start slave io_thread;
Slave_SQL_Running為No.則複制出錯,檢視Last_error字段排除錯誤後執行: start slave sql_thread;
三 雙主互為主備部署解析
1 實驗環境準備
192.168.1.2 master1.test.com
192.168.1.3 master2.test.com
和一主多從的步驟是一樣的,隻是同樣的步驟執行2次而已
2 雙主配置
在masterA上建立一個賬戶,使用者masterB同步資料
masterA操作
> GRANT REPLICATION SLAVE ON *.* TO 'master'@'192.168.1..%' IDENTIFIED BY '123456';
> FLUSH PRIVILEGES;
> FLUSH TABLES WITH READ LOCK;
mysqldump -uroot -p123456 --databases test >/tmp/testA.sql
scp /tmp/testA.sql 192.168.1.103:/tmp
> UNLOCK TABLES;
masterB:上同樣的建立複制到賬戶,并導入資料
mysql> GRANT REPLICATION SLAVE ON *.* TO 'master'@'192.168.1.%' IDENTIFIED BY '123456';mysql> FLUSH TABLES WITH READ LOCK;
mysqldump -uroot -p123456 --databases test >/tmp/testB.sql
scp /tmp/testB.sql 192.168.1.102:/tmp/
# 分别在master1 和 master2上分别導入對方的資料.但是存在一個問題: 因為存在主鍵沖突的情況,導出資料的時候,不要把主鍵給導出來了;如果是一方沒有資料,那就直接導入資料就好了,不過也要跳過主鍵;
mysql -uroot -p123456 </tmp/testA.sql
mysql -uroot -p123456 </tmp/testB.sql
3 修改master1和master2 的配置檔案 ,隻是server-id 不同
master1:的配置檔案
#datadir=/mysqldata
server-id = 1
log-bin = mysql-bin
relay-log-index = mysql-relay.index
sync-binlog = 1
auto-increment-increment = 2
auto-increment-offset = 1
# master2:的配置檔案
vim /etc/my.cnf
### Master ####
server-id = 2
log-slave-updates
skip-slave-start
slave-skip-errors = all
sync-binlog = 1
auto-increment-offset = 2
4 分别在master1和master2上擷取File和Position位置
master1:
| mysql-bin.000002| 106 | test | mysql
master2:
| mysql-bin.000003| 106 | test | mysql
5 配置主從,分别在master1和 master2上配置對方的從
master1上：
-> MASTER_HOST='192.168.1.3',
-> MASTER_USER='master',
-> MASTER_LOG_FILE='mysql-bin.000003',
master2上：
-> MASTER_LOG_FILE='mysql-bin.000002',
用>show processlist檢視程序狀态
6 測試，在其中一個資料庫test中建立新表，插入資料，在另一個資料庫中檢視是否同步。
四 主從複制中常見的故障及解決方法
1 從伺服器上show slave status\G;檢視slave狀态為NO
2 主伺服器當機了,如何把從伺服器提升會主伺服器
在一主多從的環境中，必須選擇資料最新的從伺服器做新的主伺服器，以保證資料的完整可靠。在一主兩從的環境中，Server1當機後，等到Server1和Server2把當機前同步到的日志都執行完，比較Master_Log_File和Read_Master_Log_Pos就可以判斷出誰快誰慢，因為Server2從 Server1同步的資料（1589）比Server3從Server1同步的資料（1293）新，是以應該提升Server2為新的主伺服器。
3 主從複制突然停止了,該怎麼處理
複制錯誤多半是因為日志錯誤引起的,是以首先要搞清楚是主日志錯誤還是中繼日志錯誤，從錯誤資訊裡就能判斷，如果不能判斷，則使用下面的mysqlbinlog指令：
shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null
shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null
# 如果沒有錯誤,則不會有任何輸出,反之如果有錯誤,則會顯示出來.
如果是主日志錯誤,則需要在從伺服器設定SET GLOBAL sql_slave_skip_counter
mysql> SET GLOBAL sql_slave_skip_counter = 1;
注：如果有多個錯誤,可能需要執行多次（提醒：主從伺服器資料可能是以不一緻）
如果是中繼日志錯誤,隻要在從伺服器使用CHANGE MASTER TO即可,系統會抛棄目前的中繼日志,重新下載下傳。
mysql> CHANGE MASTER TO
MASTER_LOG_FILE=’<Relay_Master_Log_File>’,
MASTER_LOG_POS=<Exec_Master_Log_Pos>;
另外，不同的應用環境系統總會出現各種各樣的錯誤，處理錯誤的最重要依據是根據日志中錯誤資訊分析定位故障所在，建議在出現故障時候，不要忙着百度谷歌找答案，先去日志上看看，大部分的應用故障都會有相應的日志輸出，通過日志分析錯誤是基本的”救火之道“。
本文轉自super李導51CTO部落格，原文連結： http://blog.51cto.com/superleedo/1897681，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)