[[201845]]

新的一周，老張?jiān)俅闻c大家見面，我們又要面臨快速的生活節(jié)奏而令人厭惡的工作!現(xiàn)在大多數(shù)人選擇放松自己的方式就是玩游戲，最為突出的可能就要屬手游”王者榮耀”。

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

當(dāng)時(shí)也想讓別人帶帶我，說你只要給多少錢，就能快速帶你從倔強(qiáng)青銅到最強(qiáng)王者，但最后我在裝逼和省錢的抉擇上，我選擇了省錢。我心想就玩一個(gè)游戲，無非你就是比我玩的時(shí)間長(zhǎng)，有技巧，有經(jīng)驗(yàn)嘛，但凡我多花點(diǎn)時(shí)間，絕對(duì)比你玩的好。

話雖這么說，老張我也不喜歡把時(shí)間浪費(fèi)在游戲上，但我喜歡抽時(shí)間寫博文，給大家多分享知識(shí)。因?yàn)槲艺J(rèn)為技術(shù)重在交流，溝通，只有互相多學(xué)習(xí)，才能進(jìn)步得更快!既然玩?zhèn)€游戲都可以分段位，那么我們所工作于技術(shù)這個(gè)領(lǐng)域更是層級(jí)分明。

雖然我不能教大家怎么在游戲中提升自己，但我可以給大家分享讓自己在數(shù)據(jù)庫(kù)領(lǐng)域里面級(jí)別提升。做一個(gè)人人敬仰的大神，一個(gè)最強(qiáng)的王者!

MySQL 數(shù)據(jù)庫(kù)知識(shí)脈絡(luò)，大致可以分為四大模塊：

• MySQL 體系結(jié)構(gòu);
• MySQL 備份恢復(fù);
• MySQL 高可用集群;
• MySQL 優(yōu)化。

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

第一部分：倔強(qiáng)青銅篇

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

當(dāng)然在這個(gè)階段，我們也要學(xué)會(huì)如何安裝 MySQL 數(shù)據(jù)庫(kù)和一些常用命令的使用。

常用命令總結(jié)： 

create database name; 創(chuàng)建數(shù)據(jù)庫(kù)
 use databasename; 選擇數(shù)據(jù)庫(kù)
 drop database name; 直接刪除數(shù)據(jù)庫(kù)，不提醒
 show tables; 顯示表
 describe tablename; 表的詳細(xì)描述
 select 中加上distinct去除重復(fù)字段

顯示當(dāng)前mysql版本和當(dāng)前日期
 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ù)庫(kù)
 mysql>show databases; 顯示所有數(shù)據(jù)庫(kù)
 mysql>show tables; 顯示數(shù)據(jù)庫(kù)mysql中所有的表
 mysql>desc user; 顯示表mysql數(shù)據(jù)庫(kù)中user表的列信息）
 grant
 創(chuàng)建一個(gè)可以從任何地方連接到服務(wù)器的一個(gè)超管賬戶，必須分配一個(gè)密碼
 mysql> grant all privileges on *.* to 'user_name'@'localhost' identified by  'password' ;
 格式：grant select on 數(shù)據(jù)庫(kù).* to 用戶名@登錄主機(jī) 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;

恢復(fù)：
 mysql -hhostname -uusername -ppassword databasename< backup.sql; 

在這里舉兩個(gè)典型案例，MySQL 5.6 和 MySQL 5.7 在初始化數(shù)據(jù)時(shí)候的安裝差異。

MySQL 5.6：初始化數(shù)據(jù)時(shí)需要進(jìn)到家目錄的 script 目錄下

執(zhí)行：

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

此時(shí)數(shù)據(jù)庫(kù)密碼為空。

MySQL 5.7：初始化數(shù)據(jù)時(shí)需要進(jìn)到家目錄的 bin 目錄下

執(zhí)行：

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

已然已經(jīng)廢棄了使用 mysql_install_db 這個(gè)命令進(jìn)行初始化數(shù)據(jù)的操作了。

注：–initialize 會(huì)自動(dòng)生成密碼在 error log 里面。如果加 –initialize-insecure 密碼為空

第二部分：秩序白銀篇

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

 

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

通過一條 sql 語(yǔ)句進(jìn)入數(shù)據(jù)庫(kù)的過程細(xì)分，又可以由8個(gè)小部分組成如下圖：

 

1-6 都是經(jīng)歷 mysql-server 層部分，7 是我們數(shù)據(jù)庫(kù)的存儲(chǔ)引擎層部分。因此拋出了我們要學(xué)習(xí)各個(gè)存儲(chǔ)引擎的區(qū)別。

