在實際企業應用環境當中，單台mysql資料庫是不足以滿足日後業務需求的。譬如伺服器發生故障，沒有備份伺服器來提供服務的話，業務就得停止。介于這種情況，我們來學習一下mysql主從複制。
說明：由于MySQL不同版本之間的(二進制日志)binlog格式可能會不一樣，是以最好的搭配組合是Master的MySQL版本和Slave的版本相同或者更低，
Master的版本肯定不能高于Slave版本。（版本向下相容）
使用mysql主從複制的好處有：
1、采用主從伺服器這種架構，穩定性得以提升。如果主伺服器發生故障，我們可以使用從伺服器來提供服務。
2、在主從伺服器上分開處理使用者的請求，可以提升資料處理效率。
3、将主伺服器上的資料複制到從伺服器上，保護資料免受意外的損失。
環境描述：
新企業要搭建架構為主從複制的mysql資料庫。
主伺服器（mysql-master）：IP位址：192.168.48.128，mysql已安裝，沒有使用者資料。
從伺服器（mysql-slave）：IP位址：192.168.48.130，mysql已安裝，沒有使用者資料。
主從伺服器均可正常提供服務。
主從複制配置如下：
在主伺服器上操作：
1)、確定/etc/my.cnf中有如下參數，沒有的話需手工添加，并重新開機mysql服務。
[mysqld]
log-bin=mysql-bin 啟動二進制檔案
server-id=1 伺服器ID
2)、登入mysql，在mysql中添加一個backup的賬号，并授權給從伺服器。
[root@localhost ~]# mysql -uroot –p123456 登入mysql
mysql> grant replication slave on *.* to 'backup'@'192.168.48.130' identified by 'backup'; 建立backup使用者，并授權給192.168.48.130使用。
3)、查詢主資料庫狀态，并記下FILE及Position的值，這個在後面配置從伺服器的時候要用到。
mysql> show master status;
在從伺服器上操作：
1)、確定/etc/my.cnf中有log-bin=mysql-bin和server-id=1參數，并把server-id=1修改為server-id=10。修改之後如下所示：
server-id=10 伺服器ID
2）、重新開機mysql服務。
[root@localhost ~]# mysqladmin -p123456 shutdown
[root@localhost ~]# mysqld_safe --user=mysql &
3)、登入mysql，執行如下語句
[root@localhost ~]# mysql -uroot –p123456
mysql> change master to master_host='192.168.48.128',master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=401;
4)、啟動slave同步。
mysql> start slave;
5)、檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均為Yes，則主從複制連接配接正常。
mysql> show slave status\G
驗證配置是否正常，mysql主從能否正常複制。
在主資料庫上建立一個庫，并且在庫中寫一個表和一些資料。
mysql> create database mysqltest;
mysql> use mysqltest;
mysql> create table user(id int(5),name char(10));
mysql> insert into user values (00001,'zhangsan');
在從資料庫中驗證一下，是否正常複制到資料。
mysql> show databases;
mysql> select * from mysqltest.user;
從上圖中的結果，我們可以看到mysql主從複制已經在起作用了，我們在主資料庫中寫入的資料已經複制到我們的從資料庫中了。
環境如下：
單台資料庫存有資料，要更新為主從複制的架構。
主資料庫：mysql-master：IP192.168.48.128，資料庫正常提供服務，有資料。
從資料庫：mysql-slave：IP192.168.48.130，資料庫正常提供服務，無資料。
更新大緻步驟如下：
1、修改主資料庫配置檔案，開啟總從複制必要的功能。
2、在主資料庫中建立一個賬号并授權給從資料庫使用。
3、導出主資料庫中的資料作。
4、修改從資料庫配置檔案，開啟總從複制必要的功能。
5、把剛才主資料庫中導出的資料導入到從資料庫。
6、在從資料庫中配置連接配接主資料庫要用到的賬戶、密碼等。
7、啟動主從複制功能，并檢查是否正常複制。
開始更新:
在主資料庫上操作：
3)、檢視已有的資料庫有哪些；
4)、進行鎖表操作，不讓資料進行寫入動作，這麼做事為了防止從資料庫的原始資料和主資料庫的原始資料不一緻。
mysql> flush tables with read lock;
5)、查詢主資料庫狀态，并記下FILE及Position的值，這個在後面配置從伺服器的時候要用到。
mysql> show master status;
6)、切換一個終端，使用mysqldump指令将剛才查詢到的兩個庫導出來。
mysql> mysqldump –uroot –p123456 mysqltest > mysqltest.sql
mysql> mysqldump –uroot –p123456 test1234 > test1234.sql
7)、将導出來的庫檔案傳送到從資料庫的/root目錄下。
[root@localhost ~]#scp mysqltest.sql test1234.sql [email protected]:/root/
這中間需要輸入一次驗證密碼。見下圖
8)、切換回之前的終端，進行表解鎖操作。
mysql> unlock tables;
主資料庫伺服器上的操作告一段落。
在從資料庫上操作：
2)、重新開機mysql服務。
3)、登入資料庫，确認要同步的庫名不存在。
4)、建立名為mysqltest和test1234的庫。
mysql> create database test1234;
4)、切換一個終端，将傳過來的兩個資料檔案分别導入對應的資料庫下。
[root@localhost ~]# mysql -uroot –p123456 mysqltest < mysqltest.sql
[root@localhost ~]# mysql -uroot –p123456 test1234 < test1234.sql
5)、切換回之前的終端，執行如下語句
mysql> change master to master_host='192.168.48.128',master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=1650;
6)、啟動主從複制功能。
7)、
檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均為Yes，則主從複制連接配接正常。
驗證主從是否正常配置：
在主資料庫上插入資料：
mysql> show tables;
mysql> select * from user;
mysql> insert into user values (4,'ergou');
mysql> insert into user values (5,'sanwazi');
在從資料庫上檢視是否正常同步：
上圖可以看出，主從之間已經可以正常複制了。
今天就先到這裡吧O(∩_∩)O~
mysql主從複制碰到的小問題
一個朋友在做mysql主從複制的時候碰到了一些問題，他已經按照我之前的教程一步步做了。但是就是不知道是怎麼回事。從資料庫中使用指令show slave status\G檢視到的結果如下圖:
我們知道，要想mysql主從複制成功的先決條件是Slave_IO_Running和Slave_SQL_Running均為Yes，而上圖所示的結果顯然是有一個不滿足條件。
再根據Last_IO_Error的錯誤代碼2013及下面的錯誤提示我們可以知道，從資料庫無法正常連接配接到主資料庫。
這裡原因有三：
1、主資料庫沒有給從資料庫登入授權
2、網絡不通
3、有防火牆
明白這三點之後我們就可以針對原因依次來解決它們：
登入主資料庫，給從資料庫授權
[root@localhost ~]# mysql -uroot –p123456 登入主資料庫
mysql> grant replication slave on *.* to 'backup'@'192.168.48.130' identified by 'backup'; 建立backup使用者，并授權給從資料庫連接配接使用。
檢查網絡是否通暢
[root@localhost ~]# ping -c 4 192.168.48.128 再從伺服器上ping主伺服器。
分别在主從伺服器上檢查防火牆狀态
我們可以直接關閉防火牆
運作指令：[root@localhost ~]# setup
選擇防火牆（Firewall configuretion）這一項.
将它Disabled掉。
或者你可以直接使用指令：
[root@localhost ~]# service iptables stop 即時生效，系統重新開機後失效
[root@localhost ~]# chkconfig iptables off 目前狀态無效，系統重新開機後生效
大家可以結合使用
以上情況都排除完畢，我們來再測試一下主從是否能正常連接配接。
在從資料庫中執行如下指令：
mysql>stop slave;
mysql>start slave;
然後檢視主從連接配接狀态
mysql>show slave status\G
經過上述操作，我那位朋友成功排除了主從不能複制的故障。
故障2：報錯如下:
意思就是從上的server_id和主的一樣的，經檢視發現從上的/etc/my.cnf中的server_id=1這行我沒有注釋掉（在下面複制部分我設定了server_id），于是馬上把這行注釋掉了，然後重新開機mysql，發現還是報同樣的錯誤。
使用如下指令檢視了一下server_id
show variables like 'server_id';
發現，mysql并沒有從my.cnf檔案中更新server_id，既然這樣就隻能手動修改了
mysql> set global server_id=10; #此處的數值和my.cnf裡設定的一樣就行
mysql> slave start;
如此執行後，slave恢複了正常。
不過稍後蚊子使用/etc/init.d/mysqld restart重新開機了mysql服務，然後檢視slave狀态，發現又出現了上面的錯誤，然後檢視server_id發現這個數值又恢複到了1。
之後蚊子又重新檢視了一下/etc/my.cnf的内容，确認應該不是這個檔案的問題，于是去google查了一下，看到mysql在啟動的時候會查找/etc/my.cnf、DATADIR/my.cnf，USER_HOME/my.cnf。
===========================主從配置常見問題===========================
一、做了MySQL主從也有一段時間了，這兩天檢查磁盤空間情況，發現放資料庫的分區磁盤激增了40多G，一路檢視下來，發現配置好主從複制以來到現在的binlog就有40多G，原來根源出在這裡，檢視了一下my.cnf，看到binlog的 size是1G就做分割，但沒有看到删除的配置，在MySQL裡show了一下variables：
mysql>show variables like '%log%';
查到了，
| expire_logs_days | 0 |
這個預設是0，也就是logs不過期，這個是一個global的參數，是以需要執行
set global expire_logs_days=8;
這樣8天前的log就會被删除了，如果有回複的需要，請做好備份工作，但這樣設定還不行，下次重新開機mysql了，配置又恢複預設了，是以需在my.cnf中設定，
expire_logs_days = 8
這樣重新開機也不怕了。
現在我在生産環境下的做法是将此時間設為0，然後備份mysql日志檔案，然後再手動清理此檔案。
想要恢複資料庫以前的資料,執行
mysql>show binlog events;
由于資料量很多，檢視起來很麻煩，光打開個檔案就要閃半天，是以應該适當删除部分可不用的日志。
并且如果使用的時間足夠長的話，會把我的硬碟空間都給吃掉。
1、登入系統，/usr/bin/mysql
使用mysql檢視日志：
mysql>show binary logs;
+—————-+———–+
| Log_name | File_size |
| ablelee.000001 | 150462942 |
| ablelee.000002 | 120332942 |
| ablelee.000003 | 141462942 |
2、删除bin-log(删除ablelee.000003之前的而沒有包含ablelee.000003)：
mysql> purge binary logs to ′ablelee.000003′;
Query OK, 0 rows affected (0.16 sec)
3、查詢結果(現在隻有一條記錄了)：
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: ablelee.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
1 row in set (0.01 sec)
(ablelee.000001和ablelee.000002已被删除)
mysql> show binary logs;
| ablelee.000003 | 106 |
1 row in set (0.00 sec)
(删除的其它格式運用!)
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
用于删除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引檔案中的清單中被删除，這樣被給定的日志成為第一個。
例如：
PURGE MASTER LOGS TO 'mysql-bin.010'; PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
二、現在手上蠻多項目的資料庫用的是MySQL，由于權限等原因，暫時不友善部署Nagios監控MySQL主從複制，是以我一般在從機上配置了SHELL腳本用來監控MySQL的主從狀态(設定為每十分鐘運作一次)，并且每次出問題時将确切日期寫進錯誤日志，友善事後排查原因，腳本内容如下：
#!/bin/bash
#check MySQL_Slave Status
#crontab time 00:10
MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'`
MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`
STATUS=$(/usr/local/webserver/mysql/bin/mysql -u yuhongchun -pyuhongchun101 -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running")
IO_env=`echo $STATUS | grep IO | awk ' {print $2}'`
SQL_env=`echo $STATUS | grep SQL | awk '{print $2}'`
if [ "$MYSQLPORT" == "3306" ]
then
echo "mysql is running"
else
mail -s "warn!server: $MYSQLIP mysql is down" [email protected]
fi
if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]
echo "Slave is running!"
echo "####### $date #########">> /data/data/check_mysql_slave.log
echo "Slave is not running!" >> /data/data/check_mysql_slave.log
mail -s "warn! $MySQLIP_replicate_error" [email protected] << /data/data/check_mysql_slave.log
建議每十分鐘運作一次。
*/10 * * * * root /bin/sh /root/mysql_slave.sh
記得在每台MySQL從機上配置設定一個yuhongchun的使用者，權限大些也沒關系，隻限定在本地運作，如下所示：
grant all privileges on *.* to "yuhongchun"@"127.0.0.1" identified by "yuhongchun101";
grant all privileges on *.* to "yuhongchun"@"localhost" identified by "yuhongchun101";
腳本設計思路：
1、此腳本應該能适應各種各樣不同的内外網環境，即IP不同的環境;
2、讓腳本也順便監控下MySQL是否正常運作;
三、innodb_buffer_pool_size的設定。
這個參數定義了InnodDB存儲引擎的表資料和索引資料的最大記憶體緩沖區大小。和MyISAM存儲引擎不同，MyISAM的key_buffer_size隻緩存索引鍵，而innodb_buffer_pool_size卻是同時為資料塊和索引塊 做緩存，這個特征和Oracle是一樣的，這個值設得越高，通路表中資料需求的I/O就越少。在一個專用的資料庫伺服器，可以設定這個參數達機器實體記憶體的80%，我現在一般的做法是配置成實體記憶體的 1/4，比如8G記憶體的生産資料庫，我一般會配置成2G左右。
四、測試了很長一段時間的MySQL的負載均衡，最後綜合了老男孩和其它技術高手的意見，最終決定還是用LVS+Keepalived來作為MySQL的負載均衡，這是因為後端機器超過10台時，LVS的性能還是最好的;如果在3-5台左右，HAProxy也可以很輕松的搞定工作。
五、大家都很清，磁盤I/O總會成為資料庫的性能瓶頸，這時候我們應該如何在生産環境下選擇合适的RAID級别呢?
1、如果資料讀寫都很頻繁，可靠性要求也很高，最好選擇RAID10;
2、如果資料讀很頻繁，寫相對較少，對可靠性有一定要求，可以選擇RAID5;
3、如果資料讀寫都很頻繁，但可靠性要求不高，可以選擇RAID0。
4、對于核心業務的資料庫主從同步，建議從機的備份時間往後延遲一段時間，通常的做法是延遲一天左右。