新的一周,老張(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.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ù)庫密碼為空。
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 語句進(jìn)入數(shù)據(jù)庫的過程細(xì)分,又可以由8個(gè)小部分組成如下圖:
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)圖:
我們要學(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):
一般中小型公司都使用這種架構(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 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):
可以實(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)注!