How to automatically create a backup of all customers MySQL databases on Plesk for Windows?

Question

How to automatically create backup of all customers MySQL databases?

Answer

Note: All customers data including the databases can be backed up using Plesk Server Backup. This article describes how to create a task that will backup only customers MySQL databases and nothing more.

1. Log into the server via RDP.

2. Create a directory to store the backups inside %plesk_dir%DatabasesMySQL, for example %plesk_dir%DatabasesMySQLbackup

3. Create backup.bat file in this directory, with the following content:

   To store databases in single file

   "%plesk_dir%MySQLbinmysqldump.exe" -uadmin -ppassword_for_mysql -P3306 --all-databases --events -r "%plesk_dir%DatabasesMySQLbackupbackup_databases.sql"
   COPY "%plesk_dir%DatabasesMySQLbackupbackup_databases.sql" "%plesk_dir%DatabasesMySQLbackupbackup_databases_%time:~0,2%%time:~3,2%%time:~6,2%_%date:~-10,2%%date:~-7,2%%date:~-4,4%.sql"

   To store databases in separate files

   "%plesk_dir%MySQLbinmysql.exe" -uadmin -ppassword_for_mysql -P3306 -Ne"SHOW DATABASES" > "%plesk_dir%DatabasesMySQLbackupdb_list.txt"
   cd %plesk_dir%DatabasesMySQLbackup
   for /F "tokens=1,2* " %%j in (db_list.txt) do "%plesk_dir%MySQLbinmysqldump.exe" -uadmin -ppassword_for_mysql -P3306 --routines --databases %%j > "%plesk_dir%DatabasesMySQLbackup%%j_%time:~0,2%%time:~3,2%%time:~6,2%_%date:~-10,2%%date:~-7,2%%date:~-4,4%.sql"

   In backup.bat specify the password for MySQL server instead of “password_for_mysql”. To reset MySQL password navigate Tools & Settings > Database Servers > localhost (3306) > Change password.

4. Log into Plesk.

5. Go to Tools & Settings > Scheduled Tasks and click Add Task.

6. Specify the path %plesk_dir%DatabasesMySQLbackupbackup.bat in the Command field:

   

7. Test the command by clicking ‘Run now‘ button.

8. Save the task by clicking Ok if everything looks fine.