關(guān)系型數(shù)據(jù)庫之主從復(fù)制應(yīng)用

一、MySQL簡單復(fù)制相關(guān)概念：

1. mysql復(fù)制的意義：Mysql復(fù)制是使得mysql完成高性能應(yīng)用的前提

2. mysql復(fù)制的機(jī)制：    

Slave端線程: 

IO thread: 向主服務(wù)請求二進(jìn)制日志中的事件

當(dāng)讀取完畢后，IO線程將進(jìn)行睡眠，當(dāng)主服務(wù)器有新數(shù)據(jù)時，則主服務(wù)器喚醒從服務(wù)器的IO線程

SQL thread：從中繼日志讀取事件并在本地執(zhí)行，

如果二進(jìn)制日志開啟式，同樣會記錄二進(jìn)制日志，但為了節(jié)約空間和提高性能，需要關(guān)閉

從服務(wù)器不能執(zhí)行寫操作，如果執(zhí)行寫操作則和主服務(wù)器不同步。 

Master端:

binlog dump: 將IO thread請求的事件發(fā)送給對方；

默認(rèn)為異步工作模式：主要主服務(wù)器自己寫完，不管從服務(wù)器是否寫完，就將返回

二、MySQL復(fù)制的常見構(gòu)架

1.主從構(gòu)架

1)對于一從多主，只有新版本Mysql可以實(shí)現(xiàn)。 通常來說，一個從只能有一個主服務(wù)器，但是可以輪換。在某一時刻只能有一臺主服務(wù)器。 

MariaDB-10：支持多主模型，多源復(fù)制(multi-source replication)

2)一主多從：執(zhí)行寫操作只能對主服務(wù)器進(jìn)行。 多從會增大主服務(wù)器壓力。 

此時需要一個調(diào)度器，來分離讀寫請求到主從服務(wù)器上。所謂讀寫分離

3)讀寫分離：主從模型下，讓前端分發(fā)器能識別讀/寫，并且按需調(diào)度至目標(biāo)主機(jī)；

amoeba，mysql-proxy可以實(shí)現(xiàn)讀寫分離調(diào)度

讀服務(wù)器進(jìn)行負(fù)載均衡，使用一致性哈希算法，虛擬節(jié)點(diǎn)來分配訪問。

2.雙主構(gòu)架

1)使用server_id來避免循環(huán)賦值

2)必須設(shè)定雙方的自動增長屬性，以避免沖突， 一個使用偶數(shù)一個實(shí)用技術(shù)

第一臺服務(wù)器：

  auto_increment_increment=1 定義自動增長字段起始值

  auto_increment_offset=2 步長

第二臺服務(wù)器

auto_increment_increment=2 定義自動增長字段起始值

auto_increment_offset=2 步長

通過以上設(shè)定來解決沖突問題。 

3)數(shù)據(jù)不一致； 在雙主模型下某些時刻會導(dǎo)致數(shù)據(jù)不同步。

第一個服務(wù)器鎖定第一個字段改第二個字段

第二臺服務(wù)器鎖定第二個字段改第一個字段

例如:一個表包含： Age, Salary

如果一個人的年齡為31而工資2900，執(zhí)行以下操作

A： update t1 set Salary=salary+1000 WHERE Age>=30;

B: update t1 set Age=Age-3 WHERE Salary < 3000;

會導(dǎo)致：

服務(wù)器A Salary 變?yōu)?900，年齡31

服務(wù)器B Salary 變?yōu)槟挲g28， 工資2900

4)功能：

不能分?jǐn)倢懻埱?，兩個服務(wù)器寫壓力一致。 

三、復(fù)制構(gòu)架擴(kuò)展

1.主從服務(wù)器之間的差距

長時間運(yùn)行后，主從可能不同步。 因?yàn)橹鞣?wù)器可以寫并發(fā)，但是從服務(wù)器的同步只能是但進(jìn)程。 

從服務(wù)器落后，有時候需要認(rèn)為設(shè)計(jì)，來做備份。 

2.一主多從的環(huán)境： 

1)一主多從的環(huán)境中，為了利用各從服務(wù)器的緩存能力。需要一些負(fù)載均衡算法，來綁定特定查詢到特定服務(wù)器上，來使得緩存命中。這是這樣做使得均衡效果被打破，使得有的服務(wù)器過于繁忙。

2)為了解決此問題，可以引入中心緩存服務(wù)器。

