建站
咨詢
售后
建站咨詢
新聞中心
如何對(duì)Mysql 數(shù)據(jù)表壓縮
633M -rw-rw---- 1 mysql mysql 632M Oct 25 17:51 url_comment_0.ibd
網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、小程序制作、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了克什克騰免費(fèi)建站歡迎大家使用！
12K -rw-rw---- 1 mysql mysql 8.7K Oct 25 18:16 url_comment_0.frm
178M -rw-rw---- 1 mysql mysql 178M Oct 25 18:53 url_comment_0.MYD
99M -rw-rw---- 1 mysql mysql 98M Oct 25 18:53 url_comment_0.MYI結(jié)論:由上面數(shù)據(jù)可知innodb plugin能有效壓縮innodb數(shù)據(jù)文件,近50%,另外相同的情況下使用MyISAM表也可較大的減少數(shù)據(jù)大小(178+99633M).
當(dāng)然實(shí)際的壓縮比例和表的結(jié)構(gòu)等有關(guān),如字段為varchar會(huì)有較大的壓縮比,而int類型壓縮率會(huì)低些~
其它的壓縮技術(shù)還可以使用myisampack,ARCHIVE,infobright等技術(shù),但是這些技術(shù)常是用于數(shù)據(jù)表歸檔操作,令數(shù)據(jù)表有較大壓縮比,但有不能進(jìn)行update,insert操作等缺點(diǎn),不在本文討論范圍~
MySQL的備份與還原，非常規(guī)備份，全量備份，增量備份
1：官方百萬(wàn)級(jí)別的測(cè)試數(shù)據(jù)庫(kù)：
官方測(cè)試數(shù)據(jù)庫(kù)github網(wǎng)址：
下載到目錄，解壓即可，運(yùn)行命令：
2：自己創(chuàng)建簡(jiǎn)單測(cè)試數(shù)據(jù)庫(kù)：
快速隨機(jī)生成測(cè)試語(yǔ)言的網(wǎng)站：
選擇sql和想生成的字段，點(diǎn)擊生成Generate!生成即可。
在MySQL輸入生成的語(yǔ)句即可。
3：測(cè)試備份還原時(shí)用到的命令
刪庫(kù)跑路測(cè)試（先備份好）
還原后查詢庫(kù)的表數(shù)據(jù)是否完整。
采用復(fù)制整個(gè)數(shù)據(jù)存放目錄
1：查看數(shù)據(jù)庫(kù)數(shù)據(jù)存放位置
有兩種方法：
1)：在數(shù)據(jù)庫(kù)中用命令 show variables like 'datadir'; 查看
2)：在配置文件中查看，配置了 datadir 目錄的可查看。沒(méi)有配置的默認(rèn)為 /var/lib/mysql/ 位置
Linux中查看配置文件
2：復(fù)制目錄或者目錄下某個(gè)數(shù)據(jù)庫(kù)名
3：還原時(shí)直接復(fù)制文件夾到數(shù)據(jù)庫(kù)目錄即可
mysqldump又可叫做全量備份。
參數(shù) --databases 同 -B ，單獨(dú)一個(gè)庫(kù)，也可省略。
1、備份命令mysqldump格式
格式：mysqldump -h主機(jī)名 -P端口 -u用戶名 -p密碼 database 數(shù)據(jù)庫(kù)名 文件名.sql
備份testDatabase數(shù)據(jù)庫(kù)
2、備份MySQL數(shù)據(jù)庫(kù)為帶刪除表的格式
備份MySQL數(shù)據(jù)庫(kù)為帶刪除表的格式，能夠讓該備份覆蓋已有數(shù)據(jù)庫(kù)而不需要手動(dòng)刪除原有數(shù)據(jù)庫(kù)。
3、直接將MySQL數(shù)據(jù)庫(kù)壓縮備份
備份并壓縮
4、備份MySQL數(shù)據(jù)庫(kù)某個(gè)(些)表
備份testDatabase中的myTable表，不需要用參數(shù) --databases 或者 -B
5、同時(shí)備份多個(gè)MySQL數(shù)據(jù)庫(kù)
同時(shí)備份testDatabase和 employees兩個(gè)庫(kù)
6、備份服務(wù)器上所有數(shù)據(jù)庫(kù)
參數(shù) --all-databases 同 -A
7、還原MySQL數(shù)據(jù)庫(kù)的命令
1) 不指定數(shù)據(jù)名還原，默認(rèn)生成原數(shù)據(jù)庫(kù)名稱，還原所有數(shù)據(jù)庫(kù)。
2) 指定數(shù)據(jù)名還原，還原指定單個(gè)數(shù)據(jù)庫(kù)，需在數(shù)據(jù)庫(kù)種預(yù)先創(chuàng)建一個(gè)testDatabase名稱。
3) 還原壓縮的MySQL數(shù)據(jù)庫(kù)
4) 進(jìn)入數(shù)據(jù)庫(kù)用source導(dǎo)入
增量備份是針對(duì)于數(shù)據(jù)庫(kù)的bin-log日志進(jìn)行備份的，增量備份是在全量的基礎(chǔ)上進(jìn)行操作的。增量備份主要是靠mysql記錄的bin-log日志。
1：查看是否開(kāi)啟bin-log日志
進(jìn)入mysql輸入命令可查看。
顯示如下為開(kāi)啟狀態(tài)，日志文件在/var/lib/mysql/以binlog.00001的格式保存。
如未開(kāi)啟，需要在配置文件種配置
2：查看目前使用的bin-log日志文件
進(jìn)入mysql查看命令。
顯示如下，目前使用的是binlog.000022文件，所有操作都記錄在此文件。
查看當(dāng)前testDatabase的表myTable數(shù)據(jù)如下，
3：刷新日志，使用新的日志文件（備份）
在命令端執(zhí)行命令
日志文件從 binlog.000022 變?yōu)?binlog.000023
這時(shí)相當(dāng)與已經(jīng)備份成功，備份文件即為上次的binlog.000022日志文件。
4：刪除數(shù)量，從日志還原數(shù)據(jù)
1) 刪除ABC行
查詢以及沒(méi)有ABC行列。
2) 恢復(fù)數(shù)據(jù)ABC行
退出mysql，在命令端用mysqlbinlog命令恢復(fù)到binlog.000022日志狀態(tài)。
進(jìn)入數(shù)據(jù)庫(kù)再次查看數(shù)據(jù)，ABC已經(jīng)恢復(fù)。
增量備份完成。
如何壓縮Mysql數(shù)據(jù)庫(kù)
壓縮表從名字上來(lái)看，簡(jiǎn)單理解為壓縮后的表，也就是把原始表根據(jù)一定的壓縮算法按照一定的壓縮比率壓縮后生成的表。
1.1 壓縮能力強(qiáng)的產(chǎn)品
表壓縮后從磁盤占用上看要比原始表要小很多。如果你熟悉列式數(shù)據(jù)庫(kù)，那對(duì)這個(gè)概念一定不陌生。比如，基于 PostgreSQL 的列式數(shù)據(jù)庫(kù) Greenplum；早期基于 MySQL 的列式數(shù)據(jù)庫(kù) inforbright；或者 Percona 的產(chǎn)品 tokudb 等，都是有壓縮能力非常強(qiáng)的數(shù)據(jù)庫(kù)產(chǎn)品。
1.2 為什么要用壓縮表？
情景一：磁盤大小為 1T，不算其他的空間占用，只能存放 10 張 100G 大小的表。如果這些表以一定的比率壓縮后，比如每張表從 100G 壓縮到 10G，那同樣的磁盤可以存放 100 張表，表的容量是原來(lái)的 10 倍。情景二：默認(rèn) MySQL 頁(yè)大小 16K，而 OS 文件系統(tǒng)一般塊大小為 4K，所以在 MySQL 在刷臟頁(yè)的過(guò)程中，有一定的概率出現(xiàn)頁(yè)沒(méi)寫(xiě)全而導(dǎo)致數(shù)據(jù)壞掉的情形。比如 16K 的頁(yè)寫(xiě)了 12K，剩下 4K 沒(méi)寫(xiě)成功，導(dǎo)致 MySQL 頁(yè)數(shù)據(jù)損壞。這個(gè)時(shí)候就算通過(guò) Redo Log 也恢復(fù)不了，因?yàn)閹缀跤兴械年P(guān)系數(shù)據(jù)庫(kù)采用的 Redo Log 都記錄了數(shù)據(jù)頁(yè)的偏移量，此時(shí)就算通過(guò) Redo Log 恢復(fù)后，數(shù)據(jù)也是錯(cuò)誤的。所以 MySQL 在刷臟數(shù)據(jù)之前，會(huì)把這部分?jǐn)?shù)據(jù)先寫(xiě)入共享表空間里的 DOUBLE WRITE BUFFER 區(qū)域來(lái)避免這種異常。此時(shí)如果 MySQL 采用壓縮表，并且每張表頁(yè)大小和磁盤塊大小一致，比如也是 4K，那 DOUBLE WRITE BUFFER 就可以不需要，這部分開(kāi)銷就可以規(guī)避掉了。查看文件系統(tǒng)的塊大?。?/p>
root@ytt-pc:/home/ytt#??tune2fs?-l?/dev/mapper/ytt--pc--vg-root??|?grep?-i?'block?size'Block size: ? ? ? ? ? ? ? 4096
1.3 壓縮表的優(yōu)勢(shì)
壓縮表的優(yōu)點(diǎn)非常明顯，占用磁盤空間??！由于占用空間小，從磁盤置換到內(nèi)存以及之后經(jīng)過(guò)網(wǎng)絡(luò)傳輸都非常節(jié)省資源。
簡(jiǎn)單來(lái)講：節(jié)省磁盤 IO，減少網(wǎng)絡(luò) IO。
1.4 壓縮表的缺陷
當(dāng)然壓縮表也有缺點(diǎn)，壓縮表的寫(xiě)入（INSERT,UPDATE,DELETE）比普通表要消耗更多的 CPU 資源。
壓縮表的寫(xiě)入涉及到解壓數(shù)據(jù)，更新數(shù)據(jù)，再壓縮數(shù)據(jù)，比普通表多了解壓和再壓縮兩個(gè)步驟，壓縮和解壓縮需要消耗一定的 CPU 資源。所以需要選擇一個(gè)比較優(yōu)化的壓縮算法。
1.5 MySQL 支持的壓縮算法
這塊是 MySQL 所有涉及到壓縮的基礎(chǔ)，不僅僅用于壓縮表，也用于其它地方。比如客戶端請(qǐng)求到 MySQL 服務(wù)端的數(shù)據(jù)壓縮；主從之間的壓縮傳輸；利用克隆插件來(lái)復(fù)制數(shù)據(jù)庫(kù)操作的壓縮傳輸?shù)鹊取?/p>
從下面結(jié)果可以看到 MySQL 支持的壓縮算法為 zlib 和 zstd，MySQL 默認(rèn)壓縮算法為 zlib，當(dāng)然你也可以選擇非 zlib 算法，比如 zstd。至于哪種壓縮算法最優(yōu)，暫時(shí)沒(méi)辦法簡(jiǎn)單量化，依賴表中的數(shù)據(jù)分布或者業(yè)務(wù)請(qǐng)求。
MYSQL表壓縮和整理
MYSQL表壓縮和整理如下：
633M
-rw-rw----
1
mysql
mysql
632M
Oct
25
17:51
url_comment_0.ibd
12K
-rw-rw----
1
mysql
mysql
8.7K
Oct
25
18:16
url_comment_0.frm
178M
-rw-rw----
1
mysql
mysql
178M
Oct
25
18:53
url_comment_0.MYD
99M
-rw-rw----
1
mysql
mysql
98M
Oct
25
18:53
url_comment_0.MYI
結(jié)論:由上面數(shù)據(jù)可知innodb
plugin能有效壓縮innodb數(shù)據(jù)文件,近50%,另外相同的情況下使用MyISAM表也可較大的減少數(shù)據(jù)大小(178+99633M).
當(dāng)然實(shí)際的壓縮比例和表的結(jié)構(gòu)等有關(guān),如字段為varchar會(huì)有較大的壓縮比,而int類型壓縮率會(huì)低些。
當(dāng)前名稱：mysql怎么壓縮數(shù)據(jù) mysql 數(shù)據(jù)壓縮
網(wǎng)址分享：http://fisionsoft.com.cn/article/dodpdge.html
