MySQL Backup Strategies: How to Backup All and Multiple Databases

This article will outline two approaches to performing regular database backups. The first method involves backing up databases specified in a list, while the second entails bulk backing up of all databases. These procedures can be executed on both Linux and Windows platforms using dbForge Studio for MySQL. The software facilitates the execution of routine tasks through both command prompt and PowerShell interfaces.

Contents

• Backup MySQL databases using mysqldump
  • Dump multiple MySQL databases
  • Dump all MySQL databases
• Backup databases using GUI tool for MySQL through PowerShell script
  • Backing up all MySQL databases
  • Backing up multiple databases from a list
  • Backing up the databases by a mask
  • Creating and using bat file in the command-line interface
• Conclusion

When having only several databases to manage, regular backup operations can be done quite easily and seamlessly, either with the help of a few simple scripts or by configuring a tool that will perform the backup creation automatically. But sometimes, the situation is more complicated. When, for instance, there are hundreds of databases, backing up each one manually can turn out to be quite a time-consuming task. So, finding a solution that would allow backing up all databases, or only the required ones, without affecting the server performance is highly important for developers and DBAs.

Backup MySQL databases using mysqldump

Let us begin with figuring out how to use the mysqldump command-line utility during the backup creation process. The tool generates SQL statements to reproduce the database’s structure and content. It provides a straightforward and efficient means to create backups, allowing for the preservation of data in case of unforeseen circumstances like hardware failures or accidental deletions. Additionally, mysqldump is rather versatile and can be customized to include or exclude specific databases or tables, providing flexibility in the backup process. Lastly, since this utility generates SQL scripts, it ensures that the backups are in a universally recognized format, making it easy to restore the database on different MySQL installations.

Dump multiple MySQL databases

In order to perform a dump of multiple MySQL databases using mysqldump, you’ll need to specify each database individually in a single command. The exact command may vary slightly depending on whether you have authentication set up or not:

# Authentication disabled
mysqldump --databases database1 database2 database3 > my_backups.sql

# Authentication enabled
mysqldump -u username -p --databases database1 database2 database3 > my_backups.sql

• username should be replaced with your MySQL username.
• database1, database2, database3, etc., should be replaced by the actual names of the databases you wish to back up.
• backup.sql is the name of the backup file. You can change it to whatever you prefer.

Dump all MySQL databases

The syntax does not differ very much when it comes to backing up all the databases. The only thing you have to change in your command is to use the --all-databases flag instead of listing the names of the databases:

# Authentication disabled
mysqldump --all-databases > my_backups.sql

# Authentication enabled
mysqldump -u username -p --all-databases > my_backups.sql

Note: When using the --all-databases flag, keep in mind that the internal mysql database is included as well.

Backup databases using GUI tool for MySQL through PowerShell script

In this section, we are going to learn more about automated backups with the help of endless capabilities of PowerShell scripts. Discover how you can optimize and simplify your backup creation procedures by harnessing the versatility and automation potential of PowerShell scripting.

Backing up all MySQL databases

To accomplish the task, we need to create a script.

1. Open a plain text editor such as a Notepad.
2. Type in the following code:

Set-Location -Path "C:\Program Files\MySQL\MySQL Server 5.7\bin\" -PassThru

.\mysql.exe --host=localhost --user=root --password=root --skip-column-names --execute="SELECT s.SCHEMA_NAME FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME NOT IN ('
mysql', 'information_schema', 'sys', 'performance_schema')" | Out-File "D:\backup\all_databases_backup\PowerShell\alldatabases.txt"

foreach($DBname in Get-Content "D:\backup\all_databases_backup\PowerShell\alldatabases.txt")
{Write-Host $DBname
&"C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com" /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:$DBname /outputfile:"D:\backup\all_databases_backup\PowerShell\all_DB_backup\$DBname.sql"}

Where:

C:\Program Files\MySQL\MySQL Server 5.7\bin\ – MySQL server path.