3)由于換從服務(wù)器工作在旁路模式下，所以是否緩存取決于客戶端程序。

memcached：可以提供緩存能力+API 

公共緩存服務(wù)器，性能比Mysql自身差

3.多級復(fù)制： 主指向一個從，從同時也作為其他從服務(wù)器的主 

master –> slave/master —> slave

中間服務(wù)器需要開啟二進(jìn)制日志和中繼日志 

多級復(fù)制可以降低主服務(wù)器產(chǎn)生mysqldump的壓力，把壓力分?jǐn)偨o下一集。 但是可能使得數(shù)據(jù)不能更好跟新。 

可以把中間服務(wù)器的讀寫引擎改為black hole，來降低本地壓力，只生成二進(jìn)制日志作為中繼服務(wù)器（relay server），然后把二進(jìn)制日志發(fā)送給下游服務(wù)器

4.模擬一從多主模型： 

        根據(jù)時間，來卻換不同的主。 

        通常用于在不同服務(wù)器之間做數(shù)據(jù)收集。 

5.環(huán)狀模型： 

每臺服務(wù)器都是下一臺服務(wù)器的主服務(wù)器同時也是上一臺服務(wù)器的從服務(wù)器，使得每臺服務(wù)器都是主從，形成傳遞環(huán)。

每臺服務(wù)器的修改都會同步到環(huán)上任何一臺服務(wù)器中。 

server_id 不能相同。 

6.常見mysql構(gòu)架

    1)一主一從，并且讀取分離 

    2)一主多從，主負(fù)責(zé)讀，從負(fù)責(zé)寫

    3)一主多從外加一個冷備服務(wù)器， 只用于備份。 每一段時間關(guān)掉進(jìn)行備份。       

    4)多主模型： 通過心跳信息探測主服務(wù)器的健康狀態(tài)，如果一個主掛掉，馬上切換另一個主

潛在問題： 第一臺服務(wù)器上未提交的事務(wù)，切換時將被回滾。 

GTID（GLOBAL TRANSACTION id）： 保證在特定服務(wù)器上的事務(wù)是完整的，如果執(zhí)行不成功就回滾。 

7.高級應(yīng)用架構(gòu)： 

讀寫分離+負(fù)載均衡： 讀直到從服務(wù)器上面讀，寫只在主服務(wù)器上面寫。 需要在讀寫分離器之前加memocached

amoeba, mysql proxy

8.異地同步: 主要避免自然災(zāi)害

四、 簡單主從構(gòu)架配置過程

1.配置需留意:

1)版本注意：主從版本一致，或者主版本高于從版本

2)從哪兒開始復(fù)制：

1.都從0開始：

2.主服務(wù)器已經(jīng)運(yùn)行一段時間，并且存在不小的數(shù)據(jù)集：

把主服務(wù)器備份，然后在從服務(wù)恢復(fù)，從主服務(wù)器上備份時所處的位置開始復(fù)制；

應(yīng)用案例(1):配置主從復(fù)制簡單架構(gòu)

Master服務(wù)器配置如下:

--停止mysql服務(wù)
# service mysqld stop  
--創(chuàng)建mysql服務(wù)日志目錄及權(quán)限授予                           
# mkdir -pv /mydata/binlogs/
# chown -R mysql.mysql /mydata/binlogs/
--修改my.cnf配置文件:
[mysqld]
server-id       = 1
log-bin=/mydata/binlogs/master-bin
--啟動mysql服務(wù)
# service mysqld start
--查詢二進(jìn)制日志參數(shù)是否啟動
mysql > SHOW GLOBAL VARIABLES LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
+---------------------------------+-------+
--創(chuàng)建擁有復(fù)制權(quán)限的用戶
mysql > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';
mysql > FLUSH PRIVILEGES;

Slave服務(wù)器配置如下:

--停止mysql服務(wù)
# service mysqld stop  
--創(chuàng)建mysql服務(wù)日志目錄及權(quán)限授予                           
# mkdir -pv /mydata/relaylogs/
# chown -R mysql.mysql /mydata/relaylogs/
--修改my.cnf配置文件:
[mysqld]
server-id       = 11
relay-log       = /mydata/relaylogs/relay-bin
#log-bin=OFF
--啟動mysql服務(wù)
# service mysqld start
--查詢二進(jìn)制中繼日志參數(shù)是否啟動
> SHOW GLOBAL VARIABLES LIKE 'relay%';
+-----------------------+-----------------------------+
| Variable_name         | Value                       |
+-----------------------+-----------------------------+
| relay_log             | /mydata/relaylogs/relay-bin |
| relay_log_index       |                             |
| relay_log_info_file   | relay-log.info              |
| relay_log_purge       | ON                          |
| relay_log_recovery    | OFF                         |
| relay_log_space_limit | 0                           |
+-----------------------+-----------------------------+

