處理MySQL的ibdata1文檔過大問題

本人遇到一次在安裝zabbix監控的時候,yum安裝的MySQL數據庫,後面用了一段時間發現data目錄下的ibdata1的空間特別大,反而我的zabbix數據庫的空間很小,這樣的情況在後面備份zabbix數據庫的時候會很不方便,所以想着要怎麼解決下。
ibdata1文檔是什麼?

    ibdata1是一個用來構建innodb系統表空間的文檔,這個文檔包含了innodb表的元數據、撤銷記錄、修改buffer和雙寫buffer。如果file-per-table選項打開的話,該文檔則不一定包含所有表的數據。當innodb_file_per_table選項打開的話,新創建表的數據和索引則不會存在系統表空間中,而是存放在各自表的.ibd文檔中.

    顯然這個文檔會越來越大,innodb_autoextend_increment選項則指定了該文檔每次自動增長的步進,默認是8M.

是什麼原因導致ibdata1文檔會越來越大?

    ibdata1存放數據,索引和緩存等,是MYSQL的最主要的數據。所以隨着數據庫越來越大,表也會越大,這個無法避免的。如果時間長了,越來越大,我們在處理日誌和空間的時候就不是那麼方便了,就不知從何入手了。接下來我們就要處理下這樣的情況,分庫存儲數據。

該如何處理呢?

    首先我們把數據庫文檔備份下來,然後直接刪除ibdata文檔(為了保險起見最好先全備一次,做到數據安全和完整),然後再重新導入數據庫文檔即可!

具體操作步驟如下(截圖並不完整,但是首先要弄懂大概情況和原理):

    1、停止業務,備份一次全庫

        mysqldump -uroot -ppassword --all-databases --add-dorp-table > /root/all_mysql.sql

    2、備份完成,停止數據庫

        systemctl stop mariadb 或者 service mysqld stop

    3、修改配置文檔

        在[mysqld]下增加下面配置 innodb_file_per_table=1 驗證配置是否生效,可以重啟mysql後,執行 #service mysqld restart

    4、驗證

        mysql -uroot -ppassword mysql

        show variables like '%per_table%';

        +-----------------------+-------+

        | Variable_name | Value |

        +-----------------------+-------+

        | innodb_file_per_table | ON |

        +-----------------------+-------+

        1 row in set (0.00 sec)

        innodb_file_per_table的狀態變為ON

    5、刪除ibdata1文檔和日誌

        rm -rf ibdata1

        rm -rf ib_logfile*

    6、還原數據庫

        mysql -uuser -ppassword

        source /root/all_mysql.sql

        數據文檔單獨存放(共享表空間改為每個表獨立的表空間文檔)。

(最近一直在忙沒顧上寫,把最近遇到的少許問題整理分享一下)有不對之處還請指出

關鍵詞:文檔 ibdata mysql 數據庫 數據 table innodb 空間 處理 per

相關推薦:

InnoDB存儲引擎結構介紹

MySql在生產環境中是用mysqldump還是xtrabackup備份和恢復數據

MySQL共享表空間擴容

Mysql數據庫的備份和恢復

如何給MySQL共享表空間擴容

MySQL的ibdata1文檔佔用過大

Mysql運維管理-MySQL數據庫存儲引擎知識19

Mysql多實例的配置和管理

MySQL單獨存放表空間Innodb_file_per_table