D:\backup\all_databases_backup\PowerShell\all_DB_backup – location at your computer to store the output files.

C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com – dbForge Studio for MySQL path.

3. Assign your own values to the User Id, Password, Host and Port parameters.
4. ave the file with the *.ps1 file extension.
5. S1 file extension (for example, all_DB_backup.ps1).

Please note that we do not intend to explain all aspects of the command-line syntax within this article. For more information, refer to our documentation center.

Script Execution

After the backup has been completed successfully, a folder all_DB_backup with SQL files will be created.

Backing up multiple databases from a list

To accomplish the task, we will need the following set of files:

1. A TXT file with a list of databases to be backed up named DB_list.txt.
2. A PS file with a script.

First, let’s specify the databases we want to back up.

1. Open a new Notepad document and make a list of databases.
2. Save the new TXT file as DB_list.txt.

Please note, that the list of databases should be written in a columnar form without any delimiters, each database in a new line.

Then, we need to create a script in a PS1 file.

1. Open a plain text editor such as Notepad.
2. Type in the following code:

foreach($DBname in Get-Content "D:\backup\all_databases_backup\PowerShell\DB_list.txt")
{Write-Host $DBname
&"C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com" /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:$DBname /outputfile:"D:\backup\DB_databases_backup\PowerShell\DB_list_backup\$DBname.sql"}

Where:

D:\backup\DB_databases_backup\PowerShell\DB_list_backup – location at your computer to store output files.

C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com – dbForge Studio for MySQL path.

3. Assign your own values to the User ID, Password, Host, and Port parameters.
4. Save the file with the *.ps1 file extension (for example, DB_list_backup.ps1).

Script Execution

After the backup has been completed successfully, a folder DB_list_backup with SQL files will be created.

Backing up the databases by a mask

To accomplish the task, we need to create a script.

1. Open a plain text editor such as Notepad.
2. Type in the following code:

Set-Location -Path "C:\Program Files\MySQL\MySQL Server 5.7\bin\" -PassThru

.\mysql.exe --host=localhost --user=root --password=root --skip-column-names --execute="SELECT s.SCHEMA_NAME FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema') and s.SCHEMA_NAME like '%$args%' " | Out-File "D:\backup\all_databases_backup\PowerShell\DB_by_mask.txt"

foreach($DBname in Get-Content "D:\backup\all_databases_backup\PowerShell\DB_by_mask.txt")
{Write-Host $DBname
&"C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com" /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:$DBname /outputfile:"D:\backup\all_databases_backup\PowerShell\DB_by_mask_backup\$DBname.sql"}

Where:

C:\Program Files\MySQL\MySQL Server 5.7\bin\ – server path.

D:\backup\all_databases_backup\PowerShell\DB_by_mask_backup – location at your computer to store output files.

C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com – dbForge Studio for MySQL path.

3. Assign your own values to the User ID, Password, Host, and Port parameters.
4. Save the file with the *.ps1 file extension (for example, DB_by_mask_backup.ps1).

Script Execution

You need to execute the script with an extra parameter. For example, DB_by_mask_backup.ps1 test_DB_name.

After the backup has been completed successfully, a folder DB_by_mask_backup with SQL files will be created.

Creating and using bat file in the command-line interface

You can also create an executable BAT file to run the chief task via the command-line interface.

To create a BAT file:

1. Open a plain text editor such as Notepad.
2. Type in the following code:

SetLocal EnableExtensions EnableDelayedExpansion

Set Backup="C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"

goto DB_backup%1

rem backup of all databases
:DB_backup
:DB_backup1

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" --host=localhost --user=root --password=root --skip-column-names --execute="SELECT s.SCHEMA_NAME FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')" > "D:\backup\all_databases_backup\alldatabases.txt"

FOR /F "eol=; tokens=1,2* delims=, " %%e in (alldatabases.txt) do ( 
%backup%  /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:%%e /outputfile:"D:\backup\all_databases_backup\all_DB_backup\%%e.sql"
)
goto finish

