MariaDB資料庫主從架構和配置優化
--------------------------------------------------------------------------------------------------------------------------------------------
一、MySQL複制
1、擴充方式：Scale UP，Scale Out
2、MySQL的擴充
讀寫分離
複制：每個節點都有相同的資料集
向外擴充
基于二進制日志發生更改，實作主從複制
單向複制，由主到從複制
3、複制的功用
資料分布、負載均衡讀、備份、高可用和故障切換、MySQL更新測試
二、MySQL讀寫分離
讀寫分離應用：
mysql-proxy：Oracle
Atlas：奇虎，基于mysql-proxy
DBProxy：美團
Cetus：網易
Amoeba
Cobar：阿裡巴巴，基于Amoeba的更新版
Mycat：基于Cobar
ProxySQL
三、主從複制
1、一主一從
2、一主多從
3、主從複制原理
主伺服器資料更新時，會産生二進制日志，之後通過slave服務線程（線程1）讀取二進制日志，并且和從伺服器的IO Thread（線程2）傳輸二進制日志，寫入中繼日志relay log，接着從伺服器通過SQL Thread的線程（線程3）讀取中繼日志中SQL語句并寫入資料庫中，進而實作了主從複制
要求主伺服器必須啟動二進制日志，如果從伺服器開自己的從伺服器，也需要開啟二進制日志
使用者通路量大時，主從複制會有延遲
4、讀寫分離方法：MySQL垂直分區
其他資訊、使用者資訊、消息資訊各放各的伺服器，資料表之間無法join，需要結合軟體開發來實作表之間的連接配接
5、讀寫分離方法：MySQL水準分片
仍然需要配合業務部門的軟體開發來實作
6、MySQL複制概念
主從複制線程：
主節點：
dump Thread：為每個Slave的I/O Thread啟動一個dump線程，用于向其發送binary log events
從節點：
I/O Thread：向Master請求二進制日志事件，并儲存于中繼日志中
SQL Thread：從中繼日志中讀取日志事件，在本地完成重放
跟複制功能相關的檔案：
master.info：用于儲存slave連接配接至master時的相關資訊，例如賬号、密碼、伺服器位址等
relay-log.info：儲存在目前slave節點上已經複制的目前二進制日志和本地replay log日志的對應關系
主從複制特點：
異步複制：用戶端發送同步指令完畢，何時主從同步不予理會
主從資料不一緻比較常見
複制架構：
Master/Slave, Master/Master（除非對資料準确度要求不高，一般不推薦使用）, 環狀複制
一主多從
從伺服器還可以再有從伺服器
一從多主:适用于多個不同資料庫（mysql版本大于5.6）
複制需要考慮二進制日志事件記錄格式
STATEMENT（5.0之前）
ROW（5.1之後，推薦）
MIXED
7、MySQL複制模型
四、實作主從複制（版本最好一樣）
主節點配置：
1、 啟用二進制日志
[mysqld]
log_bin
2、為目前節點設定一個全局惟一的ID号
server_id=#
log-basename=master 可選項，設定datadir中日志名稱，確定不依賴主機名
3、建立有複制權限的使用者賬号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
從節點配置：
1、啟動中繼日志
server_id=# 為目前節點設定一個全局惟的ID号
relay_log=relay-log relay log的檔案路徑，預設值hostname-relay-bin
relay_log_index=relay-log.index 預設值hostname-relay-bin.index
2、 進入資料庫進行遠端複制，對主伺服器進行遠端連接配接記不住輸入help change，會有提示，接着help chage master to，會有提示指令
mysql> CHANGE MASTER TO MASTER_HOST='host', MASTER_USER='repluser',
MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.xxxxx',
MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
3、如果主節點已經運作了一段時間，且有大量資料時，如何配置并啟動slave節點
通過備份恢複資料至從伺服器
複制起始位置為備份時，二進制日志檔案及其POS
如果要啟用級聯複制,需要在從伺服器啟用以下配置，及主---從1----從2，從1需要加的内容
log_slave_updates
複制架構中應該注意的問題：
1、限制從伺服器為隻讀
在從伺服器上設定read_only=ON
注意：此限制對擁有SUPER權限的使用者均無效
阻止所有使用者, 包括主伺服器複制的更新
mysql> FLUSH TABLES WITH READ LOCK;
2、RESET SLAVE
在從伺服器清除master.info ，relay-log.info, relay log ，開始新的relay log ,注意：需要先STOP SLAVE
RESET SLAVE ALL 清除所有從伺服器上設定的主伺服器同步資訊如：PORT, HOST, USER和 PASSWORD 等
3、sql_slave_skip_counter = N 從伺服器忽略幾個主伺服器的複制事件，global變量，複制時候産生錯誤，是否跳過錯誤，預設值是0，關閉
4、如何保證主從複制的事務安全
參看https://mariadb.com/kb/en/library/server-system-variables/
在master節點啟用參數：
sync_binlog=1 每次寫後立即同步二進制日志到磁盤，性能差
如果用到的為InnoDB存儲引擎：
innodb_flush_log_at_trx_commit=1 每次事務送出立即同步日志寫磁盤
innodb_support_xa=ON 預設值，分布式事務MariaDB10.3.0廢除
sync_master_info=# #次事件後master.info同步到磁盤
在slave節點啟用伺服器選項：
skip_slave_start=ON 不自動啟動slave,在slave節點啟用參數：
sync_relay_log=# #次寫後同步relay log到磁盤
sync_relay_log_info=# #次事務後同步relay-log.info到磁盤
五、半同步複制
半同步複制實作：一主多從，如果至少一個從伺服器同步成功，主伺服器将會向用戶端傳回成功資訊；或者所有從伺服器長時間沒有同步，超過了限定時長，主伺服器也将會向用戶端傳回成功資訊
1、主伺服器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1;，寫入檔案時永久儲存
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;逾時長為1s，機關是毫秒
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql>SHOW GLOBAL STATUS LIKE '%semi%‘;
2、從伺服器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
stop slave; start slave;，這兩步用來啟用插件
附show plugins;：檢視插件
工作中建議master--slave半同步--slave1,2,3
六、
1、複制過濾器：
讓從節點僅複制指定的資料庫，或指定資料庫的指定表
2、兩種實作方式：
(1) 伺服器選項：主伺服器僅向二進制日志中記錄與特定資料庫相關的事件
注意：此項和binlog_format相關
參看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-dbbinlog_do_db = 資料庫白名單清單，多個資料庫需多行實作，選項，/etc/my.cnf的[mysqld]
binlog_ignore_db = 資料庫黑名單清單，選項
問題：基于二進制還原将無法實作；不建議使用
(2) 從伺服器SQL_THREAD在replay中繼日志中的事件時，僅讀取與特定資料庫(特定表)相關的事件并應用于本地
問題：會造成網絡及磁盤IO浪費
在從伺服器上添加的複制過濾器相關變量，先stop slave停止線程，然後使用set replicate...=DB設定名單
replicate_do_db= 指定複制庫的白名單，隻适用于global選項，使用必須use進入目前庫進行增删改操作，不能在其它表中用DB.TBL
replicate_ignore_db= 指定複制庫黑名單
replicate_do_table= 指定複制表的白名單
replicate_ignore_table= 指定複制表的黑名單
replicate_wild_do_table= foo%.bar% 支援通配符
replicate_wild_ignore_table=
3、基于SSL複制：
在預設的主從複制過程或遠端連接配接到MySQL/MariaDB所有的連結通信中的資料都是明文的，外網裡通路資料或則複制，存在安全隐患。通過SSL/TLS加密的方式進行複制的方法，來進一步提高資料的安全性
參看：https://mariadb.com/kb/en/library/replication-with-secure-connections/
主伺服器開啟SSL：[mysqld] 加一行ssl
主伺服器配置證書和私鑰；并且建立一個要求必須使用SSL連接配接的複制賬号
從伺服器使用CHANGER MASTER TO 指令時指明ssl相關選項
Master伺服器配置
log-bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ----CA憑證
ssl-cert=/etc/my.cnf.d/ssl/master.crt ----CA頒發的證書
ssl-key=/etc/my.cnf.d/ssl/master.key ----主伺服器的私鑰
儲存後進入資料庫輸入
grant replication slve on *.* to ssluser@'HOST' identified by 'passwd' require ssl;，建立強制加密的使用者
Slave伺服器配置
mysql>
CHANGE MASTER TO
MASTER_HOST='MASTERIP',
MASTER_USER='rep',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
CA伺服器：建立目錄放證書，openssl genrsa 2048 >cakey.pem，
openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650，生成自簽名，互動式設定；
openssl req -newkey rsa:1024 -days 100 -nodes -keyout master.key > master.csr，生成主伺服器私鑰檔案 > 生成證書申請，互動式填寫與上一步一樣
openssl x509 -req -in master.csr -days 100 -CA cacert.pem -CAkey cakey.pem -set_serial 01 >master.crt
openssl req -newkey rsa:1024 -days 100 -nodes -keyout master.key > slave.csr
openssl x509 -req -in slave.csr -days 100 -CA cacert.pem -CAkey cakey.pem -set_serial 02 >slave.crt
證書申請完畢，将生成的檔案放到對應的目錄下
七、複制的監控和維護
1、 清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER
RESET SLAVE
2、 複制監控
SHOW MASTER STATUS
SHOW BINLOG EVENTS
SHOW BINARY LOGS
SHOW SLAVE STATUS
SHOW PROCESSLIST
3、 從伺服器是否落後于主服務
Seconds_Behind_Master: 0
4、 如何确定主從節點資料是否一緻
percona-tools
5、 資料不一緻如何修複
删除從資料庫，重新複制
八、高可用
1、MMM: Multi-Master Replication Manager for MySQL，Mysql主主複制管理器是一套靈活的腳本程式，基于perl實作，用來對mysql replication進行監控和故障遷移，并能管理mysql Master-Master複制的配置(同一時間隻有一個節點是可寫的)
官網： http://www.mysql-mmm.org
https://code.google.com/archive/p/mysql-master-master/downloads
2、MHA： Master High Availability，對主節點進行監控，可實作自動故障轉移至其它從節點；通過提升某一從節點為新的主節點，基于主從複制實作，還需要用戶端配合實作，目前MHA主要支援一主多從的架構，要搭建MHA,要求一個複制叢集中必須最少有三台資料庫伺服器，一主二從，即一台充當master，一台充當備用master，另外一台充當從庫，出于機器成本的考慮，淘寶進行了改造，目前淘寶TMHA已經支援一主一從
官網:https://code.google.com/archive/p/mysql-master-ha/
3、Galera Cluster：wsrep(MySQL extended with the Write Set Replication)
通過wsrep協定在全局實作複制；任何一節點都可讀寫，不需要主從複制，實作多主讀寫
九、MHA叢集架構
工作原理
1、從當機崩潰的master儲存二進制日志事件（binlog events）
2、識别含有最新更新的slave
3、應用差異的中繼日志（relay log）到其他的slave
4、應用從master儲存的二進制日志事件（binlog events）
5、提升一個slave為新的master
6、使其他的slave連接配接新的master進行複制
實驗，建議centos7上做，不要在centos6做
1、準備初步環境，四台主機 ，1主2從1管理器
2、主機之間實作基于key實作，時間需要同步
随便一台主機，ssh-keygen,ssh-copy-id,在自己主機生成公鑰，将此目錄拷貝至每個裝置對應的/root，實作裝置之間互通基于key驗證
3、主伺服器上：
vim /etc/my.cnf
skip_name_resolve=1 --------->必須項
4、建立賬戶
複制賬号：mysql>grant replication slave on *.* to repluser@'192.168.8.%' identified by 'magedu';
管理賬号：mysql>grant all on *.* to mhauser@'192.168.8.%' identified by 'magedu';
5、從伺服器改檔案
從1：
server_id=2 不同節點此值各不相同
read_only
relay_log_purge=0
從2：
server_id=3 不同節點此值各不相同
改好後重新開機服務
6、兩台從伺服器：mysql>CHANGE MASTER TO MASTER_HOST=‘主伺服器_IP', MASTER_USER='repluser', MASTER_PASSWORD=‘magedu', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;，設定主從同步，show slave status\G檢查同步狀态，複制完成後start slave開啟線程
7、基于epel源安裝工具
管理節點安裝manager工具包：mha4mysql-manager、mha4mysql-node
被管理節點安裝node工具包：mha4mysql-node
8、修改管理主機配置檔案
vim /etc/mastermha/app1.cnf ----------->自己建立
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/ -------->注意有沒有根下/data目錄
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
[server1]
hostname=192.168.8.17---------->主伺服器
candidate_master=1
[server2]
hostname=192.168.8.27 --------->代替主伺服器的從伺服器
[server3]
hostname=192.168.8.37 -------->從伺服器
9、mha驗證啟動
masterha_check_ssh --conf=/etc/mastermha/app1.cnf --------->8的配置檔案建哪了就寫哪
masterha_check_repl --conf=/etc/mastermha/app1.cnf 同上
10、啟動執行
masterha_manager --conf=/etc/mastermha/app1.cnf 該步驟前台執行，確定安全可以加hub
11、測試
主機當機後會依舊執行程式，系統自動更換主伺服器
十、Galera Cluster
1、內建了Galera插件的MySQL叢集，是一種新型的，資料不共享的，高度備援的高可用方案，目前Galera Cluster有兩個版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的叢集架構，是一個既穩健，又在資料一緻性、完整性及高性能方面有出色表現的高可用解決方案
2、特點
多主架構：真正的多點讀寫的叢集，在任何時候讀寫資料，都是最新的
同步複制：叢集不同節點之間資料同步，沒有延遲，在資料庫挂掉之後，資料不會丢失
并發複制：從節點APPLY資料時，支援并行執行，更好的性能
故障切換：在出現資料庫故障時，因支援多點寫入，切換容易
熱插拔：在服務期間，如果資料庫挂了，隻要監控程式發現的夠快，不可服務時間就會非常少。在節點故障期間，節點本身對叢集的影響非常小
自動節點克隆：在新增節點，或者停機維護時，增量資料或者基礎資料不需要人工手動備份提供，Galera Cluster會自動拉取線上節點資料，最終叢集會變為一緻
對應用透明：叢集的維護，對應用程式是透明的
3、在一個伺服器發生事務，在另一台伺服器也會發生同樣的事務
4、Galera Cluster包括兩個元件
Galera replication library (galera-3)
WSREP：MySQL extended with the Write Set Replication
5、WSREP複制實作：percona-cluster、MariaDB-Cluster
6、注意：都至少需要三個節點，不能安裝mariadb-server，安裝後會自動解除安裝mariadb-server，安裝自己的叢集版
基于epel源安裝，epel源位址：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.59/yum/centos7-amd64/
yum install MariaDB-Galera-server
7、編輯配置檔案
vim /etc/my.cnf.d/server.cnf
wsrep_provider = /usr/lib64/galera/libgalera_smm.so -------->程式路徑
wsrep_cluster_address="gcomm://192.168.8.7,192.168.8.17,192.168.8.27" ---->3個伺服器ip位址
binlog_format=row ----------->基于行的複制方式
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2 -------->基于鎖的模式
bind-address=0.0.0.0
下面配置可選項
wsrep_cluster_name = 'mycluster' 預設my_wsrep_cluster
wsrep_node_name = 'node1'
wsrep_node_address = ‘192.168.8.7'
8、其它裝置的配置檔案按照剛才配置檔案格式進行更改，或者覆寫
9、首次啟動時，需要初始化叢集，在其中一個節點上執行指令
/etc/init.d/mysql start --wsrep-new-cluster
10、其它主機正常啟動其它節點
service mysql start ----------->注意mysql不帶d
11、檢視叢集中相關系統變量和狀态變量
SHOW VARIABLES LIKE 'wsrep_%'\G ----->伺服器變量
SHOW STATUS LIKE 'wsrep_%'\G ------>伺服器目前狀态
SHOW STATUS LIKE 'wsrep_cluster_size'\G
12、測試
十一、複制的問題和解決方案：
1、資料損壞或丢失
Master： MHA + semi repl
Slave： 重新複制
2、混合使用存儲引擎
MyISAM：不支援事務
InnoDB： 支援事務
3、不惟一的server id
重新複制
4、複制延遲
需要額外的監控工具的輔助
一從多主:mariadb10版後支援
多線程複制：對多個資料庫複制
十二、性能衡量名額
1、資料庫服務衡量名額：
qps: query per second
tps: transaction per second
2、壓力測試工具：
mysqlslap
Sysbench：功能強大，https://github.com/akopytov/sysbench
tpcc-mysql
MySQL Benchmark Suite
MySQL super-smack
MyBench
十三、生産環境my.cnf配置示例
硬體：記憶體32G
十四、MYSQL配置最佳實踐
1、高并發大資料的網際網路業務，架構設計思路是“解放資料庫CPU，将計算轉移到服務層”，并發量大的情況下，這些功能很可能将資料庫拖死，業務邏輯放到服務層具備更好的擴充性，能夠輕易實作“增機器就加性能”
2、參考：
阿裡巴巴Java開發手冊
58到家資料庫30條軍規解讀
http://zhuanlan.51cto.com/art/201702/531364.htm