Backing Up and Restoring MySQL Databases (Windows Version)

Operating environment

MySQL

• MySQL 8.0 Community Edition

Windows

• Windows 11

Prerequisites

MySQL

• MySQL 8.0

Windows

• Windows 11
• Windows 10

Windows Server

• Windows Server 2022
• Windows Server 2019
• Windows Server 2016
• Windows Server 2012 R2

precondition

• A database for MySQL is installed.
• The database to be backed up already exists.
• You have set environment variables so that you can run mysql from the command prompt.

What are the types of backups?

Although not discussed in detail here, MySQL has the following types of backups:

Backing up databases with the GUI (MySQL Workbench)

If you want to operate securely in the GUI without using commands, you can back up with MySQL Workbench. The type of backup performed by MySQL Workbench will be "mysqldump".

Start MySQL Workbench.

Select the connection that contains the database you want to back up.

With the database to be backed up, select the Administration tab from the Navigator on the left, and then select Data Export.

The Data Export screen opens. The screen is reasonably wide, so expand the window until you can see the "Start Export" button at the bottom right.

First, select the databases that you want to back up. You can also select more than one.

In "Objects to Export", you can choose to back up procedures and triggers as well. This field is optional.

In "Export Options", you can select the following two options.

It doesn't matter which way you output. The above selection is easy to remove if you want to restore only a specific table. The selection below outputs to a single file, making it easier to move and manage files as a database unit. In this case, I have selected the following.

After setting the settings, click the "Start Export" button in the lower right corner to start the export.

The export starts and completes.

If you output it as a single file, it will look like this:

If you export to a folder, it will look like this:

Restoring a database with the GUI (MySQL Workbench)

Open MySQL Workbench, select a connection, select the Administration tab from the Navigator on the left, and select Data Import/Restore. As it says "Import/Restore", you can create a new database from the exported file or overwrite the original database.

Expand the window until you see the "Start Import" button in the lower right corner.

Select "Import Options" according to the exported file format. Since I exported as a single file this time, I selected "Import from Self-Contained File" below and specified the exported file.

Select the schema (database) to which you want to restore. You can restore to an existing database or restore it as a new database from the "New" button on the right.

After specifying the specifications, click the "Start Import" button at the bottom right to start the restoration.

Once the restoration is complete, make sure that your data is back.

Create an account for database backup by command

There is no problem if you execute a single command and back it up, but for example, if you prepare the command as a file in advance and execute it, you need to write the password in plain text, so it is safer to create a separate account for backup execution.

Some of the permissions required for backup may increase or decrease depending on the information required.

• EVENT
• LOCK TABLES
• SELECT
• SHOW VIEW

If you create it with a command, it will look like this. Use the MySQL command-line tool.

create user '<ユーザー名>'@'<ホスト名>' identified by '<パスワード>';
GRANT EVENT, LOCK TABLES, SELECT, SHOW VIEW ON <データベース名>.* TO '<ユーザー名>'@'<ホスト名>';
flush privileges;

example

create user 'backup_user'@'localhost' identified by 'password';
GRANT EVENT, LOCK TABLES, SELECT, SHOW VIEW ON test_database.* TO 'backup_user'@'localhost';
flush privileges;

Folder permissions for the backup destination

The permissions of the folder where the backup file is saved must be set in the same way as the permissions for executing the command. If you run the command with Administrators privileges, you do not need to set any additional permissions on the folder.

Backing up a database with the command (mysqldump)

There are several backup by command, but this time we will back up with "mysqldump", which has been used for a long time and is stable.

Right-click on the Start menu to launch Terminal (Command Prompt). Note that it is not "MySQL 8.0 Command Line Client".

You can back it up with the following command:

mysqldump -u <ユーザー名> -p<パスワード> --no-tablespaces --single-transaction --triggers --routines --events --default-character-set=utf8 <データベース名> > "<バックアップファイルパス>"

example

mysqldump -u backup_user -ppassword --no-tablespaces --single-transaction --triggers --routines --events --default-character-set=utf8 test_database > "C:\Temporary\Backup.sql"

• --default-character-set=utf8 If you specify the same character code as when backing up in the GUI, it will be the same as when backing up in the GUI. This way, you can also import it in the GUI.
• Do not include a space between the password field and -p the password.
• If you also want to output tablespace information, --no-tablespaces remove . In that case, the permissions of the running user must be . PROCESS

A backup file will be created in the specified location.

Restoring a database with the command (mysqldump)

The files backed up by mysqldump are in a format that can be executed in SQL, so all you have to do is execute the target SQL. I think that most restores are done manually, so there is no problem if you run it as the root account.

If you want to run it from a command, run it at the command prompt instead of PowerShell. This is because the symbol cannot be used in < PowerShell.

Run the following command:

mysql -u root -p<root のパスワード> <データベース名> < <バックアップした SQL のファイルパス>

example

mysql -u root -ppassword test_database < C:\Temporary\Backup.sql

Automated database backups

For Windows, it is common to use the Task Scheduler. First, create a batch file. Create a bat file, enter the contents as they were backed up by the command, and save it. Character encoding is saved with Shift-JIS. The location and file name of the batch file are arbitrary.

Register with Task Scheduler. Right-click on the Start menu and select Computer Management.

From the menu on the left, select Computer Management > System Tools > Task Scheduler > Task Scheduler Library. Select "Create Task..." from the menu on the right.

Configure the General tab. "Name" will appear in the list of tasks, so enter a name that is easy to understand. In the security options, check "Run regardless of whether the user is logged on or not".

In the "Trigger" tab, you can set when to perform the backup. Set this setting according to your operation.

In the "Operation" tab, set it to launch the batch file you just created.

After setting each one, confirm with the OK button.

Enter your password to register to run under the specified account.

After that, check if the backup will be executed at the specified time.