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
6 THING TO KNOW ABOUT USING A HEADER BIDDER
Header bidding is increasing in popularity rapidly. Publishers are looking for new ways to increase revenue, and header bidding seems to be the answer. But with increased popularity comes misunderstandings about how header bidder works and how publishers can maximise yield using this technology. Here are some things that every publisher should know before implementing […]
read more
LOOKING FOR AN ONLINE BOOKING TOOL?
If you’re looking at an online booking tool that will easily integrate and work with your website you can check out this post: HOW TO ADD THE TIDYCAL BOOKINGS PAGE TO A WORDPRESS PAGE This is the tool used: TIDYCAL – SIMPLE BOOKING SOLUTION there is a free version that you can use and if […]
read more
