[[201845]]

新的一周，老張再次與大家見面，我們又要面臨快速的生活節奏而令人厭惡的工作!現在大多數人選擇放松自己的方式就是玩游戲，最為突出的可能就要屬手游”王者榮耀”。

據說這款游戲上到70旬老者，下至小學生都玩，老張我也玩。段位低得可憐(PS：最近剛玩哈)，剛剛白銀。

當時也想讓別人帶帶我，說你只要給多少錢，就能快速帶你從倔強青銅到最強王者，但最后我在裝逼和省錢的抉擇上，我選擇了省錢。我心想就玩一個游戲，無非你就是比我玩的時間長，有技巧，有經驗嘛，但凡我多花點時間，絕對比你玩的好。

話雖這么說，老張我也不喜歡把時間浪費在游戲上，但我喜歡抽時間寫博文，給大家多分享知識。因為我認為技術重在交流，溝通，只有互相多學習，才能進步得更快!既然玩個游戲都可以分段位，那么我們所工作于技術這個領域更是層級分明。

雖然我不能教大家怎么在游戲中提升自己，但我可以給大家分享讓自己在數據庫領域里面級別提升。做一個人人敬仰的大神，一個最強的王者!

MySQL 數據庫知識脈絡，大致可以分為四大模塊：

• MySQL 體系結構;
• MySQL 備份恢復;
• MySQL 高可用集群;
• MySQL 優化。

從四大模塊中，抽離7個部分給大家做分析

第一部分：倔強青銅篇

剛接觸 MySQL 數據庫的小白首先要了解，MySQL 常用操作命令以及 MySQL 各個版本的特點。從官方 5.1 到 MySQL 5.7，每個版本之間的跨度經歷了哪些功能和性能上面的提升。

當然在這個階段，我們也要學會如何安裝 MySQL 數據庫和一些常用命令的使用。

常用命令總結： 

create database name; 創建數據庫
 use databasename; 選擇數據庫
 drop database name; 直接刪除數據庫，不提醒
 show tables; 顯示表
 describe tablename; 表的詳細描述
 select 中加上distinct去除重復字段

顯示當前mysql版本和當前日期
 select version(),current_date;

修改mysql中root的密碼：
 shell>mysql -u root -p
 mysql> update user set password=password(“root123″) where user=’root’;

mysql> flush privileges  刷新權限
 mysql>use dbname； 打開數據庫
 mysql>show databases; 顯示所有數據庫
 mysql>show tables; 顯示數據庫mysql中所有的表
 mysql>desc user; 顯示表mysql數據庫中user表的列信息）
 grant
 創建一個可以從任何地方連接到服務器的一個超管賬戶，必須分配一個密碼
 mysql> grant all privileges on *.* to 'user_name'@'localhost' identified by  'password' ;
 格式：grant select on 數據庫.* to 用戶名@登錄主機 identified by “密碼”

刪除授權：
 mysql> revoke all privileges on *.* from root@”%”;
 mysql> delete from user where user=”root” and host=”%”;
 mysql> flush privileges;

重命名表:
 mysql > alter table t1 rename t2;

備份：
 mysqldump -hhostname -uusername -ppassword databasename > backup.sql;

恢復：
 mysql -hhostname -uusername -ppassword databasename< backup.sql; 

在這里舉兩個典型案例，MySQL 5.6 和 MySQL 5.7 在初始化數據時候的安裝差異。

MySQL 5.6：初始化數據時需要進到家目錄的 script 目錄下

執行：

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ 
 
--datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql  

此時數據庫密碼為空。

MySQL 5.7：初始化數據時需要進到家目錄的 bin 目錄下

執行：

/usr/local/mysql/bin/mysqld --user=mysql --datadir=/data/mysql 
 
--basedir=/usr/local/mysql/ --initialize  

已然已經廢棄了使用 mysql_install_db 這個命令進行初始化數據的操作了。

注：–initialize 會自動生成密碼在 error log 里面。如果加 –initialize-insecure 密碼為空

第二部分：秩序白銀篇

大概了解完 MySQL 的安裝，我們來介紹下 MySQL 的體系結構。先看下官方版本的圖：

 

從圖中我們可以看出：MySQL 體系結構分兩部分(mysql server 層 + mysql 存儲引擎層)