這里只介紹兩種最長(zhǎng)使用的 Innodb 和 Myisam 區(qū)別

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

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

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

4. 存儲(chǔ)結(jié)構(gòu)

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

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

5. 統(tǒng)計(jì)記錄行數(shù)

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

(innodb：沒有保存表的總行數(shù)，select count(*) from table;就會(huì)遍歷整個(gè)表，消耗相當(dāng)大)

第三部分：榮耀黃金篇

想學(xué)好數(shù)據(jù)庫(kù)，就要先學(xué)習(xí)體系結(jié)構(gòu)。體系結(jié)構(gòu)就好比房子的地基，如果地基不穩(wěn)，是蓋不了高樓的。由于在 mysql server 層各個(gè)版本之間差異不大，所以我主要研究存儲(chǔ)引擎層部分。我們來看下 Innodb 的體系結(jié)構(gòu)圖：

 

 

我們要學(xué)會(huì)把這體系結(jié)構(gòu)分成主要的三大部分：內(nèi)存組成 、線程工作、磁盤存儲(chǔ)

在內(nèi)存組成里面需要學(xué)習(xí)：數(shù)據(jù)庫(kù)內(nèi)存模塊由 data_buffer，index_buffer，insert buffer，redo log buffer，double writer buffer 主要內(nèi)存組成。

針對(duì) Innodb 存儲(chǔ)引擎的三大特性有：兩次寫，自適應(yīng)哈希索引，插入緩沖;

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

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

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

——主要內(nèi)存模塊–>磁盤的刷新機(jī)制：

a. binlog cache—>binlog 文件

通過參數(shù) sync_binlog 控制

這個(gè)參數(shù)是對(duì)于 MySQL 系統(tǒng)來說是至關(guān)重要的，他不僅影響到 Binlog 對(duì) MySQL 所帶來的性能損耗，而且還影響到 MySQL 中數(shù)據(jù)的完整性。對(duì)于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：

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

● sync_binlog=n，當(dāng)每進(jìn)行 n 次事務(wù)提交之后，MySQL 將進(jìn)行一次 fsync 之類的磁盤同步指令來將 binlog_cache 中的數(shù)據(jù)強(qiáng)制寫入磁盤。

在 MySQL 中系統(tǒng)默認(rèn)的設(shè)置是 sync_binlog=0，也就是不做任何強(qiáng)制性的磁盤刷新指令，這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是最大的。因?yàn)橐坏┫到y(tǒng) Crash，在 binlog_cache 中的所有 binlog 信息都會(huì)被丟失。

而當(dāng)設(shè)置為“1”的時(shí)候，是最安全但是性能損耗最大的設(shè)置。因?yàn)楫?dāng)設(shè)置為 1 的時(shí)候，即使系統(tǒng) Crash，也最多丟失 binlog_cache 中未完成的一個(gè)事務(wù)，對(duì)實(shí)際數(shù)據(jù)沒有任何實(shí)質(zhì)性影響。

從以往經(jīng)驗(yàn)和相關(guān)測(cè)試來看，對(duì)于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為 0 和設(shè)置為 1 的系統(tǒng)寫入性能差距可能高達(dá)5倍甚至更多。

b. redo log buffer—>redo log

通過參數(shù) innodb_flush_log_at_trx_commit 控制

有三個(gè)參數(shù)值：

0：log buffer 將每秒一次地寫入 log file 中，并且 log file 的 flush (刷到磁盤) 操作同時(shí)進(jìn)行。該模式下在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫入磁盤的操作。

1：每次事務(wù)提交時(shí) mysql 都會(huì)把 log buffer 的數(shù)據(jù)寫入 log file，并且 flush (刷到磁盤) 中去，該模式為系統(tǒng)默認(rèn)。

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

c. 臟頁(yè) data_buffer—->數(shù)據(jù)文件

1. 通過參數(shù) innodb_max_dirty_pages_pct 控制：它的含義代表臟頁(yè)刷新占 buffer_pool 的比例;個(gè)人建議調(diào)整為 25-50%;

2. 日志切換會(huì)產(chǎn)生檢查點(diǎn) checkpoint，可以誘發(fā)對(duì)臟頁(yè)的刷新

——線程工作：

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

master thread 是數(shù)據(jù)庫(kù)的主線程，優(yōu)先級(jí)別最高，里面包含 1s 和 10s 對(duì)數(shù)據(jù)庫(kù)的操作。

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

purge thread ：刪除無用 undo 頁(yè)。默認(rèn)1個(gè)，最大可以調(diào)整到 32。

主要的數(shù)據(jù)文件也是我們需要學(xué)習(xí)：

