SCRIPT TO BACKUP ALL MYSQL DATABASES IN VESTACP
December 11, 2019 / by Marco / Categories : Business
If you have multiple databases hosted on VestaCP the easiest way I found to backup all the databases is to use a script. The first thing you need to do is create a backup account in mySQL that has view only access to all the databases.
CREATE USER 'backupuser'@'localhost'
IDENTIFIED BY 'xxx';
GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD,
REPLICATION CLIENT
ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;
Then use this bash script. You can change the variables accordingly but the most important is the backup location, username and password which was created above:
#! /bin/bash
TIMESTAMP=$(date +”%F”)
BACKUP_DIR=”/web/backup/$TIMESTAMP”
MYSQL_USER=”xxx”
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD=”xxx”
MYSQLDUMP=/usr/bin/mysqldump
mkdir -p “$BACKUP_DIR/mysql”
databases=`$MYSQL –user=$MYSQL_USER -p$MYSQL_PASSWORD -e “SHOW DATABASES;” | grep -Ev “(Database|information_schema|performance_schema)”`
for db in $databases; do
$MYSQLDUMP –force –opt –user=$MYSQL_USER -p$MYSQL_PASSWORD –databases $db | gzip > “$BACKUP_DIR/mysql/$db.gz”
done
Hopefully, this has helped someone.
Marco / admin / 30 Nov
Writing day to day ramblings about making money, business, technology, sharing awesome deals and everything else that I know I'll forget. Follow my personal blog https://marcotran.com.au
I've recently also turned Vegan and started this website Veggie Meals - check it out
"When technology speaks for itself, that is art" - MT
Affiliate Compensated: there are some articles with links to products or services that I may receive a commission.
OTHER ARTICLES YOU MAY LIKE
USING THE LATEST CLAUDE MODEL TO BUILD GREAT TOOLS
If you have ever had an idea for a small tool but stopped because coding felt too hard, the latest Claude model makes that gap much smaller. You can now describe what you want in plain English, ask Claude to plan it, build the first version, improve the design, write the logic, test the workflow, […]
read more
REVIEWING AND UNBOXING THE TOOCKI USB-C RIGHT ANGLE 1.2M CABLE
USB C cables are among the most ordinary accessories we own, yet they can make a surprisingly noticeable difference to how comfortable our devices are to use while charging. The Toocki USB C right angle cable is a good example of a simple accessory that tries to solve a very common irritation, because instead of […]
read more
