第二十一周作業

1、回顧并詳細總結MySQL的存儲引擎、索引；

常用存儲引擎的對比：

InnoDB：5.5版本之后默認的存儲引擎，具有提交、回滾和崩潰恢復能力的事務安全機制，但是對比MyISAM，InnoDB寫處理效率差一些，并且會占用更多的磁盤空間以保留數據和索引。
處理大量的短期事務；
數據存儲于“表空間(table space)”中；
(1) 所有InnoDB表的數據和索引放置于同一個表空間中；
表空間文件：datadir定義的目錄下
數據文件：ibddata1, ibddata2, …
(2) 每個表單獨使用一個表空間存儲表的數據和索引；
innodb_file_per_table=ON

數據文件(存儲數據和索引)：tbl_name.ibd, 
表格式定義：tbl_name.frm

基于MVCC來支持高并發，支持所有的四個隔離級別，默認級別為REPEATABLE READ; 間隙鎖防止幻讀；
使用聚集索引
支持“自適應hash索引”
鎖粒度：行級鎖

MariaDB (XtraDB (percona))

數據存儲：表空間
并發：MVCC, 間隙鎖
索引：聚集索引、輔助索引
性能：預計操作、自適應hash、插入緩存區
備份：支持熱備(xtrabacup)

MyISAM：5.5之前版本的默認存儲引擎，MyISAM不支持事務、外鍵，其優勢是訪問速度快，并且支持全文索引，對事務完整性沒有要求或者以SELECT、INSERT為主的應用基本上都可以使用這個引擎來創建表。
支持全文索引(FULLTEXT index)、壓縮、空間函數(GIS); 但不支持事務，且為表級鎖；
崩潰后無法安全恢復

適用場景：只讀（或者寫較少）、表較?。梢越邮荛L時間進行修復操作）
Aria：crash-safe

文件：
tbl_name.frm: 表格式定義
tbl_name.MYD: 數據文件
tbl_name.MYI: 索引文件

特性：
加鎖和并發：表級鎖
修復：手工或自動修復、但可能丟失數據
索引：非聚集索引
延遲更新索引鍵：
壓縮表

行格式：dynamic, fixed, compressed, compact, redundent

其它的存儲引擎：
CSV：將普通的CSV(字段通過逗號分隔)作為MySQL表使用；
MRG_MYISAM：將多個MyISAM表合并成為一個虛擬表；
BLACKHOLE：類似于/dev/null，不真正存儲任何數據；
MEMORY：所有數據都保存于內存中，內存表；支持hash索引；表級鎖；
臨時表
PERFORMANCE_SCHEMA：偽存儲引擎；
ARCHIVE：只支持SELECT和INSERT操作；支持行級鎖和專用緩存區；
FEDERATED：用于訪問其它遠程MySQL服務器一個代理，它通過創建一個到遠程MySQL服務器的客戶端連接，并將查詢傳輸到遠程服務器執行，而后完成數據存??；
在MariaDB的上實現是FederatedX

MariaDB支持的其它存儲引擎：
OQGraph
SphinxSE
TokuDB
Cassandra
CONNECT
SQUENCE

MySQL中的索引：

基本法則：索引應該構建在被用作查詢條件的字段上；

索引類型：
B+ Tree索引：順序存儲，每一個葉子節點到根結點的距離是相同的；左前綴索引，適合查詢范圍類的數據；

可以使用B-Tree索引的查詢類型：
全值匹配：精確某個值, “Jinjiao King”；
匹配最左前綴：只精確匹配起頭部分，”Jin%”
匹配范圍值：
精確匹配某一列并范圍匹配另一列：
只訪問索引的查詢

不適合使用B-Tree索引的場景：
如果不從最左列開始，索引無效； (Age,Name)
不能跳過索引中的列；(StuID,Name,Age)
如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引優化查詢；(StuID,Name)

Hash索引：基于哈希表實現，特別適用于精確匹配索引中的所有列；
注意：只有Memory存儲引擎支持顯式hash索引；

適用場景：
只支持等值比較查詢，包括=, IN(), <=>; 

不適合使用hash索引的場景：
存儲的非為值的順序，因此，不適用于順序查詢；
不支持模糊匹配；

空間索引（R-Tree）：
MyISAM支持空間索引，它可以使用諸如GEOMETRY這樣的地址空間數據類型。和B-Tree索引不同，空間索引不會要求WHERE子句使用索引的最左前綴。它同時全方位的索引了數據。這樣就可以高效的使用任何數據組合進行查找，然而，必須使用MySQL GIS函數，例如MBRCONTAINS()，才能得到這個好處。