通過一條 sql 語句進入數據庫的過程細分，又可以由8個小部分組成如下圖：

 

1-6 都是經歷 mysql-server 層部分，7 是我們數據庫的存儲引擎層部分。因此拋出了我們要學習各個存儲引擎的區別。

這里只介紹兩種最長使用的 Innodb 和 Myisam 區別

1. 事務的支持不同(innodb支持事務，myisam不支持事務)

2. 鎖粒度(innodb行鎖應用，myisam表鎖)

3. 存儲空間(innodb既緩存索引文件又緩存數據文件，myisam只能緩存索引文件)

4. 存儲結構

(myisam：數據文件的擴展名為.MYD myData ，索引文件的擴展名是.MYI myIndex)

(innodb：所有的表都保存在同一個數據文件里面 即為.Ibd)

5. 統計記錄行數

(myisam：保存有表的總行數，select count(*) from table;會直接取出出該值)

(innodb：沒有保存表的總行數，select count(*) from table;就會遍歷整個表，消耗相當大)

第三部分：榮耀黃金篇

想學好數據庫，就要先學習體系結構。體系結構就好比房子的地基，如果地基不穩，是蓋不了高樓的。由于在 mysql server 層各個版本之間差異不大，所以我主要研究存儲引擎層部分。我們來看下 Innodb 的體系結構圖：

 

 

我們要學會把這體系結構分成主要的三大部分：內存組成 、線程工作、磁盤存儲

在內存組成里面需要學習：數據庫內存模塊由 data_buffer，index_buffer，insert buffer，redo log buffer，double writer buffer 主要內存組成。

針對 Innodb 存儲引擎的三大特性有：兩次寫，自適應哈希索引，插入緩沖;

1. double write(兩次寫)作用：可以保證頁損壞之后，有副本直接可以進行恢復。

2. adaptive hash index(自適應哈希索引)作用：Innodb 存儲引擎會監控對表上索引的查找，如果觀察到建立哈希索引可以帶來速度上的提升，則建立哈希索引。讀寫速度上也有所提高。

3. insert buffer (插入緩沖)作用：針對普通索引的插入把隨機 IO 變成順序 IO，并合并插入磁盤

——主要內存模塊–>磁盤的刷新機制：

a. binlog cache—>binlog 文件

通過參數 sync_binlog 控制

這個參數是對于 MySQL 系統來說是至關重要的，他不僅影響到 Binlog 對 MySQL 所帶來的性能損耗，而且還影響到 MySQL 中數據的完整性。對于“sync_binlog”參數的各種設置的說明如下：

● sync_binlog=0，當事務提交之后，MySQL 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中的信息到磁盤，而讓 Filesystem 自行決定什么時候來做同步，或者 cache 滿了之后才同步到磁盤。

● sync_binlog=n，當每進行 n 次事務提交之后，MySQL 將進行一次 fsync 之類的磁盤同步指令來將 binlog_cache 中的數據強制寫入磁盤。

在 MySQL 中系統默認的設置是 sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統 Crash，在 binlog_cache 中的所有 binlog 信息都會被丟失。

而當設置為“1”的時候，是最安全但是性能損耗最大的設置。因為當設置為 1 的時候，即使系統 Crash，也最多丟失 binlog_cache 中未完成的一個事務，對實際數據沒有任何實質性影響。

從以往經驗和相關測試來看，對于高并發事務的系統來說，“sync_binlog”設置為 0 和設置為 1 的系統寫入性能差距可能高達5倍甚至更多。

b. redo log buffer—>redo log

通過參數 innodb_flush_log_at_trx_commit 控制

有三個參數值：

0：log buffer 將每秒一次地寫入 log file 中，并且 log file 的 flush (刷到磁盤) 操作同時進行。該模式下在事務提交的時候，不會主動觸發寫入磁盤的操作。

1：每次事務提交時 mysql 都會把 log buffer 的數據寫入 log file，并且 flush (刷到磁盤) 中去，該模式為系統默認。

2：每次事務提交時 mysql 都會把 log buffer 的數據寫入 log file，但是 flush (刷到磁盤) 操作并不會同時進行。該模式下，MySQL 會每秒執行一次 flush (刷到磁盤) 操作

c. 臟頁 data_buffer—->數據文件

