by Banidom
During the development of a series of application for our customer, the team run into several database synchronization issues. We are running the new applications with the AWS RDS MYSQL8.0 while the legacy system and applications are saving data onto the RDS MySQL5.7.
After experimenting, we decided that there must be a synchronization system to manage between the two RDS. This is to allow immediate need to keep content from RDS MySQL5.7 into RDS MySQL8.0. With these, new applications will only need to access data from the latest MySQL version. The objective is to have the synchro in real-time.
AWS RDS does provide a fully managed relational database management system that allows users to launch, operate, and scale relational databases in the Amazon Cloud Services. However, ensuring data consistency between different RDS of identical schema implementation is not provided – at least not yet at the time of writing. It is in the works, but there is no committed ETA.
A few MySQL database usage terms is in order. These terms will be referred to continuously in this article. SQL commands are divided into four subgroups, DDL, DML, DCL, and TCL.
DDL is the short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.
DCL is the short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions, and other controls of the database system.
TCL is the short name of Transaction Control Language which deals with a transaction within a database.
In MySQL, a trigger is a stored program invoked automatically in response to some – not all – DML event such as insert, update, or delete that occurs in the associated table. MySQL supports only row-level triggers. It doesn’t support statement-level triggers. There are two aspects of the implementation that require attention:
- Ensuring real-time synchro implementation because of DML.
Ensuring real-time synchro requires that every data change – through DML command – in MySQL5.7 databases will be updated in MySQL8.0 databases. There is an identical schema in both RDS. Our scope of work only acted in one direction. Key delivery is to ensure RDS MySQL8.0 is consistent with its mirror MySQL5.7 RDS.
- Monitoring database, DDL changes.
To ensure any DDL command is monitored so that the fist part is operational and accurate. Operational implies that every aspect of the synchronization process works, and accurately implies every content is synchronized.
The implementation is divided into two modules:
- First is we ensuring continuous real-time synchronization, and the second is to monitor and react to any DDL change to the source schema.
- The second part is needed as in our environment, the customer in the midst of development and requires making some changes to the table structures.
The synchronization is not fully autonomous. Regular verification must be done to ensure alteration (ALTER) of target environment tables having the same data structure definition as the source. The manual intervention is also necessary for miss synchronization attempt on the table with different fields.
Our recommendation is always best to fully migrate the data into a singular data environment. Maintaining synchronization like could cause a lot of DBA work to ensure completeness and effectiveness. We have effectively executed this strategy for one of our valued clients, yielding impressive results.
