Mysqldump нет места на диске что делать

Опубликовано: 29.03.2017

Есть у меня хороший почтарь, да не один, а несколько. Они все вертятся в связке Exim + DBmail + MySQL, естественно там есть много чего еще, но это основная связка, и нас из неё интересует последняя составляющая mySQL. Дело в том, что база юзеров и всего имеющегося мусора вертится по мускулем и хостится в базе типо InnoDB, которую нужно повсевременно сжимать и исследовать. Лучше каждую ночь, чтоб мусор скопившийся за денек, не забивал место в табличном пространстве.

К большенному огорчению база InnoDB работает по принципу ниппеля, т.е. туда дуй, оттуда х… Объясняя это легкодоступным языком- табличное место может как расширяться так и уменьшаться, но физический размер базы при всем этом меняется исключительно в сторону роста. Проще говоря если у вас база была на максимуме 20гигов, и после чего вы её почистив освободили 90% места, снутри база у вас будет свободна на 90% (поглядеть свободное место можно командой в консоли управления сервером: mysql> show table status; ), но снаружи она так и будет занимать на диске 20 Гиг физического места диска. Итак вот у меня, на одном из серверов, она успела разрастись, пока я прочухал тот факт, что запамятовал установить чистящий скриптик, до 250 Гигов, которые естественно работают как часы, но самой цифрой действуют мне на нервишки. В этой связи нужно что то делать. Но делать можно только одним методом, а конкретно перегнать базу в дамп sql и затянуть его назад, за ранее дропнув имеющегося монстра. В этой связи, ровно как и для дефрагментации табличного места, нашим самым близким другом является системная утилита mysqldump, входящая в стандартный набор поставки сервера mySQL.

*** Для дефрагментации InnoDB также можно использовать метод двойной перегонки базы, с помощью команды ALTER конвертировать её в тип MyISAM, а потом назад в тип InnoDB.
Данная утилита дает возможность сделать моментальный слепок базы данных (на тему мгновенности естественно это я так, для прекрасного словца- полтора гига шло минут 7, 250 прошли за 2.5 часа) представляющий из себя текстовый файл с набором SQL команд для сотворения и наполнения таблиц.
Фактически приступаем к работе:

# mysqldump -uSQL_USER_EXIM -p EXIM_DATABASE > /tmp/exim.sql

-u либо –user= – имя юзера
-p –password - запрос пароля
exim_database – имя базы данных
exim.sql – файл для вывода дампа

за один раз можно загнатьв дамп несколько баз, если перечислить их вереницей, после функции -B либо –databases:

# mysqldump -uSQL_USER_EXIM -p database1 -B database2 database3 > /tmp/database.sql

также можно снять дамп всех таблиц, если использовать оператор -A либо –all-databases:

# mysqldump -uSQL_USER_EXIM -p -A > /tmp/database.sql

Данная операция займет некое время и место на диске, зависящее от объема базы SQL.

После чего останавливаем сервер mySQL

# /usr/local/mysql/support-files/mysql.server stop

*****
С версии 6.x доступен так именуемый on-line backup для всех типов баз. Его отличительные особенности то, что он не перекрывает выполнение INSERT/UPDATE/DELETE и производится простейшими командами:
BACKUP DATABASE to
RESTORE [OVERWRITE]
*****

Делаем запасную копию базы данных которую мы предполагаем экспортировать, из папки %mysql%/data переписываем файл базы и журналов InnoDB и подобающую ей папку с файлами *.frm в самое скрытое место на винте.

После чего удаляем таблицу и надлежащие ей файлы, и запускаем сервер:

# /usr/local/mysql/support-files/mysql.server start

Сервер малость поразмыслит и восстановит пустую базу InnoDB, после этого мы заливаем в неё наш бэкап. Сделать это можно 2-мя методами, или из командной строчки:

# mysql -uSQL_USER_EXIM -p EXIM_DATABASE < /tmp/exim.sql

или из консоли сервера:

mysql> use EXIM_DATABASE;
mysql> source exim.sql

После этого смотрим производительность и размер диска, занимаемый базой.
******
Парочка нужных советов:

При ограниченных ресурсах памяти либо большой базе данных, следует использовать опцию –quick либо –opt, это позволит утилите mysqldump начать делать дамп базы без подготовительной загрузки её в память.

Также может появиться необходимость указания шифровки в какой вы желали бы получить дамп вашей базы, для этого употребляется функция –default-character-set=

Функция -f позволит продолжать выполнение резервирования даже при получении ошибки в процессе выполнения процедуры.

Комфортная команда для автоматического резервирования со сжатием:
mysqldump -uROOT -pPASSWD DBNAME | gzip -c > `date “+%Y-%m-%d”`.gz

Также может быть резервироватьна удаленный хост:
mysqldump -uROOT -PNUMBER_PORT -hHOST -pPASSWD DBNAME