How VPS - How to use/setup VPS
  • Home
  • Management guides
    • Web servers software
      • Directadmin
      • Hocvps Script
      • Centmin Mod
      • CWP
      • Kloxo-MR
      • Plesk
    • Control Panels
    • Securing VPS/Servers
      • SSL Certificates
      • Upgrading
      • Authentication
  • Operating System
    • CentOS
    • Fedora
    • Debian
    • Linux
    • Arch
    • BSD
    • CoreOS
  • Reviews
  • Coupon
    • Domain Coupon
    • Hosting Coupon
No Result
View All Result
  • Home
  • Management guides
    • Web servers software
      • Directadmin
      • Hocvps Script
      • Centmin Mod
      • CWP
      • Kloxo-MR
      • Plesk
    • Control Panels
    • Securing VPS/Servers
      • SSL Certificates
      • Upgrading
      • Authentication
  • Operating System
    • CentOS
    • Fedora
    • Debian
    • Linux
    • Arch
    • BSD
    • CoreOS
  • Reviews
  • Coupon
    • Domain Coupon
    • Hosting Coupon
No Result
View All Result
How VPS - How to use/setup VPS
No Result
View All Result
Home Operating System CentOS

Backup Multiple MySQL Or MariaDB Databases Automatically

How VPS by How VPS
October 1, 2019
in CentOS
0
0
SHARES
17
VIEWS
Share on FacebookShare on Twitter

Contents

  1. Introduction
    1. Database Backup(s)
    2. Cronjob Setup
    3. What’s Next
  2. Want to contribute?


Introduction

In this write-up, we’ll walk through how to backup multiple MySQL or MariaDB databases that sit on the same machine using a custom bash script and setting up a cron job.

Database Backup(s)