全文索引(FULLTEXT)：
FULLTEXT是MyISAM表的一種特殊索引，它從文本中找到關鍵字，而不是直接和索引中的值進行比較。

索引優點：
索引可以降低服務需要掃描的數據量，減少了IO次數；
索引可以幫助服務器避免排序和使用臨時表；
索引可以幫助將隨機I/O轉為順序I/O；

高性能索引策略：
獨立使用列，盡量避免其參與運算；
左前綴索引：索引構建于字段的左側的多少個字符，要通過索引選擇性來評估
索引選擇性：不重復的索引值和數據表的記錄總數的比值；
多列索引：
AND操作時更適合使用多列索引；
選擇合適的索引列次序：將選擇性最高放左側；

2、以實戰案例的形式總結MySQL的備份類型以及如何恢復等詳細操作；

MySQL常見的三種備份方式：

1）mysqldump+binlog，完全備份，通過備份二進制日志實現增量備份

2）lvm2快照+binlog：幾乎熱備，物理備份

3）xtrabackup: 
對InnoDB：熱備，支持完全備份和增量備份
對MyISAM引擎：溫備，只支持完全備份

環境：

源庫：192.168.0.23

目標庫：192.168.0.24

方式1：mysqldump+binlog

#+++++++++++++++++++++源庫++++++++++++++++++++++++++#
###開啟數據庫的二機制日志###
~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql_bin
~]# systemctl start mariadb.service
> show global variables like '%log_bin%'; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

###使用mysqldump工具對數據庫進行全備###
~]# mysqldump -A --single-transaction --master-data=2 >/tmp/all.sql

###對數據庫做一些操作###
> create database mydb;
> use mydb;
> create table t1 (id int,name char(10));
> insert into t1 values (1,'Tom');
> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | Tom  |
+------+------+

###假設此時數據庫發生故障，需要通過最近一次的全備和二進制日志在備庫上將數據庫恢復到故障點前的狀態###
###在備份文件確定起始pos和日志文件名###
~]# vim /tmp/all.sql
...
CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000003', MASTER_LOG_POS=245;
...

###在源庫上導出全備后生成的log日志###
~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql_bin.000003 >/tmp/incre.sql

###將全備和增備數據傳送到目標庫系統上###
~]# scp /tmp/all.sql /tmp/incre.sql root@192.168.0.24:/tmp/

#+++++++++++++++++++++目標庫++++++++++++++++++++++++++#
###在目標庫上分別恢復全備和增備###
> set sql_log_bin=0;            #臨時關閉二進制日志，為了防止導入全備時產生大量二進制日志
> source /tmp/all.sql;       #導入全備
~]# mysql </tmp/incre.sql    #導入增備

###恢復后庫和表數據都存在，恢復成功！###
> select * from mydb.t1;
+------+------+
| id   | name |
+------+------+
|    1 | Tom  |
+------+------+
1 row in set (0.00 sec)

方式2：基于lvm2的快照

#+++++++++++++++++++++源庫++++++++++++++++++++++++++#
###創建一個LV，專門用來存放MySQL的數據文件###
~]# pvcreate /dev/sdb1
~]# vgcreate myvg /dev/sdb1
~]# lvcreate -n mydata -L 2G myvg
~]# mke2fs -t ext4 /dev/myvg/mydata
~]# mount /dev/myvg/mydata /var/lib/mysql

###制作lvm2快照###
mysql> FLUSH TABLES WITH READ LOCK;  #鎖定所有表
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;   #記錄二進制日志文件及事件位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000004 |      245 |              |                  |
+------------------+----------+--------------+------------------+
~]# lvcreate -L 1G -s -n mydata_snap /dev/myvg/mydata   #創建快照
mysql> UNLOCK TABLES; 

###對數據庫做一些操作###
mysql> create database mydb;
mysql> use mydb;
mysql> create table t1 (id int,name char(20));
mysql> insert into t1 values (1,'Tom'),(2,'Jerry');
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Tom   |
|    2 | Jerry |
+------+-------+

###假設此時數據庫發生故障，需要使用lvm快照和二進制日志在備庫上將數據庫恢復到故障點前的狀態
###掛載并備份快照備份###
~]# mount /dev/myvg/mydata_snap /mnt/mydata_snap/
~]# cd /mnt/mydata_snap/
~]# tar -zcvf /tmp/mydata.tar.gz ./*