1. 通過參數 innodb_max_dirty_pages_pct 控制：它的含義代表臟頁刷新占 buffer_pool 的比例;個人建議調整為 25-50%;

2. 日志切換會產生檢查點 checkpoint，可以誘發對臟頁的刷新

——線程工作：

Innodb 四大 IO 線程：write thread，read thread，insert buffer thread，redo log thread

master thread 是數據庫的主線程，優先級別最高，里面包含 1s 和 10s 對數據庫的操作。

page cleaner thread：幫助刷新臟頁的線程，5.7 版本可以增加多個。

purge thread ：刪除無用 undo 頁。默認1個，最大可以調整到 32。

主要的數據文件也是我們需要學習：

參數文件：MySQL 5.6 版本 my.cnf 和 MySQL 5.7 版本的 my.cnf

這里給大家兩個模板：老張根據生產環境上測試而出的參數。其中根據真實內存去適當調整 innodb_buffer_pool 大小就可以了。(建議物理內存的50-80%)

[client] 
port    = 3306 
socket    = /tmp/mysql.sock 
#default-character-set=utf8 
 [mysql] 
#default-character-set=utf8 
[mysqld] 
port    = 3306 
socket    = /tmp/mysql.sock 
basedir    = /usr/local/mysql 
datadir    = /data/mysql 
open_files_limit    = 3072 
back_log = 103 
max_connections = 512 
max_connect_errors = 100000 
table_open_cache = 512 
external-locking = FALSE 
max_allowed_packet = 128M 
sort_buffer_size = 2M 
join_buffer_size = 2M 
thread_cache_size = 51 
query_cache_size = 32M 
tmp_table_size = 96M 
max_heap_table_size = 96M 
slow_query_log = 1 
slow_query_log_file = /data/mysql/slow.log 
log-error = /data/mysql/error.log 
long_query_time = 0.05 
server-id = 1323306 
log-bin = /data/mysql/mysql-bin 
sync_binlog = 1 
binlog_cache_size = 4M 
max_binlog_cache_size = 128M 
max_binlog_size = 1024M 
expire_logs_days = 7 
key_buffer_size = 32M 
read_buffer_size = 1M 
read_rnd_buffer_size = 16M 
bulk_insert_buffer_size = 64M 
character-set-server=utf8 
default-storage-engine=InnoDB 
binlog_format=row 
#gtid_mode=on 
#log_slave_updates=1 
#enforce_gtid_consistency=1 
interactive_timeout=100 
wait_timeout=100 
transaction_isolation = REPEATABLE-READ 
innodb_additional_mem_pool_size = 16M 
innodb_buffer_pool_size = 1434M 
innodb_data_file_path = ibdata1:1024M:autoextend 
innodb_flush_log_at_trx_commit = 1 
innodb_log_buffer_size = 16M 
innodb_log_file_size = 256M 
innodb_log_files_in_group = 2 
innodb_max_dirty_pages_pct = 50 
innodb_file_per_table = 1 
innodb_locks_unsafe_for_binlog = 0 
[mysqldump] 
quick 
max_allowed_packet = 32M  

MySQL 5.7 版本的參數文件：

