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.<\/p>\n

CREATE<\/span> USER<\/span> 'backupuser'<\/span>@'localhost'<\/span>\r\n    IDENTIFIED<\/span> BY<\/span> 'xxx'<\/span>;\r\n<\/pre>\n
GRANT<\/span> SELECT<\/span>, SHOW<\/span> VIEW<\/span>, LOCK<\/span> TABLES<\/span>, RELOAD,\r\n    REPLICATION<\/span> CLIENT<\/span>\r\n    ON<\/span> *.* TO<\/span> 'backupuser'<\/span>@'localhost'<\/span>;\r\n<\/pre>\n
FLUSH<\/span> PRIVILEGES<\/span>;<\/pre>\n
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:<\/p>\n
#! \/bin\/bash<\/p>\n
TIMESTAMP=$(date +”%F”)
\nBACKUP_DIR=”\/web\/backup\/$TIMESTAMP”
\nMYSQL_USER=”xxx”
\nMYSQL=\/usr\/bin\/mysql
\nMYSQL_PASSWORD=”xxx”
\nMYSQLDUMP=\/usr\/bin\/mysqldump<\/p>\n
mkdir -p “$BACKUP_DIR\/mysql”<\/p>\n
databases=`$MYSQL –user=$MYSQL_USER -p$MYSQL_PASSWORD -e “SHOW DATABASES;” | grep -Ev “(Database|information_schema|performance_schema)”`<\/p>\n
for db in $databases; do
\n$MYSQLDUMP –force –opt –user=$MYSQL_USER -p$MYSQL_PASSWORD –databases $db | gzip > “$BACKUP_DIR\/mysql\/$db.gz”
\ndone<\/p>\n
<\/p>\n
Hopefully, this has helped someone.<\/p>\n
DO YOU LIKE WHAT YOU'VE READ?<\/div>
Join our subscription list and receive our content right in your mailbox. If you like to receive some Great deals our Freebies then subscribe now!\r\n\r\n
\n
\n\n
Name<\/label>\n<\/div>\n
Email<\/label>\n<\/div>\n
\n<\/div>\n<\/form>\n<\/div>\n<\/p>\r\n\r\n<\/div><\/div>\n
<\/div>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":28186,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[616,497,1564,747,1653,479,1968,393],"class_list":["post-28150","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business","tag-backup","tag-database","tag-databases","tag-mysql","tag-mysqldump","tag-script","tag-vestacp","tag-zip"],"_links":{"self":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/28150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/comments?post=28150"}],"version-history":[{"count":3,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/28150\/revisions"}],"predecessor-version":[{"id":28185,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/28150\/revisions\/28185"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/media\/28186"}],"wp:attachment":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/media?parent=28150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/categories?post=28150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/tags?post=28150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}