Mysql實現企業級資料庫主從複製架構實戰 環境背景：公司規模已經形成，用戶數據已成為公司的核心命脈，一次老王一不小心把資料庫文件刪除，通過mysqldump備份策略恢復用了兩個小時，在這兩小時中，公司業務中斷，損失100萬，老王做出深刻反省，公司也因此對於資料庫的性能和可靠性提出更高要求。 要求對 ...
環境背景：公司規模已經形成，用戶數據已成為公司的核心命脈，一次老王一不小心把資料庫文件刪除，通過mysqldump備份策略恢復用了兩個小時，在這兩小時中，公司業務中斷，損失100萬，老王做出深刻反省，公司也因此對於資料庫的性能和可靠性提出更高要求。
要求對資料庫進行改造，使其承載力進行提升，故障修複時間減少，有沒有能實現的方案呢？
Mysql實現資料庫主從複製實戰架構及其原理
1、實驗架構及其原理
2、過程分析
② mysql的進程讀事務日誌，事務日誌的內容做到資料庫記憶體中；此時可以回覆客戶端，數據為臟數據
⑤ I/O thread線程：從主的資料庫上，把二進位文件的內容拉過來，寫到relay log中繼日誌中
⑥ SQL thread線程：把relay log內容拉出來，寫到資料庫記憶體中
⑦ 從資料庫也可以把執行的操作記錄到自己的二進位文件中，非必須
3、主從的優勢
一個master 寫入，多個slave同時讀出；大大提高了讀的效率
現實中，很多都是讀的請求大，寫的請求相對小的多，如電商網站，大多都是人們去訪問，下單的較少；所以主從的關係已經能很好的提高性能了
4、實驗前準備
① iptables -F && setenforce 清空防火牆策略，關閉selinux
② 拿兩台伺服器都使用yum 方式安裝Mysql 服務，要求版本一致
實戰一：Mysql實現資料庫簡單一主多從複製實戰
1、環境準備
centos 系統伺服器3 台、一臺用戶做Mysql 主伺服器，2台用於做Mysql 從伺服器，配置好yum 源、 防火牆關閉、各節點時鐘服務同步、各節點之間可以通過主機名互相通信
|
機器名稱 |
IP配置 |
服務角色 |
備註 |
|
master-mysql |
192.168.30.107 |
主資料庫 |
二進位日誌 |
|
slave-mysql1 |
192.168.30.7 |
從資料庫 |
中繼日誌 |
|
slave-mysql2 |
192.168.30.2 |
從資料庫 |
中繼日誌 |
2、在主master 主伺服器上
① vim /etc/my.cnf 修改mysql主配置文件，對master進行配置，包括打開二進位日誌，指定唯一的servr ID
server-id=1 #配置server-id，讓主伺服器有唯一ID號 log-bin=mysql-bin #打開Mysql日誌，日誌格式為二進位 skip-name-resolve #關閉名稱解析，（非必須）
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%' IDENTIFIED BY 'along'; 分析：在Master的資料庫中建立一個備份帳戶：每個slave使用標準的MySQL用戶名和密碼連接master。進行複製操作的用戶會授予REPLICATION SLAVE許可權。
在Master的資料庫執行show master status，查看主伺服器二進位日誌狀態，位置號
3、在從slave mysql1上
vim /etc/my.cnf 打開中繼日誌，指定唯一的servr ID，設置只讀許可權
server-id=2 #配置server-id，讓從伺服器有唯一ID號 relay_log = mysql-relay-bin #打開Mysql日誌，日誌格式為二進位 read_only = 1 #設置只讀許可權 log_bin = mysql-bin #開啟從伺服器二進位日誌，（非必須） log_slave_updates = 1 #使得更新的數據寫進二進位日誌中
② 啟動從伺服器複製線程，讓slave連接master，並開始重做master二進位日誌中的事件。
MariaDB [(none)]> change master to master_host='192.168.30.107', -> master_user='slave', -> master_password='along', -> master_log_file='mysql-bin.000001', -> master_log_pos=245; MariaDB [(none)]> start slave; # 啟動複製線程，就是打開I/O線程和SQL線程；實現拉主的bin-log到從的relay-log上；再從relay-log寫到資料庫記憶體里
可使用SHOW SLAVE STATUS\G查看從伺服器狀態，如下所示，也可用show processlist \G查看當前複製狀態：
Slave_IO_Running: Yes #IO線程正常運行
Slave_SQL_Running: Yes #SQL線程正常運行
4、測試
5、若要繼續添加新salve，實現一主多從
假如master 已經運行很久了，想對新安裝的slave 進行數據同步，甚至它沒有master 的數據。
① 進行完全備份 mysqldump --all-databases > /backup/mysql-all-backup-`date +%F-%T`.sql 把備份生成的文件發給salve-mysql2機器上 scp /backup/mysql-all-backup-2017-11-20-22\:04\:06.sql @192.168.30.2: ② 查看現在的二進位文件狀態 MariaDB [(none)]> show master status;
mysql -uroot -p < mysql-all-backup-2017-11-20-22\:04\:06.sql
MariaDB [(none)]> change master to master_host='192.168.30.107', -> master_user='slave', -> master_password='along', -> master_log_file='mysql-bin.000003', -> master_log_pos=500;
6、測試三台機器的一主多從關係
7、解除主從關係，恢復獨立的伺服器
① MariaDB [(none)]> stop slave; 關閉兩個線程
relay-log =mysql-relay-log read-only = 1 log_slave_updates = 1
③ systemctl restart mariadb 重啟服務
實戰二：實現主從從架構及複製過濾器
架構原理：一個主master，一個從slave1；從slave1再做主，另一個slave2以他為主做從；大體做法與上實驗相似
複製過濾原理：複製過濾器：（黑、白名單）僅複製有限一個或幾個資料庫相關的數據，而非所有；由複製過濾器進行；
(1) 主伺服器 主伺服器僅向二進位日誌中記錄有關特定資料庫相關的寫操作； binlog_do_db= #僅允許從複製這個庫的二進位日誌 binlog_ignore_db= #除了這個庫，其他都允許複製 (2) 從伺服器 從伺服器的SQL THREAD僅重放關註的資料庫或表相關的事件，並將其應用於本地； Replicate_Do_DB= #只複製主的這個資料庫數據 Replicate_Ignore_DB= #除了這個都複製
1、環境準備
|
機器名稱 |
IP配置 |
服務角色 |
備註 |
|
master-mysql |
192.168.30.107 |
主資料庫 |
二進位日誌 |
|
slave-mysql1 |
192.168.30.7 |
從資料庫 |
中繼日誌 |
|
slave-mysql2 |
192.168.30.2 |
從資料庫 |
中繼日誌 |
2、在主master 主伺服器上
① vim /etc/my.cnf 修改mysql主配置文件，對master進行配置，打開二進位日誌，指定唯一的servr ID，設置複製過濾 server-id=1 #配置server-id，讓主伺服器有唯一ID號 log-bin=mysql-bin #打開Mysql日誌，日誌格式為二進位 skip-name-resolve #關閉名稱解析，（非必須） binlog_ignore_db=home #除了home資料庫，其他都允許從複製主的二進位文件 #binlog_do_db=along #僅允許從複製along資料庫的二進位文件
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%' IDENTIFIED BY 'along'; 分析：在Master的資料庫中建立一個備份帳戶：每個slave使用標準的MySQL用戶名和密碼連接master。進行複製操作的用戶會授予REPLICATION SLAVE許可權。
在Master的資料庫執行show master status，查看主伺服器二進位日誌狀態，位置號
3、在從slave mysql1上
vim /etc/my.cnf 打開中繼日誌，指定唯一的servr ID，設置只讀許可權
server-id=2 #配置server-id，讓從伺服器有唯一ID號 relay_log = mysql-relay-bin #打開Mysql日誌，日誌格式為二進位 read_only = 1 #設置只讀許可權 log_bin = mysql-bin #開啟從伺服器二進位日誌，（必須） log_slave_updates = 1 #使得更新的數據寫進二進位日誌中
② 啟動從伺服器複製線程，讓slave連接master，並開始重做master二進位日誌中的事件。
MariaDB [(none)]> change master to master_host='192.168.30.107', -> master_user='slave', -> master_password='along', -> master_log_file='mysql-bin.000001', -> master_log_pos=245; MariaDB [(none)]> start slave; # 啟動複製線程，就是打開I/O線程和SQL線程；實現拉主的bin-log到從的relay-log上；再從relay-log寫到資料庫記憶體里
可使用SHOW SLAVE STATUS\G查看從伺服器狀態，如下所示，也可用show processlist \G查看當前複製狀態：
Slave_IO_Running: Yes #IO線程正常運行
Slave_SQL_Running: Yes #SQL線程正常運行
4、測試主從和複製過濾
在主上創建一個along、home庫；從上自動生成along、home資料庫
① 在主上：在along庫中創建一個classes的表；從上自動生成
MariaDB [home]> create table classes (id int not null,name varchar(20));
② 在主上：在home庫中創建一個classes的表；從上沒有生成
MariaDB [home]> create table classes (id int not null,name varchar(20));
5、設置slave-mysql2 為slave-mysql1 的從，且在mysql2 設置複製過濾
因為上邊主配置文件已經開啟了自己的二進位文件；且slave-mysql1 是從開始就同步master的，所以授權命令也同步過了
MariaDB [home]> select user,host from mysql.user; 可以查看自己授權過的用戶
（2）slave-mysql2 上，可以像上實驗一樣，先給主的完全備份在本機恢復一下
① 在主上完備 mysqldump --all-databases > /backup/mysql-all-backup-`date +%F-%T`.sql scp /backup/mysql-all-backup-2017-11-21-11:14:59.sql @192.168.30.2: ② 進行master的完全備份恢復 mysql -uroot -p < mysql-all-backup-2017-11-20-22\:04\:06.sql ③ 在slave-mysql2 上 vim /etc/my.cnf 修改主配置文件，設為從；且設置過濾 server-id =3 relay-log =mysql-relay-log read-only = 1 log-bin = mysql-bin log_slave_updates = 1 replicate_do_dB=along #只複製它的主的along資料庫
啟動從伺服器複製線程，讓slave連接master，並開始重做master二進位日誌中的事件。
MariaDB [(none)]> change master to master_host='192.168.30.107', -> master_user='slave', -> master_password='along', -> master_log_file='mysql-bin.000008', -> master_log_pos=773; MariaDB [(none)]> start slave;
⑤ MariaDB [(none)]> show slave status \G; 查看，兩個進程打開，且只複製主的along資料庫
6、測試主從從和slave-mysql2的複製過濾
（1）在主上刪除job資料庫，master 和slave-mysql1 都刪除成功
MariaDB [home]> drop database job; 刪除job庫
因為slave-mysql2 只同步slave-mysql1 的along庫，所以沒有刪除
（2）在主上的along資料庫，創建一個grade 表，master 和slave-mysql1 都刪除成功
MariaDB [along]> create table grade (id int not null,name varchar(20));
實戰三：mysql資料庫雙主的實現
為瞭解決雙主同時對一個資料庫進行寫入，採用自增長ID來解決，兩個mysql寫入用奇偶ID岔開
① 創建表，設置ID為自增長 create table userInfo (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); ② 定義一個節點使用奇數id：從1開始，步長為2， auto_increment_increment=2 #表示自增長欄位每次遞增的量，步長 auto_increment_offset=1 #表示自增長欄位從那個數開始 ③ 另一個節點使用偶數id：從2開始，步長為2， auto_increment_increment=2 auto_increment_offset=2
1、環境準備
|
機器名稱 |
IP配置 |
服務角色 |
備註 |
|
mysql1 |
192.168.30.107 |
資料庫 |
中繼日誌、二進位日誌 |
|
mysql2 |
192.168.30.7 |
資料庫 |
中繼日誌、二進位日誌 |
2、配置總配置文件，除了ID號和起始數，兩邊都是一樣的
server-id =1 #mysql1的配置ID為1，mysql2的ID為2 relay-log =mysql-relay-log log-bin = mysql-bin log_slave_updates = 1 auto_increment_increment=2 #表示自增長欄位每次遞增的量，步長 auto_increment_offset=1 #表示自增長欄位從那個數開始，mysql1從1開始；mysql2從2開始
3、互相設為對方的從
（1）授權遠程登錄的用戶 mysql1、2 上 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%' IDENTIFIED BY 'along'; （2）開啟複製線程 ① mysql1 上 MariaDB [(none)]> change master to master_host='192.168.30.7', -> master_user='slave', -> master_password='along', -> master_log_file='mysql-bin.000002', -> master_log_pos=245; MariaDB [(none)]> start slave; # 啟動複製線程 ② mysql2 上 MariaDB [(none)]> change master to master_host='192.168.30.107', -> master_user='slave', -> master_password='along', -> master_log_file='mysql-bin.000002', -> master_log_pos=245; MariaDB [(none)]> start slave; # 啟動複製線程
4、測試雙方互為主從的關係
在mysql1上，刪除test資料庫；mysql2 上也自動刪除
在mysql2上，創建xiaohei資料庫；mysql2 上也自動生成
5、設置自增長ID的表
MariaDB [along]> create table home (id int PRIMARY KEY AUTO_INCREMENT,name varchar(20));
MariaDB [along]> insert into home(name) value('mayun'),('mahuateng'),('wangjianlin');
MariaDB [along]> insert into home(name) value('dinglei'),('liyanhong'),('leijun');
實驗四：實現半同步複製的一主多從
原理：介於非同步複製和全同步複製之間，主庫在執行完客戶端提交的事務後不是立刻返回給客戶端，而是等待至少一個從庫接收到並寫到relay log中才返回給客戶端。相對於非同步複製，半同步複製提高了數據的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。所以，半同步複製最好在低延時的網路中使用。
註意：本來是應該最少2個從mysql，才能有真正的效果，但是原理都是一樣的，我就只用了一主一從
1、環境準備
|
機器名稱 |
IP配置 |
服務角色 |
備註 |
|
master-mysql |
192.168.30.107 |
主資料庫 |
二進位日誌 |
|
slave-mysql |
192.168.30.7 |
從資料庫 |
中繼日誌 |
2、按照實戰一，實現主從
3、載入模塊，實現半同步
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 載入模塊 MariaDB [(none)]> show global variables like 'rpl_semi%'; 查看是否開啟 MariaDB [(none)]> set global rpl_semi_sync_master_enabled = on; 開啟
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'; set global variables rpl_semi_sync_slave_enabled = on; 為了主從同步，再重啟啟動下slave 兩個進程 MariaDB [(none)]> stop slave; MariaDB [(none)]> start slave;
4、查詢日誌，驗證
③ 啟動半同步的binlog 轉儲到slave (id:2)上
實驗五：實現MHA 高可用mysql資料庫架構
原理：總之就是當主master mysql宕機時，從slave mysql頂上去的一系列操作
① 從宕機崩潰的master 保存二進位日誌事件（binlog events ）;
③ 應用差異的中繼日誌(relay log) 到其他slave;
④ 應用從master 保存的二進位日誌事件(binlog events);
1、環境準備
|
機器名稱 |
IP配置 |
服務角色 |
備註 |
|
master-mysql |
192.168.30.107 |
主資料庫 |
二進位日誌、中繼日誌 |
|
slave-mysql1 |
192.168.30.7 |
從資料庫 |
二進位日誌、中繼日誌 |
|
slave-mysql2 |
192.168.30.2 |
從資料庫 |
二進位日誌、中繼日誌 |
|
MHA manager |
192.168.30.3 |
MHA的管理節點 |
|
2、實現三台伺服器的一主多從
① 每個節點都需開啟二進位和中繼日誌，因為主會宕機，當主的機器修複完畢，可以作為從繼續使用，所以中繼日誌是必須的；從也會在主宕機的時候，頂為主，所以二進位日誌也是必須的
② 各從節點必須顯示啟用其read-only 屬性，並關閉relay_log_purge 清理中繼日誌的功能
（1）vim /etc/my.cnf 修改配置文件 ① 主的配置文件 server-id=1 log-bin=mysql-bin relay-log=mysql-relay-log skip-name-resolve ② 從的配置文件，各個從的配置文件除了ID，其他都相同 server-id =2[/3] #各自對應自己的id relay-log =mysql-relay-log log-bin = mysql-bin read_only = on relay_log_purge = 0 skip_name_resolve systemctl start mariadb 啟動服務 （2）在主上：授權 MariaDB [(none)]> grant replication slave,replication client on *.* to slave@'192.168.30.%' identified by 'along'; （3）在從上：開啟I/O，SQL線程，實現主從 MariaDB [(none)]> change master to master_host='192.168.30.107', master_user='slave', master_password='along', master_log_file='mysql-bin.000001', master_log_pos=245; MariaDB [(none)]> start slave ;
3、配置MHA的準備
需安裝2個包 rz，我已經放到我網盤裡，需要的私聊http://pan.baidu.com/s/1kV8BCJt
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
yum -y localinstall mha4mysql-*
分析：MHA 集群中的各節點彼此之間均需要基於ssh 互信通信，以實現遠程式控制制及數據管理功能。
ssh-keygen -t rsa 生成公私秘鑰對，可以直接敲3個回車，不須加密 ssh-copy-id -i .ssh/id_rsa.pub root@192.168.30.7: ssh-copy-id -i .ssh/id_rsa.pub root@192.168.30.2: ssh-copy-id -i .ssh/id_rsa.pub root@192.168.30.3: 把公鑰發給其他3個機器 註意：每個mysql伺服器都需要發送自己的公鑰
MariaDB [(none)]> grant all on *.* to 'mhaadm'@'192.168.30.%' identified by 'along';
註意：MHA manager 需要做很多事，所以給很大的許可權；且已經實現主從，所以只需在master上執行授權命令
mkdir /etc/mha_master 創建配置文件存放目錄
vim /etc/mha_master/app.cnf 設置配置文件，註意註釋不要加在配置文件中，否則檢測不過
[server default] // 適用於server1,2,3 個server 的配置 user=mhaadm //mha 管理用戶 password=along //mha 管理密碼 manager_workdir=/etc/mha_master/app //mha_master 自己的工作路徑 manager_log=/etc/mha_master/manager.log // mha_master 自己的日誌文件 remote_workdir=/mydata/mha_master/app // 每個遠程主機的工作目錄在何處 ssh_user=root // 基於ssh 的密鑰認證 repl_user=slave // 資料庫用戶名 repl_password=along // 資料庫密碼 ping_interval=1 // ping 間隔時長 [server1] // 節點1 hostname=192.168.30.107 // 節點1 主機地址 ssh_port=22 // 節點1 的ssh 埠 candidate_master=1 // 將來可不可以成為master 候選節點/ 主節點 [server2] hostname=192.168.30.7 ssh_port=22 candidate_master=1 [server3] hostname=192.168.30.2 ssh_port=22 candidate_master=1
4、開啟服務之前的檢測
註意：開啟服務之前的檢查非常有必要，因為mha服務是管理mysql的，所有要求很嚴格，如果檢查通不過，開啟服務時會有一堆錯誤。每次啟動服務之前都需檢測環境。
masterha_check_ssh -conf=/etc/mha_master/app.cnf
[info]All SSH connection tests passed successfully.
② 檢查管理的MySQL 複製集群的連接配置參數是否OK ：
masterha_check_repl -conf=/etc/mha_master/app.cnf
MySQL Replication Health is OK.
③ 註意：如果測試時會報錯 ，可能是從節點上沒有賬號，因為這個架構，任何一個從節點，將有可能成為主節點，所以也需要創建賬號。
MariaDB [(none)]> grant all on *.* to 'mhaadm'@'192.168.30.%' identified by 'along';
MariaDB [(none)]> FLUSH PRIVILEGES;
5、啟動MHA
nohup masterha_manager -conf=/etc/mha_master/app.cnf &> /etc/mha_master/manager.log &
啟動成功後，可用過如下命令來查看master 節點的狀態:
masterha_check_status -conf=/etc/mha_master/app.cnf
app (pid:3777) is running(0:PING_OK), master:192.168.30.107
上面的信息中"app (pid:3777)is running(0:PING_OK)" 表示MHA 服務運行OK ，否則，則會顯示為類似"app is stopped(1:NOT_RUNNINg)."
（2）如果要停止MHA ，需要使用master_stop 命令。
masterha_stop -conf=/etc/mha_master/app.cnf
6、測試MHA 測試故障轉移
(1) 在master 節點關閉mariadb 服務， 模擬主節點數據崩潰
tail -20 /etc/mha_master/manager.log 日誌文件中出現如下信息，表示manager 檢測到192.168.30.107節點故障，而後自動執行故障轉移，將192.168.30.7 提升為主節點。
註意：故障轉移完成後，manager 將會自動停止，此時使用
masterha_check_status 命令檢測將會遇到錯誤提示，如下所示：
7、提供新的從節點以修複複製集群
原有 master 節點故障後，需要重新準備好一個新的 MySQL 節點。基於來自於master 節點的備份恢複數據後，將其配置為新的 master 的從節點即可。註意，新加入的節點如果為新增節點，其 IP 地址要配置為原來 master 節點的 IP ，否則，還需要修改 app.cnf 中相應的 ip 地址。隨後再次啟動 manager ，並再次檢測其狀態。
除了增加新的mysql 節點，也可以將壞掉主mysql 修複，再將其作為從加入集群中。由於機器有限，我就用修複好的主作為從，修複複製集群。
mysqldump --all-databases > /backup/mysql-all-backup-`date +%F-%T`.sql
scp /backup/mysql-all-backup-2017-11-26-14\:03\:19.sql @192.168.30.107:
mysql -uroot -p < mysql-all-backup-2017-11-26-14\:03\:19.sql
MariaDB [(none)]> show master status;
MariaDB [(none)]> change master to master_host='192.168.30.7', master_user='slave', master_password='along', master_log_file='mysql-bin.000003
