Database replication is a service for shipping changes to your database, off to a copy housed on another server, potentially even in another data center.
Each change to the records of information in your database or groups of them are organized into transactions. These each get unique identifiers or change numbers. Those groupings of changes are logged to transaction logs which are then moved across to the sibling database server, and applied there.
Oracle offers this facility in the form of Standby Database aka Dataguard. Although it can be messy to setup, it is fairly bulletproof. If you don’t receive any errors in your logfiles, you can rest assured that the data on your main or master database server looks exactly the same as what’s on your secondary server. With MySQL the situation can be a bit more complicated. MySQL replication was built based on statements, not changed data. So those same statements or instructions (SQL statements of DML & DDL) get rerun on the secondary database server. Some of the problems associated with this are:
Caveats aside, MySQL replication is powerful, and quite fast. With the right caution, and correct tools, it can be as robust and reliable as Oracle’s own standby database technology.
Replication can also be a powerful high availability tool in your arsenal. Setup as a master-master cluster, you can keep your application pointed to the “active” side or role while performing blocking operations on the inactive database server. Since master-master cluster also known as circular replication applies changes to both databases, you’ll need to stop replication during the blocking operation. Once it is completed on the inactive side, redirect the webservers & application to the database where you completed the operation, then reenable replication and watch the databases catch up with each other!