資料庫對於網際網際網路公司來說是一個公司的心臟，沒有了它這個公司絕對只是一堆 ppt 。由此對於一個運維來說資料庫絕對是維護的重中之重，每天都要對資料庫進行增量備份，每週要進行一次完全備份。常用的備份工具 mysqldump 這是一個邏輯被分工具那就意味著效能將會被計算消耗一些；extrabackup 這是一個物理備份工具，具有較好的效能；還有一種藉助 lvm 進行備份的方法，這種方法的顯然不靠譜，因為 lvm 儲存的資料不具有硬體級恢復資料的特性，一旦遇到極端情況，我們只能接受資料丟失。
資料庫需要備份什麼？1 資料，日誌；2 程式，儲存例程；3 配置檔案。既然要備份這些資料，我們需要了解他們的結構，配置檔案好理解也好備份，程式和儲存例程的備份也比較好備份只要提供統樣的執行環境。
資料備份就很麻煩了，我們需要充分的瞭解 mariadb 的執行原理。其中在 mariadb 儲存時使用的是黑盒儲存，那就造成一個問題，我們要檢視資料庫中的資料只有使用 mysql 客戶端。那麼我們備份這些資料就有兩個方法。一個是邏輯備份，我們把資料反向生成一個 mysql 客戶端產生資料的方法，這個備份會有一個極大的問題，反向生成會很慢，恢復資料也會很慢；另一個是物理備份，我們直接把黑盒資料複製一份，但是這種備份就很麻煩了，備份資料庫我們不能把資料庫停下來吧，萬一來了一個大單資料庫停會造成很大的影響。每天都例行對資料庫進行維護貌似只有某個交通公司在這麼幹，作為以使用者為中心的公司這麼做很有可能會流失客戶，備份的特性依賴於儲存引擎。
儲存引擎
表型別：表級別概念，不建議在同一個庫中的表上使用不同的 ENGINE；
CREATE TABLE … ENGINE[=]STORAGE_ENGINE_NAME … #定義資料庫表使用的儲存引擎
SHOW TABLE STATUS #檢視儲存引擎
常見的儲存引擎：
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
當前資料庫支援的儲存引擎
mysql> SHOW ENGINES;
InnoDB：InnoBase
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
資料儲存於 “表空間 (table space)” 中：
(1) 所有 InnoDB 表的資料和索引儲存於同一個表空間中；
表空間檔案：datadir 定義的目錄中
檔案：ibdata1, ibdata2, …
(2) innodb_file_per_table=ON，意味著每表使用單獨的表空間檔案；
資料檔案（資料和索引，儲存於資料庫目錄）: tbl_name.ibd
表結構的定義：在資料庫目錄，tbl_name.frm
事務型儲存引擎，適合對事務要求較高的場景中；但較適用於處理大量短期事務；
基於 MVCC（Mutli Version Concurrency Control）支援高併發；支援四個隔離級別，預設級別為 REPEATABLE-READ；間隙鎖以防止幻讀；
使用聚集索引（主鍵索引）；
支援” 自適應 Hash 索引 “；
鎖粒度：行級鎖；間隙鎖；
總結：
資料儲存：表空間；
併發：MVCC，間隙鎖，行級鎖；
事務：REPEATABLE-READ；適用於大量短期事務；
索引：聚集索引、輔助索引；
效能：預讀操作、記憶體資料緩衝、記憶體索引 WordPress 加速快取、自適應 Hash 索引、插入操作 WordPress 加速快取區；
備份：支援熱備；
MyISAM：
支援全文索引（FULLTEXT index）、壓縮、空間函式（GIS）；
不支援事務
鎖粒度：表級鎖
崩潰無法保證表安全恢復
適用場景：只讀或讀多寫少的場景、較小的表（以保證崩潰後恢復的時間較短）；
檔案：每個表有三個檔案，儲存於資料庫目錄中
tbl_name.frm：表格式定義；
tbl_name.MYD：資料檔案；
tbl_name.MYI：索引檔案；
特性：
加鎖和併發：表級鎖；
修復：手動或自動修復、但可能會丟失資料；
索引：非聚集索引；
延遲索引更新；
表壓縮；
日誌備份
日誌備份就很難理解了，mariadb 設計時就考慮到資料的完全性，必須保證資料寫入成功，那麼設計一套記錄日誌：
訪問日誌
慢訪問日誌，訪問時間較長的命令
二進位制日誌，記錄導致資料庫變化的命令。
中繼日誌，資料庫主從
那麼為什麼要備份日誌，因為我們備份資料後，資料庫發生變化那麼怎麼保證資料的完全性，這裡我們可以藉助二進位制日誌，把備份的資料庫後變更的操作再次執行一遍，我們就可以恢復資料庫的資料了。
邏輯備份恢復資料庫

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#首先準備好備份
mysqldump –databases DATABASENAME –single-transaction -R –triggers -E –flush-logs –master-data=2 > /tmp/backup.sql
#然後備份二進位制日誌，去掉不想執行的語句
mysqlbinlog –stop-position=# /var/lib/mysql/master-log.00000# > /tmp/bin.log
mysql < /tmp/bin.log #然後備份二進位制日誌，去掉不想執行的語句

