Question MySQL DB live clone/copy

[JeKrNL]

Yesterday we had an 2.3Gb database to restore from Plesk backup. This restore took around 3 hours. Ofcourse this is always to long for a live site, so I'm looking for a quicker solution to restore a DB when needed.

In Plesk there is a nice feature "Copy Database" where the destination can be chosen. Is it possible to create a "live" backup/copy/clone of the database (eg nightly)? So when something goes wrong I just have to switch credentials in the Website and we are back to the latest backup.

Found already some scripts to create a backup, but all to file and no scripts where the DB is directly live and accessible from Plesk.

eg:
https://scottlinux.com/2011/03/04/rotate-mysql-backups-with-logrotate/
https://github.com/todiadiyatmo/bash-backup-rotation-script

 

[lvalics]

I use automysqlbackup to create a daily/weekly/monthly backup. It is not the perfect solution, but in years saved my *** a few times (or a lot of times).

wget -O /etc/cron.daily/automysqlbackup.sh http://www.grafxsoftware.com/download/backup-plesk/automysqlbackup.sh
chmod 700 /etc/cron.daily/automysqlbackup.sh
mkdir /home/backups

This script was modified to run in PLESK env, so will backup all database in a directory, each DB in separate files, so when you need to restore one it is easy.

Just run /etc/cron.daily/automysqlbackup.sh to test.

Also I have a small shell script to restore all SQL file in current folder. I never restore from PLESK, cause large DB can create issues. Command line is still better

for filename in *.sql
do

database="${filename/.sql/}"
echo $filename "-" $database.

mysql -uadmin '-pPASSWORDHERE' "$database" < $filename
echo "----"
echo

done​

 

[UFHH01]

Hi lvalics,

instead of:

mysql -uadmin '-pPASSWORDHERE' "$database" < $filename

I would use:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` "$database" < $filename

 

[lvalics]

Not in all case work Don't ask me why, usually I use the same, but I got sometimes when it not work.

 

[JeKrNL]

Tnx. Will try automysqlbackup.
Large DB with Plesk Backup/Restore is taking a very long time. Copy DB in Plesk with a 2.3G DB is done is 3 till 5 minutes.