[client] 
port    = 3306 
socket    = /data/mysql/mysql.sock 
[mysql] 
prompt="\u@db \R:\m:\s [\d]> " 
no-auto-rehash 
[mysqld] 
user    = mysql 
port    = 3306 
basedir    = /usr/local/mysql 
datadir    = /data/mysql/ 
socket    = /data/mysql/mysql.sock 
character-set-server = utf8mb4 
skip_name_resolve = 1 
open_files_limit    = 65535 
back_log = 1024 
max_connections = 500 
max_connect_errors = 1000000 
table_open_cache = 1024 
table_definition_cache = 1024 
table_open_cache_instances = 64 
thread_stack = 512K 
external-locking = FALSE 
max_allowed_packet = 32M 
sort_buffer_size = 4M 
join_buffer_size = 4M 
thread_cache_size = 768 
query_cache_size = 0 
query_cache_type = 0 
interactive_timeout = 600 
wait_timeout = 600 
tmp_table_size = 32M 
max_heap_table_size = 32M 
slow_query_log = 1 
slow_query_log_file = /data/mysql/slow.log 
log-error = /data/mysql/error.log 
long_query_time = 0.1 
server-id = 3306101 
log-bin = /data/mysql/mysql-binlog 
sync_binlog = 1 
binlog_cache_size = 4M 
max_binlog_cache_size = 1G 
max_binlog_size = 1G 
expire_logs_days = 7 
gtid_mode = on 
enforce_gtid_consistency = 1 
log_slave_updates 
binlog_format = row 
relay_log_recovery = 1 
relay-log-purge = 1 
key_buffer_size = 32M 
read_buffer_size = 8M 
read_rnd_buffer_size = 4M 
bulk_insert_buffer_size = 64M 
lock_wait_timeout = 3600 
explicit_defaults_for_timestamp = 1 
innodb_thread_concurrency = 0 
innodb_sync_spin_loops = 100 
innodb_spin_wait_delay = 30 
transaction_isolation = REPEATABLE-READ 
innodb_buffer_pool_size = 1024M 
innodb_buffer_pool_instances = 8 
innodb_buffer_pool_load_at_startup = 1 
innodb_buffer_pool_dump_at_shutdown = 1 
innodb_data_file_path = ibdata1:1G:autoextend 
innodb_flush_log_at_trx_commit = 1 
innodb_log_buffer_size = 32M 
innodb_log_file_size = 2G 
innodb_log_files_in_group = 2 
innodb_max_undo_log_size = 4G 
innodb_io_capacity = 4000 
innodb_io_capacity_max = 8000 
innodb_flush_neighbors = 0 
innodb_write_io_threads = 8 
innodb_read_io_threads = 8 
innodb_purge_threads = 4 
innodb_page_cleaners = 4 
innodb_open_files = 65535 
innodb_max_dirty_pages_pct = 50 
innodb_flush_method = O_DIRECT 
innodb_lru_scan_depth = 4000 
innodb_checksum_algorithm = crc32 
innodb_lock_wait_timeout = 10 
innodb_rollback_on_timeout = 1 
innodb_print_all_deadlocks = 1 
innodb_file_per_table = 1 
innodb_online_alter_log_max_size = 4G 
internal_tmp_disk_storage_engine = InnoDB 
innodb_stats_on_metadata = 0 
innodb_status_file = 1 
innodb_status_output = 0 
innodb_status_output_locks = 0 
performance_schema = 1 
performance_schema_instrument = '%=on' 
[mysqldump] 
quick 
max_allowed_packet = 32M  

——日志文件：

1. 錯誤日志 error log：對 mysql 啟動，運行，關閉過程進行了記錄。

2. 全量日志 general log：查詢日志記錄了所有對 mysql 數據庫請求的信息，不論這些請求是否得到了正確的執行。

3. 二進制日志 binlog：記錄了對數據庫執行更改的所有操作。但是并不包括 select 和 show 這類操作。

4. 中繼日志 relay log：主從同步，從庫需要把主庫傳遞過來的日志，記錄到自己的 relay log 里面。

5. 慢查詢日志 slow log：運行時間超過某值的所有 sql 語句都記錄到慢查詢日志文件中。

——對數據庫的表設計也要學習清楚

數據類型的選擇，主要參考官方文檔。

——數據碎片的整理

產生碎片的原因：

1. 主要是因為對大表進行刪除操作;

2. 其次隨機方式插入新數據，可能導致輔助索引產生大量的碎片;

整理碎片的方法：

1. 備份數據表，導入導出，刪除舊表

2. 執行 alter table table_name engine=innodb;

——收集統計信息

保證統計信息的準確性，才能確保我們的 sql 執行計劃準確。收集方法：

1. 重啟 mysql 服務

2. 遍歷 tables 表

——學習分區表

分區表的種類：

1. range

2. list

3. hash

4. key

——學習對索引的認識

大致分為：

1. 如何查看數據庫中索引：show index from table_name;

2. 學會查看數據庫索引的選擇性：select count(distinct c1)/count(*) from table_name; 選擇性越高，越適合創建索引

3. 創建索引的過程中，學會查看執行計劃。內功心法：先看 type 值，再看 key，再看 rows，最后看 extra;

mysql> use test;  
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Database changed 
mysql> explain select * from sbtest; 
+----+-------------+--------+------+---------------+------+---------+------+-------+-------+ 
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra | 
+----+-------------+--------+------+---------------+------+---------+------+-------+-------+ 
|  1 | SIMPLE      | sbtest | ALL  | NULL          | NULL | NULL    | NULL | 98712 | NULL  |  
+----+-------------+--------+------+---------------+------+---------+------+-------+-------+  

