Backup Script MySQL and MariaDB

Clique aqui para a Versão em Português

Introduction

One of the most important tasks for a Database administrator it’s the backup of your databases.

The objective of this post is to create a backup script that will be scheduled in the crontab and will execute the backup routines every day at 02:00 a.m. The “backup” user will have only the minimal privileges. Possible enough just to create the backup.

In this post the CentOS 5 is the reference. But the latest versions should work properly. With some adjustments it will work on Debian and derivatives too.

Creation and configuration of the “backup” user

To create the user we need administrative privileges on MySQL. In this example we gonna use the “root” user.

Using the BASH shell of the Database machine log in the MySQL Shell:

$mysql -u root -p

Provide the access password and the MySQL Shell prompt will be presented:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 996333
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

 

After the logon we must create the “backup” user and define the password:

mysql>create user backup@localhost identified by 'your_password';

In this example backup@localhost user can access MySQL only from the Database machine. The user password is “your_password”. If we try to access MySQL from a different machine or even trying to pass the local IP address we will get a permission denied message.

Now we must grant the privileges to backup@localhost user so he can complete the backup routines:

mysql>GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES ON *.* TO 'backup'@'localhost';

Backup@localhost user must have the following privileges: SELECT, RELOAD, SHOW DATABASES and LOCK TABLES to accomplish his task.

Some tests can be done to verify if the user is configured correctly.

Try login in MySQL Shell:

$mysql -u root -p

Then show the databases available:

mysql> show databases;

Supposing that there is an database named “test” with table1 carry out an select on this table:

mysql> use backup; select * from table1;

If everything went ok we can proceed to the next step. Otherwise go back and verify every step.

Creating the backup script

Since the script is commented there will be no more comments about it.

O script será salvo no diretório /opt/scripts com o nome de backup_mysql.sh

The script will be saved in the /opt/scripts directory with the name: backup_mysql.sh

#!/bin/bash
###################################################################
# Name : backup_mysql.sh
# MySQL Backup Script 
# Created : 31/03/2017 - Adail Antonio #
# Last modified : 31/03/2017 - Adail Antonio #
###################################################################

# Changelog 


##### Variables
declare DATA=`date +%Y%m%d_%H%M%S`
declare DIR_BACKUP="/opt/backups/mysql/"  #  Define the backup directory
declare PASSWORD="your_password"
declare USER="backup"
DIR_DEST_BACKUP=$DIR_BACKUP$DATA
###################################################################

##### Secundary routines
mkdir -p $DIR_BACKUP/$DATA # Create the daily backup directory
echo "MYSQL"
echo "Initiating the backup... "
##################################################################

# Backup Function
execute_backup(){
echo "Backup started at $DATA"
 # Receive the database names 
 DATABASES=$(mysql -u $USER -p$PASSWORD -e "show databases")
 # Remove the "database" word from the result
 #DATABASES=${DATABASES:9:${#DATABASES}}

declare CONT=0

# Begin the for iterations 

for database in $DATABASES
 do
 if [ $CONT -ne 0 ]; then    # ignore the first array item 
     NAME="backup_my_"$database"_"$DATA".sql"


    echo "Stating backup of [$banco]"
   # Create the database dump
   mysqldump --hex-blob --lock-all-tables -u $USER -p$PASSWORD --databases $database > $DIR_DEST_BACKUP/$NOME
 

   # Verify if it went well
   if [ $? -eq 0 ]; then
      echo "Backup of [$database] complete"
   else
      echo "ERROR in backup of [$database]"
   fi

fi
 CONT=`expr $CONT + 1`
 done


DATA=`date +%Y%m%d_%H%M%S`

echo "End of backup: $DATA"
}

execute_backup 2>> $DIR_BACKUP/$DATA/backup.log 1>> $DIR_BACKUP/$DATA/backup.log

###################################################################

 

It’s important to remember to grant execution permissions:

$sudo chmod +x /opt/scripts/backup_mysql.sh

 

Crontab schedulling

Para que o processo de automatização seja concluído, precisamos configurar o crontab da máquina, para que ela execute o script no horário desejado.

To conclude the automation process we must configure the crontab:

$sudo crontab -e

Two lines will be inserted the first one execute the /opt/scripts/backup_mysql.sh  every day at 02:00 am.

00 02 * * * /opt/scripts/backup_mysql.sh

The second one sets the retention time of the backups. In this particular case will be retained backups of the last 31 days:

0 23 * * * /usr/bin/find /opt/backups/mysql -type d -ctime +31 -exec rm -rf '{}' \;

Save the crontab and your system is configured for backup your MySQL Databases!

Good job!

 

Photo by Pixabay on Pexels.com