mysql之主從複製篇
http://2526575.blog.51cto.com/2516575/689199?token=BAkPV1ddW23Ssa6EnpGNjJs
在實際企業應用環境當中，單臺mysql數據庫是不足以滿足日後業務需求的。譬如服務器發生故障，沒有備份服務器來提供服務的話，業務就得停止。介於這種情況，我們來學習一下mysql主從複製。
使用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的賬號，並授權給從服務器。
[[email protected] ~]# 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。修改之後如下所示：
[mysqld]
log-bin=mysql-bin 啓動二進制文件
server-id=10 服務器ID
2）、重啓mysql服務。
[[email protected] ~]# mysqladmin -p123456 shutdown
[[email protected] ~]# mysqld_safe --user=mysql &
3)、登錄mysql，執行如下語句
[[email protected] ~]# 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主從能否正常複製。
在主數據庫上新建一個庫，並且在庫中寫一個表和一些數據。
[[email protected] ~]# mysql -uroot –p123456
mysql> create database mysqltest;
mysql> use mysqltest;
mysql> create table user(id int(5),name char(10));
mysql> insert into user values (00001,'zhangsan');
在從數據庫中驗證一下，是否正常複製到數據。
[[email protected] ~]# mysql -uroot –p123456
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、啓動主從複製功能，並檢查是否正常複製。
開始升級:
在主數據庫上操作：
1)、確保/etc/my.cnf中有如下參數，沒有的話需手工添加，並重啓mysql服務。
[mysqld]
log-bin=mysql-bin 啓動二進制文件
server-id=1 服務器ID
2)、登錄mysql，在mysql中添加一個backup的賬號，並授權給從服務器。
[[email protected] ~]# mysql -uroot –p123456 登錄mysql
mysql> grant replication slave on *.* to 'backup'@'192.168.48.130' identified by 'backup'; 創建backup用戶，並授權給192.168.48.130使用。
3)、查看已有的數據庫有哪些；
mysql> show databases;
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目錄下。
[[email protected] ~]#scp mysqltest.sql test1234.sql [email protected]:/root/
這中間需要輸入一次驗證口令。見下圖
8)、切換回之前的終端，進行表解鎖操作。
mysql> unlock tables;
主數據庫服務器上的操作告一段落。
在從數據庫上操作：
1)、確保/etc/my.cnf中有log-bin=mysql-bin和server-id=1參數，並把server-id=1修改爲server-id=10。修改之後如下所示：
[mysqld]
log-bin=mysql-bin 啓動二進制文件
server-id=10 服務器ID
2)、重啓mysql服務。
[[email protected] ~]# mysqladmin -p123456 shutdown
[[email protected] ~]# mysqld_safe --user=mysql &
3)、登錄數據庫，確認要同步的庫名不存在。
[[email protected] ~]# mysql -uroot –p123456
mysql> show databases;
4)、創建名爲mysqltest和test1234的庫。
mysql> create database mysqltest;
mysql> create database test1234;
4)、切換一個終端，將傳過來的兩個數據文件分別導入對應的數據庫下。
[[email protected] ~]# mysql -uroot –p123456 mysqltest < mysqltest.sql
[[email protected] ~]# 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;
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)、啓動主從複製功能。
mysql> start slave;
7)、
檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均爲Yes，則主從複製連接正常。
mysql> show slave status\G
驗證主從是否正常配置：
在主數據庫上插入數據：
mysql> show databases;
mysql> show tables;
mysql> use mysqltest;
mysql> select * from user;
mysql> insert into user values (4,'ergou');
mysql> insert into user values (5,'sanwazi');
在從數據庫上查看是否正常同步：
mysql> show databases;
mysql> use mysqltest;
mysql> select * from user;
上圖可以看出，主從之間已經可以正常複製了。
今天就先到這裏吧O(∩_∩)O~
mysql主從複製碰到的小問題
一個朋友在做mysql主從複製的時候碰到了一些問題，他已經按照我之前的教程一步步做了。但是就是不知道是怎麼回事。從數據庫中使用命令show slave status\G查看到的結果如下圖:
我們知道，要想mysql主從複製成功的先決條件是Slave_IO_Running和Slave_SQL_Running均爲Yes，而上圖所示的結果顯然是有一個不滿足條件。
再根據Last_IO_Error的錯誤代碼2013及下面的錯誤提示我們可以知道，從數據庫無法正常連接到主數據庫。
這裏原因有三：
1、主數據庫沒有給從數據庫登錄授權
2、網絡不通
3、有防火牆
明白這三點之後我們就可以針對原因依次來解決它們：
登錄主數據庫，給從數據庫授權
[[email protected]host ~]# mysql -uroot –p123456 登錄主數據庫
mysql> grant replication slave on *.* to 'backup'@'192.168.48.130' identified by 'backup'; 創建backup用戶，並授權給從數據庫連接使用。
檢查網絡是否通暢
[[email protected] ~]# ping -c 4 192.168.48.128 再從服務器上ping主服務器。
分別在主從服務器上檢查防火牆狀態
我們可以直接關閉防火牆
運行命令：[[email protected] ~]# setup
選擇防火牆（Firewall configuretion）這一項.
將它Disabled掉。
或者你可以直接使用命令：
[[email protected] ~]# service iptables stop 即時生效，系統重啓後失效
[[email protected] ~]# 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。