###在源庫上導出全備后生成的log日志###
~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql_bin.000004 >/tmp/incre.sql  ###將快照備份包和增量日志備份發送到目標庫###
~]# scp /tmp/mydata.tar.gz /tmp/incre.sql root@192.168.0.24:/tmp

#+++++++++++++++++++++目標庫++++++++++++++++++++++++++#
###在目標庫上進行全備和增量日志恢復###
~]# tar xf /tmp/mydata.tar.gz -C /var/lib/mysql
~]# systemctl start mariadb.service
mysql </tmp/incre.sql    #導入增量日志
###恢復后庫和表數據都存在，恢復成功！###
mysql> select * from mydb.t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Tom   |
|    2 | Jerry |
+------+-------+

方式3：xtrabackup

#+++++++++++++++++++++源庫++++++++++++++++++++++++++#
###安裝xtrabackup包###
~]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

###進行全備###
~]# mkdir /backup
~]# innobackupex --user=root /backup
~]# ls /backup/
2017-07-14_21-08-00      #生成的全備文件夾 

###對數據庫做一些操作后進行增量備份###
mysql> create database mydb; mysql> use mydb;
mysql> create table t1 (id int,name char(20));
mysql> insert into t1 values (1,'Tom'),(2,'Jerry');
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Tom   |
|    2 | Jerry |
+------+-------+
~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/2017-07-14_20-23-25           #這里要指明最近一次全備文件夾名稱
~]# ls /backup/
2017-07-14_21-08-00  2017-07-14_21-09-15
~]# scp -r /backup/* root@192.168.0.24:/backup

#+++++++++++++++++++++目標庫++++++++++++++++++++++++++#
###在目標庫上對全量和增量備份集進行準備（prepare),準備的目的是對已提交的事務進行重放，未提交的事務進行回滾。###
~]# innobackupex --apply-log --redo-only /backup/2017-07-14_21-08-00/
~]# innobackupex --apply-log --redo-only /backup/2017-07-14_21-08-00/ --incremental-dir=/backup/2017-07-14_21-09-15/

###使用合并后的全量備份集進行恢復###
~]# innobackupex --copy-back /backup/2017-07-14_21-08-00/

###修改恢復后的數據文件權限，并啟動mysql服務###
~]# chown -R mysql.mysql /var/lib/mysql/*
~]# systemctl start mariadb.service

###恢復后庫和表數據都存在，恢復成功！###
mysql> select * from mydb.t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Tom   |
|    2 | Jerry |
+------+-------+

3、MySQL的復制有哪些類型，詳細描述復制原理，并完成實際的操作。

方式1：主從模式

主從復制原理：

1) Slave服務器上執行start slave,開啟主從復制開關。
2) 此時，Slave服務器的IO線程會通過在Master上授權的復制用戶權限請求連接Master服務器，并請求從指定binlog日志文件的指定位置（日志文件名和位置就是在配置主從復制服務時執行change master命令時指定的）之后發送binlog日志內容。
3) Master服務器接收到來自Slave服務器的IO線程的請求后，Master服務器上負責復制IO線程根據Slave服務器的IO線程請求的信息讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給Slave端的IO線程。返回信息中除了binlog日志內容外，還有本次返回日志內容后在Master服務器的新的Binlog文件名稱以及在binlog中的下一個指定更新位置。
4) 當Slave服務器的IO線程獲取到來自Master服務器上IO線程發送日志內容及日志文件位置點后，將binlog日志內容依次寫入到Slave端自身的relay log（即中繼日志）文件（MySQL-relay-bin.xxxxxx）的最末端，并將新的binlog文件名和位置記錄到master-info文件中，以便下一次讀取Master端新binlog日志時能夠告訴Master服務器需要新binlog日志的哪個文件哪個位置開始請求的binlog日志內容。
5) Slave服務器端的SQL線程會實時的檢測本地RelayLog中新增加的日志內容，然后及時的把Log文件中的內容解析成在Master端曾經執行的SQL語句的內容，并在自身Slave服務器上按語句的順序執行應用這些SQL語句，應用完畢后清理應用過的日志。
6) 經過了上面的過程，就可以確保在Master端和Slave端執行了同樣的SQL語句。當復制狀態正常的情況下，Master端和Slave端的數據是完全一樣的。