Slave服務(wù)器連接主服務(wù)器:

mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass';
命令:	CHANGE MASTER TO            
	MASTER_HOST = '',  主服務(wù)器地址
	MASTER_USER='', 使用哪個用戶連接
	MASTER_PASSWORD='',  密碼
	MASTER_LOG_FILE='',  用來復(fù)制特定的哪一個二進(jìn)制文件
	MASTER_LOG_POS=;從這個二進(jìn)制文件的哪個位置開始復(fù)制
最重要的參數(shù)只有前三個，就可以開始工作.

連接主服務(wù)器后，查詢Master服務(wù)器線程及日志位置:

mysql > SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  2 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
--可以看到目前沒有線程啟動

mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      496 |              |                  |
+-------------------+----------+--------------+------------------+

連接主服務(wù)器后，查詢Slave服務(wù)器線程狀態(tài)及日志位置:

--查詢Slave服務(wù)器線程狀態(tài)及Slave復(fù)制日志狀態(tài)
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: 
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: 
Slave_IO_Running: No
Slave_SQL_Running: No

--啟動復(fù)制線程:
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 496
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 781
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
--查詢線程服務(wù)啟動信息，它會日志信息記錄在錯誤信息日志文件中
--查詢?nèi)罩疚恢媚夸?
# ps aux | grep mysqld
--查詢錯誤日志
# tail /mydata/data/slave.samlee.com.err 
160621 17:43:48 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='172.16.100.7', master_port='3306', master_log_file='', master_log_pos='4'.

根據(jù)以上信息查詢可以清楚的知道Slave服務(wù)當(dāng)前的狀態(tài)及位置信息。

以上為Mysql主從復(fù)制簡單應(yīng)用部署過程。

主從復(fù)制架構(gòu)應(yīng)用測試(1):從0開始復(fù)制-->

步驟(1)：Master服務(wù)器上創(chuàng)建數(shù)據(jù)庫寫入操作

mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      496 |              |                  |
+-------------------+----------+--------------+------------------+ 
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      496 |              |                  |
+-------------------+----------+--------------+------------------+
mysql > CREATE DATABASE mydb;
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      579 |              |                  |
+-------------------+----------+--------------+------------------+

步驟(2)：Slave服務(wù)執(zhí)行查詢操作

--查詢Slave服務(wù)器狀態(tài)信息可以知道Slave服務(wù)器已經(jīng)同步至579的位置
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 579            --同步日志位置
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 864
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

步驟(3)：Master服務(wù)器上創(chuàng)建表插入數(shù)據(jù)寫入操作

mysql > USE mydb;
mysql > CREATE TABLE t1(id int);
mysql > INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      864 |              |                  |
+-------------------+----------+--------------+------------------+

步驟(4)：Slave服務(wù)執(zhí)行查詢操作

--查詢Slave服務(wù)器狀態(tài)信息可以知道Slave服務(wù)器已經(jīng)同步至864的位置
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1149
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
--查詢所示
mysql > USE mydb;
mysql > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1             |
+----------------+
mysql > SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

主從復(fù)制架構(gòu)應(yīng)用測試(2):從指定位置開始復(fù)制

步驟(1)：測試準(zhǔn)備操作:

Slave服務(wù)器準(zhǔn)備:

mysql > STOP SLAVE;
mysql > DROP DATABASE mydb;

Master服務(wù)器準(zhǔn)備操作:

mysql > DROP DATABASE mydb;

步驟(2):

為了演示效果，我們讓Master服務(wù)器上直接產(chǎn)生大批的數(shù)據(jù),然后讓Slave服務(wù)器不從0開始復(fù)制。

Master服務(wù)器上操作:

--導(dǎo)入數(shù)據(jù)
# mysql < hellodb.sql 
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |     8544 |              |                  |
+-------------------+----------+--------------+------------------+
mysql > DROP DATABASE mydb;
mysql > CREATE DATABASE mydb;
mysql > USE hellodb;
mysql > CREATE TABLE t1 (id int);
--鎖定日志位置執(zhí)行備份
# mysqldump --all-databases --flush-logs --master-data=2 --lock-all-tables > all.sql
--傳送至Slave服務(wù)器
# scp all.sql root@172.16.100.8:/root

Slave服務(wù)器上操作:

--在停止Slave服務(wù)下恢復(fù)數(shù)據(jù)庫
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: 
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1149
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
lave_SQL_Running: No
--恢復(fù)備份
# mysql < all.sql 
--查詢主從起始位置信息，要去備份SQL中查詢
# cat all.sql | grep 'master-bin'
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=245;

--指定從備份位置開始同步主從數(shù)據(jù)
mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=245;

--啟動Slave復(fù)制線程
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 245
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master服務(wù)器上操作:

--創(chuàng)建samleedb數(shù)據(jù)庫
mysql > CREATE DATABASE samleedb;
--查詢?nèi)罩臼录恢?mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      336 |              |                  |
+-------------------+----------+--------------+------------------+

Slave服務(wù)器上操作:

--查詢Slave服務(wù)狀態(tài)信息
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002        --可以查看到是從master-bin.000002日志文件同步
Read_Master_Log_Pos: 336              --日志事件位置為:336
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 621
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

簡單主從復(fù)制實(shí)驗(yàn)過程的一些總結(jié):
1)主服務(wù)器二進(jìn)制日志如果在數(shù)據(jù)庫初始化的時候開啟了，則從服務(wù)器需要指定初始化結(jié)束后的日志進(jìn)行復(fù)制。否則貌似會出現(xiàn)一些奇怪的錯誤。也許是因?yàn)闊o法再次創(chuàng)建系統(tǒng)庫所致

2)主服務(wù)器最好在系統(tǒng)庫初始化完成后再開啟二進(jìn)制日志。 這樣從服務(wù)器就可以直接從第一個二進(jìn)制日志開始復(fù)制。

3)如果錯誤日志中出現(xiàn)錯誤通常就是slave sql 線程，Slave I/O 產(chǎn)生的。 如果slave I/O出問題通常與權(quán)限和鏈接有關(guān)。 Slave sql出問題通常與二進(jìn)制日志在從服務(wù)器執(zhí)行有關(guān)。

1.在主從架構(gòu)中跟復(fù)制相關(guān)的文件:
1)master.info: 用于保存了從服務(wù)器連接到主服務(wù)器需要的信息。每一行有一個值。 不同的mysql版本格式不一樣。 如果不想鏈接，直接刪了就好了
2)relay-log.info: 二進(jìn)制日志和中繼日志的位置， 日志坐標(biāo)， 會不停的自動更新。由于更新不會馬上執(zhí)行，會存在緩沖區(qū)，如果斷電，信息會丟失   

為了復(fù)制安全性，需要在從服務(wù)器上，需要在從服務(wù)器上開啟以下參數(shù):

sync_master_info  =1     
sync_relay_log    =1   
sync_relay_log_info =1

3)從服務(wù)器意外崩潰時，建議使用pt-slave-start命令來啟動slave;
2. 基于行和基于語句的復(fù)制：
    1)基于語句：
      優(yōu)點(diǎn):數(shù)據(jù)量小，易于查看和識別，適應(yīng)性強(qiáng)
      缺點(diǎn):有些語句無法精確復(fù)制，使用觸發(fā)器存儲過程等代碼的應(yīng)用實(shí)現(xiàn)精確復(fù)制。
     
    2)基于行復(fù)制：
      優(yōu)點(diǎn):都能精確完成復(fù)制，包括出發(fā)器存儲過程，能完成幾乎所有的復(fù)制功能， 較少占用CPU資源，可以減少鎖的使用
      缺點(diǎn):通過日志無法判斷執(zhí)行了哪些語句， 數(shù)據(jù)可能略大。
     
    3)單獨(dú)使用基于語句的場景幾乎不存在，默認(rèn)為混合模式。

3.從服務(wù)器落后于主服務(wù)器如何檢測:

mysql> SHOW SLAVE STATUS \G; --可以查看     
Seconds_Behind_Master: 0
這里顯示落后主服務(wù)器多少秒。 
如果主服務(wù)器繁忙，這個值通常都為正值， 如果此值在波動而不是變大。都可以接受。

