Как загрузить дамп базы данных mysql ubuntu
Обновлено: 05.07.2024
В этом руководстве объясняется, как создавать резервные копии и восстанавливать базы данных MySQL или MariaDB из командной строки с помощью утилиты mysqldump.
Файлы резервных копий, созданные утилитой mysqldump, в основном представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может создавать файлы в форматах CSV и XML.
Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.
Если вы не сделаете резервную копию своих баз данных, программная ошибка или отказ жесткого диска могут иметь катастрофические последствия. Чтобы сэкономить время и нервы, настоятельно рекомендуется принять меры предосторожности и регулярно создавать резервные копии баз данных MySQL.
Синтаксис команды Mysqldump
Прежде чем перейти к использованию команды mysqldump, давайте начнем с обзора основного синтаксиса.
Выражения утилиты mysqldump имеют следующую форму:
Чтобы использовать команду mysqldump, сервер MySQL должен быть доступен и запущен.
Резервное копирование единой базы данных MySQL
Например, чтобы создать резервную копию базы database_name с именем database_name используя пользовательский root и сохранить ее в файл с именем database_name.sql вы должны выполнить следующую команду:
Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.
Если вы вошли в систему как тот же пользователь, которого используете для экспорта, и этот пользователь не требует пароля, вы можете опустить параметры -u и -p :
Резервное копирование нескольких баз данных MySQL
Для резервного копирования нескольких баз данных MySQL с помощью одной команды вам нужно использовать параметр --database за которым следует список баз данных, которые вы хотите --database . Каждое имя базы данных должно быть разделено пробелом.
Приведенная выше команда создаст файл дампа, содержащий обе базы данных.
Резервное копирование всех баз данных MySQL
Используйте параметр --all-databases для резервного копирования всех баз данных MySQL:
Как и в предыдущем примере, приведенная выше команда создаст один файл дампа, содержащий все базы данных.
Резервное копирование всех баз данных MySQL в отдельные файлы
Утилита mysqldump не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :
Приведенная выше команда создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.
Создание сжатой резервной копии базы данных MySQL
Если размер базы данных очень велик, рекомендуется сжать вывод. Для этого просто направьте вывод в утилиту gzip и перенаправьте его в файл, как показано ниже:
Создать резервную копию с отметкой времени
Если вы хотите хранить более одной резервной копии в одном месте, вы можете добавить текущую дату к имени файла резервной копии:
Приведенная выше команда создаст файл в следующем формате database_name-20180617.sql
Восстановление дампа MySQL
Вы можете восстановить дамп MySQL с помощью инструмента mysql . Общий синтаксис команды следующий:
В большинстве случаев вам потребуется создать базу данных для импорта. Если база данных уже существует, сначала ее нужно удалить.
В следующем примере первая команда создаст базу данных с именем database_name а затем импортирует в нее дамп database_name.sql :
Восстановление единой базы данных MySQL из полного дампа MySQL
Если вы создали резервную копию всех своих баз данных с помощью параметра -all-databases и хотите восстановить одну базу данных из файла резервной копии, содержащего несколько баз данных, используйте параметр --one-database как показано ниже:
Экспорт и импорт базы данных MySQL одной командой
Вместо создания файла дампа из одной базы данных и последующего импорта резервной копии в другую базу данных MySQL вы можете использовать следующий однострочный файл:
Приведенная выше команда передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед запуском команды убедитесь, что база данных уже существует на удаленном сервере.
Автоматизируйте резервное копирование с помощью Cron
Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.
Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:
Создайте файл с именем .my.cnf в домашнем каталоге пользователя:
Скопируйте и вставьте следующий текст в файл .my.cnf.
Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.
Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ:
Создайте каталог для хранения резервных копий:
Откройте ваш пользовательский файл crontab:
Добавьте следующее задание cron, которое будет создавать резервную копию базы данных с именем mydb каждый день в 3 часа ночи:
Не забудьте заменить username своим фактическим именем пользователя. Мы также избегаем знаков процента ( % ), потому что они имеют особое значение в crontab.
Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:
Конечно, вам нужно настроить команду в соответствии с местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим руководством по поиску файлов в Linux с помощью командной строки .
Выводы
Это руководство охватывает только основы, но оно должно стать хорошим началом для всех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump.
Если вы хотите узнать больше о работе с MySQL из командной строки, ознакомьтесь с нашим руководством по управлению учетными записями пользователей и базами данных MySQL .
Вы также можете проверить руководство о том, как сбросить пароль root MySQL, если вы его забыли.
Если у вас есть какие-либо вопросы или отзывы, не стесняйтесь оставлять комментарии.
В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.
Синтаксис и базовая команда
Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:
mysqldump [опции] -p <база> > <в какой файл сделать дамп>
Пример базовой команды для резервирования базы:
mysqldump -v -uroot -p base > /tmp/dump.sql
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
| Параметр | Описание |
|---|---|
| -u | Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права. |
| -p | Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее). |
* полный перечень параметров смотрите в официальном руководстве.
Примеры создания дампа MySQL
1. С последующим архивированием
mysqldump -uroot -p $ | gzip > /tmp/$.sql.gz
* в данном примере мы сначала создали переменную DBNAME, в которую внесли значение с именем базы, которую необходимо забэкапить. После выполняем команду mysqldump, результат выполнения которой по конвееру отдаем архиватору gzip. В результате мы получит дамп по пути /tmp/<имя базы>.sql.gz
Или с подробным выводом информации на экран (дольше по времени):
mysqldump -v -uroot -p $ | gzip > /tmp/$.sql.gz
2. Для одновременно нескольких баз
Просто перечисляем имена баз через пробел и добавляем параметр -B
mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiply_bases.sql
3. Для всех баз одной командой
Для этого ставим --all-databases, вместо имен баз
mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql
4. Резервирование только структуры базы
Для уточнения, это создание копии только самих таблиц без данных. Делается добавлением параметра --no-data
mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql
5. Создание копии определенной таблицы
Для этого после базы через пробел перечисляем названия таблиц
mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql
6. Резервирование прав доступа на СУБД
Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.
mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql
* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;
7. Проигнорировать определенную таблицу
Выполняется при помощи ключа ignore-table:
mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sql
Скрипт для резервного копирования
Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.
Резервное копирование в phpMyAdmin
В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.
В верхней части меню кликаем по Экспорт:
В разделе «Способ экспорта» ставим переключатель в положение Обычный:
* обычный режим откроет дополнительные опции для резервного экспорта данных.
Примечание: Для выполнения руководства использовался сервер Ubuntu 12.04 и MySQL 5.5, но более современные версии программного обеспечения будут работать подобным образом.
Бэкап базы данных MySQL при помощи mysqldump
Для начала нужно экспортировать БД. Об экспортировании БД при помощи mysqldump можно прочесть здесь. Базовый синтаксис команды выглядит так:
mysqldump -u username -p database_to_backup > backup_name.sql
Восстановление БД
Для этого создайте пустую БД для хранения импортированных данных. Войдите в MySQL:
mysql -u username -p
Создайте новую БД, чтобы переместить в неё данные из дампа, а затем закройте командную строку MySQL:
CREATE DATABASE database_name;
exit
Перенаправьте дамп-файл в файл БД:
mysql -u username -p database_name < backup_name.sql
Скопированные данные будут восстановлены в новой БД.
Копирование таблицы MySQL в текстовый файл
Также MySQL позволяет сохранять данные из таблицы прямо в текстовые файлы с помощью оператора select.
Общий синтаксис команды:
SELECT * INTO OUTFILE 'table_backup_file' FROM name_of_table;
Данный оператор сохранит данные из таблицы в файл на сервере MySQL. Имейте в виду: если файл с таким именем уже существует, операция не будет выполнена.
Примечание: Эта опция сохраняет только данные таблицы. Если таблица имеет сложную структуру, которую нужно сохранить, этот метод использовать не рекомендуется.
Утилита automysqlbackup
Утилита automysqlbackup доступна в стандартных репозиториях Ubuntu. Она позволяет выполнять бэкап БД автоматически на регулярной основе.
Чтобы установить эту программу, введите в терминал:
sudo apt-get install automysqlbackup
Главный конфигурационный файл утилиты находится в /etc/default/automysqlbackup; откройте его с правами администратора:
sudo nano /etc/default/automysqlbackup
Как видите, данный файл по умолчанию присваивает множество переменных из файла /etc/mysql/debian.cnf, который содержит данные для авторизации. Из этого файла automysqlbackup считывает пользователя, пароль и БД, резервные копии которых нужно создать.
ls /var/lib/automysqlbackup
daily monthly weekly
Каталог daily содержит подкаталог для каждой БД, в котором хранится сжатый sql дамп, полученный в результате последнего запуска команды:
Для настройки автоматического запуска резервного копирования система Ubuntu устанавливает вместе с этой программой демона cron.
Репликация баз данных
Для резервного копирования данных можно также использовать репликацию MySQL в связке с любым из перечисленных выше методов.
Но поскольку репликация постоянно фиксирует изменения динамической системы, она может пострадать от резервного копирования, потому что при этом фиксируется определенный момент времени.
Чтобы устранить эту проблему, можно:
- Временно отключить репликацию
- Или временно сделать сервер резервного копирования доступным только для чтения.
Временное отключение репликации
Чтобы временно отключить репликацию на slave-сервере, введите:
mysqladmin -u user_name -p stop-slave
Есть ещё один метод, который не отключает репликацию полностью, а просто ставит её на паузу:
mysql -u user_name -p -e 'STOP SLAVE SQL_THREAD;'
Остановив репликацию, можно выполнить бэкап при помощи любого из вышеперечисленных методов. При этом не нужно останавливать master-сервер.
После этого просто возобновите репликацию:
mysqladmin -u user_name -p start-slave
Настройка доступа к серверу резервного копирования
Также можно получить последовательный набор данных, временно сделав их доступными только для чтения.
Это можно сделать как на сервере master, так и на slave.
Для начала откройте MySQL с правами root:
Запишите все кэшированные изменения на диск и настройте систему только для чтения с помощью команд:
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
Выполните бэкап при помощи mysqldump.
После того как резервное копирование будет выполнено, верните систему в её обычное состояние:
SET GLOBAL read_only = OFF;
UNLOCK TABLES;
Нерекомендуемые методы резервного копирования
Скрипт mysqlhotcopy
MySQL предоставляет perl-скрипт для быстрого резервного копирования по имени mysqlhotcopy. Этот инструмент позволяет очень быстро скопировать БД на локальной машине, но он имеет некоторые ограничения, из-за которых его лучше не использовать.
Во-первых, этот скрипт копирует только данные, хранящиеся при помощи механизмов MyISAM и Archive. Большинство пользователей не меняют механизмы хранения для своих БД, а MySQL, начиная с версии 5.5, по умолчанию использует механизм InnoDB. Следовательно, скрипт mysqlhotcopy не может скопировать такой тип данных.
Во-вторых, данные, скопированные при помощи этого скрипта, можно запустить только на той же машине, на которой хранится БД. То есть mysqlhotcopy не сможет скопировать данные с удалённого сервера.
Копирование файлов таблиц
Этот подход имеет те же недостатки, что и скрипт mysqlhotcopy.
Конечно, использовать эту технику с механизмами хранения, которые хранят свои данные в файлах, довольно разумно; однако InnoDB, механизм хранения MySQL по умолчанию, нельзя скопировать таким образом.
Заключение
В целом, метод резервного копирования зависит от индивидуальных потребностей и ресурсов сервера, а также от производственной среды. Какой бы метод вы выбрали, обязательно проверьте резервные копии и попробуйте восстановить данные.
Как вы знаете, все важные данные ваших сайтов, их настройки, статьи, комментарии и другая информация хранятся в базе данных. Потеря этой информации может иметь очень тяжелые последствия для проекта. Поэтому важно своевременно делать резервные копии базы данных MySQL. Также эти копии могут быть очень полезными при переносе проекта на другой сервер.
В этой инструкции мы рассмотрим как выполняется резервное копирование MySQL или mariadb базы данных, а также как восстановить информацию в базе из копии. Кроме того, мы разберем как настроить автоматическое создание копий через определенный промежуток времени.
Резервное копирование базы данных
Все что вам нужно для резервного копирования MySQL - это доступ к серверу с операционной системой Linux, на котором установлен сервер баз данных, а также имя базы данных и параметры доступа к ней.
Для экспорта информации из базы данных в формате SQL можно использовать утилиту mysqldump. Вот ее синтаксис:
$ mysqldump опции имя_базы [имя_таблицы] > файл.sql
- -A - копировать все таблицы из всех баз данных;
- -i - записывать дополнительную информацию в комментариях;
- -c - использовать имена колонок для инструкции INSERT;
- -a - включать все возможные опции в инструкцию CREATE TABLE;
- -k - отключает первичные ключи на время копирования;
- -e - использовать многострочный вариант инструкции INSERT;
- -f - продолжить даже после ошибки;
- -h - имя хоста, на котором расположен сервер баз данных, по умолчанию localhost;
- -n - не писать инструкции для создания базы данных;
- -t - не писать инструкции для создания таблиц;
- -d - не записывать данные таблиц, а только их структуру;
- -p - пароль базы данных;
- -P - порт сервера баз данных;
- -Q - брать все имена таблиц, баз данных, полей в кавычки;
- -X - использовать синтаксис XML вместо SQL;
- -u - пользователь, от имени которого нужно подключаться к базе данных.
В большинстве случаев нам достаточно задать имя пользоваться, пароль, а также имя базы данных. Дальше рассмотрим примеры работы с утилитой. Например самая простая команда экспорта базы данных:
mysqldump -u имя_пользователя -p имя_базы > data-dump.sql
Вам нужно будет ввести пароль пользователя базы данных и больше ничего команда не выведет, поскольку мы отправили все данные в файл, но вы можете посмотреть информацию о резервной копии с помощью такой команды:
head -n 5 data-dump.sql
Но если во время создания копии возникнут какие-либо ошибки, они будут выведены на экран и вы сразу о них узнаете. Более сложный вариант, это выполнить резервное копирование MySQL с другого хоста, если у вас есть к нему доступ:
mysqldump -h хост -P порт -u имя_пользователя -p имя_базы > data-dump.sql
Копирование таблицы MySQL может быть выполнено простым добавлением имени таблицы в конец строки:
mysqldump -u имя_пользователя -p имя_базы имя_таблицы > data-dump.sql
Также, чтобы выполнять автоматическое резервное копирование может понадобиться сразу задать пароль, для этого указывайте его сразу после опции -p, без пробела:
mysqldump -u имя_пользователя -pпароль имя_базы > data-dump.sql
Мы можем делать бэкап вручную время от времени, но это не совсем удобно, поскольку есть другие важные дела. Поэтому используем планировщик cron, чтобы автоматизировать процесс. Тут есть два способа более простой, и более сложный, но точный. Допустим, нам нужно создавать резервную копию каждый день, тогда просто создайте скрипт в папке /etc/cron.daily/ со следующим содержимым:
sudo vi /etc/cron.daily/mysql-backup
!/bin/bash
/usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql
Папку /backups/mysql-dump.sql нужно заменить на свою папку для резервных копий. Осталось дать скрипту права на выполнение:
chmod ugo+x /etc/cron.daily/mysql-backup
Дальше планировщик будет запускать его каждый день и делать копирование базы данных. Но есть еще один, более точный способ, который позволяет указать точное время выполнения. Сначала выполните команду:
Добавьте в открывшейся файл такую строку и сохраните изменения:
30 2 * * * /usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql
Команда будет выполняться каждый день, в 2:30, это удобно, поскольку ночью обычно меньше нагрузка на сервер. Как вы поняли, первое число - это минуты, второе - часы, третье день, дальше неделя и месяц. Звездочка значит, что этот параметр не имеет значения.
Восстановление из резервной копии
Восстановить резервную копию mysql или mariadb из существующего SQL файла тоже очень просто. Поскольку использовался синтаксис sql мы просто можем выполнить все команды с помощью стандартного клиента mysql.
Сначала нужно создать новую базу данных. Для этого авторизуйтесь на mysql сервере с правами суперпльзователя:
Затем создайте новую базу данных, например, с именем new_database, если база данных уже существует, то этого делать не нужно:
mysql> CREATE DATABASE new_database;
Дальше закройте оболочку, нажав сочетание клавиш Ctrl+Q и импортируйте данные из файла командой:
mysql -u пользователь -p база_данных < data-dump.sql
Для экспорта мы направляли данные стандартного вывода в файл, а здесь происходит обратная операция, данные из файла направляются на стандартный ввод программы. Успешно выполненная команда ничего не выведет, и чтобы убедиться что все прошло успешно, просто посмотрите содержимое базы.
Выводы
Теперь вы знаете как выполняется копирование базы данных MySQL, а также как восстановить скопированную информацию. Мы рассмотрели все возможные опции mysqldump чтобы вы могли настроить утилиту так, как вам нужно. Резервное копирование базы данных MySQL это очень важный момент и в определенной ситуации может сохранить много времени, поэтому обязательно настройте у себя на сервере!
Backup и восстановление MySQL (MariaDB) базы данных с mysqldump
Большую часть времени, вы можете легко управлять своей базы данных с веб-инструментов, например через PHPMyAdmin. К сожалению, бывают случаи, когда необходимо восстановить большое количество данных. Это не может быть достигнуто через PHPMyAdmin из-за пределов РНР (либо максимальное время работы скрипта не позволяет или невозможно загрузить большой архив с БД).
Использование MySQL с командной строки
Если вы используете MySQL на Linux или MS Windows, вы можете использовать командную строку для выполнения большинства задач. Если вы используете MS Windows, убедитесь, что путь к исполняемым файлам командной строки входит в системном пути. В противном случае, вам придется ввести полный путь к каждой команды.
Как сделать резервную копию базы данных MySQL
Создание простого резервной копии базы данных
Кроме того, вы можете выбрать это в качестве альтернативы:
Резервное копирование нескольких баз данных
Вы можете сделать копию нескольких баз данных в один файл, используя опцию базы данных:
Создание резервной копии всех баз данных может быть достигнуто с помощью опции все-базы данных:
Резервное копирование таблиц InnoDB
Если в базе данных есть столбцы InnoDB, вам нужно будет отключить ссылочную целостность при восстановлении данных. К сожалению, это не может быть сделано с помощью утилиты mysqldump. Для этого нужно открыть сам файл и добавить:
Сжатие MySQL Dump файлов
Если вы используете mysqldump в оболочке Linux, вы можете сжать через Gzip, чтобы сжать файл дампа (при условии, что вы установили gzip):
Восстановление из резервной копии MySQL
Но, если это выполнится, использовать ту же команду без указания имени базы данных:
Более подробную информацию о mysqldumpможно найти на официальном сайте MySQL.
Читайте также: