Question automate the creation of dumps for select databases

[zakaria]

Server operating system version

Debian 10.13

Plesk version and microupdate number

Plesk Obsidian 18.0.53 Update #2

hello, hope everyone is doing well.

I want to automate the creation of dumps for select databases. then download it to an FTP server.
I tried to look for the commands needed to achieve this so then I write a script and configure a Cron job with it but couldn't find it.

 

[Kaspar]

[..] I tried to look for the commands needed to achieve this so then I write a script and configure a Cron job with it but couldn't find it.

This should not be too hard. How far did get? What commands did you already try?

 

[pleskpanel]

You might try GitHub - sixhop/AutoMySQLBackup: A fork and further development of AutoMySQLBackup from sourceforge. http://sourceforge.net/projects/automysqlbackup/ as a starting point or even easier, as of Plesk Obsidian 18.0.54 the ability to back up databases separately has been added in the Backup Manager!

 

[zakaria]

I finally got the time to work on this objective and wrote this PHP script to do the job.

<?php
$return_var = NULL;
$output = NULL;
$command1 = "/usr/bin/mysqldump -u username1 -p'password' databasename1 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz";
$command2 = "/usr/bin/mysqldump -u username2 -p'password' databasename2 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz";
$command3 = "/usr/bin/mysqldump -u username3 -p'password' databasename3 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz";
$command4 = "/usr/bin/mysqldump -u username4 -p'password' databasename4 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz";
exec($command1, $output, $return_var);
exec($command2, $output, $return_var);
exec($command3, $output, $return_var);
exec($command4, $output, $return_var);
?>

I tried to make it more optimized, like this

<?php

$commands = array("/usr/bin/mysqldump -u username1 -p'password' databasename1 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz",
                  "/usr/bin/mysqldump -u username2 -p'password' databasename2 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz",
                  "/usr/bin/mysqldump -u username3 -p'password' databasename3 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz",
                  "/usr/bin/mysqldump -u username4 -p'password' databasename4 | gzip > /var/www/vhosts/'Databases Backup'/Database_Backup-$(date +%d-%m-%y_%H:%M:%S).gz");

foreach ($commands as $command) {
    exec($command);
}

?>

I didn't continue for the ftp part because I can't open the ports, currently we're using 4G modems for our connection and even if I open the ports on my side it's closed from the ISP side so can only download it manually, unless someone here got a suggesting for me to try.

 

[Ohya]

I didn't continue for the ftp part because I can't open the ports, currently we're using 4G modems for our connection and even if I open the ports on my side it's closed from the ISP side so can only download it manually, unless someone here got a suggesting for me to try.

Why not run script from your FTP server ?

You can connect to SSH ?

You can do some things

Put a script in your Plesk server.
Conect from your FTP backup server over SSH and run that script, then script finish download directory when backup is stored.

In any case you can do something like this

SSH=$(ssh -p 22 IPserver)

$SSH plesk db dump $YOURDB > /root/$YOURDB
$SSH zstd --rm /root/$YOURDB
rsync -a user@YOUPLESKIP:/root/${YOURDB}.zst /backupdir/
$SSH rm -f /root/${YOURDB}.zst

This is just a idea if you cant send from your plesk server to your NAS because you cant open port or enable DMZ

So make your FTP to conect to your server and get files.

You also can make some script to dump all your databases and save in one dir and then , so run a script directly

$SSH /bin/backup_script
And then just rsync a dir.

Some time ago a I make easy script to do a database plesk backup

Backup-Mysql-FTP/skdumpbd.sh at master · Skamasle/Backup-Mysql-FTP

Backup all Mysql Data bases to FTP. Contribute to Skamasle/Backup-Mysql-FTP development by creating an account on GitHub.

github.com

 

[zakaria]

thanks for the great idea.
I didn't think of it, I can't initiate a connection from the Plesk server side, but I can connect to it from my side.
so, I just need to connect to it via ssh for example and use rsync to download the dump files, I'll post again when I get it done.

 

[Ohya]

thanks for the great idea.
I didn't think of it, I can't initiate a connection from the Plesk server side, but I can connect to it from my side.
so, I just need to connect to it via ssh for example and use rsync to download the dump files, I'll post again when I get it done.

You can algo make direct mysqldump over network

mysqldump -u user -h host -p$password db > db

Less secure but posible