Дефрагментация и сжатие InnoDB

02 Dec 2009 | Автор: anchous |

Есть у меня отличный почтарь, да не один, а несколько. Все они крутятся в связке 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 <db_name> to <file>
RESTORE <file> [OVERWRITE]
*****

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

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

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

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

# 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

VN:F [1.9.21_1169]
Rating: 8.5/10 (2 votes cast)
VN:F [1.9.21_1169]
Rating: -2 (from 2 votes)
Дефрагментация и сжатие InnoDB, 8.5 out of 10 based on 2 ratings

Теги: , , , , , , ,

Ваш отзыв