參數(shù)文件：MySQL 5.6 版本 my.cnf 和 MySQL 5.7 版本的 my.cnf

這里給大家兩個(gè)模板：老張根據(jù)生產(chǎn)環(huán)境上測(cè)試而出的參數(shù)。其中根據(jù)真實(shí)內(nèi)存去適當(dāng)調(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. 錯(cuò)誤日志 error log：對(duì) mysql 啟動(dòng)，運(yùn)行，關(guān)閉過程進(jìn)行了記錄。

2. 全量日志 general log：查詢?nèi)罩居涗浟怂袑?duì) mysql 數(shù)據(jù)庫(kù)請(qǐng)求的信息，不論這些請(qǐng)求是否得到了正確的執(zhí)行。

3. 二進(jìn)制日志 binlog：記錄了對(duì)數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作。但是并不包括 select 和 show 這類操作。

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

5. 慢查詢?nèi)罩?slow log：運(yùn)行時(shí)間超過某值的所有 sql 語(yǔ)句都記錄到慢查詢?nèi)罩疚募小?/p>

——對(duì)數(shù)據(jù)庫(kù)的表設(shè)計(jì)也要學(xué)習(xí)清楚

數(shù)據(jù)類型的選擇，主要參考官方文檔。

——數(shù)據(jù)碎片的整理

產(chǎn)生碎片的原因：

1. 主要是因?yàn)閷?duì)大表進(jìn)行刪除操作;

2. 其次隨機(jī)方式插入新數(shù)據(jù)，可能導(dǎo)致輔助索引產(chǎn)生大量的碎片;

整理碎片的方法：

1. 備份數(shù)據(jù)表，導(dǎo)入導(dǎo)出，刪除舊表

2. 執(zhí)行 alter table table_name engine=innodb;

——收集統(tǒng)計(jì)信息

保證統(tǒng)計(jì)信息的準(zhǔn)確性，才能確保我們的 sql 執(zhí)行計(jì)劃準(zhǔn)確。收集方法：

1. 重啟 mysql 服務(wù)

2. 遍歷 tables 表

——學(xué)習(xí)分區(qū)表

分區(qū)表的種類：

1. range

2. list

3. hash

4. key

——學(xué)習(xí)對(duì)索引的認(rèn)識(shí)

大致分為：

1. 如何查看數(shù)據(jù)庫(kù)中索引：show index from table_name;

2. 學(xué)會(huì)查看數(shù)據(jù)庫(kù)索引的選擇性：select count(distinct c1)/count(*) from table_name; 選擇性越高，越適合創(chuàng)建索引

