Automate MySQL Tasks

In this example we will use MySQL CLI to automatically backup and and restore the db when required.

We will: create a db, backup the db using an automated script, and finally truncate and restore it back

Create DB

You will use a modified version of the Sakila database for the lab, which is an adapted version from the following source: https://dev.mysql.com/doc/sakila/en/ under New BSD license [Copyright 2021 - Oracle Corporation].

Import Data

At root, import the data locally from:

wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0110EN-SkillsNetwork/datasets/sakila/sakila_mysql_dump.sql

Create MySQL Instance

Click on CREATE in MySQL IDE

Edit Password

This will keep the password hidden in the conf file and prevents us from having to enter it over and over again specially when we plan on automating the process

  • Replace the password field with the value given when we started the MySQL instance above
  • When done CTRL O
  • CTRL X
  • get out of nano editor
sudo nano ~/.my.cnf

# RESPONSE
  GNU nano 6.2          /home/theia/.my.cnf                   
[client]
host = mysql
port = 3306
user = root
password = yhb9QfN4WrJZ8fI5HCu3LRmj

Start MySQL

As you see we can login to MySQL without using password

~$ mysql
mysql>

Create db

mysql> create database sakila;
Query OK, 1 row affected (0.02 sec)

Restore DB

Connect to db

mysql> use sakila;
Database changed

Populate from Script

mysql> source sakila_mysql_dump.sql;

List Tables

mysql> SHOW FULL TABLES WHERE table_type = 'BASE TABLE';
+------------------+------------+
| Tables_in_sakila | Table_type |
+------------------+------------+
| actor            | BASE TABLE |
| address          | BASE TABLE |
| category         | BASE TABLE |
| city             | BASE TABLE |
| country          | BASE TABLE |
| customer         | BASE TABLE |
| film             | BASE TABLE |
| film_actor       | BASE TABLE |
| film_category    | BASE TABLE |
| inventory        | BASE TABLE |
| language         | BASE TABLE |
| payment          | BASE TABLE |
| rental           | BASE TABLE |
| staff            | BASE TABLE |
| store            | BASE TABLE |
+------------------+------------+
15 rows in set (0.01 sec)

Create Backup Script

You will create a shell script that does the following:

  • Writes the database to an sqlfile created with a timestamp, using the mysqldump command
  • Zips the sqlfile into a zip file using the gzip command
  • Removes the sqlfile using rm command
  • Deletes the backup after 30 days

Before you create the script, let’s understand each of the command blocks you will be adding to the file.

  • To start with, you have a database that you want to back up. You will store the name of the database in a variable.
  • It is always a good practice to print some logs, which can help in debugging.
  • You will also set the backup folder where the SQL and zipped files will be stored.
  • You will decide and set the number of days the backup will need to be kept.
  • You will set the name of the SQL file where you will dump the database as “all-database-“ suffixed with the current timestamp and .sql extension, and
  • the zip file in which you will compress the SQL file as “all-database-“ suffixed with the current timestamp and .gz extension.
DATABASE='sakila'
echo "Pulling Database: This may take a few minutes"

backupfolder=/home/theia/backups

keep_day=30

sqlfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql
zipfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).gz
  • Now that all the placeholders are created, you will create the SQL backup. In MySQL, it can be accomplished with the mysqldump command. Depending on whether the operation is successful or not, a log is printed. If the operation is successful, you will compress the .sql file into a .gz and delete the the .sql file.
if mysqldump  $DATABASE > $sqlfile ; then
   echo 'Sql dump created'
    # Compress backup 
    if gzip -c $sqlfile > $zipfile; then
        echo 'The backup was successfully compressed'
    else
        echo 'Error compressing backupBackup was not created!' 
        exit
    fi
    rm $sqlfile 
else
   echo 'pg_dump return non-zero code No backup was created!' 
   exit
fi
  • Finally, you will remove any backups that are in the system for longer than the time you decided to retain the backup.
find $backupfolder -mtime +$keep_day -delete

Create Script File

Now that we went through all the steps let’s create the script file:

  • File>New>name it: sqlbackup.sh
  • Save the script in it
#!/bin/sh
# The above line tells the interpreter this code needs to be run as a shell script.

# Set the database name to a variable. 
DATABASE='sakila'

# This will be printed on to the screen. In the case of cron job, it will be printed to the logs.
echo "Pulling Database: This may take a few minutes"

# Set the folder where the database backup will be stored
backupfolder=/home/theia/backups

# Number of days to store the backup 
keep_day=30

sqlfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql
zipfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).gz

