Mysql主從複製(master-slave)實際操作案例_Mysql

在這一章節裡， 我們來瞭解下如何在 Mysql 中進行使用者授權及主從複製
 
這裡先來瞭解下 Mysql 主從複製的優點:
 
1、 如果主伺服器出現問題， 可以快速切換到從伺服器提供的服務
2、 可以在從伺服器上執行查詢操作， 降低主伺服器的訪問壓力
3、 可以在從伺服器上執行備份， 以避免備份期間影響主伺服器的服務
注意一般只有更新不頻繁的資料或者對即時性要求不高的資料可以通過從伺服器查詢， 即時性要求高的資料仍然需要從主要資料庫獲得
 
在這裡我們首先得完成使用者授權， 目的是為了給從伺服器有足夠的許可權來遠程登入到主伺服器的 Mysql
 
在這裡我假設
主伺服器的 IP 為: 192.168.10.1
從伺服器的 IP 為: 192.168.10.2
 
Mysql grant 使用者授權
 
查看 Mysql 的使用者表

複製代碼 代碼如下:

msyql> mysql -uroot -p123123;
msyql> select user, host, password from mysql.user;

結果如下:

複製代碼 代碼如下:

+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root             | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+------------------+-----------+-------------------------------------------+

從如上表中看以看出 root 使用者只能從本機登入 Mysql， 也就是來自 localhost 或者 127.0.0.1
 
現在來通過 grant 命令來添加授權使用者

複製代碼 代碼如下:

msyql> ？ grant   //查看 grant 的詳細用法
 
msyql> grant all on *.* to user1@192.168.10.2 identified by "123456"; // *.* = 所有的資料庫.所有的表
//或者
msyql> grant replication slave on *.* to 'user2'@'192.168.10.%' identified by "123456"; // %代表萬用字元

通過了 grant 命令給予了來自 192.168.10.2 的使用者 user1 許可權， 允許其遠程登入, 如下:

複製代碼 代碼如下:

+------------------+--------------+-------------------------------------------+
| user             | host         | password                                  |
+------------------+--------------+-------------------------------------------+
| root             | localhost    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root             | 127.0.0.1    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| user1            | 192.168.10.2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| user2            | 192.168.10.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+--------------+-------------------------------------------+

此時就可以在 192.168.10.2 的機器上訪問 10.1 的 Mysql 了， 如下:

複製代碼 代碼如下:

msyql> mysql -uuser1 -p123456 -h192.168.10.1;

Mysql bin-log 日誌
 
開啟 bin-log 二進位日誌， 它儲存了所有增刪改的操作， 以便於資料恢複或同步

修改主伺服器 mysql 設定檔:

複製代碼 代碼如下:

shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
[mysqld]
 
#開啟慢查詢日誌， 記錄查詢過長的 sql 語句，以便於最佳化
log_slow_queries   = /var/log/mysql/mysql-slow.log
 
#開啟 bin-log 日誌
log-bin            = /var/log/msyql/mysql-bin.log

添加完成後重啟 Mysql 服務

複製代碼 代碼如下:

shawn@Shawn:~$ sudo /etc/init.d/mysql restart

現在你可以通過如下命令來查看 bin-log 日誌是否成功開啟

複製代碼 代碼如下:

mysql> show variables like "%log_%";
 
| log_bin                 | ON        |
| log_slow_queries        | ON        |

如果顯示為 ON， 那麼就可以在 /var/log/mysql/ 檔案夾看到 mysql-bin.000001 二進位檔案
 
關於 bin-log 日誌的相關操作:

複製代碼 代碼如下:

mysql> flush logs；

此時就會多一個最新的 bin-log 日誌

複製代碼 代碼如下:

mysql> show master status;

查看最後一個 bin-log 日誌， 如下:

複製代碼 代碼如下:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
 
mysql> show master logs;

查看所有 bin-log 日誌， 如下:

複製代碼 代碼如下:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      4340 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
 
mysql> reset master;

清空所有 bin-log 日誌

複製代碼 代碼如下:

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000001 | more

查看 bin-log 日誌內容

複製代碼 代碼如下:

#如果有字元集問題的話可以執行:
shawn@Shawn:~$ mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 | mysql -uroot -p123123 test;
恢複 mysql-bin.000002 中所有的操作到 test 資料庫中

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-position="193" --stop-position="398" | mysql -uroot -p123123 test;
恢複 mysql-bin.000002 中指定的操作(position)到 test 資料庫中

 
Mysql 主從複製 - 資料同步
 
到這一步的時候首先確保 Mysql 使用者授權已經完成以及 Mysql bin-log 日誌已經成功開啟
並確保每台伺服器的 server-id 是唯一的
 
再次修改主伺服器(192.168.10.1)的 mysql 設定檔:

複製代碼 代碼如下:

shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
#取消 server-id 注釋符號
server-id   = 1
/****************************/
 
#重啟 Mysql 服務
shawn@Shawn:~$ sudo /etc/init.d/mysql restart

到這裡， 主伺服器的配置已經完成， 很簡單
 
這次我們主要做的是讓從伺服器同步主伺服器的資料， 同步的是將來所有對主服務做的增刪改操作， 但是現有主伺服器中的大量資料得先手動同步到從伺服器， 操作如下:

