Backup and restore MySQL databases for Windows

Operation confirmation environment

MySQL

• MySQL 8.0 Community Edition

Windows

• Windows 11

Required environment

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

• MySQL database must be installed
• The database to be backed up already exists
• Configure environment variables and allow you to run MySQL from the command prompt

About Backup Types

I won't go into detail here, but MySQL has the following types of backups:

Backing up a database with a GUI (MySQL Workbench)

If you want to operate securely in the GUI without using commands, you can perform a backup in MySQL Workbench. The backup type performed in MySQL Workbench is "mysqldump".

Launch MySQL Workbench.

Select the connection with the database you want to back up.

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

The Data Export screen opens. The screen is quite large, so expand the window until you see the "Start Export" button in the lower right corner.

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

In "Objects to Export", you can choose whether to back up procedures, triggers, etc. as well. This item is optional.

In "Export Options", you can choose from the following two options:

It doesn't matter which one you output. If you choose above, it is easy because you can easily remove it if you want to restore only a specific table. The following selections are output to a single file, making it easier to move and manage files as a database unit. This time I have selected the following.

Once configured, click the Start Export button at the bottom right to start exporting.

The export starts and completes.

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

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

Restoring a database with a GUI (MySQL Workbench)

Open MySQL Workbench, select your connections, select the Administration tab from 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 bottom right corner.

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

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

After specifying, click the "Start Import" button in the lower right corner to start the recovery.

Make sure your data is back when the restoration is complete.

Create an account for database backup via command

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

The permissions required for backup are as follows, but 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: Run it with 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 backup destination

The permissions for the folder where the backup file is stored must be set to the same permission as the permission to execute the command. If you want to run commands with the Administrators permission, you don't need to set any additional permissions for the folder.

Backing up a database with the command (mysqldump)

There are several command backups, but this time I will back up with the old and stable "mysqldump".

Right-click on the Start menu and launch Terminal (Command Prompt). Please note that it is not a "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 will be the same character code as when backed up in the GUI. This allows you to import them into the GUI as well.
• Do not put a space between the password field -p and the password.
• If you also want to output tablespace information, --no-tablespaces please uncheck . In that case, the permissions of PROCESS the execution user are required.

A backup file will be created in the specified location.

Restoring a database with the command (mysqldump)

The files backed up with mysqldump are in a format that can be run in SQL, so you only need to run the target SQL. I think the restore is often done manually, so it's okay to run it on the root account.

If you want to run it from a command, run it at the command prompt instead of PowerShell. This is < because PowerShell does not allow symbols.

Run the following command:

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

example

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

Automatic database backup

For Windows, it is common to use a task scheduler. Start by creating a batch file. Create a bat file, enter the contents as they were backed up with commands, and save them. Save character codes with Shift-JIS. The location and file name of the batch file can be arbitrary.

Register with the task scheduler. Right-click on the Start menu and select "Manage Computer".

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

Set the General tab. The "name" will be displayed in the task list, so put a name that is easy to understand. In the security options, check "Run whether the user is logged on or not".

The Triggers tab sets when the backup should be performed. This setting should be set according to your operation.

In the Operations tab, set the batch file you just created to launch.

Once you have set each of them, confirm them with the OK button.

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

Now check if the backup will run at the specified time.