When, like me, you get paranoid to losing your data or web server functionality…
I have created a little perl script to allow the synchronization of MySQL databases and /home between my Webservers.
The master server (where the script runs from) is the main production server, the salve server is on standby just in case.
I can appreciate that the passwords are clearly inserted in the script and it is a security issue. Nonetheless, I am the only admin and no other users have ftp or other privileges on both servers. I am sure that there is a way to prevent this but I am happy with the current situation and, obviously I am also making a regular backup of the master server on an external backup provider (I use rsync.net).
#!/usr/bin/perl
# Performs a synchronisation of home folder and dumps sql databases
# from one Virtual server to another using rsync and secure shell
#
# Written by G.Serex Sharpnet UK (c) 03.12.2020
# Var definitions
############### SQL Config #############
# SQL root username
$username = "root";
# Local SQL root password
$password = "localmysqlpassword";
# Remote SQL root password
$rpassword = "remotemysqlpassword";
# The dumped files path . (absolute path + trailing / please)
$dumped_dbs_path = "/root/mysql/";
# The dumped file name
$dumped_db = "dump.sql";
# Name of the database to exclude from the dump (here the mysql and sys are obviously dedicated to each server, so don't dump them!)
$exclude_database = "mysql,sys,information_schema,performance_schema";
################ SSH Config #################
# The remote host name
$remotehost = "ipaddress";
#The ssh username
$sshusername = "root";
#The ssh port
$sshport = "xx";
#____ E N D _ V A R _ D E F S. ________________
# First check and optimise the lot.
# A little house keeping
system("/usr/bin/mysqlcheck --optimize --all-databases --auto-repair -u $username -p$password");
# Dump the dbs
system("/usr/bin/mysqlpump -u $username -p$password --exclude-databases=$exclude_database --add-drop-table --result-file=$dumped_dbs_path$dumped_db");
# Transfer them abroad
system("/usr/bin/rsync -avz -e 'ssh -p $sshport' $dumped_dbs_path $sshusername\@$remotehost:$dumped_dbs_path");
# Restore the dump abroad
system("/usr/bin/ssh -p $sshport $sshusername\@$remotehost 'mysql -u root -p$rpassword < $dumped_dbs_path$dumped_db'");
# rsync the home directory
system("/usr/bin/rsync -avz --delete -e 'ssh -p $sshport' /home/ $sshusername\@$remotehost:/home");
exit;