Backing Up All MySQL Server Databases or Only the Required Ones

July 9th, 2019

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 an SQL Server agent that will perform the backup 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.

In this article, we aim to provide a working example of backing up databases specified by a list and bulk backing up of all databases. The task will be performed by means of dbForge Studio for MySQL, which allows performing routine tasks in both, command prompt and PowerShell. In this article, we will cover both variants.

Backing Up All Databases of a Selected Connection

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.

  1. Assign your own values to the User Id, Password, Host and Port parameters.
  2. Save the file with the  .PS1 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 the 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.

  1. Assign your own values to the User Id, Password, Host and Port parameters.
  2. 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.

  1. Assign your own values to the User Id, Password, Host and Port parameters.
  2. 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.

Bat file

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.

  1. Assign your own values to the User Id, Password, Host and Port parameters.
  2. 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 which name matches 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:

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.

Conclusion

MySQL Server backup is an important task and is meant for protecting data stored in SQL Server 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. And three working scripts for backing up all databases, backing databases from a list and backing databases by a mask were given as well.

Leave a Comment