真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

青銅到王者,快速提升你MySQL數(shù)據(jù)庫的段位!

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

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

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

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

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

成都創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括呼和浩特網(wǎng)站建設(shè)、呼和浩特網(wǎng)站制作、呼和浩特網(wǎng)頁制作以及呼和浩特網(wǎng)絡(luò)營銷策劃等。多年來,我們專注于互聯(lián)網(wǎng)行業(yè),利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等,向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案,呼和浩特網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前,我們服務(wù)的客戶以成都為中心已經(jīng)輻射到呼和浩特省份的部分城市,未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任!

獨(dú)家新課程上線>>MySQL體系結(jié)構(gòu)深入剖析及實(shí)戰(zhàn)DBA視頻課程



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

● MySQL 體系結(jié)構(gòu);

● MySQL 備份恢復(fù);

● MySQL 高可用集群;

● MySQL 優(yōu)化。

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


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

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

新特性參考博文>>http://suMongoDB.blog.51cto.com/4979448/1949800

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


常用命令總結(jié):

create database name; 創(chuàng)建數(shù)據(jù)庫
use databasename; 選擇數(shù)據(jù)庫
drop database name; 直接刪除數(shù)據(jù)庫,不提醒
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ù)庫
mysql>show databases; 顯示所有數(shù)據(jù)庫
mysql>show tables; 顯示數(shù)據(jù)庫mysql中所有的表
mysql>desc user; 顯示表mysql數(shù)據(jù)庫中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ù)庫.* 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.6MySQL 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ù)庫密碼為空。

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 數(shù)據(jù)庫的段位!

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

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

青銅到王者,快速提升你 MySQL 數(shù)據(jù)庫的段位!

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

這里只介紹兩種最長使用的 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ù)庫,就要先學(xué)習(xí)體系結(jié)構(gòu)。體系結(jié)構(gòu)就好比房子的地基,如果地基不穩(wěn),是蓋不了高樓的。由于在 mysql server 層各個(gè)版本之間差異不大,所以我主要研究存儲(chǔ)引擎層部分。我們來看下 Innodb 的體系結(jié)構(gòu)圖:

青銅到王者,快速提升你 MySQL 數(shù)據(jù)庫的段位!

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

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

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

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

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

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

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

a. binlog cache--->binlog 文件

通過參數(shù) sync_binlog 控制

這個(gè)參數(shù)是對于 MySQL 系統(tǒng)來說是至關(guān)重要的,他不僅影響到 Binlog 對 MySQL 所帶來的性能損耗,而且還影響到 MySQL 中數(shù)據(jù)的完整性。對于“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ù),對實(shí)際數(shù)據(jù)沒有任何實(shí)質(zhì)性影響。

