Создать/развернуть дамп MySQL БД

Категория: MySQL

Как веб разработчику, мне часто приходится создавать и разворачивать дампы баз данных, и в основном это MySQL. В данной заметке я оставлю частые задачи по работе с дампами MySQL БД. На вскидку, при развертывании InnoDB таблицы ~ 1GB уходит около получаса. Если вам нужно засечь время создания/восстановления дампа - используйте команду time (time mysql ...).

Создание дампа MySQL базы

Создать полный дамп MySQL:

mysqldump -uroot --all-databases --routines --triggers -p > mysql.dump

Создать и сжать дамп БД:

mysqldump -uroot -proot DATABASE | gzip > ./db.sql.gz

Создать дамп структуры MySQL:

mysqldump -u%user% -p%pass% --no-data db_name > db_name.dump

Сжать SQL дамп:

gzip --best < db_name.sql > db_name.sql.gz

Исключить таблицы юзеров WP:

mysqldump -uUSER -pPASS --ignore-table=blog.wp_users --ignore-table=blog.wp_usermeta blog > blog_without_users.sql

Создать полный дамп БД - каждая БД в отдельном архиве (shell-скрипт):

#!/bin/bash
dblist=`mysql -u%user% -p%pass% -e "show databases" | sed -n '2,$ p'`
for db in $dblist; do
    mysqldump -u%user% -p%pass% --routines --triggers $db | gzip --best > $db.sql.gz
done

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

Восстановить MySQL дамп:

mysql -uUSER -hlocalhost -pPASS DB_NAME < /path/dump.sql

Восстановить MySQL дамп из zip архива:

unzip -p dump.sql.zip | mysql -u root -p%pass% database%

Восстановить .sql.gz дамп:

gunzip -c dump.sql.gz | mysql -uroot -hlocalhost -p%pass% %database%

Выдернуть конкретную таблицу из общего дампа:

awk '/CREATE TABLE `table_name`/,/UNLOCK TABLES/' db_name.dump
Примечание

Если при восстановлении базы данных вы получаете ошибки:

ERROR 2006 (HY000) at line 38207: MySQL server has gone away

или ошибку в интерфейсе PhpMyAdmin:

Найдено 1 ошибок при анализе.
  1. Отсутствует выражение. (near "ON" at position 25)
SQL запрос:
  SET FOREIGN_KEY_CHECKS = ON;
Ответ MySQL:
#2006 - MySQL server has gone away

Решение:

Нужно увеличить значение max_allowed_packet в конфиге /etc/mysql/conf.d/mysql.cnf:

max_allowed_packet = 64M

или установить этот параметр при импорте:

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0; SET GLOBAL max_allowed_packet=64*1024*1024;" -uroot -p database_name < ./dump.sql

Проверить значение параметра:

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';

#mysql  #dump #restore #backup #mysqldump #db dump #restore database #mysql dump

категория: MySQL