MySQL 設定 Replication (Master - Slave)

MySQL 設定寫入 Master 後, 自動 Replication 到 Slave 去, 運作基本原理是:

1. INSERT/UPDATE/DELETE 語法, 自動寫入 Master 的 binlog file.
2. 由 GRANT REPLICATION 授權的帳號, 自動將 SQL 語法 repl 到 Slave 的 DB 執行.
3. 因而完成 Replication 的動作.

下述詳細可見: MySQL 5.0 Reference Manual :: 15 Replication

設定 Replication 的操作 (Master)

1. $ sudo vim /etc/mysql/my.cnf # 下面是 Debian Linux 的設定, 找到下面的設定, 新增/修改 成下面這樣子.
   

   #bind-address           = 127.0.0.1
   server-id               = 1
   log_bin                 = /var/log/mysql/mysql-bin.log

   # 若是 innodb, 且有用 transaction 的話, 需再加入下面兩行
   innodb_flush_log_at_trx_commit=1
   sync_binlog=1

2. $ sudo /etc/init.d/mysql restart
3. $ mysql -u root -p # 進入 mysql
4. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_pass'; # 先假設 帳號 repl, 密碼 repl_pass, 此步驟是 設定 repl 的帳號/密碼, 格式: GRANT REPLICATION SLAVE ON *.* TO
   'repl_user'@'%.mydomain.com' IDENTIFIED BY 'repl_password'; (Replace
   <some_password>with a real password!)
5. mysql> FLUSH TABLES WITH READ LOCK; # 先讓 DB 不要再寫資料進去
6. mysql> SHOW MASTER STATUS; # 這邊資料都要記好, 等一下設定 Slave 要用
   

   +----------------------+------------+------------------+----------------------+
   | File                     | Position   | Binlog_Do_DB | Binlog_Ignore_DB  |
   +----------------------+------------+------------------+----------------------+
   | mysql-bin.000014  |      232   |                      |                          |
   +----------------------+------------+------------------+----------------------+

7. mysql> quit # 離開, 準備倒資料
8. 倒資料, 可以由下述的方法倒, 此次採用步驟 1
   

   1. $ mysqldump -u root -p DB > dbdump.sql
   2. $ mysqldump --all-databases --lock-all-tables >dbdump.sql
   3. $ mysqldump --all-databases --master-data >dbdump.sql # --master-data: 會自動將CHANGE MASTER 的語法帶在裡面

9. $ mysql -u root -p # 進入 mysql
10. mysql> UNLOCK TABLES; # dump 完資料後, 進去 mysql 解除唯讀
11. 再來就是將 dbdump.sql scp 到 Slave 去即可.
12. Master 就到此為止.

設定 Replication 的操作 (Slave)

1. $ sudo vim /etc/mysql/my.cnf
   

   server-id               = 2  # server-id 不能與其它機器相同
   log_bin                 = /var/log/mysql/mysql-bin.log

2. $ mysql -u root -p # 進入 mysql
3. mysql> create database DBNAME;
4. mysql> use DBNAME; source dbdump.sql; 或 $ mysql -u root -p DBNAME < dbdump.sql
5. mysql> CHANGE MASTER TO
        MASTER_HOST='MASTER_HOSTNAME',
        MASTER_USER='repl',
        MASTER_PASSWORD='repl_pass',
        MASTER_LOG_FILE='mysql-bin.000014',
        MASTER_LOG_POS=232; # 這邊就要用到之前 Master 抄下來的值.
6. mysql> START SLAVE; # 這樣子就會開始 Replication 了, 會將 LOG_POS 之後新的資料開始 sync 回來.
7. mysql> show master status; # 檢查一下設定
8. mysql> show slave status; # 檢查一下設定, 看是不是有異常狀況.

測試

1. 在 master: mysql> create database test2;
2. 在 slave: mysql> show database; # 應該會看到 test2
3. 在 master 上的任何操作應該都會馬上 replication 到 slave 去.

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant replication slave,replication client on database1.* to uncoccpy@'192.168.%.%' identified by 'rePlication08#23';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

