mysql主從、主主復制詳解
Mysql 主從、主主復制詳解
一、復制的介紹
MySQL支持單向、異步復制，復制過程中一個服務器充當主服務器，而一個或多個其它服務器
充當從服務器。主服務器將更新寫入二進制日志文件，並維護文件的一個索引以跟蹤日志循環。
這些日志可以記錄發送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從
服務器在日志中讀取的最後一次成功更新的位置。從服務器接收從那時起發生的任何更新，然後
封鎖並等待主服務器通知新的更新。
請注意當你進行復制時，所有對復制中的表的更新必須在主服務器上進行。否則，你必須要小心，
以避免用戶對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的沖突。
單向復制有利於健壯性、速度和系統管理：
l 主服務器/從服務器設置增加了健壯性。主服務器出現問題時，你可以切換到從服務器作為
份。
l 通過在主服務器和從服務器之間切分處理客戶查詢的負荷，可以得到更好的客戶響應時間。
SELECT查詢可以發送到從服務器以降低主服務器的查詢處理負荷。但修改數據的語句仍然
應發送到主服務器，以便主服務器和從服務器保持同步。如果非更新查詢為主，該負載均
衡策略很有效，但一般是更新查詢。
l 使用復制的另一個好處是可以使用一個從服務器執行備份，而不會干擾主服務器。在備份
過程中主服務器可以繼續處理更新。
MySQL 提供了數據庫的同步功能，這對我們實現數據庫的冗災、備份、恢復、負載均衡等都是
有極大幫助的
MySQL 使用3 個線程來執行復制功能(其中1 個在主服務器上，另兩個在從服務器上。當發出
START SLAVE時，從服務器創建一個I/O線程，以連接主服務器並讓主服務器發送二進制日志。
主服務器創建一個線程將二進制日志中的內容發送到從服務器。從服務器I/O 線程讀取主服務
器Binlog Dump線程發送的內容並將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼
日志。第3個線程是SQL 線程，從服務器使用此線程讀取中繼日志並執行日志中包含的更新。
SHOW PROCESSLIST語句可以查詢在主服務器上和從服務器上發生的關於復制的信息。
默認中繼日志使用host_name-relay-bin.nnnnnn 形式的文件名，其中host_name 是從服務
器主機名，nnnnnn是序列號。用連續序列號來創建連續中繼日志文件，從000001開始。從服
務器跟蹤中繼日志索引文件來識別目前正使用的中繼日志。默認中繼日志索引文件名為
host_name-relay-bin.index。在默認情況，這些文件在從服務器的數據目錄中被創建。中繼日
志與二進制日志的格式相同，並且可以用mysqlbinlog讀取。當SQL 線程執行完中繼日志中的
所有事件後，中繼日志將會被自動刪除。
從服務器在數據目錄中另外創建兩個狀態文件–master.info 和relay-log.info。狀態文件保存
在硬盤上，從服務器關閉時不會丟失。下次從服務器啟動時，讀取這些文件以確定它已經從主服
務器讀取了多少二進制日志，以及處理自己的中繼日志的程度。
二、實驗環境
虛擬機操作系統：Centos 5.5 64bit
數據庫版本：mysql 5.1.49 （參考“Centos 5使用yum安裝Mysql”文檔）
A: master 計算機名：beijing IP地址：192.168.20.101
B: slave 計算機名：shanghai IP地址：192.168.20.102
三、mysql的單向復制
注意 mysql 數據庫的版本，兩個數據庫版本要相同，或者slave比master版本低！
1、在主服務器上為復制設置一個連接賬戶。該賬戶必須授予REPLICATION SLAVE權限。如果
賬戶僅用於復制(推薦這樣做)，則不需要再授予任何其它權限。
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO
‘replication’@’192.168.20.%’IDENTIFIED BY ’123456′;
2、在主服務器上建立測試數據庫test1
mysql> create database test1;
mysql> use test1;
mysql> create table user(id int(4),name varchar(20));
mysql> insert into user values(1,”mary”);
mysql> insert into user values(2,”joe”);
// 刷新權限，使設置生效
mysql>Flush privileges;
3、配置主服務器的my.cof
// mysql客戶端程序不要退出，在/etc/my.cnf配置文件中添加以下內容
log-bin=mysql-bin # 啟動二進制日志系統
server-id=1 # 本機數據庫ID 標示為主服務器
log-bin=/var/log/mysql/updatelog # 設定生成log文件名，這裡的路徑沒有mysql
目錄要手動創建並給於它mysql用戶的權限。
binlog-do-db=test1 # 二進制需要同步的數據庫名
binlog-ignore-db=mysql,test # 避免同步mysql用戶配置，以免不必要的麻煩
// 創建更新日志的目錄並給mysql用戶的權限
# mkdir /var/log/mysql
# chown -R mysql.mysql /var/log/mysql
4、執行FLUSH TABLES WITH READ LOCK語句清空所有表和塊寫入語句, 並將本地需要同步
數據庫打包拷貝到從數據庫上
mysql> FLUSH TABLES WITH READ LOCK;
// 在另一個終端對主服務器數據目錄做備份
# cd /var/lib/mysql/
# tar -cvf /tmp/mysqldb.tar test1/
// 通過遠程拷貝到從服務器上，通過這個拷貝的時候需要輸入從服務器root密碼
# scp /tmp/mysqldb.tar [email protected]:/var/lib/mysql
// 對主服務器進行解鎖
mysql> UNLOCK TABLES
// 重啟mysql服務
# /etc/init.d/mysqld restart
5、配置從服務器
// 配置slave服務器/etc/my.cnf 文件，添加以下內容：
server-id=2 # 從服務器ID號，不要和主ID相同
master-host=192.168.20.155 # 指定主服務器IP地址
master-user=replication # 指定在主服務器上可以進行同步的用戶名
master-password=123456 # 密碼
master-port=3306 # 同步所用端口
master-connect-retry=60 # 斷點從新連接時間
replicate-ignore-db=mysql # 屏蔽對mysql庫的同步
replicate-do-db=test1 # 同步的數據庫的名稱
6、從服務器上裝在主服務器數據庫
# cd /var/lib/mysql/
# tar xvf mysqldb.tar
# rm mysqldb.tar
# /etc/init.d/mysqld restart
// 啟動從服務器線程：
# mysql -uroot -p123456
mysql> START SLAVE;
7、驗證配置
// 主服務器：
mysql > SHOW MASTER STATUS;
+——————+———-+—————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————+——————+
| mysql-bin.000004 | 106 | test1,netseek | mysql,test |
+——————+———-+—————+——————+
//（同步之前如果懷疑主從數據不同步可以采取：上面冷備份遠程拷貝法或者在從服務器上命行
同步方法）在從服務器執行MySQL 命令下：
mysql> SLAVE STOP; #先停止slave服務
mysql> CHANGE MASTER TO MASTER_LOG_FILE=’updatelog.000004′,MASTER_LOG_
POS=106;
// 根據上面主服務器的show master status的結果，進行從服務器的二進制數據庫記錄回歸，
達到同步的效果
mysql> SLAVE START; #啟動從服務器同步服務
// 用show slave statusG;看一下從服務器的同步情況
mysql> SHOW SLAVE STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已經在同步.
8、測試
// 在主服務器上建立一個表
mysql> use test1;
mysql> create table name(id int(4),name varchar(20));
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| user |
+—————–+
2 rows in set (0.01 sec)
// 在從服務器上查詢
mysql> use test1;
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| user |
+—————–+
2 rows in set (0.00 sec)
單項復制試驗成功！！！！
四、雙向同步配置
1、修改原slave 服務器配置
192.168.20.102
// 配置原slave服務器/etc/my.cnf文件,添加紅字的內容：
server-id=2 # 從服務器ID號，不要和主ID相同
master-host=192.168.20.155 # 指定主服務器IP地址
master-user=replication # 指定在主服務器上可以進行同步的用戶名
master-password=123456 # 密碼
master-port=3306 # 同步所用端口
master-connect-retry=60 # 斷點從新連接時間
replicate-ignore-db=mysql # 屏蔽對mysql庫的同步
replicate-do-db=test1 # 同步的數據庫的名稱
log-bin=/var/log/mysql/updatelog # 設定生成log文件名
binlog-do-db=test1 # 設置同步數據庫名
binlog-ignore-db=mysql # 避免同步mysql用戶配置，以免不必要的麻煩
2、創建更新日志的目錄並給mysql用戶的權限
# mkdir /var/log/mysql
# chown -R mysql.mysql /var/log/mysql
3、重新啟動mysql服務，創建一個同步專用賬號
# service mysqld restart
//給與從服務器用戶replication的同步權限
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO
‘replication’@’192.168.20.%’IDENTIFIED BY ’123456′;
//刷新權限，使設置生效
mysql>Flush privileges;
4、修改原master配置文件
192.168.20.101
// 配置原master務器/etc/my.cnf文件,添加紅字的內容：
log-bin=mysql-bin # 啟動二進制日志系統
server-id=1 # 本機數據庫ID 標示為主
log-bin=/var/log/mysql/updatelog # 設定生成log文件名，這裡的路徑沒有mysql
目錄要手動創建並給於它mysql用戶的權限。
binlog-do-db=test1 # 二進制需要同步的數據庫名
binlog-ignore-db=mysql,test # 避免同步mysql用戶配置，以免不必要的麻煩
master-host=192.168.20.128 # 設置從原slave數據庫同步更新
master-user=replication # 更新用戶
master-password=123456 # 密碼
master-port=3306 # 端口
replicate-do-db=test1 # 需要更新的庫
// 重啟mysql服務
# service mysqld restart
// 在B服務器查詢
192.168.20.102
# mysql -uroot -p123456
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| updatelog.000001 | 106 | test1 | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
// 在A服務器查詢
192.168.20.101
# mysql -uroot -p123456
mysql> SHOW MASTER STATUS;
// 先停止slave服務
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.20.128′,MASTER_USER=’repli
cation’,MASTER_PASSWORD=’123456′,MASTER_PORT=3306,MASTER_LOG_FILE=’upda
telog.000001′,MASTER_LOG_POS=106;
// 根據上面主服務器的show master status的結果，進行從服務器的二進制數據庫記錄回歸，
達到同步的效果
// 啟動B服務器同步服務
192.168.20.102
mysql> SLAVE START;
5、驗證配置
// 在A服務器上進入mysql命令行
192.168.20.101
mysql> SHOW SLAVE STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此處Slave_IO_Running ,Slave_SQL_Running 都應該是yes,表示從庫的I/O,Slave_SQL線程都
正確開啟.表明數據庫正在同步。
// 在B服務器上進入mysql命令行
192.168.20.102
mysql> SHOW SLAVE STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此處Slave_IO_Running ,Slave_SQL_Running 都應該是yes,表示從庫的I/O,Slave_SQL線程都
正確開啟.表明數據庫正在同步。
6、測試
// 在A服務器上建立一個表
192.168.20.101
mysql> use test1;
mysql> create table test1(id int(4),name varchar(20));
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| test1 |
| user |
+—————–+
3 rows in set (0.00 sec)
// 在B服務器上查詢
192.168.20.102
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| test1 |
| user |
+—————–+
3 rows in set (0.00 sec)
// 在B服務器上建立一個表
192.168.20.102
mysql> create table test2(id int(4),name varchar(20));
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| test1 |
| test2 |
| user |
+—————–+
4 rows in set (0.00 sec)| user |
+—————–+
3 rows in set (0.00 sec)
// 在A服務器上查詢
192.168.20.101
mysql> show tables;
+—————–+
| Tables_in_test1 |
| name |
| test1 |
| test2 |
| user |
+—————–+
4 rows in set (0.00 sec)
雙向復制試驗成功！！！
mysql5.5.9半同步復制功能部署
1.mysql5.5.9 半同步復制功能：
mysql5.5 版本支持半同步復制功能（Semisynchronous Replication），但
還不是原生的支持，是通過plugin來支持的，並且默認是沒有安裝這個插件的。
不論是二進制發布的，還是自己源代碼編譯的，都會默認生成這個插件，
一個是針對master 的一個是針對slave的，在使用之前需要先安裝這倆plugins
首先先檢查 mysql是否支持動態添加插件，
mysql.sock@mysql> select @@have_dynamic_loading ;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
支持動態增減插件，
添加插件：
mysql.sock@(none)> install plugin rpl_semi_sync_master soname
semisync_master.so ;
Query OK, 0 rows affected (0.00 sec)
mysql.sock@(none)> install plugin rpl_semi_sync_slave soname
semisync_slave.so;
Query OK, 0 rows affected (0.00 sec)
添加完插件後，系統會默認的增加了幾個系統參數
mysql.sock@(none)> show global variables like rpl_semi_sync%;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+------------------------------------+-------+
6 rows in set (0.00 sec)
這些參數是可以動態修改的
rpl_semi_sync_master_enabled ：
啟動master 支持半同步復制。
rpl_semi_sync_master_timeout ：
主庫等待半同步復制信息返回的超時間隔，默認10秒
rpl_semi_sync_master_trace_level ：
監控等級：
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
rpl_semi_sync_master_wait_no_slave ：
是否允許master 每個事物提交後都要等待slave 的receipt信號。
默認為on ，每一個事務都會等待，如果slave當掉後，當slave追趕上master的日志時
，可以自動的切換為半同步方式，如果為off,則slave追趕上後，也不會彩玉半同步的
方式復制了，需要手工發動。
rpl_semi_sync_slave_enabled ：
啟動slave 支持半同步復制。
rpl_semi_sync_slave_trace_level ：
監控等級，同 上面的rpl_semi_sync_master_trace_leve。
相應的系統的狀態變量：
mysql.sock@mysql> show global status like rpl_semi_sync%;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 902 |
| Rpl_semi_sync_master_net_wait_time | 902 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 501 |
| Rpl_semi_sync_master_tx_wait_time | 501 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
Rpl_semi_sync_master_clients ：
記錄支持半同步的slave的個數。
Rpl_semi_sync_master_net_avg_wait_time ：
master 等待slave 回復的平均等待時間。 單位毫秒.
| Rpl_semi_sync_master_net_wait_time ：
master 總的等待時間。
Rpl_semi_sync_master_net_waits ：
master 等待slave 回復的的總的等待次數。
Rpl_semi_sync_master_no_times ：
master 關閉半同步復制的次數。
Rpl_semi_sync_master_no_tx ：
master 沒有收到slave的回復而提交的次數，（應該可以理解為master 等待超時的次
數)
Rpl_semi_sync_master_status :
標記master現在是否是半同步復制狀態。
Rpl_semi_sync_master_timefunc_failures ：
The number of times the master failed when calling time functions such as
gettimeofday().
Rpl_semi_sync_master_tx_avg_wait_time ：
master 花在每個事務上的平均等待時間。
Rpl_semi_sync_master_tx_wait_time ：
master 總的等待次數。
Rpl_semi_sync_master_wait_pos_backtraverse：
我理解的意思就是後來的先到了，而先來的還沒有到的次數。
The total number of times the master waited for an event with binary
coordinates lower than events waited for previously. This can occur when
the order in which transactions start waiting for a reply is different from
the order in which their binary log events are written.
Rpl_semi_sync_master_wait_sessions：
當前有多少個session 因為slave 的回復而造成等待。
Rpl_semi_sync_master_yes_tx ：
master 成功接收到slave的回復的次數。
Rpl_semi_sync_slave_status :
標記slave 是否在半同步狀態。
========================================================
配置半同步的步驟就很簡單了：
1.先按照我們一般的配置異步復制的方式 建立好復制。
2. 啟動異步方式復制。
3.當slave追趕上master的狀態時，停止slave ：
4. 修改主從庫的半同步的參數：
主庫執行：
set global rpl_semi_master_enabled=1；
set global rpl_semi_sync_master_timeout=1000；
從庫執行 ；
set global rpl_semi_slave_enabled=1；
5. 從庫啟動slave：
start slave；
6.查看參數，根據復制的狀態調整global rpl_semi_sync_master_timeout的值。