複製代碼 代碼如下:

#清空一下主伺服器的 bin-log 日誌， （可選: 保險操作， 防止主從 bin-log 日誌混亂）
mysql> reset master;
 
#然後備份匯出主伺服器中現有的 test 資料庫
shawn@Shawn:~$ mysqldump -uroot -p123123 test -l -F > /tmp/test.sql;
 
-F = flush logs, 產生新的記錄檔， 包括 bin-log 日誌
-l = lock 資料庫， 防止在匯出的時候被寫入資料， 完成後自動解鎖
 
#完成後把檔案傳輸給從伺服器
shawn@Shawn:~$ scp /tmp/test.sql 192.168.10.2:/tmp/
 
#然後再查詢確保一下從伺服器已經成功授過權
mysql> show grants for user1@192.168.10.2\G
 
*************************** 1. row ***************************
Grants for user1@192.168.10.2:
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.10.2'
IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

完成後， 現在我們到從伺服器 (192.168.10.2) 匯入現有的資料:

複製代碼 代碼如下:

#清空一下從伺服器的 bin-log 日誌， （可選: 保險操作）
mysql> reset master;
 
#然後匯入主伺服器中現有的資料
shawn@Shawn:~$ mysqldump -uroot -p123123 test -v -f < /tmp/test.sql;

 
-v = 查看匯入的詳細資料
-f = 是當中間遇到錯誤時， 可以 skip 過去， 繼續執行下面的語句
當然你也可以用 source 命令匯入
好了， 目前為止主伺服器(192.168.10.1)和從伺服器(192.168.10.2)現有的資料已經成功手動同步
 
接下來修改從伺服器(192.168.10.2)的 mysql 設定檔:

複製代碼 代碼如下:

shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
#取消 server-id 注釋符號, 並修改值
server-id       = 2
 
#取消 master-host 注釋符號, 並修改值
master-host     = 192.168.10.1
 
#取消 master-user 注釋符號, 並修改值
master-user     = user1
 
#取消 master-password 注釋符號, 並修改值
master-password = 123456
 
#取消 master-port 注釋符號, 並修改值, 主伺服器預設連接埠號碼為: 3306
master-port     = 3306
/****************************/
 
#重啟 Mysql 服務
shawn@Shawn:~$ sudo /etc/init.d/mysql restart

設定檔修改完成， 此時在從伺服器中登入自己的 Mysql, 而不是遠程登入主伺服器(192.168.10.1)

複製代碼 代碼如下:

#在從伺服器中登入自身的 Mysql
msyql> mysql -uroot -p123123;
 
#查看是否已經取得同步
msyql> show slave status\G
 
*************************** 1. row ***************************
      Connect_Retry: 60
    Master_Log_FIle: mysql-bin.000002
Read_Master_Log_Pos: 106
   Slave_IO_Running: Yes 
  Slave_SQL_Running: Yes

Slave_IO_Running 如果是 Yes 的話代表成功從主伺服器中同步到 bin-log 日誌
Slave_SQL_Running 如果是 Yes 的話代表成功執行 bin-log 日誌中的 SQL 陳述式
此時的 Master_Log_FIle 和 Read_Master_Log_Pos 的值應該對應主伺服器中的 show master status 命令的值
Connect_Retry 中的 60 代表每 60 秒就去主伺服器同步 bin-log 日誌
 
OK, 如果你看到的是那兩個關鍵的 Yes， 那你就可以去測試了， 在主伺服器插入新的資料， 再去從伺服器查看， 不出意外的話， 你會興奮一下， 資料已經同步了
 
這裡再說一下其他經常用到的命令:

複製代碼 代碼如下:

#啟動複製線程
msyql> start slave
 
#停止複製線程
msyql> stop slave
 
#動態改變到主伺服器的配置
msyql> change master to
 
#查看從資料庫運行進程
msyql> show processlist

這裡也同時說一下操作中的常見錯誤:
 
問題: 從資料庫無法同步
Slave_SQL_Running 值為 NO， 或 Seconds_Bebind_Master 值為 Null

原因:
一、 程式有可能在 slave 上進行了寫操作
二、 也有可能是 slave 機器重啟後， 交易回復造成的

解決方案一:

複製代碼 代碼如下:

msyql> stop slave;
 
msyql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
 
msyql> start slave;

解決方案二:

複製代碼 代碼如下:

msyql> stop slave;
 
#查看主伺服器上當前的 bin-log 日誌名和位移量
msyql> show master status;
 
#擷取到如下內容:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      286 |              |                  |
+------------------+----------+--------------+------------------+
 
#然後到從伺服器上執行手動同步
msyql> change master to
    -> master_host="192.168.10.1"
    -> master_user="user1"
    -> master_password="123456"
    -> master_post=3306
    -> master_log_file="mysql-bin.000005"
    -> master_log_pos=286;
    
msyql> start slave;

再次通過 show slave status 查看:
如果 Slave_SQL_Running 的值變為 Yes， Seconds_Bebind_Master 的值為 0 時， 即正常
 
好了， 如上是我自己在操作中所總結的一些內容， 如有更好的建議， 歡迎留言一起探討
順便說一下， 我使用的是 Ubuntu 12.04