mysql主從複制
标簽：mysql主從同步
mysql主從複制
在實際企業應用環境當中，單台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的賬号，并授權給從伺服器。
[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;