How to take backup of MYSQL tables daily and remove the older backups

In day to day heavy transactions happening in our DB, we sometimes wish to retain the previous states of the tables.

This not only prevents the scenarios of recovering data after deletions by mistakes but also helps during the comparison requirements.

Here is a quick take on such a use case, where the developer has been asked for making sure the tables are backed up on the daily basis.

Based on the business requirements and infra availability, the clean up period can be decided. For this example, let's take a clean-up policy of 1 day.

Let's create a sample table - 'employees', using MySQL - 

mysql> show create table employees;

+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table     | Create Table                                                                                                                                                                     |

+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| employees | CREATE TABLE `employees` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from employees;

+----+------+

| id | name |

+----+------+

|  1 | ABC  |

|  2 | DEF  |

+----+------+

2 rows in set (0.00 sec)

Let's create a stored procedure now, which will take care of the running on regular basis and meeting the expected functionality - 

Repo:

https://github.com/namitsharma99/tableBackupStoredProcedure/blob/master/backup_main_tables.sql

Raw Code:

DROP PROCEDURE backup_main_tables;

DELIMITER $$

CREATE PROCEDURE backup_main_tables()

BEGIN

  -- setup for today's backup

  SET @today_table_name := concat('bkp_employees_',date_format(curdate(), '%m_%d_%Y'));

  SET @today_stmnt := concat('create table ', @today_table_name, ' select * from employees;');

  -- setup for yesterday's backup removal

  SET @yesterday_table_name := concat('bkp_employees_',date_format(curdate()-1, '%m_%d_%Y'));

  SET @yesterday_stmnt := concat('drop table ', @yesterday_table_name);

  -- execute today's plan

  PREPARE today_stmt FROM @today_stmnt;

  EXECUTE today_stmt;

  -- remove yesterday's backup

  PREPARE yesterday_stmt FROM @yesterday_stmnt;

  EXECUTE yesterday_stmt;

END $$

DELIMITER ;

mysql> show create procedure backup_main_tables;

+--------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| Procedure          | sql_mode | Create Procedure                                                                                                                                         | character_set_client | collation_connection | Database Collation |

+--------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| backup_main_tables |          | CREATE DEFINER=`admin`@`%` PROCEDURE `backup_main_tables`()

BEGIN

  SET @today_table_name := concat('bkp_employees_',date_format(curdate(), '%m_%d_%Y'));

  SET @today_stmnt := concat('create table ', @today_table_name, ' select * from employees;');

  SET @yesterday_table_name := concat('bkp_employees_',date_format(curdate()-1, '%m_%d_%Y'));

  SET @yesterday_stmnt := concat('drop table ', @yesterday_table_name);

  PREPARE today_stmt FROM @today_stmnt;

  EXECUTE today_stmt;

  PREPARE yesterday_stmt FROM @yesterday_stmnt;

  EXECUTE yesterday_stmt;

END | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |

+--------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

1 row in set (0.00 sec)

Dry Run Example:

mysql> select current_timestamp;

+---------------------+

| current_timestamp   |

+---------------------+

| 2020-09-13 20:33:44 |

+---------------------+

1 row in set (0.00 sec)

Let's create a table supposing it was created yesterday -

mysql> create table bkp_employees_09_12_2020  select * from employees;

Query OK, 2 rows affected (0.04 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> show tables;

+--------------------------+

| Tables_in_namit_bkp_test |

+--------------------------+

| bkp_employees_09_12_2020 |

| employees                |

+--------------------------+

2 rows in set (0.00 sec)

mysql> select * from bkp_employees_09_12_2020;

+----+------+

| id | name |

+----+------+

|  1 | ABC  |

|  2 | DEF  |

+----+------+

2 rows in set (0.00 sec)

Invoking the stored procedure to test:

mysql> call backup_main_tables();

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

+--------------------------+

| Tables_in_namit_bkp_test |

+--------------------------+

| bkp_employees_09_13_2020 |

| employees                |

+--------------------------+

2 rows in set (0.00 sec)

mysql> select * from bkp_employees_09_13_2020;

+----+------+

| id | name |

+----+------+

|  1 | ABC  |

|  2 | DEF  |

+----+------+

2 rows in set (0.00 sec)

Now this appears to be working fine. 

But we cannot everyday login and manually execute this statement, correct?

So, let's schedule it on a daily basis, preferably at the COB (closure of business) timing.

We can use MySQL Events for this example, as below.

Repo:

https://github.com/namitsharma99/tableBackupStoredProcedure/blob/master/event.sql

Raw Code:

DELIMITER $$

DROP EVENT IF EXISTS run_proc_call $$

CREATE

EVENT `run_proc_call` 

ON SCHEDULE EVERY 1 DAY STARTS '2020-09-13 23:55:55'

ON COMPLETION NOT PRESERVE ENABLE

DO 

BEGIN

  call backup_main_tables();

END $$

DELIMITER ;

mysql> show events;

+----------------+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| Db             | Name          | Definer | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |

+----------------+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| namit_bkp_test | run_proc_call | admin@% | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2020-09-13 23:55:55 | NULL | ENABLED | 1094908326 | latin1               | latin1_swedish_ci    | latin1_swedish_ci |

+----------------+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

1 row in set (0.01 sec)

Also, as a good practice, it is always recommended to have exception handling in the functions/ procedures/ events. The above examples are very basic in nature, hence I haven’t used.

In MySQL, you can use exit handlers, while in SQL you can use Try-Catch blocks. But, this should be used and the errors should be logged in a dedicated logging table.

Try these out, and please leave a comment in case of any concern 👍 

Happy Coding !!