配置過程：

環境：

主庫：192.168.0.23

備庫：192.168.0.24

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
###修改主庫配置文件###
~]# vim /etc/my.cnf
[mysqld]
...
server-id=1     #此處server-id必須唯一
log-bin=master-bin  #開啟binlog日志
~]# systemctl start mariadb.service
###創建復制賬號###
mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.0.%' identified by 'replpass';
mysql> flush privileges;
mysql> show master status;   #查看主庫當前的日志pos
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000003 |      497 |              |                  |
+------------------+----------+--------------+------------------+
#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
###修改備庫配置文件###
~]# vim /etc/my.cnf
[mysqld]
...
server-id=2     #此處server-id必須唯一
~]# systemctl start mariadb.service
###配置slave參數###
mysql> change master to master_host='192.168.0.23',master_user='repluser',master_password='replpass',master_log_file='mysql_bin.000003',master_log_pos=497;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.23
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 421
               Relay_Log_File: mariadb-relay-bin.000004
                Relay_Log_Pos: 616
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes      #只要Slave_IO_Running和Slave_SQL_Running都為Yes，主從復制就配置成功了
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 421
              Relay_Log_Space: 1285
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

注意：如果主節點已經運行了一段時間，且有大量數據時，應當通過備份主服務器并恢復數據至從服務器；復制起始位置為備份時，二進制日志文件及其POS；

方式2：主主模式

主主同步實現原理與主從同步相似，只不過兩個節點互為主從，相對于主從模式，主主模式具有下列特點：

(1) 都啟用binlog和relay log 
(2) 定義自動增長的id字段的增長方式
(3) 都授權有復制權限的用戶賬號
(4) 各自把對方指定為主服務器

配置過程：

環境：

主庫：192.168.0.23

備庫：192.168.0.24

#+++++++++++++++++++++主庫1++++++++++++++++++++++++++#
###修改主庫1配置文件###
~]# vim /etc/my.cnf
[mysqld]
...
server-id=1     #此處server-id必須唯一
log-bin=master-bin  #開啟binlog日志
#下面兩個參數并非必須，但可以避免自增序列發生沖突
auto_increment_offset=1     #序列初始值，此處從奇數最小值開始
auto_increment_increment=2  #每次遞增的步長

~]# systemctl start mariadb.service


###創建復制賬號###
mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.0.%' identified by 'replpass';
mysql> flush privileges;
mysql> show master status;   #查看主庫當前的日志pos
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000003 |      507 |              |                  |
+------------------+----------+--------------+------------------+

#+++++++++++++++++++++主庫2++++++++++++++++++++++++++#
###修改備庫配置文件###
~]# vim /etc/my.cnf
[mysqld]
...
server-id=1     #此處server-id必須唯一
log-bin=master-bin  #開啟binlog日志
#下面兩個參數并非必須，但可以避免自增序列發生沖突
auto_increment_offset=2     #序列初始值，此處從偶數最小值開始
auto_increment_increment=2  #每次遞增的步長

~]# systemctl start mariadb.service

###創建復制賬號###
mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.0.%' identified by 'replpass';
mysql> flush privileges;
mysql> show master status;   #查看主庫當前的日志pos
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000003 |      507 |              |                  |
+------------------+----------+--------------+------------------+

###配置slave參數###
mysql> change master to master_host='192.168.0.23',master_user='repluser',master_password='replpass',master_log_file='mysql_bin.000003',master_log_pos=507;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.23
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 507
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 507
              Relay_Log_Space: 825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

#+++++++++++++++++++++主庫1++++++++++++++++++++++++++#
###配置slave參數###
mysql> change master to master_host='192.168.0.24',master_user='repluser',master_password='replpass',master_log_file='mysql_bin.000003',master_log_pos=507;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.24
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 507
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 507
              Relay_Log_Space: 825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2

方式3：半同步模式

半同步工作方式：介于異步復制和全同步復制之間，主庫在執行完客戶端提交的事務后不是立刻返回給客戶端，而是等待至少一個從庫接收到并寫到relay log中才返回給客戶端。相對于異步復制，半同步復制提高了數據的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。所以，半同步復制最好在低延時的網絡中使用。

原理圖：

存在的問題：