1
2
3
4
5
6
7
8
9

innobackupex –copy-back /BACKUP/DIR #恢復備份
chown -R mysql.mysql /var/lib/mysql
systemctl start mariadb.service
#恢復二進位制日誌中產生的資料
mysql < GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘USERNAME’@’HOST’ IDENTIFIED BY ‘YOUR_PASSWORD’;
mysql> FLUSH PRIVILEGES;

從站群伺服器

1
2
3
4
5
6
7
8
9

vim /etc/my.cnf
innodb_file_per_table=1
skip_name_resolve=1
server_id=#
relay_log=relay-log
啟動服務：
mysql> CHANGE MASTER TO MASTER_HOST=’HOST’,MASTER_USER=’USERNAME’,MASTER_PASSWORD=’YOUR_PASSWORD’,MASTER_LOG_FILE=’BINLOG’,MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;

ssl
配置主站群伺服器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

建立證書檔案
cd /etc/pki/CA/
touch index.txt
echo 01 > serial
(umask 066;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem
(umask 066;openssl genrsa -out mysql.key 2048; )
openssl req -new -key mysql.key -out mysql.csr -days 365
openssl ca -in mysql.csr -out mysql.crt -days 700
(umask 066;openssl genrsa -out client.key 2048; )
openssl req -new -key client.key -out client.csr -days 365
openssl ca -in client.csr -out client.crt -days 700
#複製證書檔案，並更改屬組屬主
cp cacert.pem mysql.crt mysql.key client.key client.crt /etc/mysql/
chown -R mysql.mysql /etc/mysql
#複製秘鑰證書，前提需要在 slave 上建立/etc/mysql 目錄
scp -p client.key client.crt cacert.pem 172.16.29.10:/etc/mysql

1
2
3
4
5
6
7
8
9
10
11

vim /etc/my.cnf
#在 [mysqld] 段中新增如下配置
ssl #開啟 SSL 功能
ssl-ca = /etc/mysql/cacert.pem #指定 CA 檔案位置
ssl-cert = /etc/mysql/mysql.crt #指定證書檔案位置
ssl-key = /etc/mysql/mysql.key #指定金鑰所在位置
#開啟站群伺服器
systemctl restart mariadb.service
#授權
mysql
grant replication slave,replication client on *.* to ‘tom’@’172.16.%.%’ identified by ‘tom’ require ssl;

slave 節點

1
2
3
4
5
6

#在 slave 上連線
cd /etc/mysql/
mysql –ssl-ca=cacert.pem –ssl-cert=client.crt –ssl-key=client.key -h172.16.29.2 -utom -ptom
#檢視連線狀態
s
quit

顯示如下一行使用 ssl 代表成功

配置從站群伺服器

1
2
3
4
5

systemctl restart mariadb.service
mysql
change master to master_host=’172.16.29.2′,master_user=’tom’,master_password=’tom’,master_log_file=’master-log.000008′,master_log_pos=245,master_ssl=1,master_ssl_ca=’/wp-content/uploads/sites/3/etc/mysql/cacert.pem’,master_ssl_cert=’/wp-content/uploads/sites/3/etc/mysql/client.crt’,master_ssl_key=’/wp-content/uploads/sites/3/etc/mysql/client.key’;
start slave;
show slave statusG

如圖代表配置成功

mha
安裝包下載地址 https://code.google.com/p/mysql-master-ha/downloads/list
從站群伺服器安裝

1

yum install mha4mysql-node-0.54-0.el6.noarch.rpm -y

主站群伺服器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

yum install mha4mysql-node-0.54-0.el6.noarch.rpm mha4mysql-manager-0.55-0.el6.noarch.rpm -y
[root@manager ~]# cat /usr/local/mha/mha.cnf
[server default]
user=mha_rep #MHA 管理 mysql 的使用者名稱
password=123456 #MHA 管理 mysql 的密碼
manager_workdir=/usr/local/mha #MHA 的工作目錄
manager_log=/usr/local/mha/manager.log #MHA 的日誌路徑
ssh_user=root #免秘鑰登陸的使用者名稱
repl_user=backup #主從複製賬號，用來在主從之間同步資料
repl_password=backup
ping_interval=1 #ping 間隔時間，用來檢查 master 是否正常
[server1]
hostname=192.168.253.241
master_binlog_dir=/data/mysql/
candidate_master=1 #master 宕機後，優先啟用這臺作為 master
[server2]
hostname=192.168.253.242
master_binlog_dir=/data/mysql/
candidate_master=1
[server3]
hostname=192.168.253.243
master_binlog_dir=/data/mysql/
no_master=1 #設定 na_master=1，使站群伺服器不能成為 master
#檢查 ssh 是否暢通
masterha_check_ssh –conf=/usr/local/mha/mha.cnf
#執行，這個指令碼生效後就自動退出了
nohup masterha_manager –conf=/usr/local/mha/mha.cnf > /tmp/mha_manager.log 2>&1 &

雙主
互為主從：兩個節點各自都要開啟 binlog 和 relay log；
1 、資料不一致；
2 、自動增長 id；
定義一個節點使用奇數 id
auto_increment_offset=1
auto_increment_increment=2
另一個節點使用偶數 id
auto_increment_offset=2
auto_increment_increment=2
配置：
1 、 server_id 必須要使用不同值；
2 、均啟用 binlog 和 relay log；
3 、存在自動增長 id 的表，為了使得 id 不相沖突，需要定義其自動增長方式；
服務啟動後執行如下兩步：
4 、都授權有複製許可權的使用者賬號；
5 、各把對方指定為主節點；
複製過濾器
僅複製有限一個或幾個資料庫相關的資料，而非所有；由複製過濾器進行；
有兩種實現思路：
(1) 主站群伺服器
主站群伺服器僅向二進位制日誌中記錄有關特定資料庫相關的寫操作；
問題：其它庫的 point-recovery 將無從實現；
binlog_do_db=
binlog_ignore_db=
(2) 從站群伺服器
從站群伺服器的 SQL THREAD 僅重放關注的資料庫或表相關的事件，並將其應用於本地；
問題：網際網路 IO 和磁碟 IO；
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=
總結
備份的資料有：資料，日誌，配置檔案，程式，
怎麼備份：邏輯備份，藉助 lvm 備份，物理備份（全量備份，增量備份，變數備份）
高可用：主從，雙主，加密通訊，複製過濾