rem backup databases from the list
:DB_backup2

FOR /F "eol=; tokens=1,2* delims=, " %%e in (DB_list.txt) do ( 
%backup%  /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:%%e /outputfile:"D:\backup\all_databases_backup\DB_list_backup\%%e.sql"
)
goto finish

rem backup databases by mask
:DB_backup3
set mask='%%% %%2%%%'
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" --host=localhost --user=root --password=root --skip-column-names --execute="SELECT s.SCHEMA_NAME FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')  AND s.SCHEMA_NAME LIKE %mask%" > "D:\backup\all_databases_backup\DB_by_mask.txt"

FOR /F "eol=; tokens=1,2* delims=, " %%e in (DB_by_mask.txt) do ( 
%backup%  /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:%%e /outputfile:"D:\backup\all_databases_backup\DB_by_mask_backup\%%e.sql"
)
goto finish

:finish

Where:

C:\Program Files\MySQL\MySQL Server 5.7\bin\ – server path.

D:\backup\all_databases_backup – location at your computer to store output files.

C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com – dbForge Studio for MySQL path.

3. Assign your own values to the User ID, Password, Host, and Port parameters.
4. Save the file with the *.bat extension.

Script Execution

The BAT file is executed with the following input parameters:

1 or empty — all databases in connection will be backed up

2 — all databases specified in DB_list.txt file will be backed up

3 “mask” — all databases, the names of which match the mask name, will be backed up.

The examples of execution

All databases:

all_DB_backup_with_param.bat

all_DB_backup_with_param.bat 1

The databases from a list:

all_DB_backup_with_param.bat 2

The databases by a mask:

all_DB_backup_with_param.bat 3 max	(a mask database name will be added to the constraint like ‘%max%’)
all_DB_backup_with_param.bat 3 sakila	(a mask database name will be added to the constraint like ‘%sakila%’)
all_DB_backup_with_param.bat 3 a	(a mask database name will be added to the constraint like ‘%a%’)

After the backup has been completed successfully, a corresponding folder with SQL files will be created:

all_DB_backup — for all databases

DB_list_backup — for the databases from a list

DB_by_mask_backup — for the databases chosen by a mask.

There is another way to back up all databases using the command-line interface. You can create a *.bat file with the following content:

Set Runtool="C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"

set timestamp=%DATE:~6.4%_%DATE:~3.2%_%DATE:~0.2%__%TIME:~0.2%_%TIME:~3.2%_%TIME :~6.2%

FOR /F "eol=; tokens=1,2,3,4,5,6* delims=, " %%e in (db_for_backup.txt) do (

%Runtool% /backup /connection:"User Id=%%e;Host=%%f;Port=%%g;Character Set=%%h" /database:%%i /password:%%j /outputfile: %%i-%timestamp%.sql

)

This file will also require the creation of the following db_for_backup.txt file:

user_name1, host1, port1, encoding1, db1, password1
user_name2, host2, port2, encoding2, db2, password2
user_name3, host3, port3, encoding3, db3, password3
...
user_nameN, hostN, portN, encodingN, dbN, passwordN

For example, when you substitute the placeholder with actual data, the file contents will look somewhat like this:

root, dbfmysrv, 3320, utf8, adventureworks, root
root, 192.169.0.2, 3310, utf8, sakila, root
root, localhost, 3306, utf8, bikestore, root

By running a bat file, for example, through Windows Task Scheduler, you can configure a mass backup of databases at a certain time.

Conclusion

MySQL Server backup is an important task and is meant for protecting data stored in MySQL databases from critical loss due to hardware failures, network intrusions, human errors, etc.

In this article, we provided a solution to backing up databases specified by a list and bulk backing up of all databases. To complete this task, we created a bat file that copes with the task in a single click. Three working scripts for backing up all databases, backing databases from a list, and backing databases by a mask were given as well.

Related article:

• How to Restore a MySQL Database with Command Line or Restore Tools