# Create a backup

if mysqldump  $DATABASE > $sqlfile ; then
   echo 'Sql dump created'
    # Compress backup 
    if gzip -c $sqlfile > $zipfile; then
        echo 'The backup was successfully compressed'
    else
        echo 'Error compressing backupBackup was not created!' 
        exit
    fi
    rm $sqlfile 
else
   echo 'pg_dump return non-zero code No backup was created!' 
   exit
fi

# Delete old backups 
find $backupfolder -mtime +$keep_day -delete

Make it Executable

  • Now you need to give executable permission for the shell script file, to the owner (yourself), by running the following command in the terminal.
  • u stands for user or creator,
  • x stands for execute,
  • and r stands for read permission
  • FROM THE SHELL SCRIPT not from within MySQL
~$ sudo chmod u+x+r sqlbackup.sh

Create Folder to hold Backup

~$   mkdir /home/theia/backups

Verify Permissions

~$ ls -l
total 3128
-rw-r--r-- 1 theia users 3196680 Oct 12  2022 sakila_mysql_dump.sql
-rwxr--r-- 1 theia users    1043 Oct  3 17:35 sqlbackup.sh

Setup Cron Job

Reminder:

  • Cron is a system that helps Linux users schedule any task. It can be a shell script or a simple bash command.
  • A cron job helps us automate our routine tasks and it can be hourly, daily, monthly, etc.
  • A crontab (cron table) is a text file that specifies the schedule of cron jobs.

Each line in the crontab file contains six fields separated by a space followed by the command to be run.

  • The first five fields may contain one or more values separated by a comma or a range of values separated by a hyphen.
  • * The asterisk operator means any value or always. If you have the asterisk symbol in the Hour field, it means the task will be performed each hour.
  • , The comma operator allows you to specify a list of values for repetition. For example, if you have 1,3,5 in the Hour field, the task will run at 1 a.m., 3 a.m. and 5 a.m.
  • - The hyphen operator allows you to specify a range of values. If you have 1-5 in the Day of week field, the task will run every weekday (from Monday to Friday).
  • / The slash operator allows you to specify values that will be repeated over a certain interval between them. For example, if you have */4 in the Hour field, it means the action will be performed every four hours. It is same as specifying 0,4,8,12,16,20. Instead of an asterisk before the slash operator, you can also use a range of values. For example, 1-30/10 means the same as 1,11,21.

To understand how a crontab works, let’s set up a cron job that happens every 2 minutes.

Create Crontab

  • Open a crontab
  • Scroll to the bottom and enter the code
  • Or delete all the commented lines and add this line
  • CTRL O
  • CTRL X

Schedule every 2 Minutes

~$ crontab -e
*/2 * * * * /home/project/sqlbackup.sh > /home/project/backup.log

# verify it's listed
$ crontab -l
*/2 * * * * /home/project/sqlbackup.sh > /home/project/backup.log

Start Cron

The cron service needs to be explicitly started.

~$ sudo service cron start
 * Starting periodic command scheduler cron
   ...done.

Wait 2 minutes then check to see if the backup was created (since we set the schedule to backup every 2 minutes)

Check Cron Job

Let’s check to see if a backup was created

t$ ls -l /home/theia/backups
total 1848
-rw-rw-r-- 1 theia theia 627901 Oct  3 17:46 all-database-03-10-2024_17-46-01.gz
-rw-rw-r-- 1 theia theia 627901 Oct  3 17:48 all-database-03-10-2024_17-48-01.gz
-rw-rw-r-- 1 theia theia 627901 Oct  3 17:50 all-database-03-10-2024_17-50-01.gz

STOP Cron

~$ sudo service cron stop
 * Stopping periodic command scheduler cron
   ...done.

Schedule Every Monday at 12am

0 0 * * 1 /home/project/sqlbackup.sh > /home/project/backup.log

Every day at 6am

0 6 * * * /home/project/sqlbackup.sh > /home/project/backup.log

Truncate Tables

Now that we have automated the backup task, let’s replicate a scenario where the data is corrupted or lost and you will remove all the data in the database and restore the data from the backup.

We will create a truncate script that does the following:

  • Connects to mysql RDBMS using the credentials.
  • Lists tables using show tables and feeds the output using pipe(|) operator to the next command.
  • Iterates through each table using a while loop and truncates the table.

  1. Create a new file named truncate.sh under home/project.

  2. Use this script to truncate the tables

  3. Save file

#!/bin/sh
 
DATABASE=sakila