4.評估主從服務(wù)器表數(shù)據(jù)是否一致：
   procona-toolkit里面的一些工具可以進(jìn)行監(jiān)控
   pt-table-checksum  在主服務(wù)器上面運(yùn)行，可以自動尋找哪個表不同步

不同步的解決方法：
1.重新備份，并在從服務(wù)器上導(dǎo)入數(shù)據(jù)。
2.使用pt-table-sync完成同步
pt-summary 可以得到一些mysql的統(tǒng)計(jì)數(shù)據(jù)，有些時候有用

5.為了提高復(fù)制時的數(shù)據(jù)安全性，在主服務(wù)器上的設(shè)定：

sync_binlog = 1    
innodb_flush_log_at_trx_commit = 1
此參數(shù)的值設(shè)定為1，性能下降會較嚴(yán)重；因此，一般設(shè)定為2等，此時，主服務(wù)器崩潰依然有可能導(dǎo)致從服務(wù)器無法獲取到全部的二進(jìn)制日志事件；
如果主服務(wù)器意外崩潰，有二進(jìn)制日志中事件沒復(fù)制完損壞，可以在從服務(wù)器使用如下參數(shù)忽略： 

sql_slave_skip_counter = 0
數(shù)據(jù)目錄磁盤空間不足，也可導(dǎo)致復(fù)制無法進(jìn)行。需要結(jié)合監(jiān)控工具做整體評估。 
主服務(wù)器修改過大，使得帶寬不夠用。

五.MySQL簡單主從復(fù)制應(yīng)用擴(kuò)展

1.主從服務(wù)器需要保證時間同步，可以使用ntp服務(wù)

# crontab -e
*/5 * * * * /usr/sbin/ntpdate 172.16.100.9

2.如何限制從服務(wù)器只讀?

read-only = ON --定義在從服務(wù)器my.cnf的[mysqld]段

擴(kuò)展應(yīng)用實(shí)現(xiàn):只讓從服務(wù)器實(shí)現(xiàn)只讀狀態(tài)

步驟(1)：

Slave服務(wù)器上操作:

# vim /etc/my.cnf
[mysqld]
read-only       = ON
# service mysqld start
mysql > SHOW GLOBAL VARIABLES LIKE '%read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

Master服務(wù)器上操作:

--創(chuàng)建授權(quán)測試用戶
mysql > GRANT ALL ON mydb.* TO 'testuser'@'172.16.%.%' IDENTIFIED BY 'testpass';

步驟(2)：

Slave服務(wù)器上操作:

--查詢同步后測試用戶
mysql > USE mysql
mysql > SELECT User,Host FROM user;
+----------+-------------------+
| User     | Host              |
+----------+-------------------+
| root     | 127.0.0.1         |
| repluser | 172.16.%.%        |
| testuser | 172.16.%.%        |
| root     | ::1               |
|          | localhost         |
| root     | localhost         |
|          | master.samlee.com |
| root     | master.samlee.com |
+----------+-------------------+

Master服務(wù)器上操作:

--遠(yuǎn)程連接Slave服務(wù)器測試只讀狀態(tài)是否啟用
# mysql -utestuser -h272.16.100.8 -ptestpass
mysql > USE mydb;
mysql > SHOW TABLES;
Empty set (0.01 sec)
mysql > CREATE TABLE t1(id int);
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
##MariaDB的服務(wù)器運(yùn)行的，只讀選項(xiàng)不能執(zhí)行該語句

Tips: 僅能限制那不具有SUPER權(quán)限用戶無法執(zhí)行寫操作；因此管理員依然有效，切勿使用管理員權(quán)限在從服務(wù)器上修改數(shù)據(jù)。

想限制所有用戶:

mysql> FLUSH TABLES WITH READ LOCK;

3.如何主從復(fù)制時的事務(wù)安全
當(dāng)主服務(wù)器事務(wù)，尚未同步到二進(jìn)制日志中時， 如果主服務(wù)器宕機(jī)，則從服務(wù)器無法獲取完整的事務(wù)，當(dāng)從服務(wù)器指向其它主服務(wù)器時候，有可能導(dǎo)致事務(wù)回滾。從而使得事務(wù)丟失。
在主服務(wù)器上配置一下參數(shù)解決， 只要事務(wù)提交，則立即將事務(wù)從二進(jìn)制緩沖區(qū)同步到二進(jìn)制日志中。