4. 了解創建索引的好處

a. 提高數據檢索效率

b. 提高聚合函數效率

c. 提高排序效率

d. 個別時候可以避免回表

e. 減少多表關聯時掃描行數

f. 主鍵、唯一索引可以作為約束

——對事務的學習

先要知道事務的四大特性(ACID)：

a. 原子性(Atomicity)

事務的原子性是指事務中包含的所有操作要么都做，要么都不做，保證數據庫是一致的

b. 一致性(Consistency)

一致性是指數據庫在事務操作前和事務處理后，其中的數據必須都滿足業務規則約束.

c. 隔離性(Isolation)

隔離性是數據庫允許多個并發事務同時對數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致.

d. 持久性(Durability)

事務處理結束后，對數據的修改就是永久的

熟悉 mysql 數據庫四種事務隔離級別：

1. read uncommitted(RU)讀未提交：

一個事務中，可以讀取到其他事務未提交的變更

2. read committed(RC)讀已提交：

一個事務中，可以讀取到其他事務已經提交的變更

3. repetable read,(RR)可重復讀：

一個事務中，直到事務結束前，都可以反復讀取到事務剛開始看到的數據，不會發生變化

4. serializable(串行讀)：

即便每次讀都需要獲得表級共享鎖，每次寫都加表級排它鎖，兩個會話間讀寫會相互阻塞。

個人建議：對于交易類系統的網站，大家盡量使用事務級別比較高的RR;對于一些門戶類網站大家使用RC就可以了。

Innodb 的鎖，默認三種鎖算法：

• record;
• Gap lock;
• next-key lock

默認鎖算法是 next-key lock 間隙鎖保證不會出現幻讀現象。

數據庫字符集

先學會查看數據庫的字符集：

[root@node3 ~]# mysql -uroot -proot123 
mysql> show variables like '%char%'; 
+--------------------------+----------------------------------+ 
| Variable_name            | Value                            | 
+--------------------------+----------------------------------+ 
| character_set_client     | utf8                             |  
| character_set_connection | utf8                             |  
| character_set_database   | utf8                             |  
| character_set_filesystem | binary                           |  
| character_set_results    | utf8                             |  
| character_set_server     | utf8                             |  
| character_set_system     | utf8                             |  
| character_sets_dir       | /usr/local/mysql/share/charsets/ |  
+--------------------------+----------------------------------+ 
8 rows in set (0.00 sec)  

如果想保證不會出現中文亂碼的情況發生，必須滿足以下三點：

• 連接終端必須UTF8
• 操作系統必須UTF8
• 數據庫必須UTF8

三者統一就不會出現中文亂碼的問題

——數據庫權限問題的管理

1. 權限申請流程要設置規范，合理.

2. 測試和正式環境都要嚴格控制數據庫的寫權限，禁止分配 create，alter 這樣的權限給開發人員。并且讀權限和外業務服務分離.

3. 領導需要權限時，問清目的，發郵件說明。盡量都由DBA全權管理

4. 特權賬號 all privileges 必須由DBA人員控制

5. 單庫單用戶，禁止給我一個用戶賬號管理多個庫。

6. 只讀賬號 select，可以后期配合主從架構中read_only 一起使用

7. 禁止 root 用戶作為遠程連接用戶使用

第四部分：尊貴鉑金篇

DBA 人員，如果不能保證數據的完整性，一切操作都是徒勞無功。所以備份的重要性可想而知。雖然備份不能帶來業務上的提升，還會增加我們的成本。但是沒有數據的完整性，無法保證我們線上業務的正常運行。是數據損壞時最后的一個救命稻草。

備份按方法分：冷備和熱備

冷備：數據庫關掉，影響業務。系統級別的文件 copy(PS：現在這種基本被廢棄了)

熱備：數據庫在線備份，不影響現有業務的進行。

在熱備里面又分為：

1. 邏輯備份

a. mysqldump

b. mydumper

c. mysqlpump(mysql 5.7才出現)

2. 裸文件備份

物理底層去 copy 文件，工具是 percona-xtrabackup

按內容又可以分為：全量備份、增量備份

生產中最常用的兩種方法：

1. mysqldump

