MySQL 複製 on Ubuntu12.04

MySQL 複製 on Ubuntu12.04

一、簡介

MySQL 複製（Replication）基於binnary logging機制，將資料在master和slave之間同步。無論機制、配置、運行維護都比MSSQL2000的複製簡單穩定很多（mssql2000之後的版本沒用過）。

Mysql master將資料更新、變化作為事件寫入binary log，Mysql slave讀取binary log的事件並將相同的更新、變化寫入自己的資料庫。

Master只管自己寫binary log，不用照看slave。Slave只要線上，資料即可持續同步；即使slave離線，恢複線上後可以繼續執行未完成的複製。這一點非常適合進行資料備份，因為在slave上備份絲毫不影響master的運行。

Master可以有多個slaves，slave也可以同時作為master並且擁有自己的slaves。

每一個master和slave必須在my.cnf中指定唯一的 server-id。

在slave上，複製可以隨時使用簡單的指令停止、恢複。

binary log有三種格式：STATEMENT，ROW，MIXED。STATEMENT格式基於SQL語句，效能高但不支援某些SQL語句；ROW格式基於行，能克服STATEMENT格式的缺點但會產生較大的日誌；MIXED結合二者特點，預設使用STATEMENT，當STATEMENT格式不適用時自動轉為ROW格式。推薦MIXED，可以在Master的my.cnf中設定此參數。

二、設定Mysql複製非常簡單，情境如下。

OS：Ubuntu12.04 X86_64

Master：主機名稱 mysql-0，IP 192.168.150.200

Slave： 主機名稱 mysql-1，IP 192.168.150.204

1、在Master上：

編輯檔案/etc/mysql/my.cnf 確保有如下幾行（後3行是InnoDB引擎必須的，建議加上）
[mysqld]

bind-address=0.0.0.0
server-id=1
log-bin=mysql-binary-log　　　#這個名字自己隨便起
binlog_format=MIXED
innodb_fast_shutdown=0
innodb_flush_log_at_trx_commit=1
sync_binlog=1

然後重啟Master的mysql server。

建立一個使用者

mysql> create user 'repl'@'192.168.150.200' identified by 'pass4repl';
mysql> grant replication slave on *.* to 'repl'@'192.168.150.200';

為slave獲得binary log座標，首先要使資料庫暫時唯讀，執行：
mysql> FLUSH TABLES WITH READ LOCK;

此終端視窗不動，另外開啟一個終端視窗串連到mysql server，執行：
mysql> SHOW MASTER STATUS;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| mysql-binary-log.000001 |      106 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

記下來這兩個值mysql-binary-log.000001、106，一會兒設定slave時要用到，注意，這是我的測試結果，你的結果很可能不同。（Binlog_Do_DB 和 Binlog_Ignore_DB也許能決定哪些庫複製哪些庫不複製，這個以後研究）

如果Master上已經有了資料，則需要先用mysqldump或者未經處理資料拷貝產生現有資料的”快照“，應用到Slave伺服器上，再配置Slave。

一般用mysqldump做備份，再拷貝到slave上恢複就能滿足大部分需求。特殊情況稍微複雜些，可以參考官方連結：
16.1.1.5 Creating a Data Snapshot Using mysqldump
16.1.1.6 Creating a Data Snapshot Using Raw Data Files

如果Master上沒有使用者資料，或者已經把資料快照應用到Slave上，就可以在剛才執行”FLUSH TABLES WITH READ LOCK;“的終端視窗裡執行
mysql> UNLOCK TABLES;

以解鎖Master資料庫，然後繼續配置Slave。（或者退出該終端視窗也可）

2、在Slave上：

編輯檔案/etc/mysql/my.cnf 確保有如下2行
[mysqld]

report-host=192.168.150.200
server-id=2

重啟一下slave的mysql server。

然後在mysql中執行：
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.150.200',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='pass4repl',
    -> MASTER_LOG_FILE='mysql-binary-log.000001',
    -> MASTER_LOG_POS=106;

大功告成！ 

3、簡單的管理工作（結果資料來自官方文檔）

在Master上

mysql> SHOW PROCESSLIST \G;
*************************** 4. row ***************************
    Id: 10
  User: root
  Host: slave1:58371
    db: NULL
Command: Binlog Dump
  Time: 777
  State: Has sent all binlog to slave; waiting for binlog to be updated
  Info: NULL

mysql> SHOW SLAVE HOSTS;
+-----------+--------+------+-------------------+-----------+
| Server_id | Host  | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
|        10 | slave1 | 3306 |                0 |        1 |
+-----------+--------+------+-------------------+-----------+
1 row in set (0.00 sec)

在slave上

mysql> SHOW SLAVE STATUS \G
mysql> STOP SLAVE;
mysql> START SLAVE; 

參考：http://dev.mysql.com/doc/refman/5.5/en/replication.html Mysql的官方文檔寫得真心不錯，清晰易懂。

禁用Mysql複製：
1、在Slave上停止複製，執行：
mysql> STOP SLAVE;

2、在Master上，編輯 /etc/mysql/my.cnf，注釋掉如下幾行：
report-host=192.168.150.200
server-id=2

3、在Slave上尋找master.info檔案：
~$ sudo find / -name master.info | grep master.info
找到後，刪除，或者重新命名，或者移動到其他目錄。
重啟Slave的mysql server。
mysqld-relay-bin.* 等檔案可以刪除了。

3、在Master上，編輯 /etc/mysql/my.cnf，注釋掉如下幾行：
server-id=1
log-bin=mysql-binary-log
binlog_format=MIXED
innodb_flush_log_at_trx_commit=1
sync_binlog=1

innodb_fast_shutdown=0 可以選擇保留
重啟Slave的mysql server。
mysql-binary-log.* 等檔案可以刪除了。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------