sync_binlog=1
innodb_flush_log_at_trx_commit=1

Tips:此變量設(shè)置后，會產(chǎn)生一些性能損耗，主要原因是MyISAM或者Innodb是默認(rèn)自動提交事務(wù)的。為了提高性能可以關(guān)掉自動提交，同時開啟以上選項(xiàng)用。

全同步過程: 主服務(wù)器寫操作 –> 寫操作同步到二進(jìn)制日志 –> 寫操作被復(fù)制到從服務(wù)器的二進(jìn)制日志中 –> 才從服務(wù)器上執(zhí)行寫操作 –> 然后返回給主服務(wù)器

4.使用半同步復(fù)制（semi-synchronously）機(jī)制可以在主服務(wù)器性能一定損失的前提下實(shí)現(xiàn)更好地同步.
前面部分與全同步相同，只是在對于一主多從的場景中，主服務(wù)器只等待一個最快的從服務(wù)器范圍寫完成狀態(tài)。
半同步的配置: mysql5.5以后google提供，在程序安裝lib/plugin目錄下的兩個插件semisync_master.so, semisync_slave.so。

擴(kuò)展應(yīng)用實(shí)現(xiàn):實(shí)現(xiàn)半同步復(fù)制配置及應(yīng)用

Master服務(wù)器配置操作:

--裝載semisync_master.so模塊
mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
--裝載后，將會多出幾個選項(xiàng):
> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| 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_master_enabled      ：控制是否開啟半同步
rpl_semi_sync_master_timeout      ：半同步超時時長，多長時間半同步從服務(wù)器回復(fù)，跳過半同步繼續(xù)執(zhí)行。單位為毫秒，默認(rèn)為10秒鐘
rpl_semi_sync_master_wait_no_slave：如果半同步服務(wù)器不回應(yīng)，則跳過半同步服務(wù)器繼續(xù)執(zhí)行。

--修改前兩個參數(shù):
mysql > SET GLOBAL rpl_semi_sync_master_enabled='ON'; 
mysql > SET GLOBAL rpl_semi_sync_master_timeout=2000;

--配置完成如下:
mysql > SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 2000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

Slave服務(wù)器配置操作:

--裝載semisync_slave.so模塊
mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
--裝載后，會出現(xiàn)以下參數(shù)選項(xiàng):
mysql > SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
--從服務(wù)器這里只需要把第一個開啟就可以了：
mysql > SET GLOBAL rpl_semi_sync_slave_enabled=1;
mysql > SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
--如果不重啟I/O 線程，則半同步從服務(wù)器特性不能被主服務(wù)器識別，此時主服務(wù)器上進(jìn)行操作，會超時，然后跳過半同步。
mysql > STOP SLAVE IO_THREAD;
mysql > START SLAVE IO_THREAD;

Master服務(wù)器配置操作:執(zhí)行數(shù)據(jù)寫入操作

mysql > USE mydb;
Database changed
mysql > CREATE TABLE t2(name char(20));
Query OK, 0 rows affected (0.09 sec)

mysql > CREATE TABLE t3(name char(20));
Query OK, 0 rows affected (0.02 sec)

mysql > CREATE TABLE t4(name char(20));
Query OK, 0 rows affected (0.01 sec)

--在主服務(wù)器驗(yàn)正半同步復(fù)制是否生效：
mysql > SHOW GLOBAL STATUS LIKE '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
這里顯示，半同步客戶端有一個，說明半同步正常使用。 
如果由于網(wǎng)絡(luò)或其他原因，檢測到半同步超時，則可以重啟從服務(wù)器的1/O線程，來重啟半同步功能。

5.在主從復(fù)制架構(gòu)中，僅復(fù)制一部分?jǐn)?shù)據(jù)解決方案，使用復(fù)制過濾器實(shí)現(xiàn)

主服務(wù)器過濾： 主服務(wù)器僅往二進(jìn)制日志中記錄像樣的服務(wù)器
優(yōu)缺點(diǎn)：主服務(wù)器磁盤I/O 帶寬節(jié)約
缺點(diǎn)：但是其他數(shù)據(jù)庫數(shù)據(jù)不安全，無法完成時間點(diǎn)還原

參數(shù)： 
binlog_do_db= 
binlog_igore_db=

在從服務(wù)器上，僅讀取相應(yīng)的信息
基于庫:

