How to sync your one mysql database with another (for backup purpose only)

Note: This blog post describes a way of replicating / copying one mysql database to different one via database triggers approach only. There can be a lot other ones and probably are different approaches to solve the same problem as well.

In this blog post, we will look into how we can keep one mysql database sync with other one using database triggers.

Few months back, I was working on one of our client’s project which has a backend written in PHP & MySQL. They had hosted their app over apache and mysql servers. They have configured two different deployments production (live to the world) and staging (under development).

Problem: The problem client was facing is that the staging DB always needed to be updated manually whenever they make any changes to production. So, they wanted to automate the process in someway so that whenever any operation (Create / Update / Delete operation) is being performed over production, it gets carried over to staging as well. In this way staging DB will always be in sync with production.

You might be thinking this problem isn’t a new one, but as a developer and beginner to mysql of course, it was new to me and of course to you too (if you’re beginner too).

I search about the problem on google and get to know about database triggers — which solved this problem.

What is a database trigger?

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update. — via MySql Dev. Documentation

A database trigger is special stored procedure that is run when specific actions occur within a database. Most triggers are defined to run when changes are made to a table’s data.Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT, UPDATE, and DELETE. — via EssentialSql

As far as I read, triggers only work upon database table(s). so we have to define separate triggers for each table in the database.

Here’s the basic trigger syntax:

CREATE TRIGGER <trigger_name> <trigger_action> ON <table_name> 
FOR EACH ROW
BEGIN
// handle what to do on trigger activation.
END;

Here, The CREATE TRIGGER statement creates a trigger named <trigger_name> that is associated with the <table_name> table. It also includes clauses that specify the trigger action time, the triggering event, and what to do when the trigger activates:

Whether you’re on windows or linux or macOS and using xampp/wampp/mamp/navicat/etc server tool, All you need is to create two databases with the names given below:

1. test_staging_db: It is database which we call staging db. where triggers may push changes/updates.

2. test_production_db: It is database which we call production db. on which we define / write triggers which will push changes to staging db.

Assuming both databases have same schema and hosted on the same server.

If you have created both databases, now you can execute this code so it will creates user table on both databases.

/*
TABLE STRUCTURE FOR `users`
*/
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`name` varchar(30) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Recap. What we need?
1. To write basic triggers for `INSERT`, `UPDATE` and `DELETE` events/operations on `test_production_db.users` table.

2. To handle failures / errors / exceptions while copying change over staging database from production database.

Below is what each trigger will do:
- on INSERT in test_production_db.users, INSERT same row in test_staging_db.users
- on UPDATE in test_production_db.users, UPDATE same row in test_staging_db.users
- on DELETE in test_production_db.users, DELETE same row from test_staging_db.users

Here’s a basic triggers script for handling all three events.

DELIMITER //
-- TRIGGER FOR INSERT
DROP TRIGGER IF EXISTS `test_prod_db_users_ai`;
CREATE TRIGGER `test_prod_db_users_ai` AFTER INSERT ON `test_production_db`.`users` FOR EACH ROW
BEGIN
INSERT INTO `test_staging_db`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);
END; //
-- TRIGGER FOR UPDATE
DROP TRIGGER IF EXISTS `test_prod_db_users_au`;
CREATE TRIGGER `test_prod_db_users_au` AFTER UPDATE ON `test_production_db`.`users` FOR EACH ROW
BEGIN
UPDATE `test_staging_db`.`users`
SET name = NEW.name,
age = NEW.age
WHERE id = NEW.id;
END; //
-- TRIGGER FOR DELETE
DROP TRIGGER IF EXISTS `test_prod_db_users_ad`;
CREATE TRIGGER `test_prod_db_users_ad` AFTER DELETE ON `test_production_db`.`users` FOR EACH ROW
BEGIN
DELETE FROM `test_staging_db`.`users`
WHERE id = NEW.id;
END; //
DELIMITER;

When you execute above script over test_production_db it will define triggers for all CRUD operations on user tables in which if you see we’re performing same action on test_staging_db so whenever any row added / updated / deleted from test_production_db then it will also be updated in test_staging_db

Handling Trigger(s) Failures

What if trigger fail? How we have to handle them?

Possible Failures (can be these and more):

  • What If test_staging db is not there or unable to connect?
  • What If test_staging db performing Insert / Update or Delete fails?

We’re going to create new table over test_production_db in which we store all errors we catch. This is basic schema of how table will be look like:

-- -----------------------
-- Table structure for `errors`
-- -----------------------
DROP TABLE IF EXISTS `errors`;
CREATE TABLE IF NOT EXISTS `errors` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`code` varchar(30) NOT NULL,
`message` TEXT NOT NULL,
`query_type` varchar(50) NOT NULL,
`record_id` int(11) NOT NULL,
`on_db` varchar(50) NOT NULL,
`on_table` varchar(50) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

After executing the above table script, we need to modify our triggers so that they can handle failure(s) and error(s) and store them into errors table.

DELIMITER //
-- TRIGGER FOR INSERT
DROP TRIGGER IF EXISTS `test_prod_db_users_ai`;
CREATE TRIGGER `test_prod_db_users_ai` AFTER INSERT ON `test_production_db`.`users` FOR EACH ROW
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errorCode CHAR(5) DEFAULT '00000';
DECLARE errorMessage TEXT DEFAULT '';
    -- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
END;
    -- Perform the insert
INSERT INTO `test_staging_db`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);
    -- Check whether the insert was successful
IF errorCode != '00000' THEN
INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'insert', NEW.id, 'test_staging_db', 'users');
END IF;
END; //
-- TRIGGER FOR UPDATE
DROP TRIGGER IF EXISTS `test_prod_db_users_au`;
CREATE TRIGGER `test_prod_db_users_au` AFTER UPDATE ON `test_production_db`.`users` FOR EACH ROW
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errorCode CHAR(5) DEFAULT '00000';
DECLARE errorMessage TEXT DEFAULT '';
    -- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
END;
    -- Perform the update
UPDATE `test_staging_db`.`users`
SET name = NEW.name,
age = NEW.age
WHERE id = NEW.id;

-- Check whether the update was successfull
IF errorCode != '00000' THEN
INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'update', NEW.id, 'test_staging_db', 'users');
END IF;
END; //
-- TRIGGER FOR DELETE
DROP TRIGGER IF EXISTS `test_prod_db_users_ad`;
CREATE TRIGGER `test_prod_db_users_ad` AFTER DELETE ON `test_production_db`.`users` FOR EACH ROW
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errorCode CHAR(5) DEFAULT '00000';
DECLARE errorMessage TEXT DEFAULT '';
    -- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
END;
    -- Perform the delete
DELETE FROM `test_staging_db`.`users`
WHERE id = NEW.id;

-- Check whether the delete was successfull
IF errorCode != '00000' THEN
INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'delete', OLD.id, 'test_staging_db', 'users');
END IF;
END; //
DELIMITER;

After that we have written a PHP script which runs every after N minutes via some cron job and checks for if there’re any new errors in errors table then email them to the Ops team via email client in PHP.

If you have any thoughts or suggestions, please comment.

Cheers,