Как сделать дамп базы данных MySQL

Простейший синтаксис команды для создания дампа выглядит так:

mysqldump -u user_name -p database_name > backup.sql

Если дамп сделан на сервере и его потом надо загрузить себе на локальный компьютер:

scp username@server_ip:/path/to/backup.sql /path/to/local/folder/

Выше это копирование при помощи scp, но у такого способа есть минус — если дамп большой или соединение нестабильное, то в случае сбоя придётся качать заново. Чтобы этого избежать, можно загрузить дамп при помощи утилиты rsync:

rsync -avz username@server_ip:/path/to/backup.sql /path/to/local/folder/

Флаги значат следующее:

• -a — активируется поддержка архивации данных, сохраняются права доступа и временные метки;
• -v — процесс синхронизации отображается на экране;
• -z — сжимает данные при передаче.

Возвращаюсь, собственно, к созданию дампа.

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

mysqldump -u root -p --lock-all-tables --quick --no-tablespaces database_name > full_backup.sql

Помимо флага --lock-all-tables, который, собственно блокирует запись на период создания дампа, в команде выше используются ещё следующие флаги:

• --quick  — заставляет mysqldump считывать данные построчно, а не грузить всю базу в память (поведение по дефолту) это позволяет снижать потребление памяти при дампе больших баз;
• --no-tablespaces — в mysqldump исключает информацию о табличных пространствах (tablespaces) из дампа базы данных.

Флаг --no-tablespaces:

• исключает информацию о табличных пространствах (tablespaces) из дампа базы данных;
• появился в новых версиях MySQL и решает проблемы совместимости при экспорте/импорте данных;
• не влияет на структуру и данные, поэтому безопасен в использовании;
• уместно использовать всегда, кроме случаев, когда мы точно знаем зачем и почему хотим использовать табличные пространства.

Флаг --quick:

• заставляет mysqldump считывать данные построчно, а не грузить всю базу в память (поведение по дефолту) это позволяет снижать потребление памяти при дампе больших баз;
• не стоит использовать если база маленькая — особых преимуществ не даст;
• не стоит использовать если памяти много и важна скорость создания дампа (загрузка всей таблицы в память может быть быстрее).

Итого для дампа небольшой базы, в случае если кратковременная блокировка БД на запись не критична, наиболее универсальным способом сделать дамп становится такой:

mysqldump -u root -p --lock-all-tables --no-tablespaces database_name > full_backup.sql