Резервное копирование MySQL: инструкция
В статье рассмотрим способы резервного копированияMySQL, приведем примеры по наиболее востребованным ключам и параметрам. В качестве тестовой платформы можно использовать облачные базы данных
Because there are several ways to backup MySQL database on Windows, this article will review all the different options so that you can select the method that works for you. This article is limited to MySQL database backups for a Microsoft Windows Server only. If you are using a Linux-based OS, see How to Automate MySQL Database Backups in Linux.
Note that most of the methods described in this article are fully compatible with MariaDB, with the exception of hot physical backups.
Несмотря на надёжность современных компьютеров и серверов, время от времени случаются поломки. А значит пользователи постоянно рискуют потерять все свои данные. Чтобы решить проблему потери информации, специалисты рекомендуют делать резервное копирование MySQL.
Резервное копирование, или бэкап ― это создание копии файлов и папок на дополнительном носителе информации. Резервное копирование позволяет восстанавливать повреждённые данные на основном носителе. В этой статье мы расскажем, как сделать бэкап MySQL.
Аналогично при бэкапе отдельных баз данных: перейдите на страницу интересующей БД, а далее во вкладку Экспорт.
Если вы решили сделать бэкап базы данных MySQL из командной строки в Windows, то для этого понадобиться утилита mysqldump.exe, она поставляется вместе с MySQL и расположена в каталоге с установленной MySQL/MariaDB в папке bin. Например, если СУБД установлена в C:\Server\bin\mysql-8.0, то программа mysqldump.exe будет находиться в папке C:\Server\bin\mysql-8.0\bin\.
Для её использования откройте командную строку и перетащите туда программу. Программу можно использовать с разнообразными опциями.
Из-за особенностей PowerShell по работе с кодировками, в экспортируемых базах данных может быть испорчены все нелатинские символы. Подробности смотрите в статье «Решение проблем с кодировкой вывода в PowerShell и сторонних утилитах, запущенных в PowerShell».
Если вы хотите сделать резервную копию всех баз данных в один файл, то выполните:
mysqldump.exe -u root -p --all-databases --result-file=all-databases.sql
Кстати, файл нужно искать в той папке, которую вы видите в приглашении командной строки.
Для того, чтобы сделать резервную копию только одной базы данных (например, rsyslog):
mysqldump.exe -u root -p rsyslog --result-file=rsyslog.sql
Чтобы сделать резервную копию нескольких баз данных используйте опцию —databases, а после него через пробел перечислите желаемые для бэкапа базы данных:
mysqldump.exe -u root -p --databases rsyslog syslog --result-file=rsyslog_syslog.sql
mysqldump.exe -u root -p wordpress wp_posts --result-file=wordpress_posts.sql
Для того, чтобы сделать резервную копию нескольких таблиц, перечислите их через пробел после названия БД:
mysqldump.exe -u root -p wordpress wp_posts wp_comments --result-file=wordpress_posts_comments.sql
В этом руководстве мы рассказываем, как выполнить резервное копирование и восстановление баз данных MySQL или MariaDB из командной строки с помощью утилиты mysqldump.
Файлы резервных копий, созданные утилитой mysqldump, представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может генерировать файлы в формате CSV и XML. Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.
- Синтаксис команды Mysqldump
- Резервное копирование одной базы данных MySQL
- Резервное копирование нескольких баз данных MySQL
- Резервное копирование всех баз данных MySQL
- Резервное копирование всех баз данных MySQL в отдельные файлы
- Создание сжатой резервной копии базы данных MySQL
- Создать резервную копию с отметкой времени
- Восстановление дампа MySQL
- Восстановление одной базы данных MySQL из полного дампа MySQL
- Экспорт и импорт базы данных MySQL одной командой
- Автоматизация резервного копирования с помощью Cron
- Заключение
- Что такое MySQL?
- Бэкап базы данных MySQL при помощи mysqldump
- Восстановление БД
- Копирование таблицы MySQL в текстовый файл
- Утилита automysqlbackup
- Репликация баз данных
- Временное отключение репликации
- Настройка доступа к серверу резервного копирования
- Нерекомендуемые методы резервного копирования
- Скрипт mysqlhotcopy
- Копирование файлов таблиц
- Заключение
- How to create a physical MySQL database backup
- Manual creation of a MySQL Server backup by copying data files
- Creating a MySQL Server backup by copying data files using a batch script
- Using mysqlbackup utility
- Резервирование БД через phpMyAdmin
- Как создать новую базу данных MySQL
- Как восстановить базу данных MySQL из резервной копии
- Как создать бэкап отдельной таблицы
- # restore a gzipped mysqldump file without uncompressing
- Связанные статьи
- # Restoring a backup of a database or table
- # Backup direct to Amazon S3 with compression
- MySQL Backup types
- # Backup database with stored procedures and functions
- # Syntax
- # Parameters
- Где можно хранить резервные копии
- Синтаксис команд
- Одновременная архивация двух и более баз
- Заархивировать все имеющиеся базы
- Резервировать только структуру БД
- Создать резервную копию указанной таблицы
- Перенос прав доступа к СУБД
- При архивации игнорировать указанную таблицу
- Bottom line
- Как сохранить копию нескольких баз данных
- Как создать бекап базы данных MySQL
- # Tranferring data from one MySQL server to another
- Способы резервного копирования MySQL
- Резервирование под любой учетной записью
- # mysqldump from a remote server with compression
- # Specifying username and password
- Типовые проблемы
- Восстановление баз данных из резервной копии без phpMyAdmin в Windows
- # Creating a backup of a database or table
- How to create a logical MySQL backup
- Simple backup of MySQL database using
- Backup using
- Backup using
- Backup using
- Backup using
- Выводы
Синтаксис команды Mysqldump
Прежде чем приступить к использованию команды mysqldump, начнем с обзора основного синтаксиса.
Выражения утилиты mysqldump имеют следующую форму:
mysqldump [options] > file.sql
options
— параметры mysqldumpfile.sql
— дамп (резервная копия) файла
Для использования команды mysqldump сервер MySQL должен быть доступен и запущен.
Резервное копирование одной базы данных MySQL
Наиболее распространенный вариант использования инструмента mysqldump — резервное копирование одной базы данных.
Например, чтобы создать резервную копию базы данных с именем database_name
, используя пользователя root
, и сохранить ее в файл с именем database_name.sql
, вы должны выполнить следующую команду:
mysqldump -u root -p database_name > database_name.sql
Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.
Если вы вошли в систему как тот же пользователь, которого вы используете для выполнения экспорта, и этот пользователь не требует пароля, вы можете пропустить опции -u
и -p
:
mysqldump database_name > database_name.sql
Резервное копирование нескольких баз данных MySQL
ля резервного копирования нескольких баз данных MySQL одной командой вам нужно использовать параметр --database
, за которым следует список баз данных, которые вы хотите сделать резервную копию. Каждое имя базы данных должно быть разделено пробелом.
mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql
Команда выше создаст файл дампа, содержащий обе базы данных.
Резервное копирование всех баз данных MySQL
Используйте опцию --all-database
для резервного копирования всех баз данных MySQL:
mysqldump -u root -p --all-databases > all_databases.sql
Как и в предыдущем примере, команда выше создаст один файл дампа, содержащий все базы данных.
Резервное копирование всех баз данных MySQL в отдельные файлы
Утилита mysqldump не предоставляет возможность резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла bash FOR:
for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done
Команда выше создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.
Создание сжатой резервной копии базы данных MySQL
Если размер базы данных очень большой, рекомендуется сжать вывод. Для этого просто перенаправьте вывод в утилиту gzip
и перенаправьте его в файл, как показано ниже:
mysqldump database_name | gzip > database_name.sql.gz
Создать резервную копию с отметкой времени
Если вы хотите сохранить более одной резервной копии в одном месте, вы можете добавить текущую дату в имя файла резервной копии:
mysqldump database_name > database_name-$(date +%Y%m%d).sql
Команда выше создаст файл в следующем формате database_name-20200223.sql
Восстановление дампа MySQL
Вы можете восстановить дамп MySQL с помощью инструмента mysql. Общий синтаксис команды выглядит следующим образом:
mysqld database_name < file.sql
В большинстве случаев вам необходимо создать базу данных куда вы будете производить импорт. Если база данных уже существует, сначала вам нужно удалить ее.
В следующем примере первая команда создаст базу данных с именем database_name
, а затем импортирует в нее дамп database_name.sql
:
mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql
Восстановление одной базы данных MySQL из полного дампа MySQL
Если вы создали резервную копию всех своих баз данных с помощью параметра -all-database
и хотите восстановить одну базу данных из файла резервной копии, который содержит несколько баз данных, используйте параметр --one-database
, как показано ниже:
mysql --one-database database_name < all_databases.sql
Экспорт и импорт базы данных MySQL одной командой
Вместо того, чтобы создавать файл дампа из одной базы данных и затем импортировать резервную копию в другую базу данных MySQL, вы можете использовать следующую однострочную команду:
mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name
Команда выше передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name
. Перед выполнением команды убедитесь, что база данных уже существует на удаленном сервере.
Автоматизация резервного копирования с помощью Cron
Автоматизация процесса резервного копирования баз данных так же проста, как создание задания cron, которое будет запускать команду mysqldump в указанное время.
Подробно про cron можно прочитать в нашей статье.
Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:
- Создайте файл с именем
.my.cnf
в вашем домашнем каталоге пользователя:sudo nano ~/.my.cnf
Скопируйте и вставьте следующий текст в файл
.my.cnf
.[client] user = dbuser password = dbpasswd
Не забудьте заменить
dbuser
иdbpasswd
на пользователя базы данных и пароль пользователя. - Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ, используя команду
cmod
(подробнее про которую можно прочесть тут):chmod 600 ~/.my.cnf
- Создайте каталог для хранения резервных копий при помощи комадны
mkdir
(про нее тоже есть статья):mkdir ~/db_backups
- Откройте ваш пользовательский файл crontab:
crontab -e
Добавьте следующее задание cron, которое будет создавать резервную копию имени базы данных mydb каждый день в 3 часа ночи:
0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql
Не забудьте заменить
username
вашим реальным именем пользователя.Вы также можете создать еще один cron job, чтобы удалить любые резервные копии старше 30 дней:
find /path/to/backups -type f -name "*.sql" -mtime +30 -delete
Конечно, вам нужно настроить команду в соответствии с вашим местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим Руководством по поиску файлов в Linux с помощью командной строки.
Заключение
Это руководство охватывает только основы, но оно должно быть хорошим началом для тех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump. Если вы хотите найти больше материалов про базы данных, то просто наберите sql в нашем поиске!
Рекомендуем
2 ноября, 2015 11:48 дп
Что такое MySQL?
MySQL – это популярная система управления базами данных (СУБД), использующая для управления данными язык запросов SQL. MySQL идеально подходит для хранения данных сайта или веб-приложения.
Резервное копирование (или бэкап) – очень важная для сохранности любых данных операция. Особенно это касается баз данных. Бэкап базы данных MySQL можно выполнить несколькими способами, о чём и пойдёт речь в этой статье.
Примечание: Для выполнения руководства использовался сервер Ubuntu 12.04 и MySQL 5.5, но более современные версии программного обеспечения будут работать подобным образом.
Бэкап базы данных MySQL при помощи mysqldump
Утилита mysqldump – один из самых простых и удобных способов создания резервной копии MySQL.
Для начала нужно экспортировать БД. Об экспортировании БД при помощи mysqldump можно прочесть здесь. Базовый синтаксис команды выглядит так:
Восстановление БД
Чтобы восстановить дамп БД, созданный при помощи mysqldump, нужно просто перенаправить вывод в файл MySQL.
Для этого создайте пустую БД для хранения импортированных данных. Войдите в MySQL:
Создайте новую БД, чтобы переместить в неё данные из дампа, а затем закройте командную строку MySQL:
CREATE DATABASE database_name;
exit
Перенаправьте дамп-файл в файл БД:
Скопированные данные будут восстановлены в новой БД.
Копирование таблицы 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 считывает пользователя, пароль и БД, резервные копии которых нужно создать.
Стандартное место хранения резервных копий – /var/lib/automysqlbackup. Найдите этот каталог и ознакомьтесь со структурой бэкапов:
ls /var/lib/automysqlbackup
daily monthly weekly
Каталог daily содержит подкаталог для каждой БД, в котором хранится сжатый sql дамп, полученный в результате последнего запуска команды:
ls -R /var/lib/automysqlbackup/dailey
.:
database_name information_schema performance_schema
./database_name:
database_name_2013-08-27_23h30m.Tuesday.sql.gz
./information_schema:
information_schema_2013-08-27_23h30m.Tuesday.sql.gz
./performance_schema:
performance_schema_2013-08-27_23h30m.Tuesday.sql.gz
Для настройки автоматического запуска резервного копирования система Ubuntu устанавливает вместе с этой программой демона cron.
Репликация баз данных
Для резервного копирования данных можно также использовать репликацию MySQL в связке с любым из перечисленных выше методов.
Репликация – это процесс зеркалирования данных с ведущего сервера на другие (тип master-slave) или с любого сервера связки на остальные серверы (тип master-master).
Но поскольку репликация постоянно фиксирует изменения динамической системы, она может пострадать от резервного копирования, потому что при этом фиксируется определенный момент времени.
Чтобы устранить эту проблему, можно:
- Временно отключить репликацию
- Или временно сделать сервер резервного копирования доступным только для чтения.
Временное отключение репликации
Чтобы временно отключить репликацию на slave-сервере, введите:
Есть ещё один метод, который не отключает репликацию полностью, а просто ставит её на паузу:
Остановив репликацию, можно выполнить бэкап при помощи любого из вышеперечисленных методов. При этом не нужно останавливать master-сервер.
После этого просто возобновите репликацию:
Настройка доступа к серверу резервного копирования
Также можно получить последовательный набор данных, временно сделав их доступными только для чтения.
Это можно сделать как на сервере master, так и на slave.
Для начала откройте MySQL с правами root:
mysql -u root -p
Запишите все кэшированные изменения на диск и настройте систему только для чтения с помощью команд:
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 не сможет скопировать данные с удалённого сервера.
Копирование файлов таблиц
Следующий метод, который не рекомендуется применять, – это простое копирование файлов таблиц MySQL.
Этот подход имеет те же недостатки, что и скрипт mysqlhotcopy.
Конечно, использовать эту технику с механизмами хранения, которые хранят свои данные в файлах, довольно разумно; однако InnoDB, механизм хранения MySQL по умолчанию, нельзя скопировать таким образом.
Заключение
Как видите, MySQL предоставляет самые различные методы копирования данных. Все они имеют свои преимущества и недостатки, некоторые из них проще, некоторые – более широкого применения.
В целом, метод резервного копирования зависит от индивидуальных потребностей и ресурсов сервера, а также от производственной среды. Какой бы метод вы выбрали, обязательно проверьте резервные копии и попробуйте восстановить данные.
Tags: MySQL, Ubuntu 12.04
How to create a physical MySQL database backup
Ultimately, any database is stored in a file or set of files. If you save these files to another location, you can later use them to restore the data from a particular time period.
Unlike logical backups, physical backups are created much faster, because it is only a matter of copying files. The restoration is also fast, for the same reason.
However, physical backups have two important disadvantages:
- Backups are portable only to other machines that have identical or similar hardware characteristics.
- The solution for creating hot backups can be too expensive for small businesses, as the only Windows tool that allows you to create a physical backup without stopping the server is MySQL Enterprise Backup.
Manual creation of a MySQL Server backup by copying data files
The easiest way to create a backup is to simply copy the contents of a MySQL data directory.
- Find MySQL Server data directory. To do this, run the following command:
select @@datadir;
The easiest way to do this is through MySQL Workbench.
- Find and stop the MySQL Server service
- Press win+R
- Enter services.msc
- Find the MySQL Server service in the list of services
- Right-click on it and select Stop
- Copy or pack the contents of a MySQL Server data directory to another location
- Start MySQL Server by clicking Start in the context menu of the service.
Creating a MySQL Server backup by copying data files using a batch script
All actions described in the previous section can be performed using one batch script.
First, set values for the variable values mysql_data_dir
,backup_dir
, mysql_service_name
and then run the script. It will stop the service, copy the contents of the data folder to a new subdirectory, and then start the MySQL Service.
set mysql_data_dir=C:\ProgramData\MySQL\MySQL Server 8.0\Data set backup_dir=D:\Temp set mysql_service_name=MySQL80 NET STOP %mysql_service_name% set mysql_backup_folder=%backup_dir%\mysql-backup-%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%-%time::=.% mkdir %mysql_backup_folder% xcopy /e /k /h /i "%mysql_data_dir%" "%mysql_backup_folder%" NET START %mysql_service_name%
Database restoration from files
- Stop MySQL Service
- Empty MySQL Server data directory completely
- Copy the saved data to the data directory
- Start the service
Using mysqlbackup utility
The main disadvantage of the previous method is the need to stop MySQL Server. And although copying itself should take little time, even a short shutdown of the server may be unacceptable.
For a windows server, Oracle Corporation has developed a MySQL Enterprise backup product that allows you to create hot physical backups.
This solution includes a console utility – mysqlbackup, which allows creating physical backups. It is as easy to use as mysqldump.
mysqlbackup --user=root --password --backup-image=backup.mbi \ --backup-dir=D:\Temp\EnterpriseBackup backup-to-image
–backup-image – Backup file name
–backup-dir – Directory in which backup will be created
To restore data from backups, you will need to perform preparatory steps, namely to stop the MySQL Service and clear the MySQL Server data directory. Then you need to use the same utility to restore data but with different parameters.
mysqlbackup --datadir=C:\ProgramData\MySQL\MySQL Server 8.0\Data\ --backup-image=backup.mbi --backup-dir=D:\Temp\EnterpriseBackup copy-back-and-apply-log
–datadir – MySQL Server data directory
–backup-image – Backup file name
–backup-dir – The directory containing the backup file.
The mysqlbackup utility supports on-the-fly compression, a partial backup, incremental backup, and many other features. Details are available here.
The main drawback of this solution is the need to purchase MySQL Enterprise, which can be expensive for small and medium-sized businesses.
Резервирование БД через phpMyAdmin
Зайдем в пункт «Экспорт» верхнего меню и в разделе «Способ экспорта» поставим переключатель в положение «Обычный». После этого откроется ряд дополнительных опций. Из них нас интересует способ компрессии, например ZIP. Остается кликнуть ОК – запустится загрузка дампа с выполнением резервной копии и ее сохранением на локальном компьютере.
Как создать новую базу данных MySQL
Чтобы развернуть бэкап, нужна существующая БД. Если её нет, можно создать новую. Для этого:
Подключитесь к серверу MySQL:
Создайте базу данных:
Вместо db_name введите нужное имя.
Готово, вы создали новую БД, теперь вы можете развернуть на ней резервную копию.
Как восстановить базу данных MySQL из резервной копии
Для восстановления резервной копии используется утилита mysql. Синтаксис:
mysql [опции] [имя_базы] < [путь_и_имя_файла].sql
Например, восстановим базу данных db_name из файла резервной копии db_backup.sql, которая хранится в корневом каталоге. Для этого используем команду:
mysql -uroot -p db_name < /db_backup.sql
Если БД с таким названием не существует, вы увидите ошибку:
ERROR 1049 (42000): Unknown database ‘db_name’:
Посмотреть список баз данных можно при помощи команды:
Как создать бэкап отдельной таблицы
При помощи mysqldump можно создать резервную копию отдельной таблицы. Для этого нужно добавить имя таблицы после названия БД:
mysqldump -u root -p db_name table_name > /db_table_backup.sql
В результате резервная копия таблицы table_name базы данных db_test будет сохранена в файле db_table_backup.sql.
Посмотреть список таблиц в БД можно при помощи команды:
mysqlshow -u root -p table_name
# restore a gzipped mysqldump file without uncompressing
Note: -c
means write output to stdout.
Связанные статьи
- mysqldump в PowerShell портит нелатинские символы при экспорте базы данных (РЕШЕНО) (97%)
- Как в phpMyAdmin поменять настройки экспорта по умолчанию (58.8%)
- MariaDB для Windows (55.8%)
- Как обновить MySQL (55.8%)
- Как обновить MariaDB в Windows (55.8%)
- Ошибки при настройке и установке Apache, PHP, MySQL/MariaDB, phpMyAdmin (RANDOM — 55.4%)
# Restoring a backup of a database or table
db_name
needs to be an existing database;- your authenticated user has sufficient privileges to execute all the commands inside your
filename.sql
; - The file extension
.sql
is fully a matter of style. Any extension would work. - You cannot specify a table name to load into even though you could specify one to dump from. This must be done within
filename.sql
.
Alternatively, when in the MySQL Command line tool, you can restore (or run any other script) by using the source command:
# Backup direct to Amazon S3 with compression
If you wish to make a complete backup of a large MySql installation and do not have sufficient local storage, you can dump and compress it directly to an Amazon S3 bucket. It’s also a good practice to do this without having the DB password as part of the command:
You are prompted for the password, after which the backup starts.
MySQL Backup types
- Logical – the result of a logical backup is a .sql script for database recreation. This script is created using the mysqldump utility. The main advantage of this backup is that it has no third-party dependencies and can be restored on any MySQL Server.
- Physical – this backup is created by copying the database files. Creating and restoring such a backup is faster than a logical backup. We recommend using it for large databases.
- Hot – if a MySQL Server is running on an InnoDB subsystem, then you can create transactional-consistent backups without stopping writing to a MySQL Server
- Partial – the backup is not created for the entire DBMS, but for specific databases or tables. This is useful when different backup strategies are applied to different data.
# Backup database with stored procedures and functions
By default stored procedures and functions or not generated by mysqldump
, you will need to add the parameter --routines
(or -R
):
When using --routines
(opens new window) the creation and change time stamps are not maintained, instead you should dump and reload the contents of mysql.proc
.
# Syntax
- mysqldump -u [username] -p[password] [other options] db_name > dumpFileName.sql /// To Backup single database
- mysqldump -u [username] -p[password] [other options] db_name [tbl_name1 tbl_name2 tbl_name2 …] > dumpFileName.sql /// To Backup one or more tables
- mysqldump -u [username] -p[password] [other options] —databases db_name1 db_name2 db_name3 … > dumpFileName.sql /// To Backup one or more complete databases
- mysqldump -u [username] -p[password] [other options] —all-databases > dumpFileName.sql /// To Backup entire MySQL server
# Parameters
The output of a mysqldump
operation is a lightly commented file containing sequential SQL statements that are compatible with the version of MySQL utilities that was used to generate it (with attention paid to compatibility with previous versions, but no guarantee for future ones). Thus, the restoration of a mysqldump
ed database comprises execution of those statements. Generally, this file
DROP
s the first specified table or viewCREATE
s that table or view- For tables dumped with data (i.e. without the
--no-data
option) LOCK
s the tableINSERT
s all of the rows from the original table in one statement
The presence of the DROP
before CREATE
for each table means that if the schema is present, whether or not it is empty, using a mysqldump
file for its restoration will populate or overwrite the data therein.
Где можно хранить резервные копии
Бэкап можно хранить на любом внешнем носителе. Желательно, чтобы этот носитель не был подключен к основному устройству постоянно.
- Внешний жёсткий диск. Он представляет собой тот же жёсткий диск, что и в компьютере, но подключается с помощью USB-разъема. Это надёжное устройство для хранения информации, однако его нужно беречь от падений.
- Флешка. Флешка менее надёжна, чем жёсткий диск, так как её легче сломать или потерять. Всё-таки в первую очередь она создана для переноса данных. Однако её большими плюсами являются маленькие размеры и простота использования.
- Облачные хранилища. Это популярный метод хранения информации. Плюсами облачного хранения копий являются:
- Автоматическое копирование. Можно настроить сохранение данных, например, раз в сутки или раз в неделю.
- Экономия. Облачное хранение часто обходится дешевле, чем материальные носители, и не требует обслуживания со стороны пользователя.
- Безопасность. Современные облачные технологии обеспечивают высокую информационную безопасность за счёт шифрования данных.
Синтаксис команд
Создать дамп базы данных можно через командную строку с помощью утилиты mysqldump. Ее устанавливают вместе с пакетом MySQL (идет в стандартном комплекте). Возможности одинаковы на Linux и на Windows. Запуск приложения допускается как на локальном, так и на удаленном сервере СУБД. Общий синтаксис:
mysqldump -v -h127.0.0.1 -uroot base -p > /tmp/dump.sql
Здесь мы сделали резервный архив БД под названием base, разместили его в каталоге /tmp. Коннект осуществляется к локальной базе (IP 127.0.0.1) под аккаунтом root. Это был простейший вариант по созданию дампа для резервного копирования базы MySQL. Теперь перейдем к описанию базовых параметров утилиты mysqldump:
- -h – адрес хоста, куда требуется подключаться для создания дампа.
- -u – аккаунт, от имени которого будет выполнено резервирование (важно заранее убедиться в наличии соответствующих прав).
- -p – пароль используемого аккаунта. При работе через скрипт его вводят в формате p12345, в остальных случаях лучше оставить только символ -p, это безопаснее.
Мы перечислили только наиболее востребованные параметры. Если интересует их полный перечень для более глубокого изучения вопроса, он доступен на официальном сайте в документации по ссылке.
Приведем варианты команд для нескольких частных случаев.
Мы сделали переменную DBNAME, где затем разместили значение «имя базы», подлежащей архивации. И только потом запустили утилиту mysqldump, чтобы по завершении передать очередь исполнения архиватору GZIP.
Итог: дамп, размещенный в каталоге /tmp/<имя базы>.sql.gz. Если пользователю надо, чтобы процесс отображался на экране, команда примет вид:
Одновременная архивация двух и более баз
Задача простая, достаточно перечислить названия БД через пробел и использовать опцию -B. Вот так будет выглядеть команда:
mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiplе_bases.sql
Заархивировать все имеющиеся базы
Также можно запускать резервное копирование баз данных MySQL при помощи параметра —all-databases, указывая его вместо имени базы. Пример команды:
mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql
Резервировать только структуру БД
Важно учитывать, что в таком режиме в архив будут сохранены таблицы без каких-либо данных. Режим выбирается при помощи параметра —no-data:
mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql
Создать резервную копию указанной таблицы
В этом случае к имени базы добавляет наименование таблиц. Например, как в команде:
mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql
Перенос прав доступа к СУБД
Система способна выгрузить действующие учетки вместе с актуальными паролями. Такая функция нужна для экспорта СУБД на другой хост без риска утраты доступа к базе. Команда будет выглядеть так:
Чтобы обеспечить работоспособность после переноса при помощи такого дампа, нужно выполнить команду flush privileges (через SQL Shell).
При архивации игнорировать указанную таблицу
Подразумевается выполнение команды с ключом ignore-table:
mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sql
Bottom line
Remember that protecting your data isn’t just about creating a backup file. It will be necessary to ensure the transfer of the backup file to storage and to regularly delete old backups in order to prevent the storage from overflowing.
Whether you create a batch script for an automated MySQL Server backup using /
, or use SqlBak\SQLBackupAndFTP, the main thing is not to forget that you are creating a backup so that you can restore it later. So test your restore scripts from time to time.
Hopefully, these instructions will help you find the right backup procedures that work the best for your needs.
Как сохранить копию нескольких баз данных
Чтобы сохранить копию нескольких баз данных, используйте ключ —databases, а после него через пробел введите названия БД. У вас должно получиться подобное:
mysqldump --databases db_1 db_2 > db_backup.sql
Чтобы сделать бэкап всех баз, используйте ключ —all-databases:
mysqldump --all-databases > db_backup.sql
Как создать бекап базы данных MySQL
Синтаксис команды для создания резервной копии:
mysqldump [опции] [имя_базы] > [путь_и_имя_файла].sql
В результате выполнения будет создан файл .sql.
Для примера создадим бекап базы данных db_name и сохраним файл db_backup.sql в корневой директории. Для этого используем команду:
mysqldump -u root -p db_name > /db_backup.sql
Даже если команда была выполнена успешно, вывода на экран не будет. Результат сразу запишется в файл:
MySQL backup database
# Tranferring data from one MySQL server to another
If you need to copy a database from one server to another, you have two options:
- Store the dump file in the source server
- Copy the dump file to your destination server
- Load the dump file into your destination server
On the source server:
On the destination server, copy the dump file and execute:
If the destination server can connect to the host server, you can use a pipeline to copy the database from one server to the other:
On the destination server
Similarly, the script could be run on the source server, pushing to the destination. In either case, it is likely to be significantly faster than Option 1.
Способы резервного копирования MySQL
Способов сделать копирование базы данных несколько:
- Можно временно выключить MySQL-сервер и скопировать файлы из папки /var/lib/mysql/db/. Само копирование занимает мало времени, однако восстановление сервера может занять часы. Копировать базу нужно регулярно, а постоянное отключение нехорошо для сервиса.
- Можно использовать снэпшоты. Для них не нужно останавливать MySQL-сервер. Однако создание снимка может повлиять на работоспособность БД.
- Для копирования MySQL можно использовать утилиту mysqldump, которая была создана Игорем Романенко. С её помощью создаётся дамп содержимого базы данных. Дамп ― это файл с копией БД. Файл состоит из SQL-команд.
- прост в использовании,
- лучше переносятся между версиями MySQL,
- можно копировать как все имеющиеся БД, так и отдельно выделенные таблицы,
- совместим практически с любой СУБД (не только MySQL),
- можно выгружать данные в форматах CSV и XML.
Недостатки использования дампов:
- медленное создание копии БД (особенно для тяжёлых файлов),
- требует много дискового пространства.
Третий вариант имеет много плюсов. Ниже мы расскажем, как сделать резервную копию MySQL с помощью утилиты mysqldump.
Резервирование под любой учетной записью
Примеры, приведенные выше, были рассчитаны на подключение к БД при помощи пользователя root. На практике же процедуру выполняют и под другими учетками. Главное, чтобы у выбранного пользователя были права для доступа к базе, дамп которой планируется создать. Можно создать отдельный аккаунт с ограниченными правами для выполнения архивации. Выполним это командой SQL:
Здесь мы создали учетку backup, задали пароль backup123. Прав у пользователя будет достаточно, чтобы подключиться к БД на локальном сервере. Чтобы не вводить вручную при каждом запуске одни и те же параметры, можно записать файл конфигурации:
Теперь команда примет «облегченный» вид:
mysqldump base > /tmp/dump.sql
# mysqldump from a remote server with compression
In order to use compression over the wire for a faster transfer, pass the --compress
option to mysqldump
. Example:
Important: If you don’t want to lock up the source db, you should also include --lock-tables=false
. But you may not get an internally consistent db image that way.
To also save the file compressed, you can pipe to gzip
.
# Specifying username and password
If you need to specify the password on the command line (e.g. in a script), you can add it after the -p
option without a space:
If you password contains spaces or special characters, remember to use escaping depending on your shell / system.
Optionally the extended form is:
(Explicity specifying the password on the commandline is Not Recommended due to security concerns.)
Типовые проблемы
Иногда можно столкнуться с ошибкой Incorrect key file for table, которая выглядит так:
mysqldump: Error 1034: Incorrect key file for table '<table name>'; try to repair it when dumping table `<table name>` at row: xxxxxx
Причин может быть несколько. Например, обнаружена логическая ошибка таблицы в БД или закончилось место на накопителе, где система сохраняет временные данные. Соответственно, для решения проблемы нужны разные подходы. Так, первым делом желательно проверить объем места на диске. В файле конфигурации, размещенном по пути /etc/my.cnf, указана опция tmpdir, которая определяет папку, куда система сохраняет временные таблицы.
Если ее нет, тогда по умолчанию будет использован путь /tmp. Проверить, достаточно ли места для создания дампа, можно командой:
Иная ситуация, если повреждена таблица. Ее необходимо восстановить, но решение зависит от типа проблемного участка базы.
MyISAM. Действия пользователя следующие. В командной строке SQL вводим команду:
> REPAIR TABLE <table name> USE_FRM;
После нее повторяем попытку создания резервной копии.
INNODB. Откроем файл конфигурации СУБД:
innodb_force_recovery = 1
Перезапустим сервер БД:
systemctl restart mysql
При повторе ошибки изменим значение innodb_force_recovery на 2. Если проблема все еще сохраняется, то на 3, и так вплоть до 6, пока резервирование не пройдет успешно. Важно учитывать, что применяемый параметр иногда приводит к потере данных. Если пришлось поднять его значение выше 2, стоит вручную проверить сохранность ценной информации после завершения резервирования.
Восстановление баз данных из резервной копии без phpMyAdmin в Windows
Для этого откройте командную строку Windows. Нажмите комбинацию WIN+x, затем выберите Windows PowerShell. Чтобы выйти из PowerShell и перейти в командную строку, выполните:
cmd
Затем смените текущую папку (укажите путь до подпапки bin внутри папки, куда установлена MySQL или MariaDB — у вас это может быть другой путь):
cd C:\Server\bin\mysql-8.0\bin\
И запустите команду вида:
mysql -uroot < C:\путь\до\файла\резервной_копии.sql
mysql -uroot < C:\Users\Alex\Downloads\localhost.sql
# Creating a backup of a database or table
Create a snapshot of a whole database:
Create a snapshot of multiple databases:
Create a snapshot of one or more tables:
Create a snapshot excluding one or more tables:
The file extension .sql
is fully a matter of style. Any extension would work.
How to create a logical MySQL backup
Simple backup of MySQL database using
When installing a MySQL server, the main backup utility is automatically installed – . This command-line utility creates a backup file with a set of commands to recreate the database.
It is easy to restore a MySQL database from a file created by mysqldump. A small database can be restored by copying the contents of the file into the SQL editor of a graphic IDE (for example, in MySQL Workbench) and running it from there. However, it is preferable to restore a large database using the mysql command utility.
Typically, is located in the MySQL Server installation directory. For example, for MySQL 8.0, the path to the directory is C:\Program Files\MySQL\MySQL Server 8.0\bin\. It is advisable to add this directory to the global PATH variable.
mysqldump --user root --password --all-databases > all-databases.sql
mysql --user root --password mysql < all-databases.sql
Often you need to backup not the entire server, but a specific database. To dump a specific database, use the name of the database instead of the –all-database parameter.
mysql --user root --password [db_name] < [db_name].sql
The fact that the backup is created in an open format has two important advantages:
- You can restore a dump on a server with a different version. Thus, switching to another version of MySQL can be done without worrying about backups.
- You can manually edit the .sql file if you need to change something before restoring.
Backup using
MySQL Workbench is a tool for visual design and it works with a MySQL database. This application also allows you to create logical backups of a MySQL database.
- Go to the Administration tab, on the Navigation panel (on the left by default)
- Select Data Export
- From the Data Export tab in the Tables To Export section, select the databases and tables that will be added to the backup file
- From the Export Option section, select the format for the exported data. Either each table will be exported to a separate .sql file, or one common .sql file will be created.
Exporting each table to a separate file can be useful if you need to restore not the entire database, but some specific tables. But, as a rule, this is not necessary, and it is easier to work with one backup file.
- Press the Export button to create a backup file.
The export section is essentially a graphic interface to the utility. Although you cannot automate the creation process using MySQL Workbench, this tool is convenient for manually creating a backup and for migrating data.
Also, you can use MySQL Workbench as a parameter constructor for . When you click the Export button, a log of the export execution will be displayed, in which there will be a
command with the parameters specified in the interface.
Dump recovery using MySQL Workbench
- Go to the Administration tab, on the Navigation panel (on the left by default)
- Select Data Import\Restore
- Select the source of the recovery Dump Project Folder or Self-Contained File, depending on what you selected at the backup stage
- If you used a Dump Project Folder, then you can select the databases and tables that need to be restored.
- If you use a Self-Contained File, then before restoring, you must select the schema into which you want to restore the dump. If you restore a dump to a server where the required schema does not exist, you can create it by clicking the New button.
- Press the Start Import button.
Backup using
SQLBackupAndFTP is a popular utility for backing up and restoring MySQL, SQL Server, and PostgreSQL. This specialized utility is designed for the automated creation of backups and sending them to storage.
This utility not only simplifies backups but also allows you to perform a one-click restore, as well as automate the restore, which can be very useful for synchronizing data or creating a test server.
- Connect to your MySQL Server by selecting the connection type MySQL Server (TCP/IP) or MySQL Server (phpMyAdmin)
- Next, from the Select Databases section, select the databases you want to back up
- The next step is to choose where to store your backups. It can be a local or network folder, FTP, or popular cloud storage services such as Dropbox, Google Drive, Amazon S3, Azure, and others. Please note, you can specify several places where backups will be sent.
- In the Schedule Backups section, create a backup schedule
- To receive email notifications about completed backups, set the Send Confirmation option
These are the basic settings, but you can also adjust the compression level of your backups, set a password for them (using the encryption option), specify a temporary folder to do the work, set up scripts to run, and much more.
- Find the backup you want to restore in the History & Restore section on the right side of the application
- Click on three dots and select “Restore from backup…”
- Make sure the correct backup is selected and click the Restore button
- Specify the name of the database where you want to restore the backup and password, if necessary, then click Restore and confirm the launch
The main feature of SQLBackupAndFTP is that this utility not only provides a good interface for creating a backup file, but also allows you to configure all the accompanying steps: compression, encryption, cloud storage, and fail notifications.
Backup using
Typically, a DBA has to maintain more than one database. A good solution for creating and maintaining backups on multiple servers is SqlBak.
This is an agent-based application for creating backups, compressing them, and then sending them to storage. All necessary settings are done in the browser.
- Run SqlBak App and connect to your MySQL Server using the server type MySQL Server (TCP/IP) or MySQL Server (phpMyAdmin)
- Go to the Dashboard page and click the Add New Job button
- In the window that opens, select the server you need, set the Backup Job in the Job Type field and click Create Job
- In the Select DBMS connection section, select the connection to your MySQL Server. If you have only one connection on the server, it will be selected automatically.
- In the Select databases section, mark all databases that you want to back up
- Now you need to configure the locations where your backups will be stored. These can be local or network folders, FTP, or cloud storage like OneDrive, Amazon S3, S3 Compatible (like Google Cloud, Wasabi), etc.
- Create a schedule to start the job
- Specify your email address for job success/fail notifications
- Click the Save & Exit button to save the settings and start the job
These are the essential settings, but you can also configure the encryption of your backups, compression, running scripts, and much more.
- Go to the Dashboard page and click on the Restore icon in the LAST RUN column to restore the last backup. Alternatively, go to the backup job setup page, find the backup you need in the Backup history section and click the Restore icon in the Actions column
- Select the backup you need and click the Restore button
- Check the settings and start the restore
Backup using
- Select the database in the left panel
- Go to the Export tab
- To back up the entire server, select Quick. If you want to backup specific databases, then you must select Custom to see additional settings.
- Select SQL format, which is the best for backup.
- Click GO.
Выводы
Команда mysqldump обладает широким функционалом, позволяющим резервировать базы в разной комбинации. При желании ее легко заменить удобным инструментом с графическим интерфейсом phpMyAdmin. Эксперименты желательно проводить на пустой БД, где нет ценной информации. Например, можно арендовать соответствующие ресурсы у Timeweb Cloud.