MySQLDUMP на примерах

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

Ну, просто поднадоело мне постоянно гуглить ключи, когда они мне «вдруг» понадобятся.

Немного теории..

MySQLDUMP это инструмент, который позволяет создавать резервные копии баз данных MySQL. На выхлопе мы получаем .sql файл с дампом базы данных. В данном файле содержится Sql код в виде текста, т.е. его всегда можно открыть текстовым редактором, чтобы посмотреть, отредактировать и т.д…
Восстановить такой дамп можно с помощью утилиты mysql через STDIN.

Кстати, есть еще такая утилита, как MySqlHotCopy, которую лучше всего использовать для создания горячих резервных копий, так как она ставит базу на блокировку и копирует файлы БД в нужное место. Но эта штука будет работать только если ее запускать на самом сервере, работает только с MyISAM и Archive-таблицами и  больше подойдет для больших БД.
Восстановить данные можно путем копирования сохраненных файлов в каталог данных MySQL.

Но, в общем-то речь не о ней… может напишу о «горячей копии» в другой статье…

Создание резервной копии базы данных

Начнем с самой распространенной команды создания дампа сайта site.ru в файл site.ru:

mysqldump -uroot -h10.30.30.10 -p site_ru > site_ru.sql
    Подробнее о ключах:

  • -u (—user=…) это имя пользователя БД;
  • -h (—host=…) это хост, на котором располагается сам сервер. Если сервер локальный, то данных параметр можно или не использовать или прописать туда localhost. Так же имейте в виду, что ip сервера после ключа нужно писать без пробела;
  • -p (—password=…) это пароль пользователя. Если этот ключ не использовать, то подключение к БД будет возможно в том случае, если пароля нет. Так же никто не запрещает вписать пароль в команду, причем после ключа не должно быть пробела (например: -p1234567890, где 1234567890 это пароль);
  • site_ru это имя БД на MySql сервере;
  • site_ru.sql это файл для дампа.

Чтобы сделать резервную копию нескольких БД, можно воспользоваться ключом -B и указать несколько БД, вот пример:

mysqldump -uroot -h10.30.30.10 -p site_ru site2_ru site3_ru > sites.sql

Если ситуация не дает времени на раздумья, и нужно делать резервную копию всех баз данных, то в данной ситуации можно воспользоваться ключом —all-databases, вот пример:

mysqldump -uroot -h10.30.30.10 -p -A > all-db.sql

Кстати, есть нюанс.

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

Первый способ заключается в блокировке таблиц, т.е. можно воспользоваться параметром —lock-tables, вот пример:

mysqldump -uroot -h10.30.30.10 -p --lock-tables site_ru > site_ru.sql

Но в момент создания резервной копии запросы клиентов будут подвешены.. а следовательно могут быть тайм-ауты.

Второй способ это использование ключа —flush-log при создании резервной копии. Этот ключ закроет старый лог действий и создаст новый. Если кто-то что-то запишет в процессе создания копии — это отразится в начале журнала и можно будет перенести это изменение в базу. Далее, чтобы наверняка, после окончания создании резервной копии, нужно выполнять команду mysqladmin -flush-logs и оставлять копию предпоследнего бинарного журнала.

Восстановление резервной копии базы данных

Тут уже будет править утилита «mysql».  Вот пример:

mysql -uroot -h10.30.30.10 -p site_ru < site_ru.sql

Еще один способ, более педантичный:

mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.35-1+deb.sury.org~xenial+0.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use site_ru;
mysql> source site_ru.sql;

Ну а если у вас БД сохранена в gz-архив, то можно скомбинировать команды mysql и zcat вот так:

zcat site_ru.sql.gz | mysql -uroot -h10.30.30.10 -p site_ru

Еще варианты использования mysqldump

Например нам нужна база данных на dev зону, так сказать песочницу, а размер основной БД очень велик. Можно воспользоваться ключом —where=»true limit 150″, которому мы явно укажем выборку данных не более 150 записей. Вот пример:

mysqldump -uroot -h10.30.30.10 -p --where="true limit 150" site_ru > site_ru.sql

Если нам нужна только структура без данных, то можно воспользоваться ключом —no-data, вот пример

mysqldump -uroot -h10.30.30.10 -p --no-data site_ru > site_ru.sql

Если нам нужна копия только одной таблицы, то вот пример команды:

mysqldump -u root -p testdb tablename > testdb_table_backup.sql

Если нам нужна копия триггеров, процедур и событий (встроенного планировщика), то вот пример:

mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p site_ru | gzip > ~/database.sql.gz

Еще можно сразу создать заархивированный дамп базы. Сделать это можно вот так:

mysqldump -uroot -p site_ru | gzip > /path/to/site_ru.sql.gz

И еще можно указать дату создания архива, вот так:

mysqldump -uroot -p site_ru | gzip > `date +/path/to/site_ru.sql.%Y%m%d.%H%M%S.gz`

Ключи для использования mysqldump

Ниже будут приведены наиболее популярные ключи mysqldump:

Развернуть список...

Источники