客戶端事務在存儲引擎層提交后，在得到從庫確認的過程中，主庫宕機了，此時，可能的情況有兩種:
1)事務還沒發送到從庫上。此時，客戶端會收到事務提交失敗的信息，客戶端會重新提交該事務到新的主上，當宕機的主庫重新啟動后，以從庫的身份重新加入到該主從結構中，會發現，該事務在從庫中被提交了兩次，一次是之前作為主的時候，一次是被新主同步過來的。
2)事務已經發送到從庫上。此時，從庫已經收到并應用了該事務，但是客戶端仍然會收到事務提交失敗的信息，重新提交該事務到新的主上。

配置方式：

環境：

主節點：192.168.0.23

備節點：192.168.0.24

1）在主備節點上均安裝半同步插件

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+

#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';                 
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+

2）修改主備節點上的配置文件

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql_bin
server-id=1

#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql_bin
server-id=2

3）在主庫上創建復制用戶

mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.0.%' identified by 'replpass';
mysql> flush privileges;

4）查看主庫當前的binlog日志位置

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000003 |      507 |              |                  |
+------------------+----------+--------------+------------------+

5）在備庫上配置slave

mysql> change master to master_host='192.168.0.23',master_user='repluser',master_password='replpass',master_log_file='mysql_bin.000003',master_log_pos=507;

6）在主備節點上分別啟動半同步服務

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
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    |
+------------------------------------+-------+

7）從節點上啟用半同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.23
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 507
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 507
              Relay_Log_Space: 825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

方式4：基于SSL的主從復制

配置過程：

環境：

主庫：192.168.0.23

備庫：192.168.0.24

1）在主備節點分別搭建CA服務器和客戶端

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
###生成一個私鑰###
~]# (umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 4096)
Generating RSA private key, 4096 bit long modulus
.........................................................................................................................................................++
.......................++
e is 65537 (0x10001)

###生成自簽證書###
~]# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:SHANXI
Locality Name (eg, city) [Default City]:XIAN
Organization Name (eg, company) [Default Company Ltd]:DCITS
Organizational Unit Name (eg, section) []:Ops
Common Name (eg, your name or your server's hostname) []:ca.dcits.com
Email Address []:fangtao@dcits.com

###為CA提供所需的目錄及文件###
~]# touch /etc/pki/CA/{serial,index.txt}
~]# echo 01>/etc/pki/CA/serial 

#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
###生成簽署請求###
~]# mkdir /etc/mysql/ssl
~]# cd /etc/mysql/ssl
~]# (umask 077;openssl genrsa -out mysql.key 2048)
Generating RSA private key, 2048 bit long modulus
.....................................................................+++
...................+++
e is 65537 (0x10001)
~]# openssl req -new -key mysql.key -out mysql.csr -days 365
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:SHANXI
Locality Name (eg, city) [Default City]:XIAN
Organization Name (eg, company) [Default Company Ltd]:DCITS
Organizational Unit Name (eg, section) []:Ops
Common Name (eg, your name or your server's hostname) []:ca.dcits.com
Email Address []:fangtao@dcits.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

###將請求發送給主庫（CA主機）###
~]# scp mysql.csr mysql.key root@192.168.0.23:/tmp/

###

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
###在主庫（CA服務器）上簽署證書###
~]# openssl ca -in /tmp/mysql.csr -out /etc/pki/CA/certs/mysql.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Jul 19 10:05:05 2017 GMT
            Not After : Jul 19 10:05:05 2018 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = SHANXI
            organizationName          = DCITS
            organizationalUnitName    = Ops
            commonName                = ca.dcits.com
            emailAddress              = fangtao@dcits.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                A4:59:A0:16:FB:D4:C2:27:1A:09:61:FA:C4:39:B7:CE:01:60:2A:28
            X509v3 Authority Key Identifier: 
                keyid:0B:15:FD:99:9B:55:BB:52:B1:F3:37:50:42:BC:59:52:0A:13:CB:D9

Certificate is to be certified until Jul 19 10:05:05 2018 GMT (365 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

###將證書發送給備庫###
~]# scp mysql.crt cacert.pem root@192.168.0.24:/etc/mysql/ssl

###修改主庫上證書相關文件的權限###
~]# chown -R mysql.mysql /etc/pki/CA 

2）分別在主備庫上修改配置文件，啟用ssl

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql_bin
server-id=1
ssl
ssl_ca=/etc/pki/CA/cacert.pem
ssl_cert=/etc/pki/CA/certs/mysql.crt
ssl_key=/etc/pki/CA/private/mysql.key