replicate_do_db=        
replicate_ignore_db=

基于表

replicate_do_table=db_name.tb_name 
replicate_ignore_table=

基于表使用通配符

replicate_wild_do_table=
replicate_wild_ignore_table=

6.配置多主模型

MasterA服務(wù)器上配置:

# service mysqld stop
# vim /etc/my.cnf
log-bin=/mydata/binlogs/master-bin
relay-log = /mydata/relaylogs/relay-bin
auto_increment_offset = 1        #起始位置
auto_increment_increment = 2     #自動增長步長   
server-id       = 1
skip_slave_start
--創(chuàng)建二進(jìn)制日志及中繼日志存儲目錄及權(quán)限授予
# mkdir -pv /mydata/{binlogs,relaylogs}
# chown -R mysql.mysql /mydata/{binlogs,relaylogs}
#  service mysqld start
--創(chuàng)建擁有復(fù)制權(quán)限的用戶
> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass'; affected (0.01 sec)
> FLUSH PRIVILEGES;
--查詢當(dāng)前服務(wù)器二進(jìn)制文件及事件位置
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 |      506 |              |                  |
+-------------------+----------+--------------+------------------+

MasterB服務(wù)器上配置:

# service mysqld stop
# vim /etc/my.cnf
server-id       = 11
log-bin=/mydata/binlogs/master-log
relay-log       = /mydata/relaylogs/relay-bin
auto_increment_increment = 2
auto_increment_offset = 2
skip_slave_start
--創(chuàng)建二進(jìn)制日志及中繼日志存儲目錄及權(quán)限授予
# mkdir -pv /mydata/{binlogs,relaylogs}
# chown -R mysql.mysql /mydata/{binlogs,relaylogs}
#  service mysqld start
--創(chuàng)建擁有復(fù)制權(quán)限的用戶
> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass'; affected (0.01 sec)
> FLUSH PRIVILEGES;
--查詢當(dāng)前服務(wù)器二進(jìn)制文件及事件位置
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      506 |              |                  |
+-------------------+----------+--------------+------------------+

MasterB服務(wù)器連接MasterA服務(wù)器配置操作:

mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=506;
mysql > SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master-log.000001
Position: 506
Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 684
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 708
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

MasterA服務(wù)器連接MasterB服務(wù)器配置操作:

mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.8',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=506;
mysql > SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master-bin.000004
Position: 506
Binlog_Do_DB: 
Binlog_Ignore_DB: 
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 506
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

測試(1):

MasterA服務(wù)器操作:

mysql > CREATE DATABASE newdb;

MasterB服務(wù)器操作:

mysql > USE newdb;
mysql > CREATE TABLE t1(id int unsigned not null primary key auto_increment,name char(20));

測試(2):

MasterA服務(wù)器操作:

mysql > USE newdb;
mysql > SHOW TABLES;
+-----------------+
| Tables_in_newdb |
+-----------------+
| t1              |
+-----------------+

MasterB服務(wù)器操作:

mysql > USE newdb;
mysql > SHOW TABLES;
+-----------------+
| Tables_in_newdb |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

mysql > INSERT INTO t1 (name) values ('jerry'),('tom'),('samlee');
mysql > SELECT * FROM t1;
+----+--------+
| id | name   |
+----+--------+
|  2 | jerry  |
|  4 | tom    |
|  6 | samlee |
+----+--------+

MasterA服務(wù)器操作:

mysql > INSERT INTO t1 (name) values ('samlee1'),('samlee2'),('samlee3');
mysql > SELECT * FROM t1;
+----+---------+
| id | name    |
+----+---------+
|  2 | jerry   |
|  4 | tom     |
|  6 | samlee  |
|  7 | samlee1 |
|  9 | samlee2 |
| 11 | samlee3 |
+----+---------+

通過測試我們看到多主模型主要是通過鎖定步長值方式進(jìn)行實(shí)現(xiàn)的。

多主模型，且高可用的解決方案：
MMM：Multi Master MySQL----請繼續(xù)關(guān)注我的博客http://gzsamlee.blog.51cto.com/MHA：MySQL HA----請繼續(xù)關(guān)注我的博客http://gzsamlee.blog.51cto.com/

7.基于SSL實(shí)現(xiàn)主從復(fù)制:

請繼續(xù)關(guān)注我的博客http://gzsamlee.blog.51cto.com