青銅到王者，快速提升你MySQL數(shù)據(jù)庫的段位！
新的一周，老張再次與大家見面，我們又要面臨快速的生活節(jié)奏而令人厭惡的工作!現(xiàn)在大多數(shù)人選擇放松自己的方式就是玩游戲，最為突出的可能就要屬手游”王者榮耀”。
據(jù)說這款游戲上到70旬老者，下至小學生都玩，老張我也玩。段位低得可憐(PS：最近剛玩哈)，剛剛白銀。
當時也想讓別人帶帶我，說你只要給多少錢，就能快速帶你從倔強青銅到最強王者，但最后我在裝逼和省錢的抉擇上，我選擇了省錢。我心想就玩一個游戲，無非你就是比我玩的時間長，有技巧，有經(jīng)驗嘛，但凡我多花點時間，絕對比你玩的好。
話雖這么說，老張我也不喜歡把時間浪費在游戲上，但我喜歡抽時間寫博文，給大家多分享知識。因為我認為技術(shù)重在交流，溝通，只有互相多學習，才能進步得更快!既然玩?zhèn)€游戲都可以分段位，那么我們所工作于技術(shù)這個領(lǐng)域更是層級分明。
雖然我不能教大家怎么在游戲中提升自己，但我可以給大家分享讓自己在數(shù)據(jù)庫領(lǐng)域里面級別提升。做一個人人敬仰的大神，一個最強的王者!
MySQL 數(shù)據(jù)庫知識脈絡(luò)，大致可以分為四大模塊：
- MySQL 體系結(jié)構(gòu);
- MySQL 備份恢復;
- MySQL 高可用集群;
- MySQL 優(yōu)化。
從四大模塊中，抽離7個部分給大家做分析
第一部分：倔強青銅篇
剛接觸 MySQL 數(shù)據(jù)庫的小白首先要了解，MySQL 常用操作命令以及 MySQL 各個版本的特點。從官方 5.1 到 MySQL 5.7，每個版本之間的跨度經(jīng)歷了哪些功能和性能上面的提升。
當然在這個階段，我們也要學會如何安裝 MySQL 數(shù)據(jù)庫和一些常用命令的使用。
常用命令總結(jié)：
create database name; 創(chuàng)建數(shù)據(jù)庫 use databasename; 選擇數(shù)據(jù)庫 drop database name; 直接刪除數(shù)據(jù)庫，不提醒 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 刷新權(quán)限 mysql>use dbname； 打開數(shù)據(jù)庫 mysql>show databases; 顯示所有數(shù)據(jù)庫 mysql>show tables; 顯示數(shù)據(jù)庫mysql中所有的表 mysql>desc user; 顯示表mysql數(shù)據(jù)庫中user表的列信息） grant 創(chuàng)建一個可以從任何地方連接到服務器的一個超管賬戶，必須分配一個密碼 mysql> grant all privileges on *.* to 'user_name'@'localhost' identified by 'password' ; 格式：grant select on 數(shù)據(jù)庫.* to 用戶名@登錄主機 identified by “密碼” 刪除授權(quán)： 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 在初始化數(shù)據(jù)時候的安裝差異。
MySQL 5.6：初始化數(shù)據(jù)時需要進到家目錄的 script 目錄下
執(zhí)行：
- /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/
- --datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql
此時數(shù)據(jù)庫密碼為空。
MySQL 5.7：初始化數(shù)據(jù)時需要進到家目錄的 bin 目錄下
執(zhí)行：
- /usr/local/mysql/bin/mysqld --user=mysql --datadir=/data/mysql
- --basedir=/usr/local/mysql/ --initialize
已然已經(jīng)廢棄了使用 mysql_install_db 這個命令進行初始化數(shù)據(jù)的操作了。
注：–initialize 會自動生成密碼在 error log 里面。如果加 –initialize-insecure 密碼為空
第二部分：秩序白銀篇
大概了解完 MySQL 的安裝，我們來介紹下 MySQL 的體系結(jié)構(gòu)。先看下官方版本的圖：
從圖中我們可以看出：MySQL 體系結(jié)構(gòu)分兩部分(mysql server 層 + mysql 存儲引擎層)
通過一條 sql 語句進入數(shù)據(jù)庫的過程細分，又可以由8個小部分組成如下圖：
1-6 都是經(jīng)歷 mysql-server 層部分，7 是我們數(shù)據(jù)庫的存儲引擎層部分。因此拋出了我們要學習各個存儲引擎的區(qū)別。
這里只介紹兩種最長使用的 Innodb 和 Myisam 區(qū)別
1. 事務的支持不同(innodb支持事務，myisam不支持事務)
2. 鎖粒度(innodb行鎖應用，myisam表鎖)
3. 存儲空間(innodb既緩存索引文件又緩存數(shù)據(jù)文件，myisam只能緩存索引文件)
4. 存儲結(jié)構(gòu)
(myisam：數(shù)據(jù)文件的擴展名為.MYD myData ，索引文件的擴展名是.MYI myIndex)
(innodb：所有的表都保存在同一個數(shù)據(jù)文件里面 即為.Ibd)
5. 統(tǒng)計記錄行數(shù)
(myisam：保存有表的總行數(shù)，select count(*) from table;會直接取出出該值)
(innodb：沒有保存表的總行數(shù)，select count(*) from table;就會遍歷整個表，消耗相當大)
第三部分：榮耀黃金篇
想學好數(shù)據(jù)庫，就要先學習體系結(jié)構(gòu)。體系結(jié)構(gòu)就好比房子的地基，如果地基不穩(wěn)，是蓋不了高樓的。由于在 mysql server 層各個版本之間差異不大，所以我主要研究存儲引擎層部分。我們來看下 Innodb 的體系結(jié)構(gòu)圖：
我們要學會把這體系結(jié)構(gòu)分成主要的三大部分：內(nèi)存組成 、線程工作、磁盤存儲
在內(nèi)存組成里面需要學習：數(shù)據(jù)庫內(nèi)存模塊由 data_buffer，index_buffer，insert buffer，redo log buffer，double writer buffer 主要內(nèi)存組成。
針對 Innodb 存儲引擎的三大特性有：兩次寫，自適應哈希索引，插入緩沖;
1. double write(兩次寫)作用：可以保證頁損壞之后，有副本直接可以進行恢復。
2. adaptive hash index(自適應哈希索引)作用：Innodb 存儲引擎會監(jiān)控對表上索引的查找，如果觀察到建立哈希索引可以帶來速度上的提升，則建立哈希索引。讀寫速度上也有所提高。
3. insert buffer (插入緩沖)作用：針對普通索引的插入把隨機 IO 變成順序 IO，并合并插入磁盤
——主要內(nèi)存模塊–>磁盤的刷新機制：
a. binlog cache—>binlog 文件
通過參數(shù) sync_binlog 控制
這個參數(shù)是對于 MySQL 系統(tǒng)來說是至關(guān)重要的，他不僅影響到 Binlog 對 MySQL 所帶來的性能損耗，而且還影響到 MySQL 中數(shù)據(jù)的完整性。對于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：
● sync_binlog=0，當事務提交之后，MySQL 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中的信息到磁盤，而讓 Filesystem 自行決定什么時候來做同步，或者 cache 滿了之后才同步到磁盤。
● sync_binlog=n，當每進行 n 次事務提交之后，MySQL 將進行一次 fsync 之類的磁盤同步指令來將 binlog_cache 中的數(shù)據(jù)強制寫入磁盤。
在 MySQL 中系統(tǒng)默認的設(shè)置是 sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統(tǒng) Crash，在 binlog_cache 中的所有 binlog 信息都會被丟失。
而當設(shè)置為“1”的時候，是最安全但是性能損耗最大的設(shè)置。因為當設(shè)置為 1 的時候，即使系統(tǒng) Crash，也最多丟失 binlog_cache 中未完成的一個事務，對實際數(shù)據(jù)沒有任何實質(zhì)性影響。
從以往經(jīng)驗和相關(guān)測試來看，對于高并發(fā)事務的系統(tǒng)來說，“sync_binlog”設(shè)置為 0 和設(shè)置為 1 的系統(tǒng)寫入性能差距可能高達5倍甚至更多。
b. redo log buffer—>redo log
通過參數(shù) innodb_flush_log_at_trx_commit 控制
有三個參數(shù)值：
0：log buffer 將每秒一次地寫入 log file 中，并且 log file 的 flush (刷到磁盤) 操作同時進行。該模式下在事務提交的時候，不會主動觸發(fā)寫入磁盤的操作。
1：每次事務提交時 mysql 都會把 log buffer 的數(shù)據(jù)寫入 log file，并且 flush (刷到磁盤) 中去，該模式為系統(tǒng)默認。
2：每次事務提交時 mysql 都會把 log buffer 的數(shù)據(jù)寫入 log file，但是 flush (刷到磁盤) 操作并不會同時進行。該模式下，MySQL 會每秒執(zhí)行一次 flush (刷到磁盤) 操作
c. 臟頁 data_buffer—->數(shù)據(jù)文件
1. 通過參數(shù) innodb_max_dirty_pages_pct 控制：它的含義代表臟頁刷新占 buffer_pool 的比例;個人建議調(diào)整為 25-50%;
2. 日志切換會產(chǎn)生檢查點 checkpoint，可以誘發(fā)對臟頁的刷新
——線程工作：
Innodb 四大 IO 線程：write thread，read thread，insert buffer thread，redo log thread
master thread 是數(shù)據(jù)庫的主線程，優(yōu)先級別最高，里面包含 1s 和 10s 對數(shù)據(jù)庫的操作。
page cleaner thread：幫助刷新臟頁的線程，5.7 版本可以增加多個。
purge thread ：刪除無用 undo 頁。默認1個，最大可以調(diào)整到 32。
主要的數(shù)據(jù)文件也是我們需要學習：
參數(shù)文件：MySQL 5.6 版本 my.cnf 和 MySQL 5.7 版本的 my.cnf
這里給大家兩個模板：老張根據(jù)生產(chǎn)環(huán)境上測試而出的參數(shù)。其中根據(jù)真實內(nèi)存去適當調(diào)整 innodb_buffer_pool 大小就可以了。(建議物理內(nèi)存的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 版本的參數(shù)文件：
- [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 啟動，運行，關(guān)閉過程進行了記錄。
2. 全量日志 general log：查詢?nèi)罩居涗浟怂袑?mysql 數(shù)據(jù)庫請求的信息，不論這些請求是否得到了正確的執(zhí)行。
3. 二進制日志 binlog：記錄了對數(shù)據(jù)庫執(zhí)行更改的所有操作。但是并不包括 select 和 show 這類操作。
4. 中繼日志 relay log：主從同步，從庫需要把主庫傳遞過來的日志，記錄到自己的 relay log 里面。
5. 慢查詢?nèi)罩?slow log：運行時間超過某值的所有 sql 語句都記錄到慢查詢?nèi)罩疚募小?/p>
——對數(shù)據(jù)庫的表設(shè)計也要學習清楚
數(shù)據(jù)類型的選擇，主要參考官方文檔。
——數(shù)據(jù)碎片的整理
產(chǎn)生碎片的原因：
1. 主要是因為對大表進行刪除操作;
2. 其次隨機方式插入新數(shù)據(jù)，可能導致輔助索引產(chǎn)生大量的碎片;
整理碎片的方法：
1. 備份數(shù)據(jù)表，導入導出，刪除舊表
2. 執(zhí)行 alter table table_name engine=innodb;
——收集統(tǒng)計信息
保證統(tǒng)計信息的準確性，才能確保我們的 sql 執(zhí)行計劃準確。收集方法：
1. 重啟 mysql 服務
2. 遍歷 tables 表
——學習分區(qū)表
分區(qū)表的種類：
1. range
2. list
3. hash
4. key
——學習對索引的認識
大致分為：
1. 如何查看數(shù)據(jù)庫中索引：show index from table_name;
2. 學會查看數(shù)據(jù)庫索引的選擇性：select count(distinct c1)/count(*) from table_name; 選擇性越高，越適合創(chuàng)建索引
3. 創(chuàng)建索引的過程中，學會查看執(zhí)行計劃。內(nèi)功心法：先看 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. 了解創(chuàng)建索引的好處
a. 提高數(shù)據(jù)檢索效率
b. 提高聚合函數(shù)效率
c. 提高排序效率
d. 個別時候可以避免回表
e. 減少多表關(guān)聯(lián)時掃描行數(shù)
f. 主鍵、唯一索引可以作為約束
——對事務的學習
先要知道事務的四大特性(ACID)：
a. 原子性(Atomicity)
事務的原子性是指事務中包含的所有操作要么都做，要么都不做，保證數(shù)據(jù)庫是一致的
b. 一致性(Consistency)
一致性是指數(shù)據(jù)庫在事務操作前和事務處理后，其中的數(shù)據(jù)必須都滿足業(yè)務規(guī)則約束.
c. 隔離性(Isolation)
隔離性是數(shù)據(jù)庫允許多個并發(fā)事務同時對數(shù)據(jù)進行讀寫和修改的能力，隔離性可以防止多個事務并發(fā)執(zhí)行時由于交叉執(zhí)行而導致數(shù)據(jù)的不一致.
d. 持久性(Durability)
事務處理結(jié)束后，對數(shù)據(jù)的修改就是永久的
熟悉 mysql 數(shù)據(jù)庫四種事務隔離級別：
1. read uncommitted(RU)讀未提交：
一個事務中，可以讀取到其他事務未提交的變更
2. read committed(RC)讀已提交：
一個事務中，可以讀取到其他事務已經(jīng)提交的變更
3. repetable read,(RR)可重復讀：
一個事務中，直到事務結(jié)束前，都可以反復讀取到事務剛開始看到的數(shù)據(jù)，不會發(fā)生變化
4. serializable(串行讀)：
即便每次讀都需要獲得表級共享鎖，每次寫都加表級排它鎖，兩個會話間讀寫會相互阻塞。
個人建議：對于交易類系統(tǒng)的網(wǎng)站，大家盡量使用事務級別比較高的RR;對于一些門戶類網(wǎng)站大家使用RC就可以了。
Innodb 的鎖，默認三種鎖算法：
- record;
- Gap lock;
- next-key lock
默認鎖算法是 next-key lock 間隙鎖保證不會出現(xiàn)幻讀現(xiàn)象。
數(shù)據(jù)庫字符集
先學會查看數(shù)據(jù)庫的字符集：
- [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)
如果想保證不會出現(xiàn)中文亂碼的情況發(fā)生，必須滿足以下三點：
- 連接終端必須UTF8
- 操作系統(tǒng)必須UTF8
- 數(shù)據(jù)庫必須UTF8
三者統(tǒng)一就不會出現(xiàn)中文亂碼的問題
——數(shù)據(jù)庫權(quán)限問題的管理
1. 權(quán)限申請流程要設(shè)置規(guī)范，合理.
2. 測試和正式環(huán)境都要嚴格控制數(shù)據(jù)庫的寫權(quán)限，禁止分配 create，alter 這樣的權(quán)限給開發(fā)人員。并且讀權(quán)限和外業(yè)務服務分離.
3. 領(lǐng)導需要權(quán)限時，問清目的，發(fā)郵件說明。盡量都由DBA全權(quán)管理
4. 特權(quán)賬號 all privileges 必須由DBA人員控制
5. 單庫單用戶，禁止給我一個用戶賬號管理多個庫。
6. 只讀賬號 select，可以后期配合主從架構(gòu)中read_only 一起使用
7. 禁止 root 用戶作為遠程連接用戶使用
第四部分：尊貴鉑金篇
DBA 人員，如果不能保證數(shù)據(jù)的完整性，一切操作都是徒勞無功。所以備份的重要性可想而知。雖然備份不能帶來業(yè)務上的提升，還會增加我們的成本。但是沒有數(shù)據(jù)的完整性，無法保證我們線上業(yè)務的正常運行。是數(shù)據(jù)損壞時最后的一個救命稻草。
備份按方法分：冷備和熱備
冷備：數(shù)據(jù)庫關(guān)掉，影響業(yè)務。系統(tǒng)級別的文件 copy(PS：現(xiàn)在這種基本被廢棄了)
熱備：數(shù)據(jù)庫在線備份，不影響現(xiàn)有業(yè)務的進行。
在熱備里面又分為：
1. 邏輯備份
a. mysqldump
b. mydumper
c. mysqlpump(mysql 5.7才出現(xiàn))
2. 裸文件備份
物理底層去 copy 文件，工具是 percona-xtrabackup
按內(nèi)容又可以分為：全量備份、增量備份
生產(chǎn)中最常用的兩種方法：
1. mysqldump
2. xtrabackup
mysqldump 參數(shù)詳解：
--single-transaction 用于保證innodb備份數(shù)據(jù)一致性，配合RR隔離級別使用；當發(fā)起事務，讀取一個快照版本，直到備份結(jié)束時，都不會讀取到本事務開始之后提交的數(shù)據(jù)；（很重要） -q, --quick 加 SQL_NO_CACHE 標示符來確保不會讀取緩存里的數(shù)據(jù)-l --lock-tables 發(fā)起 READ LOCAL LOCK鎖，該鎖不會阻止讀，也不會阻止新的數(shù)據(jù)插入 --master-data 兩個值 1和2,如果值等于1，就會添加一個CHANGE MASTER語句（后期配置搭建主從架構(gòu)） 如果值等于2，就會在CHANGE MASTER語句前添加注釋（后期配置搭建主從架構(gòu)） -c, --complete-insert； 導出完整sql語句 -d，--no-data； 不導出數(shù)據(jù)，只導表結(jié)構(gòu) -t，--no-create-info； 只導數(shù)據(jù)，不導表結(jié)構(gòu) -w, --where=name ； 按條件導出想要的數(shù)據(jù)
備份數(shù)據(jù)庫：
備份單個數(shù)據(jù)庫或單個數(shù)據(jù)庫中的指定表： mysqldump [OPTIONS] database [tb1] [tb2]… 備份多個數(shù)據(jù)庫： mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3...] 備份所有數(shù)據(jù)庫： mysqldump [OPTIONS] –all-databases [OPTIONS] 利用mysql命令恢復數(shù)據(jù)： mysql -uroot -proot23 db_name < table_name.sql xtrabackup備份原理分析： 對于Innodb，它是基于Innodb的crash recovery功能進行備份。
數(shù)據(jù)庫崩潰恢復原理介紹：Innodb 維護了一個 redo log，它記錄著 Innodb 所有數(shù)據(jù)的真實修改信息，當數(shù)據(jù)庫重啟過程中，redo log 會應用所有已經(jīng)提交的事務進行前滾，并把所有未提交的事務進行回滾，來保證宕機那一時刻的數(shù)據(jù)完整性。
XtraBackup 在備份的時候并不鎖定表，而是一頁一頁地復制 InnoDB 的數(shù)據(jù)，與此同時，XtraBackup 還有另外一個線程監(jiān)視著 transactions log，一旦 log 發(fā)生變化，就把變化過的 log pages 復制走。在全部數(shù)據(jù)文件復制完成之后，停止復制 logfile。
常用命令：
首先需要創(chuàng)建備份目錄：/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 該參數(shù)的含義：不需要系統(tǒng)創(chuàng)建時間目錄，自己可以命名;
增備原理分析：
在完整備份和增量備份文件中都有一個文件 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日 的結(jié)束 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應用日志，保證數(shù)據(jù)的完整性
第五部分：永恒鉆石篇
給大家介紹下企業(yè)中最常使用的主流 MySQL 高可用架構(gòu);
從兩方面介紹
1. 基于主從復制
a. 雙主M-M keepalived
b. MHA
2. 基于 Galera 協(xié)議;
M-M keepalived 雙主架構(gòu)：
一般中小型公司都使用這種架構(gòu)，搭建比較方便簡單;可以采用主從或者主主模式，在 master 節(jié)點發(fā)生故障后，利用 keepalived 高可用機制實現(xiàn)快速切換到 slave 節(jié)點。原來的從庫變成新的主庫。
但針對這個架構(gòu)，個人建議以下幾點：
1. 一定要完善好切換腳本，keepalived 的切換機制要合理，避免切換不成功的現(xiàn)象發(fā)生。
2. 從庫的配置盡快要與主庫一致，不能太次;避免主庫宕機發(fā)生切換，新的主庫(原來的從庫)影響線上業(yè)務進行。
3. 對于延遲的問題，在這套架構(gòu)中，也不能避免?？梢允褂?mysql 5.7 中增強半同步完成。也可以改變架構(gòu)使用 PXC，完成時時同步功能，基本上沒有延遲;
4. keepalived 無法解決腦裂的問題，因此在進行服務異常判斷時，可以修改我們的判斷腳本，通過對第三方節(jié)點補充檢測來決定是否進行切換，可降低腦裂問題產(chǎn)生的風險。
5. 采用 keepalived 這個架構(gòu)，在設(shè)置兩節(jié)點狀態(tài)時，都要設(shè)置成不搶占模式，都是 backup 狀態(tài)，通過優(yōu)先級，來決定誰是主庫。避免腦裂，沖突現(xiàn)象發(fā)生。
6. 安裝好 mysql 需要的一些依賴包;建議配置好 yum 源，用 yum 安裝 keepalived 即可。
MHA 架構(gòu)：
MySQL MHA 架構(gòu)：可以說是企業(yè)最流行，用的最多的架構(gòu)了。一些同學也經(jīng)常問我相關(guān)的問題。
既然 MHA 這么火，那么它有什么優(yōu)點呢?
1. 故障切換時，可以自行判斷哪個從庫與主庫的數(shù)據(jù)最接近，就切換到上面，可以減少數(shù)據(jù)的丟失，保證數(shù)據(jù)的一致性
2. 支持 binlog server，可提高 binlog 傳送效率，進一步減少數(shù)據(jù)丟失風險。
3. 可以配置 mysql 5.7 的增強半同步，來保證數(shù)據(jù)的時時同步
當然也會有一些比較棘手的缺點：
1. 自動切換的腳本太簡單了，而且比較老化，建議后期逐漸完善。
2. 搭建 MHA 架構(gòu)，需要開啟 linux 系統(tǒng)互信協(xié)議，所以對于系統(tǒng)安全性來說，是個不小的考驗。
PXC 架構(gòu)：
可以實現(xiàn)多個節(jié)點間的數(shù)據(jù)同步復制以及讀寫，并且可保障數(shù)據(jù)庫的服務高可用及數(shù)據(jù)一致性。
PXC 基本就屬于最完美的一套架構(gòu)設(shè)計理念：
1. 主從同步，基本上無延遲;
2. 完全兼容MySQL
3. 新增節(jié)點進入到集群，部署起來很簡單。
4. 服務高可用性可以保證，并且數(shù)據(jù)一致性更加嚴格;
第六部分：最強王者篇
進入到最后一個段位，在這里知識的高樓基本已經(jīng)建成，我們需要做的就是一些高級優(yōu)化操作了。
可以從四個部分來考慮優(yōu)化的問題：程序設(shè)計角度、系統(tǒng)維度、數(shù)據(jù)庫方面、硬件方向
參考老張我的博文《數(shù)據(jù)庫優(yōu)化之降龍十八掌》
今兒老張把 MySQL 由淺到深地向各位老鐵們，介紹了一下。真的是希望大家可以抽出時間認真去閱讀下，我寫每篇文章都很用心，作為老師主要就是把知識和經(jīng)驗傳遞給那些正在處于迷茫中，或者把大部分時間都浪費在玩游戲身上的同學們。
希望這些知識對大家有幫助，大家有什么見解，我們可以一起討論，共同進步。讓我們的生活更加充實，讓我們對技術(shù)更加熱愛!