~]# systemctl start mariadb.service
mysq> show variables like '%ssl%';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| have_openssl  | YES                           |
| have_ssl      | YES                           |
| ssl_ca        | /etc/pki/CA/cacert.pem        |
| ssl_capath    |                               |
| ssl_cert      | /etc/pki/CA/certs/mysql.crt   |
| ssl_cipher    |                               |
| ssl_key       | /etc/pki/CA/private/mysql.key |
+---------------+-------------------------------+

#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql_bin
ssl
ssl_ca=/etc/mysql/cacert.pem
ssl_cert=/etc/mysql/mysql.crt
ssl_key=/etc/mysql/mysql.key

~]# systemctl start mariadb.service
mysq> show variables like '%ssl%';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| have_openssl  | DISABLED              |
| have_ssl      | DISABLED              |
| ssl_ca        | /etc/mysql/cacert.pem |
| ssl_capath    |                       |
| ssl_cert      | /etc/mysql/mysql.crt  |
| ssl_cipher    |                       |
| ssl_key       | /etc/mysql/mysql.key  |
+---------------+-----------------------+

3）在主庫上創建復制賬號

mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.0.%' require ssl;
mysql> set password for repluser=PASSWORD('replpass');
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000004 |      484 |              |                  |
+------------------+----------+--------------+------------------+

4）在從庫上開啟同步

mysql> change master to  
master_host='192.168.0.23', 
master_user='repluser', 
master_password='replpass', 
master_log_file='mysql_bin.000004', 
master_ssl=1, 
master_ssl_ca='/etc/mysql/ssl/cacert.pem', 
master_ssl_cert='/etc/mysql/ssl/mysql.crt', 
master_ssl_key='/etc/mysql/ssl/mysql.key';

5）啟動主從同步并查看狀態

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.23
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000008
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000009
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql_bin.000008
             Slave_IO_Running: Yes        #同步狀態正常！ Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem    #證書已被應用
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql/ssl/mysql.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/ssl/mysql.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

方式5：過濾器

同步方式說明：讓從節點僅復制指定的數據庫，或指定數據庫的指定表；

有兩種實現方式：
1) 主服務器僅向二進制日志中記錄與特定數據庫（特定表）相關的事件；
問題：時間還原無法實現；不建議使用；
2) 從服務器SQL_THREAD在replay中繼日志中的事件時，僅讀取與特定數據庫（特定表）相關的事件并應用于本地；
問題：會造成網絡及磁盤IO浪費

配置過程：

環境：

主庫：192.168.0.23

備庫：192.168.0.24

1）基于數據庫過濾

#+++++++++++++++++++++主庫++++++++++++++++++++++++++#
###修改主庫配置文件###
~]# vim /etc/my.cnf
[mysqld]
...
server-id=1 
log-bin=master-bin
~]# systemctl start mariadb.service
###創建復制賬號###
mysql> grant replication slave,replication client on *.* to 'repluser'@'192.168.0.%' identified by 'replpass';
mysql> flush privileges;
mysql> show master status;   #查看主庫當前的日志pos
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000009 |      497 |              |                  |
+------------------+----------+--------------+------------------+
#+++++++++++++++++++++備庫++++++++++++++++++++++++++#
###修改備庫配置文件###
~]# vim /etc/my.cnf
[mysqld]
...
server-id=2         
replicate-do-db=mydb        #表示和哪個數據庫相關的寫入類、修改類指令會被寫入
replicate-ignore-db=testdb      #表示忽略（黑名單）
~]# systemctl start mariadb.service
###配置slave參數###
mysql> change master to master_host='192.168.0.23',master_user='repluser',master_password='replpass',master_log_file='mysql_bin.000009',master_log_pos=497;
mysql> start slave;
mysql> show slave status\G;
mysql> show global variables like 'replicate%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| replicate_annotate_row_events    | OFF       |
| replicate_do_db                  | mydb      |
| replicate_do_table               |           |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db              | testdb    |
| replicate_ignore_table           |           |
| replicate_wild_do_table          |           |
| replicate_wild_ignore_table      |           |
+----------------------------------+-----------+

2）基于表過濾

步驟與基于庫一致，只是在修改備庫參數文件時使用:

replication-do-table=TABLE_NAME

replication-ignore-table=TABLE_NAME

3）基于表的通配符

步驟與基于庫一致，只是在修改備庫參數文件時使用:

replicate-wild-do-table=PATTERN
replicate-wild-ignore-table=PATTERN