3. 創(chuàng)建索引的過程中，學(xué)會(huì)查看執(zhí)行計(jì)劃。內(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. 個(gè)別時(shí)候可以避免回表

e. 減少多表關(guān)聯(lián)時(shí)掃描行數(shù)

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

——對(duì)事務(wù)的學(xué)習(xí)

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

a. 原子性(Atomicity)

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

b. 一致性(Consistency)

一致性是指數(shù)據(jù)庫(kù)在事務(wù)操作前和事務(wù)處理后，其中的數(shù)據(jù)必須都滿足業(yè)務(wù)規(guī)則約束.

c. 隔離性(Isolation)

隔離性是數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致.

d. 持久性(Durability)

事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的

熟悉 mysql 數(shù)據(jù)庫(kù)四種事務(wù)隔離級(jí)別：

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

一個(gè)事務(wù)中，可以讀取到其他事務(wù)未提交的變更

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

一個(gè)事務(wù)中，可以讀取到其他事務(wù)已經(jīng)提交的變更

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

一個(gè)事務(wù)中，直到事務(wù)結(jié)束前，都可以反復(fù)讀取到事務(wù)剛開始看到的數(shù)據(jù)，不會(huì)發(fā)生變化

4. serializable(串行讀)：

即便每次讀都需要獲得表級(jí)共享鎖，每次寫都加表級(jí)排它鎖，兩個(gè)會(huì)話間讀寫會(huì)相互阻塞。

個(gè)人建議：對(duì)于交易類系統(tǒng)的網(wǎng)站，大家盡量使用事務(wù)級(jí)別比較高的RR;對(duì)于一些門戶類網(wǎng)站大家使用RC就可以了。

Innodb 的鎖，默認(rèn)三種鎖算法：

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

默認(rèn)鎖算法是 next-key lock 間隙鎖保證不會(huì)出現(xiàn)幻讀現(xiàn)象。

數(shù)據(jù)庫(kù)字符集

先學(xué)會(huì)查看數(shù)據(jù)庫(kù)的字符集：

[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)  

如果想保證不會(huì)出現(xiàn)中文亂碼的情況發(fā)生，必須滿足以下三點(diǎn)：

• 連接終端必須UTF8
• 操作系統(tǒng)必須UTF8
• 數(shù)據(jù)庫(kù)必須UTF8

三者統(tǒng)一就不會(huì)出現(xiàn)中文亂碼的問題

——數(shù)據(jù)庫(kù)權(quán)限問題的管理

1. 權(quán)限申請(qǐng)流程要設(shè)置規(guī)范，合理.

2. 測(cè)試和正式環(huán)境都要嚴(yán)格控制數(shù)據(jù)庫(kù)的寫權(quán)限，禁止分配 create，alter 這樣的權(quán)限給開發(fā)人員。并且讀權(quán)限和外業(yè)務(wù)服務(wù)分離.

3. 領(lǐng)導(dǎo)需要權(quán)限時(shí)，問清目的，發(fā)郵件說明。盡量都由DBA全權(quán)管理

4. 特權(quán)賬號(hào) all privileges 必須由DBA人員控制

5. 單庫(kù)單用戶，禁止給我一個(gè)用戶賬號(hào)管理多個(gè)庫(kù)。

6. 只讀賬號(hào) select，可以后期配合主從架構(gòu)中read_only 一起使用

7. 禁止 root 用戶作為遠(yuǎn)程連接用戶使用

第四部分：尊貴鉑金篇

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

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

冷備：數(shù)據(jù)庫(kù)關(guān)掉，影響業(yè)務(wù)。系統(tǒng)級(jí)別的文件 copy(PS：現(xiàn)在這種基本被廢棄了)

熱備：數(shù)據(jù)庫(kù)在線備份，不影響現(xiàn)有業(yè)務(wù)的進(jìn)行。

在熱備里面又分為：

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隔離級(jí)別使用；當(dāng)發(fā)起事務(wù)，讀取一個(gè)快照版本，直到備份結(jié)束時(shí)，都不會(huì)讀取到本事務(wù)開始之后提交的數(shù)據(jù)；（很重要）
 
-q, --quick
加 SQL_NO_CACHE 標(biāo)示符來確保不會(huì)讀取緩存里的數(shù)據(jù)-l
 
--lock-tables
發(fā)起 READ LOCAL LOCK鎖，該鎖不會(huì)阻止讀，也不會(huì)阻止新的數(shù)據(jù)插入
 
--master-data
兩個(gè)值 1和2,如果值等于1，就會(huì)添加一個(gè)CHANGE MASTER語(yǔ)句（后期配置搭建主從架構(gòu)）
如果值等于2，就會(huì)在CHANGE MASTER語(yǔ)句前添加注釋（后期配置搭建主從架構(gòu)）
 
-c, --complete-insert；
導(dǎo)出完整sql語(yǔ)句
 
-d，--no-data；
不導(dǎo)出數(shù)據(jù)，只導(dǎo)表結(jié)構(gòu)
 
-t，--no-create-info；
只導(dǎo)數(shù)據(jù)，不導(dǎo)表結(jié)構(gòu)
 
-w, --where=name ；
按條件導(dǎo)出想要的數(shù)據(jù) 

備份數(shù)據(jù)庫(kù)：

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

數(shù)據(jù)庫(kù)崩潰恢復(fù)原理介紹：Innodb 維護(hù)了一個(gè) redo log，它記錄著 Innodb 所有數(shù)據(jù)的真實(shí)修改信息，當(dāng)數(shù)據(jù)庫(kù)重啟過程中，redo log 會(huì)應(yīng)用所有已經(jīng)提交的事務(wù)進(jìn)行前滾，并把所有未提交的事務(wù)進(jìn)行回滾，來保證宕機(jī)那一時(shí)刻的數(shù)據(jù)完整性。

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

常用命令：

首先需要?jiǎng)?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)建時(shí)間目錄，自己可以命名;

增備原理分析：

在完整備份和增量備份文件中都有一個(gè)文件 xtrabackup_checkpoints 會(huì)記錄備份完成時(shí)檢查點(diǎn)的LSN。在進(jìn)行新的增量備份時(shí)，XtraBackup 會(huì)比較表空間中每頁(yè)的 LSN 是否大于上次備份完成的 LSN，如果是，則備份該頁(yè)，并記錄當(dāng)前檢查點(diǎn)的 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 號(hào)(to_lsn)是 7月21日 的開始 lsn 號(hào)(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：用來標(biāo)識(shí)當(dāng)前的增備從哪里開始

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

恢復(fù)操作：

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代表只進(jìn)行前滾操作

# –apply-log應(yīng)用日志，保證數(shù)據(jù)的完整性

第五部分：永恒鉆石篇

給大家介紹下企業(yè)中最常使用的主流 MySQL 高可用架構(gòu);

從兩方面介紹

1. 基于主從復(fù)制

a. 雙主M-M keepalived

b. MHA

2. 基于 Galera 協(xié)議;

M-M keepalived 雙主架構(gòu)：

 

一般中小型公司都使用這種架構(gòu)，搭建比較方便簡(jiǎn)單;可以采用主從或者主主模式，在 master 節(jié)點(diǎn)發(fā)生故障后，利用 keepalived 高可用機(jī)制實(shí)現(xiàn)快速切換到 slave 節(jié)點(diǎn)。原來的從庫(kù)變成新的主庫(kù)。

但針對(duì)這個(gè)架構(gòu)，個(gè)人建議以下幾點(diǎn)：

1. 一定要完善好切換腳本，keepalived 的切換機(jī)制要合理，避免切換不成功的現(xiàn)象發(fā)生。

2. 從庫(kù)的配置盡快要與主庫(kù)一致，不能太次;避免主庫(kù)宕機(jī)發(fā)生切換，新的主庫(kù)(原來的從庫(kù))影響線上業(yè)務(wù)進(jìn)行。

3. 對(duì)于延遲的問題，在這套架構(gòu)中，也不能避免?？梢允褂?mysql 5.7 中增強(qiáng)半同步完成。也可以改變架構(gòu)使用 PXC，完成時(shí)時(shí)同步功能，基本上沒有延遲;

4. keepalived 無法解決腦裂的問題，因此在進(jìn)行服務(wù)異常判斷時(shí)，可以修改我們的判斷腳本，通過對(duì)第三方節(jié)點(diǎn)補(bǔ)充檢測(cè)來決定是否進(jìn)行切換，可降低腦裂問題產(chǎn)生的風(fēng)險(xiǎn)。

5. 采用 keepalived 這個(gè)架構(gòu)，在設(shè)置兩節(jié)點(diǎn)狀態(tài)時(shí)，都要設(shè)置成不搶占模式，都是 backup 狀態(tài)，通過優(yōu)先級(jí)，來決定誰(shuí)是主庫(kù)。避免腦裂，沖突現(xiàn)象發(fā)生。

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

MHA 架構(gòu)：

 

MySQL MHA 架構(gòu)：可以說是企業(yè)最流行，用的最多的架構(gòu)了。一些同學(xué)也經(jīng)常問我相關(guān)的問題。

既然 MHA 這么火，那么它有什么優(yōu)點(diǎn)呢?

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

2. 支持 binlog server，可提高 binlog 傳送效率，進(jìn)一步減少數(shù)據(jù)丟失風(fēng)險(xiǎn)。

3. 可以配置 mysql 5.7 的增強(qiáng)半同步，來保證數(shù)據(jù)的時(shí)時(shí)同步

當(dāng)然也會(huì)有一些比較棘手的缺點(diǎn)：

1. 自動(dòng)切換的腳本太簡(jiǎn)單了，而且比較老化，建議后期逐漸完善。

2. 搭建 MHA 架構(gòu)，需要開啟 linux 系統(tǒng)互信協(xié)議，所以對(duì)于系統(tǒng)安全性來說，是個(gè)不小的考驗(yàn)。

PXC 架構(gòu)：

 

可以實(shí)現(xiàn)多個(gè)節(jié)點(diǎn)間的數(shù)據(jù)同步復(fù)制以及讀寫，并且可保障數(shù)據(jù)庫(kù)的服務(wù)高可用及數(shù)據(jù)一致性。

PXC 基本就屬于最完美的一套架構(gòu)設(shè)計(jì)理念：

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

2. 完全兼容MySQL

3. 新增節(jié)點(diǎn)進(jìn)入到集群，部署起來很簡(jiǎn)單。

4. 服務(wù)高可用性可以保證，并且數(shù)據(jù)一致性更加嚴(yán)格;

第六部分：最強(qiáng)王者篇

進(jìn)入到最后一個(gè)段位，在這里知識(shí)的高樓基本已經(jīng)建成，我們需要做的就是一些高級(jí)優(yōu)化操作了。

可以從四個(gè)部分來考慮優(yōu)化的問題：程序設(shè)計(jì)角度、系統(tǒng)維度、數(shù)據(jù)庫(kù)方面、硬件方向

參考老張我的博文《數(shù)據(jù)庫(kù)優(yōu)化之降龍十八掌》

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

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