Как бэкапить данные в MySQL

Утилиты для резервирования MySQL:

Что такое дамп базы данных и как его создать

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

Копирование базы данных может быть полезно, когда нужно выполнить:

Перенос данных на другой хостинг. Не нужно повторно создавать БД и вносить в нее все данные руками. Достаточно создать дамп и импортировать его в новый проект.
Резервное копирование. Отличный способ для экспериментов с веб-сайтом или сервером: вносите корректировки в базу данных и не бойтесь, что произойдет сбой. В случае неисправности всегда можно будет все восстановить из дампа.

Существует несколько способов создания дампов: через консоль или с помощью phpMyAdmin. Ниже рассмотрим создание дампа БД, при помощи консольной утилиты mysqldump.

Резервирование БД MySQL утилитой mysqldump

mysqldump --all --add-drop-table [--all-databases] --force [--no-data]
[-c] --password=password --user=user [база] [таблицы] > backup_file

Пример. Бекап всех БД

mysqldump --all-databases -c --password=password --user=root >> bak`date +%Y%m%d`.sql

Пример. Бекап только одной БД

mysqldump -u root -p database > database.sql
mysqldump -u root -p database > database_`date "+%Y-%m-%d"`.sql

При возникновении ошибки max_allowed_packet. Got a packet bigger than 'max_allowed_packet' bytes

При возникновении ошибки max_allowed_packet бэкапить базу данных надо так задавая размера пакета

mysqldump --single-transaction --max_allowed_packet=512M -u root database > database.sql

и на новом сервере в my.cnf примеры конфигурации MySQL, MariaDB задать параметр max_allowed_packet=64M и ребутнуть mysql.

Возможно придется увеличить net_buffer_length=1000000.

Пример. Бекап некольких БД

mysqldump -u root -p -B database1 database2 database3 > databases.sql

-c - формировать в виде полных INSERT
–all-databases (или сокращенно -A) - бекап всех баз
–databases (или сокращенно -B) для бекапа перечисленных БД
–no-data - бэкап только структуры таблиц в базах
```
mysqldump --no-data -u root -p database > database_only_scheme.sql
```
[таблицы] - бэкапить только указанные таблицы.)

Восстановление БД MySQL

mysql -u root -p [database] < database_dump.sql

Для прямой вставки из текстового файла можно воспользоваться mysqlimport. Для анализа структуры базы, например, списка таблиц: mysqlshow <база>.