Dump MySQL, создание резервной копии БД

Предисловие

Рано или поздно у всех стоит вопрос о создании копии БД. В сети есть очень много разных программ скриптов и прочего, но они хороши тогда, когда БД маленькая, ну как сейчас у нас, но если БД весит 1-2 ГГб, что делать???? Тут путь один, использовать родную командную утилиту mysqldump — тут столько букв, что я решил описать, по моему мнению, значимые параметры.

Синтаксис

В чем её преимущество — она быстрая, с кучей параметров, скорость её работы всегда будет выше, чем у phpmyadmin и им подобным.

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

где
[options] — это опции которые нужны для разных случаев, об этом ниже
db_name — база(ы) данных

Создание дампа

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev > maxnag_dev.sql

где -uroot — это параметр u означает юзер root, название этого юзера.

-ppassword — это параметр p означает пароль пользователя root

maxnag_dev — название БД, которая дампируетса.

> maxnag_dev.sql — знак больше говорит что весь поток дампа будет записан в файл maxnag_dev.sql по пути ~/maxnag, можно прописать любой путь.

Это самый простой способ получения дампа, для большинства случаев он подходит.

Восстановление дампа

maxnag@svn:~/maxnag> mysql -uroot -ppassword maxnag_dev < maxnag_dev.sql
или
maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev < maxnag_dev.sql

параметры теже, команда восстанавливает БД из файла maxnag_dev.sql в БД maxnag_dev. Обратите внимание, что первая команда не выводит на экран содержимое дампа, а вторая выводит (я думаю это тоже регулируется в настройках)

Параметры и примеры

Теперь самое интересное. Это параметры и примеры.

Дамп только «скелета» таблиц

Для получение дампа только скелета таблиц используется параметр —no-data

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword --no-data maxnag_dev > maxnag_dev.sql

Создание дампа нескольких БД

—databases, -B Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword -B maxnag_dev mybloog > twoDB.sql

Создание дампа с исключением некоторых таблиц

—ignore-table=db_name.tbl_name Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.

Создание дампа с выборочными таблицами

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev --tables user user_data > page.sql

Обратите внимание, что сначала идет название таблицы, а потом перечисляться нужные таблицы.

Создание дампа триггеров

—triggers Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user --triggers > user.sql

Кроме таблицы user в дампе содержится еще и триггеры, кусок дампа ниже

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass2` BEFORE INSERT ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass
END */;;

/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass);
END */;;

DELIMITER ;

Создание дампа процедур и ф-ций

—routines, -R — с этим ключем идет их создание.

Создание дампа планировщика заданий

—events, -E с этим ключем идет их создание.

Создание дампа в виде XML

—xml, -X Представляет дамп базы данных в виде XML

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user -X> user.xml

вот часть содержимого

<table_data name="user">
    <row>
	<field name="id">1</field>
	<field name="fam">Нагайченко</field>
	<field name="name">М</field>
	<field name="otch">В</field>
	<field name="pass">5f4dcc3b5aa765d61d8327deb882cf99</field>
	<field name="login">maxnag</field>
    </row>
</table_data>

Создание дампа с разбиением его на части

Ну это нужно, если у вас дамп несколько ГГб и естественно вытащить такой, даже в архиве проблематично, то его можно разбить на части

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | split -b 300K - maxnag.sql

Обратите внимаение, что -b, означает что мы указываем на какой объем следует делить, после этого параметра идет размер, он может быть

SIZE may have a multiplier suffix:<br>
b 512, kB 1000, K 1024, MB 1000*1000, M 1024*1024,<br>
GB 1000*1000*1000, G 1024*1024*1024, and so on for T, P, E, Z, Y.<br>

потом идет ТИРЕ, а не знак больше. И в файловой системе получается файл разбитый по указанному объему, для объединения используется команда линукса cat

Ну и напоследок, создание дампа с архивацией

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | gzip > maxnag.sql.gz

B получаем дамп уже в архиве.

————————

Все параметры можно чередовать. Это наиболее часто используемые мной параметры.

Статья просмотренна 82021 раз, зашло посетителей 25670