How to create and restore local MySQL or MariaDB backups

Introduction

This article will help you create and restore local MySQL or MariaDB backups of all your databases. We will utilize cron to schedule the backups.

Prerequisites

This requires a /root/.my.cnf file which contains credentials so the user root can log in to the MySQL / MariaDB server with sufficient privileges without entering a password. Example /root/.my.cnf contents:

[client]
user=root
password=mysecretpassword

Don’t forget to change the permissions:

sudo chmod 600 /root/.my.cnf

Test if it works with:

PING=$(sudo mysqladmin ping 2>/dev/null)
if [ "$PING" != "mysqld is alive" ]; then
  clear && echo 'Error: Unable to connect to MySQL Server!'
else
  clear && echo 'Successfully connected to the MySQL server!'
fi

Install script

sudo wget -O /usr/local/sbin/backup_mysql.sh https://git.snel.com/snelcom/backup-mysql/raw/branch/master/backup_mysql.sh
sudo chmod 700 /usr/local/sbin/backup_mysql.sh

Install cronjob

This will add a cronjob to root which will run this script daily at 0:10 am. Adjust as necessary.

(sudo crontab -l 2>/dev/null; sudo echo '10 0 * * * test -x /usr/local/sbin/backup_mysql.sh && /usr/local/sbin/backup_mysql.sh') | sudo crontab -

Recover database

Assuming you want to restore mytestdb from your backups made on 20190121-1540:

DB='mytestdb'
BACKUPDIR='/var/backup/mysql/20190121-1540'
sudo mysql -e "CREATE DATABASE IF NOT EXISTS ${DB}"
sudo -i bash -c 'for table in ${BACKUPDIR}/${DB}/*; do gunzip -c $table | mysql ${DB}; done'

Conclusion

Your daily plain-text MySQL or MariaDB backups will be stored in /var/backup/mysql. This will help greatly when you need to recover a single table, a single database or all databases.