mysql -Nse 'show tables' sakila | \
    while read table; do mysql \
    -e "use sakila;SET FOREIGN_KEY_CHECKS=0;truncate table $table;SET FOREIGN_KEY_CHECKS=1;" ;done

Set Permissions

$ sudo chmod u+x+r truncate.sh

Manually Truncate Tables w Script

$ bash truncate.sh


ERROR 1146 (42S02) at line 1: Table 'sakila.actor_info' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'sakila.customer_list' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'sakila.film_list' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'sakila.nicer_but_slower_film_list' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'sakila.sales_by_film_category' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'sakila.sales_by_store' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'sakila.staff_list' doesn't exist

Check if Tables were Truncated

  • Sign in to MySQL
  • Connect to db: sakila
  • Check all Tables
~$ mysql

mysql>  use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
22 rows in set (0.01 sec)
  • Retrieve all the rows from staff table. If the truncate script worked the output should be an Empty Set
  • quit mysql
mysql>   select * from staff;
Empty set (0.01 sec)

mysql> \q
Bye

Restore db

To restore the database:

  • You pick up a compressed zip file present in the backup folder and unzip it to extract the sql file using the gunzip command.
  • You connect to the mysql database and restore the database with the sqlfile.
  1. In the terminal window, run the following command to find the list of backup files that have been created.
~$ ls -l /home/theia/backups
total 1848
-rw-rw-r-- 1 theia theia 627901 Oct  3 17:46 all-database-03-10-2024_17-46-01.gz
-rw-rw-r-- 1 theia theia 627901 Oct  3 17:48 all-database-03-10-2024_17-48-01.gz
-rw-rw-r-- 1 theia theia 627901 Oct  3 17:50 all-database-03-10-2024_17-50-01.gz
  1. Select the file that you want to restore the data from and copy the file name.
  2. Unzip the file and extract the SQL file from the backup file.
~$ gunzip /home/theia/backups/all-database-03-10-2024_17-50-01.gz
  1. Populate and restore db with the sql that was in the zipped file we just unzipped
  2. Look in the file tree for the file name: it appears the uncompressed file becomes an sql file with the same name
  3. So we use it to populate the db
mysql sakila < /home/theia/backups/all-database-03-10-2024_17-50-01
  1. Start MySQL
  2. Connect to db sakila
  3. Select all the rows from staff table
  4. You’ll see the db has been restored
  5. Remember above the same query yielded an empty set
  6. quit MySQL
mysql
mysql>   select * from staff;
+----------+------------+-----------+------------+------------------+------------------------------+----------+--------+----------+------------------------------------------+---------------------+
| staff_id | first_name | last_name | address_id | picture          | email                        | store_id | active | username | password                                 | last_update         |
+----------+------------+-----------+------------+------------------+------------------------------+----------+--------+----------+------------------------------------------+---------------------+
|        1 | Mike       | Hillyer   |          3 | NULL             | Mike.Hillyer@sakilastaff.com |        1 |      1 | Mike     | 8cb2237d0679ca88db6464eac60da96345513964 | 2006-02-15 11:57:16 |
|        2 | Jon        | Stephens  |          4 | NULL             | Jon.Stephens@sakilastaff.com |        2 |      1 | Jon      | 8cb2237d0679ca88db6464eac60da96345513964 | 2006-02-15 11:57:16 |
+----------+------------+-----------+------------+------------------+------------------------------+----------+--------+----------+------------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> \q
Bye

Backup & Restore - 2

Create a shell script which takes the database name and back up directory as parameters and backs up the database as &lt;dbname&gt;_timestamp.sql in the backup directory. If the database doesn’t exist, it should display appropriate message. If the backup dir doesn’t exist, it should create one.

dbname=$(mysql -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$1'" | grep $1)

if [ ! -d $2 ]; then 
    mkdir $2
fi

if [ $1 == $dbname ]; then
    sqlfile=$2/$1-$(date +%d-%m-%Y).sql
    if mysqldump  $1 > $sqlfile ; then
    echo 'Sql dump created'
    else
        echo 'Error creating backup!'
    fi
else
    echo "Database doesn't exist"
fi

Write a shell script which takes the database name and the script file as parameters and restores the database from the sql file.

if [ -f $2 ]; then 
    dbname=$(mysql -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$1'" | grep $1)
    if [ $1 != $dbname ]; then
        echo "Created DB as it didn't exist"
        mysql -e "Create database $1"
    fi
    mysql -e "use $1"
    mysql $1 < $2
else
    echo "File doesn't exist"
fi

Clean up Backup Folder

sudo rm -rfv /home/theia/backups