Бекап базы данных MySql на Windows

Несмотря на надёжность современных компьютеров и серверов, время от времени случаются поломки. А значит пользователи постоянно рискуют потерять все свои данные. Чтобы решить проблему потери информации, специалисты рекомендуют делать резервное копирование MySQL.

Резервное копирование, или бэкап ― это создание копии файлов и папок на дополнительном носителе информации. Резервное копирование позволяет восстанавливать повреждённые данные на основном носителе. В этой статье мы расскажем, как сделать бэкап MySQL.

Где можно хранить резервные копии

Бэкап можно хранить на любом внешнем носителе. Желательно, чтобы этот носитель не был подключен к основному устройству постоянно.

1. Внешний жёсткий диск. Он представляет собой тот же жёсткий диск, что и в компьютере, но подключается с помощью USB-разъема. Это надёжное устройство для хранения информации, однако его нужно беречь от падений.
2. Флешка. Флешка менее надёжна, чем жёсткий диск, так как её легче сломать или потерять. Всё-таки в первую очередь она создана для переноса данных. Однако её большими плюсами являются маленькие размеры и простота использования.
3. Облачные хранилища. Это популярный метод хранения информации. Плюсами облачного хранения копий являются:

• Автоматическое копирование. Можно настроить сохранение данных, например, раз в сутки или раз в неделю.
• Экономия. Облачное хранение часто обходится дешевле, чем материальные носители, и не требует обслуживания со стороны пользователя.
• Безопасность. Современные облачные технологии обеспечивают высокую информационную безопасность за счёт шифрования данных.

Способы резервного копирования MySQL

Способов сделать копирование базы данных несколько:

1. Можно временно выключить MySQL-сервер и скопировать файлы из папки /var/lib/mysql/db/. Само копирование занимает мало времени, однако восстановление сервера может занять часы. Копировать базу нужно регулярно, а постоянное отключение нехорошо для сервиса.
2. Можно использовать снэпшоты. Для них не нужно останавливать MySQL-сервер. Однако создание снимка может повлиять на работоспособность БД.
3. Для копирования MySQL можно использовать утилиту mysqldump, которая была создана Игорем Романенко. С её помощью создаётся дамп содержимого базы данных. Дамп ― это файл с копией БД. Файл состоит из SQL-команд.

• прост в использовании,
• лучше переносятся между версиями MySQL,
• можно копировать как все имеющиеся БД, так и отдельно выделенные таблицы,
• совместим практически с любой СУБД (не только MySQL),
• можно выгружать данные в форматах CSV и XML.

Недостатки использования дампов:

• медленное создание копии БД (особенно для тяжёлых файлов),
• требует много дискового пространства.

Третий вариант имеет много плюсов. Ниже мы расскажем, как сделать резервную копию MySQL с помощью утилиты mysqldump.

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

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

mysqldump [опции] [имя_базы] > [путь_и_имя_файла].sql

В результате выполнения будет создан файл .sql.

Для примера создадим бекап базы данных db_name и сохраним файл db_backup.sql в корневой директории. Для этого используем команду:

mysqldump -u root -p db_name > /db_backup.sql

Даже если команда была выполнена успешно, вывода на экран не будет. Результат сразу запишется в файл:

MySQL backup database

Как создать бэкап отдельной таблицы

При помощи 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

Как сохранить копию нескольких баз данных

Чтобы сохранить копию нескольких баз данных, используйте ключ —databases, а после него через пробел введите названия БД. У вас должно получиться подобное:

mysqldump --databases db_1 db_2 > db_backup.sql

Чтобы сделать бэкап всех баз, используйте ключ —all-databases:

mysqldump --all-databases > db_backup.sql

Как создать новую базу данных MySQL

Чтобы развернуть бэкап, нужна существующая БД. Если её нет, можно создать новую. Для этого:

1. Подключитесь к серверу MySQL:

2. Создайте базу данных:

   Вместо 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’:

Посмотреть список баз данных можно при помощи команды:

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 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.

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:

1. 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.
2. 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.

1. Go to the Administration tab, on the Navigation panel (on the left by default)
2. Select Data Export
3. From the Data Export tab in the Tables To Export section, select the databases and tables that will be added to the backup file
4. 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. 

5. 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

1. Go to the Administration tab, on the Navigation panel (on the left by default)
2. Select Data Import\Restore
3. Select the source of the recovery Dump Project Folder or Self-Contained File, depending on what you selected at the backup stage
4. If you used a Dump Project Folder, then you can select the databases and tables that need to be restored.
5. 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.
6. 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.

1. Connect to your MySQL Server by selecting the connection type MySQL Server (TCP/IP) or MySQL Server (phpMyAdmin)
2. Next, from the Select Databases section, select the databases you want to back up
3. 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.
4. In the Schedule Backups section, create a backup schedule
5. 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.

1. Find the backup you want to restore in the History & Restore section on the right side of the application
2. Click on three dots and select “Restore from backup…”
3. Make sure the correct backup is selected and click the Restore button
4. 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.

1. Run SqlBak App and connect to your MySQL Server using the server type MySQL Server (TCP/IP) or MySQL Server (phpMyAdmin)
   
2. Go to the Dashboard page and click the Add New Job button
3. In the window that opens, select the server you need, set the Backup Job in the Job Type field and click Create Job
   
4. 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.
   
5. In the Select databases section, mark all databases that you want to back up
6. 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.
   
7. Create a schedule to start the job
8. Specify your email address for job success/fail notifications
9. 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.

1. 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
   
2. Select the backup you need and click the Restore button
3. Check the settings and start the restore
   