從以往經(jīng)驗(yàn)和相關(guān)測試來看,對于高并發(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. 臟頁 data_buffer---->數(shù)據(jù)文件

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

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

——線程工作:

Innodb 四大 IO 線程:write thread,read thread,insert buffer thread,redo log thread

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

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

purge thread :刪除無用 undo 頁。默認(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)境上測試而出的參數(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:對 mysql 啟動(dòng),運(yùn)行,關(guān)閉過程進(jìn)行了記錄。

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

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

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

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

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

數(shù)據(jù)類型的選擇,主要參考官方文檔:
https://downloads.mysql.com/docs/licenses/mysqld-5.7-com-en.pdf

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

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

1. 主要是因?yàn)閷Υ蟊磉M(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í)對索引的認(rèn)識(shí)

大致分為:

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

2. 學(xué)會(huì)查看數(shù)據(jù)庫索引的選擇性: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.主鍵、唯一索引可以作為約束

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

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

a. 原子性(Atomicity)

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

b. 一致性(Consistency)

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

c. 隔離性(Isolation)

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

d. 持久性(Durability)

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

熟悉 mysql 數(shù)據(jù)庫四種事務(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è)人建議:對于交易類系統(tǒng)的網(wǎng)站,大家盡量使用事務(wù)級(jí)別比較高的RR;對于一些門戶類網(wǎng)站大家使用RC就可以了。

Innodb 的鎖,默認(rèn)三種鎖算法:
1. record;
2. Gap lock;
3. next-key lock
默認(rèn)鎖算法是 next-key lock 間隙鎖保證不會(huì)出現(xiàn)幻讀現(xiàn)象。

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

先學(xué)會(huì)查看數(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)

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

1. 連接終端必須UTF8

2. 操作系統(tǒng)必須UTF8

3. 數(shù)據(jù)庫必須UTF8

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


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

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

2.測試和正式環(huán)境都要嚴(yán)格控制數(shù)據(jù)庫的寫權(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.單庫單用戶,禁止給我一個(gè)用戶賬號(hào)管理多個(gè)庫。

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ù)庫關(guān)掉,影響業(yè)務(wù)。系統(tǒng)級(jí)別的文件 copy(PS:現(xiàn)在這種基本被廢棄了)

熱備:數(shù)據(jù)庫在線備份,不影響現(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語句(后期配置搭建主從架構(gòu))
如果值等于2,就會(huì)在CHANGE MASTER語句前添加注釋(后期配置搭建主從架構(gòu))
 
-c, --complete-insert;
導(dǎo)出完整sql語句

-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ù)庫:

備份單個(gè)數(shù)據(jù)庫或單個(gè)數(shù)據(jù)庫中的指定表:
mysqldump [OPTIONS] database [tb1] [tb2]…

備份多個(gè)數(shù)據(jù)庫:
mysqldump [OPTIONS] –databases
[OPTIONS] DB1 [DB2 DB3...]

備份所有數(shù)據(jù)庫:
mysqldump [OPTIONS] –all-databases
[OPTIONS]

利用mysql命令恢復(fù)數(shù)據(jù):
mysql -uroot -proot23 db_name < table_name.sql

xtrabackup備份原理分析:
對于Innodb,它是基于Innodb的crash recovery功能進(jìn)行備份。

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

XtraBackup 在備份的時(shí)候并不鎖定表,而是一頁一頁地復(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ì)比較表空間中每頁的 LSN 是否大于上次備份完成的 LSN,如果是,則備份該頁,并記錄當(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):

青銅到王者,快速提升你 MySQL 數(shù)據(jù)庫的段位!

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

但針對這個(gè)架構(gòu),個(gè)人建議以下幾點(diǎn):

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

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

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

4. keepalived 無法解決腦裂的問題,因此在進(jìn)行服務(wù)異常判斷時(shí),可以修改我們的判斷腳本,通過對第三方節(jié)點(diǎn)補(bǔ)充檢測來決定是否進(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í),來決定誰是主庫。避免腦裂,沖突現(xiàn)象發(fā)生。

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

MHA 架構(gòu):

青銅到王者,快速提升你 MySQL 數(shù)據(jù)庫的段位!

MySQL MHA 架構(gòu):可以說是企業(yè)最流行,用的最多的架構(gòu)了。一些同學(xué)也經(jīng)常問我相關(guān)的問題。
既然 MHA 這么火,那么它有什么優(yōu)點(diǎn)呢?

1. 故障切換時(shí),可以自行判斷哪個(gè)從庫與主庫的數(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)切換的腳本太簡單了,而且比較老化,建議后期逐漸完善。

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

PXC 架構(gòu):

青銅到王者,快速提升你 MySQL 數(shù)據(jù)庫的段位!

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

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

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

2. 完全兼容MySQL

3. 新增節(jié)點(diǎn)進(jì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ù)庫方面、硬件方向

參考老張我的博文《數(shù)據(jù)庫優(yōu)化之降龍十八掌》:
http://sumongodb.blog.51cto.com/4979448/1949024


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

希望這些知識(shí)對大家有幫助,大家有什么見解,我們可以一起討論,共同進(jìn)步。讓我們的生活更加充實(shí),讓我們對技術(shù)更加熱愛?。╯uperZS老張的王者榮耀完結(jié))

后期會(huì)出 MySQL 高可用架構(gòu)三部曲系列,希望大家到時(shí)關(guān)注!


本文名稱:青銅到王者,快速提升你MySQL數(shù)據(jù)庫的段位!
分享鏈接:http://weahome.cn/article/jjgsid.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220(工作日)

18980820575(7×24)

提交需求

返回頂部