這個是因為replication slave,replication不能授權給單個數據庫
我們需要在my.cnf中這樣的配置
可以在選擇在那端進行過濾
master端：
binlog-do-db= test #二進制需要同步的數據庫名
binlog-ignore-db=mysql #避免同步mysql 用戶配置，以免不必要的麻煩

slave端:
replicate_do_db=test (do這個就是直接指定的意思)

replicate_ignore_db=

MySQL 多台機器的多重 Replication 設定

MySQL 要設定 Replication 可以參考此篇: MySQL 設定 Replication (Master - Slave)

但是要設定多台機器一直持續(一層一層) Replication 下去, 預設是有無法達到的.

註:

• Replication 從 A -> B 照上面設定即可.
• 但是 Replication 要設定 A -> B -> C, 會發現到, A -> B 是可以動的, B -> C 也是可以動, 但是 A -> B -> C 不會動.(A -> C 的指令, 不會被傳過去)

MySQL 多台機器多重 Replication 設定方式

想要作到 A -> B -> C, 只需要於 B 的 [mysqld] 設定下述即可:

log-bin=mysql-bin
log-slave-updates

範例

[mysqld]
server-id = 1
log-bin=mysql-bin
log-slave-updates

強迫移除 MySQL Root 密碼

這是給不小心忘記 MySQL root 密碼、不小心刪掉 root 的人, 不需要因此而重灌 MySQL.(只需要依此步驟, 即可重新設定 root 密碼)

環境: Debian / Ubuntu Linux

移除 MySQL Root 密碼步驟

1. sudo su -
2. /etc/init.d/mysql stop
3. /usr/sbin/mysqld --skip-grant-tables --user=root & # 啟動 MySQL
4. mysql -u root # 已經可以不用密碼進入囉~
5. mysql> UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root'; # 將 root 密碼清掉, 或於此設定想要的密碼.
6. mysql> quit
7. /etc/init.d/mysql restart # 完成.

MySQL 連線認證授權步驟

MySQL 在增加 user 時, 可以使用 INSERT mysql db 或 GRANT 的方式來增加 user, 但是為何使用 grant 增加, 於 user table 的 *priv 權限值都是 N, 但是權限又是正常照設定的運作?, 到底 MySQL 連線認證是怎麼樣運作的呢?..

MySQL 連線認證授權步驟:

• MySQL connect -> mysql db -> user table (id, password) -> db table (exec priv) -> user table(priv)

MySQL 有個 mysql 的 db, 記錄 user, db 的 table.

1. 一個 connect 要建立, 會先檢查 user table, 看看 帳號、密碼是否正確, 正確的話, connection 正式建立.
2. 再來是要檢查是否有執行的權限, 會再去看 db 的 table, ex: 檢查是否有 Select, Update, Insert, Delete... 等權限.
3. 而 user table 的 *_priv等 權限, 是 db table 查不到的狀況下或其它更細節的狀況才會用到.(會發現用 grant 授權的, user table 的 *_priv 等 的值, 都會是 N)

MySQL 要特別注意 要設定哪些IP 可以連進來時, 要注意不能重覆設定(除了本機外).

假設本機 IP 是 1.1.1.1, 本機要設定 localhost 和 1.1.1.1 的那個帳號可以連進來, 不然 mysql -u id -p -h hostname 會連不進去. 但是若其它外部連過來的機器(ex: 2.2.2.2), 只能設1組IP設定, ex: 2.2.2.% 或 2.2.2.2, 這兩個只能有一個存在, 若設 domain, 也要注意 domain 反解回來的 IP 有沒有跟這IP 重覆, IP 重覆在平常運作下是不會有問題, 但是在量大的狀況下, 就會發生有些 connect 會 Access denied 的狀況.

MySQL Replication Status

• show master status
• show slave status

以下是 Master 機器上, show master status 出來的 欄位 和 說明:

• Master_Host: dbm1.domain_name
• Master_User: repl
• Master_Port: 3306
• Connect_retry: 60 , 這個 mysql server 重啟動到現在已經 connect 幾次了(自己 restart 會歸零)
• Master_Log_File: dbm1-bin.009 , 目前 Master 上已經寫到第幾個了
• Read_Master_Log_Pos: 991863990 , Slave讀到 Master 這個 log file 的第幾筆了(master 上的 file)
• Relay_Log_File: dbs1-relay-bin.008 , Slave目前正在寫入的 binary log (slave 上的 file)
• Relay_Log_Pos: 303654057 , 寫到第幾筆了
• Relay_Master_Log_File: dbm1-bin.009 , Slave目前傳到 Master 上的第幾個(目前正在抓哪一個過來), 目前 Master上, 已經讀到哪個 log file(relication) binary log(一堆 SQL 指令執行的記錄, 可用 mysqlbinlog 讀取)
• Slave_IO_Running: Yes , 這個 process 有在 run(抓 binary log), 抓 log 回來 (No: 可能原因有 網路斷, 權限問題, master stop)
• Slave_SQL_Running: Yes , 是否有在執行 binary log (error)
• Replicate_do_db:
• Replicate_ignore_db:
• Last_errno: 0 , 停掉前發生什麼事情, error number, 可用 perror 查詢
• Last_error: (error message)
• Skip_counter: 0 (set db slave skip counter = 1, start slave) 跳過這一筆
• Exec_master_log_pos: 991863990 (要與 Read_Master_Log_Pos 一樣, 代表沒有 delay)
• Relay_log_space: 303654057 目前有多少空間可以寫

平常最主要就是看 Slave_IO_Running, Slave_SQL_Running 是否是 Yes, 是 Yes 的話, 應該就都是正常在跑的狀況, 若是 No 的話, 就去看一下 Last_error 

MySQL 快速為線上運作的 Master 增加 SLAVE(設定 Replication)

操作步驟

1. ssh master.hostname # 先設 MASTER
2. $ mysql -u root
3. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_password'; # 先假設 帳號 repl, 密碼 repl_password
4. mysql> FLUSH TABLES WITH READ LOCK;
5. mysql> quit
6. $ mysqldump -u root DATABASE_NAME --master-data > DATABASE_NAME.sql
   # 若不加 --master-data, 就需要用 show master status 來記下要組合出下述語法:
   # CHANGE MASTER TO MASTER_HOST='MASTER_HOSTNAME', MASTER_USER='repl', MASTER_PASSWORD='repl_passord', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=232;
7. $ mysql -u root
8. mysql> UNLOCK TABLES;
9. mysql> quit
10. # 到此 MASTER 設定就算完工, 也已經恢復上線了, 再下來就是 SLAVE 囉~
11. ssh slave.hostname
12. $ scp master.hostname:DATABASE_NAME.sql
13. $ mysql -u root
14. mysql> CREATE DATABASE DATABASE_NAME;
15. mysql> use DATABASE_NAME;
16. mysql> source DATABASE_NAME.sql;
    # 因為上述有加入 --master-data 的命令, 所以 CHANGE MASTER 等, 已經有自動加在檔案的開頭, 不過此 CHANGE MASTER 並沒有寫 MASTER 主機的資訊, 所以可於下述 /etc/my.cnf 設定, 或者去修改 DATABASE_NAME.sql, 將 CHANGE MASTER 加入主機資訊. (在此採用修改 /etc/my.cnf 的作法)
17. mysql> quit
18. $ vim /etc/my.cnf
    

    log-bin=mysql-bin
    server-id   = 3
    master-host     =   MASTER_DB.HOSTNAME
    master-user     =   repl
    master-password =   repl_password
    master-port     =  3306

19. $ sudo /usr/local/etc/rc.d/mysql-server restart # BSD 預設路徑
20. $ mysql -u root
21. mysql> start slave;
22. mysql> show slave status \G

會看到若 MASTER 有增加資料, Exec_Master_Log_Pos 這個值就會跟著增加. (Exec_Master_Log_Pos: 執行 MASTER LOG 的 POSITION), 在此任何 MASTER 的 新增/修改 都應該會自動 replication 過來 SLAVE 囉~ (中間中斷的時間, 資料也會自動補上, 因為 --master-data 已經將當時的 POSITION 記下來了, 只要倒回去, 就會自動將資料補回來)

註: 如果有完整的 mysql bin log 的話, 可以直接設好 replication 即可, 就會自動開始從 binlog 最前面開始抓, 連停機都不用