Backup using

1. Select the database in the left panel
2. Go to the Export tab
3. To back up the entire server, select Quick. If you want to backup specific databases, then you must select Custom to see additional settings.
4. Select SQL format, which is the best for backup.
5. Click GO.

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:

1. Backups are portable only to other machines that have identical or similar hardware characteristics.
2. 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.

1. Find MySQL Server data directory. To do this, run the following command:

select @@datadir;

The easiest way to do this is through MySQL Workbench.

2. 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
     

3. Copy or pack the contents of a MySQL Server data directory to another location
4. 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_nameand 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

1. Stop MySQL Service
2. Empty MySQL Server data directory completely
3. Copy the saved data to the data directory
4. 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.

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.

mysqldump [options] > file.sql

mysqldump -u root -p database_name > database_name.sql

mysqldump database_name > database_name.sql

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

mysqldump -u root -p --all-databases > all_databases.sql

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql";
done

mysqldump database_name | gzip > database_name.sql.gz

mysqldump database_name > database_name-$(date +%Y%m%d).sql

mysqld database_name < file.sql

mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql

mysql --one-database database_name < all_databases.sql

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

4.4.1 Резервное копирование баз данных

Поскольку таблицы MySQL хранятся в виде файлов, то резервное копирование
выполняется легко. Чтобы резервная копия была согласованной, выполните на
выбранных таблицах LOCK TABLES, а затем FLUSH TABLES для этих таблиц (см.
разделы section 6.7.2 Синтаксис команд LOCK TABLES/UNLOCK TABLES и see section 4.5.3 Синтаксис команды FLUSH). При этом
требуется блокировка только на чтение; поэтому другие потоки смогут
продолжать запросы на таблицах в то время, пока будут создаваться копии
файлов из каталога базы данных. Команда FLUSH TABLE обеспечивает гарантию
того, что все активные индексные страницы будут записаны на диск прежде,
чем начнется резервное копирование.

Если есть необходимость провести резервное копирование на уровне SQL, то
можно воспользоваться SELECT INTO OUTFILE или BACKUP TABLE (см. разделы
section 6.4.1 Синтаксис оператора SELECT и see section 4.4.2 Синтаксис BACKUP TABLE).

Существует еще один способ создать резервную копию базы данных —
использовать программу mysqldump или сценарий mysqlhotcopy (см. разделы
section 4.8.5 mysqldump, Получение дампов данных и структуры таблицы и see section 4.8.6 mysqlhotcopy, Копирование баз данных и таблиц MySQL). Для этого нужно выполнить следующие действия:

1. Сделать полное резервное копирование баз данных:

   shell> mysqldump --tab=/path/to/some/dir --opt --full
   или
   shell> mysqlhotcopy database /path/to/some/dir

   Можно также просто скопировать табличные файлы (файлы `*.frm', `*.MYD' и
   `*.MYI') в тот момент, когда сервер не проводит никаких обновлений. Этот
   метод используется в сценарии mysqlhotcopy.

2. 
   Если mysqld выполняется, остановить его, и затем запустить с опцией
   --log-update[=file_name] (see section 4.9.3 Журнал обновлений (update)). В файлах журнала
   обновлений находится информация, необходимая для того, чтобы повторить
   в базе данных последовательность изменений, внесенных с момента
   выполнения mysqldump.

Если дело дошло до восстановления, сначала надо попробовать восстановить
таблицы с помощью REPAIR TABLE или myisamchk -r — это должно сработать в
99,9% случаев. Если myisamchk не даст результата, попробуйте применить
следующую процедуру (эти действия применимы только в случае, если MySQL
запускался с --log-update (see section 4.9.3 Журнал обновлений (update))):

1. Восстановите исходный вариант по копии, сделанной в mysqldump.

2. Выполните следующую команду, чтобы повторить обновления из бинарного
   журнала:

   shell> mysqlbinlog hostname-bin.[0-9]* | mysql

   Если используется журнал обновлений, то можно применить:

   shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

ls используется для того, чтобы расположить все файлы журнала обновлений в
правильном порядке.

Если в системе, где выполняется резервное копирование, возникают проблемы
с производительностью, то решить их можно, установив репликацию и выполняя
резервное копирование на подчиненном сервере вместо головного (see section 4.10.1 Введение).

Пользователи файловой системы Veritas могут поступить следующим образом:

1. Из клиента (или Perl) выполнить: FLUSH TABLES WITH READ LOCK.

2. Из другого shell выполнить: mount vxfs snapshot.

3. Из первого клиента выполнить: UNLOCK TABLES.

4. Скопировать файлы из образа.

5. Демонтировать образ.

4.8.5 mysqldump, Получение дампов данных и структуры таблицы

• 4 Администрирование баз данных
  
  • 4.8 Клиентские сценарии и утилиты MySQL
    
    • 4.8.1 Обзор клиентских сценариев и утилит
      
    • 4.8.2 mysql, Утилита командной строки
      
    • 4.8.3 mysqladmin, Администрирование MySQL-сервера.
      
    • 4.8.4 Использование mysqlcheck для сопровождения и аварийного восстановления таблиц.
      
    • 4.8.5 mysqldump, Получение дампов данных и структуры таблицы
    • 4.8.6 mysqlhotcopy, Копирование баз данных и таблиц MySQL
      
    • 4.8.7 mysqlimport, импорт данных из текстовых файлов
      
    • 4.8.8 Просмотр баз данных, таблиц и столбцов
      
    • 4.8.9 perror, разъяснение кодов ошибок
      
    • 4.8.10 Как запускать SQL-команды из текстового файла