We’ll assume we have three MySQL databases on our Vultr instance named db-vultr-site, db-vultr-blog, db-vultr-app (Don’t worry about creating these databases, you’ll be able to substitute their names for yours hwere they are used in the script on Step 2 below).

  1. Log on to your MySQL or MariaDB database and run below query to create a database user db_user_backups to handle backups

    GRANT LOCK TABLES, SELECT, SHOW VIEW, REPLICATION CLIENT ON *.* TO 'db_user_backups'@'%' IDENTIFIED BY '{COMPLEX-PASSWORD}';
    

    Also run below to make sure MySQL is configured to properly restore stored procedures

    SET GLOBAL log_bin_trust_function_creators = 1;
    
  2. Setup the necessarily directory structure and files needed

    # create backup directory with environment and log file
    sudo mkdir /backups && cd /backups
    sudo touch .env db-backup.sh db-backup.log
    sudo chmod -R 775 /backups
    sudo chmod -R g+s /backups
    sudo chmod +x db-backup.sh
    
    # add mysql backup user credentials into environment file
    echo "export MYSQL_USER=db_user_backups" > /backups/.env
    echo "export MYSQL_PASS={COMPLEX-PASSWORD}" >> /backups/.env
    
  3. Open db-backup.sh nano /backups/db-backup.sh and paste the code below inside it, then save the file (Ctrl+X -> Y -> hit Enter).

    DB_NAMES=( 'db-vultr-site' 'db-vultr-blog' 'db-vultr-app' ) #replace with your own database name(s)
    BKUP_NAMES=()
    BKUP_DIR="/backups"
    
    # get total number of directories
    total_dbs=${#DB_NAMES[@]}
    
    # create backup file names
    for (( i=0; i<${total_dbs}; i++ )); do
        BKUP_NAMES[$i]="`date +%Y%m%d%H%M`-backup-$${DB_NAMES[$i]}.sql.gz"
    done
    
    # get backup users credentials
    source $BKUP_DIR/.env
    
    # create backups
    for (( i=0; i<${total_dbs}; i++ )); do
        # NOTE: --routines flag makes sure stored procedures are also backed up
        mysqldump --routines -u ${MYSQL_USER} -p${MYSQL_PASS} | gzip > ${BKUP_DIR}/${BKUP_NAMES[$i]}
    done
    

    The code above is looping through an array with the name(s) of the database(s) you want to backup and doing so.

Cronjob Setup

Setup a cronjob to run every midnight that runs the backup script and saves the result/output to backup log.

  1. Open crontab

    crontab -e
    
  2. Add below entry to crontab

    0 0 * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
    

    Note: While testing you can set cronjob to run every 1 minute instead like below

    * * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
    

    -OR- every 5 minutes (replace 5 with the number of minutes you want)

    */5 * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
    

What’s Next

You will likely not want your backup(s) on the same server(s) running your database(s), but instead on a server in a different geographic location. There are several ways to do this, ranging from using SFTP, to using custom tools provided by the myriad of cloud storage providers available out there. One good alternative is Rsync as explained here – vultr.com/docs/setup-file-mirroring-using-rsync-in-debian-ubuntu

Written by Lami Adabonyan

Want to contribute?

You could earn up to $300 by adding new articles

Submit your article
Suggest an update
Request an article
How VPS

How VPS

Related Posts

Failed to download metadata for repo 'appstream' on Centos 8
CentOS

How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8

February 25, 2022
How to Install BoltWire CMS on CentOS 7
CentOS

How to Install BoltWire CMS on CentOS 7

February 14, 2020
Showterm.io – A Terminal/Shell Recording, Upload and Share Tool for Linux
CentOS

Setup HTTP Authentication With Nginx on CentOS 7

February 14, 2020
Next Post

How to Install RStudio Server on CentOS 7

How to Install Wekan (Open Source Kanban) on CentOS 7

How to Install and Use Apache PredictionIO for Machine Learning on CentOS 7

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Follow Us

  • 121 Followers
  • 87.2k Followers

Recommended

How to Install October 1.0 CMS on a CentOS 7 LAMP VPS

3 years ago

How to Install Harbor on CentOS 7

3 years ago

How to Install WonderCMS on Debian 9

3 years ago

Installing HAProxy on Ubuntu 14.04

3 years ago

Instagram

    Please install/update and activate JNews Instagram plugin.

Categories

  • Arch
  • Authentication
  • Backups
  • BSD
  • Centmin Mod
  • CentOS
  • Control Panels
  • CoreOS
  • CWP
  • Debian
  • Directadmin
  • Encryption
  • Fedora
  • Firewalls
  • Hocvps Script
  • Hosting providers
  • Kloxo-MR
  • Linux
  • Mitigations
  • Operating System
  • Plesk
  • Reviews
  • Securing VPS/Servers
  • Security Patches
  • SSL Certificates
  • Uncategorized
  • Upgrading
  • VPS/Servers management guides
  • Vulnerability Detection
  • Web servers software
  • Webhosting Control Panel

Topics

Apache Web Server Bluehost Review 2019 Bluehost Review 2020 Bluehost Review 2021 Centmin Mod CentminMod centos install htop fsck htop install HTTP DoS attack Install Snort on an Ubuntu install Zabbix on CentOS install Zabbix on CentOS 7 Linux Commands linux guide linux install htop linux vps setup guide MariaDB MariaDB Error Mysql mysqld error optimize MariaDB optimize Mysql snort Ubuntu
No Result
View All Result

Highlights

Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

Webmin Reviews

Virtualmin Reviews

CentOS Web Panel Reviews

Ajenti Reviews

ISPConfig Reviews

Trending

Failed to download metadata for repo 'appstream' on Centos 8
CentOS

How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8

by How VPS
February 25, 2022
0

I tried to update some extensions by use yum on centOs which I specified in Dockerfile. After...

How to Fix MySQL Error "Plugin 'InnoDB' registration as a STORAGE ENGINE failed"?

How to Fix MySQL Error “Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed”?

November 17, 2020
How to optimize Mysql or MariaDB

How to optimize Mysql or MariaDB

November 3, 2020
Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

Top Free Web Hosting Control Panels To Manage VPS/Dedicated Servers

February 17, 2020
Webmin Reviews

Webmin Reviews

February 17, 2020
How VPS – How to use/setup VPS

We bring you the best Premium WordPress Themes that perfect for news, magazine, personal blog, etc. Visit our landing page to see all features & demos.
LEARN MORE »

Recent News

  • How to fix error: Failed to download metadata for repo ‘appstream’ on Centos 8 February 25, 2022
  • How to Fix MySQL Error “Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed”? November 17, 2020
  • How to optimize Mysql or MariaDB November 3, 2020

Categories

  • Arch
  • Authentication
  • Backups
  • BSD
  • Centmin Mod
  • CentOS
  • Control Panels
  • CoreOS
  • CWP
  • Debian
  • Directadmin
  • Encryption
  • Fedora
  • Firewalls
  • Hocvps Script
  • Hosting providers
  • Kloxo-MR
  • Linux
  • Mitigations
  • Operating System
  • Plesk
  • Reviews
  • Securing VPS/Servers
  • Security Patches
  • SSL Certificates
  • Uncategorized
  • Upgrading
  • VPS/Servers management guides
  • Vulnerability Detection
  • Web servers software
  • Webhosting Control Panel

[mc4wp_form]

© 2018 JNews - City News Magazine WordPress theme. All rights belong to their respective owners.
JNews is a top selling 2018 WordPress News, Blog, Newspaper & Magazine Theme.

No Result
View All Result
  • Home

© 2023 JNews - Premium WordPress news & magazine theme by Jegtheme.