2. xtrabackup

mysqldump 參數詳解： 

--single-transaction
用于保證innodb備份數據一致性，配合RR隔離級別使用；當發起事務，讀取一個快照版本，直到備份結束時，都不會讀取到本事務開始之后提交的數據；（很重要）
 
-q, --quick
加 SQL_NO_CACHE 標示符來確保不會讀取緩存里的數據-l
 
--lock-tables
發起 READ LOCAL LOCK鎖，該鎖不會阻止讀，也不會阻止新的數據插入
 
--master-data
兩個值 1和2,如果值等于1，就會添加一個CHANGE MASTER語句（后期配置搭建主從架構）
如果值等于2，就會在CHANGE MASTER語句前添加注釋（后期配置搭建主從架構）
 
-c, --complete-insert；
導出完整sql語句
 
-d，--no-data；
不導出數據，只導表結構
 
-t，--no-create-info；
只導數據，不導表結構
 
-w, --where=name ；
按條件導出想要的數據 

備份數據庫：

備份單個數據庫或單個數據庫中的指定表：
mysqldump [OPTIONS] database [tb1] [tb2]…
 
備份多個數據庫：
mysqldump [OPTIONS] –databases
[OPTIONS] DB1 [DB2 DB3...]
 
備份所有數據庫：
mysqldump [OPTIONS] –all-databases
[OPTIONS]
 
利用mysql命令恢復數據：
mysql -uroot -proot23 db_name < table_name.sql
 
xtrabackup備份原理分析：
對于Innodb，它是基于Innodb的crash recovery功能進行備份。 

數據庫崩潰恢復原理介紹：Innodb 維護了一個 redo log，它記錄著 Innodb 所有數據的真實修改信息，當數據庫重啟過程中，redo log 會應用所有已經提交的事務進行前滾，并把所有未提交的事務進行回滾，來保證宕機那一時刻的數據完整性。

XtraBackup 在備份的時候并不鎖定表，而是一頁一頁地復制 InnoDB 的數據，與此同時，XtraBackup 還有另外一個線程監視著 transactions log，一旦 log 發生變化，就把變化過的 log pages 復制走。在全部數據文件復制完成之后，停止復制 logfile。

常用命令：

首先需要創建備份目錄：/opt/data/

innobackupex --no-timestamp --defaults-file=/etc/my.cnf --user root

--socket=/tmp/mysql.sock --password root123 /opt/data/all-20170719-bak

注--no-timestamp 該參數的含義：不需要系統創建時間目錄，自己可以命名;

增備原理分析：

在完整備份和增量備份文件中都有一個文件 xtrabackup_checkpoints 會記錄備份完成時檢查點的LSN。在進行新的增量備份時，XtraBackup 會比較表空間中每頁的 LSN 是否大于上次備份完成的 LSN，如果是，則備份該頁，并記錄當前檢查點的 LSN。

7月20日的增備信息

[root@node3 all-20170720-incr]# cat xtrabackup_checkpoints  
backup_type = incremental 
from_lsn = 267719862 
to_lsn = 267720940 
last_lsn = 267720940 
compact = 0  

7月21日的增備信息

[root@node3 all-20170721-incr2]# cat xtrabackup_checkpoints 
 
backup_type = incremental 
 
from_lsn = 267720940 
 
to_lsn = 267721260 
 
last_lsn = 267721260 
 
compact = 0  

可以看出 7月20日 的結束 lsn 號(to_lsn)是 7月21日 的開始 lsn 號(from_lsn)。

增備常用命令：

7月20日的增量文件

./innobackupex --no-timestamp --user root --socket=/tmp/mysql.sock 
 
--password root123 --defaults-file=/etc/my.cnf --incremental 
 
--incremental-basedir=/opt/data/all-20170719-bak /data/xtrabackup/all-20170720-incr  

注#–incremental-basedir：用來標識當前的增備從哪里開始

7月21日的增量文件

./innobackupex --no-timestamp --user root --socket=/tmp/mysql.sock 
 
--password root123 --defaults-file=/etc/my.cnf --incremental 
 
--incremental-basedir=/data/xtrabackup/all-20170720-incr /data/xtrabackup/all-20170721-incr2  

完整備份集=全備+增備1+增備2

恢復操作：

