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.
|
1 2 |
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">'backupuser'</span>@<span class="hljs-string">'localhost'</span> <span class="hljs-keyword">IDENTIFIED</span> <span class="hljs-keyword">BY</span> <span class="hljs-string">'xxx'</span>; |
|
1 2 3 |
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span>, <span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">VIEW</span>, <span class="hljs-keyword">LOCK</span> <span class="hljs-keyword">TABLES</span>, RELOAD, <span class="hljs-keyword">REPLICATION</span> <span class="hljs-keyword">CLIENT</span> <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'backupuser'</span>@<span class="hljs-string">'localhost'</span>; |
|
1 |
<span class="hljs-keyword">FLUSH</span> <span class="hljs-keyword">PRIVILEGES</span>; |
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
HOW TO REMOVE WINDOWS 10 WELCOME SCREEN
Have you been getting the blue Windows 10 Welcome screen? It normally pops up after installing Windows 10 or after a Windows 10 update. I’ve been receiving a lot of questions relating to this and there are a few ways to remove it. One way it to change the settings in the registry setting but […]
read more
6 REASONS TO SYNCHRONIZE YOUR DATA
Technology changes and complex business requirements might compromise product data, impacting negatively on the company. Data synchronization ensures that information across the production chain is consistent. BizCaps achieves such streamlining with tools that collect data and sync it from the supplier to the consumer, increasing efficiency. Here are six reasons you require data synchronization. Increased […]
read more
