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
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
QUICK FIX TO CHANGE THE MAX SELL POWER TO LESS THAN 500W ON THE DEYE INVERTER
There are many reasons a home owner or installer might want to place a tight cap on how much solar power is exported to the public grid. Some households live under a strict export cap imposed by their local distribution network operator, others want to reduce export to focus on self consumption and battery charging, […]
read more