innobackupex --user root --socket=/tmp/mysql.sock --password root123  
--defaults-file=/etc/my.cnf --apply-log --redo-only +全備 
innobackupex  --user root --socket=/tmp/mysql.sock --password root123  
 --defaults-file=/etc/my.cnf --apply-log --redo-only 全備  --incremental-dir=增備1 
innobackupex  --user root --socket=/tmp/mysql.sock --password root123  
 --defaults-file=/etc/my.cnf --apply-log --redo-only 全備  --incremental-dir=增備2 
innobackupex --user root --socket=/tmp/mysql.sock --password root123  
--defaults-file=/etc/my.cnf --apply-log  +全備  

注# –redo-only代表只進行前滾操作

# –apply-log應用日志，保證數據的完整性

第五部分：永恒鉆石篇

給大家介紹下企業中最常使用的主流 MySQL 高可用架構;

從兩方面介紹

1. 基于主從復制

a. 雙主M-M keepalived

b. MHA

2. 基于 Galera 協議;

M-M keepalived 雙主架構：

 

一般中小型公司都使用這種架構，搭建比較方便簡單;可以采用主從或者主主模式，在 master 節點發生故障后，利用 keepalived 高可用機制實現快速切換到 slave 節點。原來的從庫變成新的主庫。

但針對這個架構，個人建議以下幾點：

1. 一定要完善好切換腳本，keepalived 的切換機制要合理，避免切換不成功的現象發生。

2. 從庫的配置盡快要與主庫一致，不能太次;避免主庫宕機發生切換，新的主庫(原來的從庫)影響線上業務進行。

3. 對于延遲的問題，在這套架構中，也不能避免。可以使用 mysql 5.7 中增強半同步完成。也可以改變架構使用 PXC，完成時時同步功能，基本上沒有延遲;

4. keepalived 無法解決腦裂的問題，因此在進行服務異常判斷時，可以修改我們的判斷腳本，通過對第三方節點補充檢測來決定是否進行切換，可降低腦裂問題產生的風險。

5. 采用 keepalived 這個架構，在設置兩節點狀態時，都要設置成不搶占模式，都是 backup 狀態，通過優先級，來決定誰是主庫。避免腦裂，沖突現象發生。

6. 安裝好 mysql 需要的一些依賴包;建議配置好 yum 源，用 yum 安裝 keepalived 即可。

MHA 架構：

 

MySQL MHA 架構：可以說是企業最流行，用的最多的架構了。一些同學也經常問我相關的問題。

既然 MHA 這么火，那么它有什么優點呢?

1. 故障切換時，可以自行判斷哪個從庫與主庫的數據最接近，就切換到上面，可以減少數據的丟失，保證數據的一致性

2. 支持 binlog server，可提高 binlog 傳送效率，進一步減少數據丟失風險。

3. 可以配置 mysql 5.7 的增強半同步，來保證數據的時時同步

當然也會有一些比較棘手的缺點：

1. 自動切換的腳本太簡單了，而且比較老化，建議后期逐漸完善。

2. 搭建 MHA 架構，需要開啟 linux 系統互信協議，所以對于系統安全性來說，是個不小的考驗。

PXC 架構：

 

可以實現多個節點間的數據同步復制以及讀寫，并且可保障數據庫的服務高可用及數據一致性。

PXC 基本就屬于最完美的一套架構設計理念：

1. 主從同步，基本上無延遲;

2. 完全兼容MySQL

3. 新增節點進入到集群，部署起來很簡單。

4. 服務高可用性可以保證，并且數據一致性更加嚴格;

第六部分：最強王者篇

進入到最后一個段位，在這里知識的高樓基本已經建成，我們需要做的就是一些高級優化操作了。

可以從四個部分來考慮優化的問題：程序設計角度、系統維度、數據庫方面、硬件方向

參考老張我的博文《數據庫優化之降龍十八掌》

今兒老張把 MySQL 由淺到深地向各位老鐵們，介紹了一下。真的是希望大家可以抽出時間認真去閱讀下，我寫每篇文章都很用心，作為老師主要就是把知識和經驗傳遞給那些正在處于迷茫中，或者把大部分時間都浪費在玩游戲身上的同學們。

希望這些知識對大家有幫助，大家有什么見解，我們可以一起討論，共同進步。讓我們的生活更加充實，讓我們對技術更加熱愛!