blog
Comparing Replication Solutions From Oracle and MySQL
Databases can fail without warning – either because of a crash caused by a software bug, or the underlying hardware components. The cloud brings another dimension to the issue, because of the ephemeral nature of compute and storage resources. To insulate our database infrastructure from these failures, we build redundancy into our systems. If an instance becomes unavailable, a standby system should be able to take the workload and carry on from there. Replication is a well known and widely adopted method for creating redundant copies of a master database.
In this post, we are going to compare the replication functionality in the two most popular database systems on the planet (according to db-engines) – Oracle and MySQL. We’ll specifically look at Oracle 12c logical replication, and MySQL 5.7. Both technologies offer reliable standby systems to offload production workloads and help in case of disaster. We will take a look at their different architectures, analyze pros and cons and go through the steps on how to setup replication with Oracle and MySQL.
Oracle Data Guard Architecture – How it Works
Oracle Data Guard assures high availability, data protection, and disaster recovery of your data. It’s probably an Oracle DBA’s first choice for replicating data. The technology was introduced in 1990 (version 7.0) with an essential apply of archive logs on standby databases. Data Guard evolved over the years and now provides a comprehensive set of services that create, maintain, manage, and monitor standby databases.
Data Guard maintains standby databases as copies of the production database. If the primary database stops responding, Data Guard can switch any standby to the production role, thus downtime. Data Guard can be used for backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard is a Ship Redo / Apply Redo technology, “redo” is the information needed to recover transactions. A production database referred to as a primary database broadcasts redo to one or more replicas referred to as standby databases. When an insert or update is made to a table, this change is captured by the log writer into an archive log, and replicated to the standby system. Standby databases are in a continuous phase of recovery, verifying and applying redo to maintain synchronization with the primary database. A standby database will also automatically re-synchronize if it becomes temporarily disconnected to the primary due to power outages, network problems, etc.
Data Guard Redo Transport Services regulate the transmission of redo from the primary database to the standby database. LGWR (log writer) process submits the redo data to one or more network server (LNS1, LSN2, LSN3, …LSNn) processes. LNS is reading from the redo buffer in the SGA (Shared Global Area) and passes redo to Oracle Net Services to transmit to the standby database. You can choose the LGWR attributes: synchronous (LogXptMode = ‘SYNC’) or asynchronous mode (LogXptMode = ‘ASYNC’). With such architecture it is possible to deliver the redo data to several standby databases or use it with Oracle RAC (Real Application Cluster). The Remote File Server (RFS) process receives the redo from LNS and writes it to a regular file called a standby redo log (SRL) file.
There are two major types of Oracle Data Guard. Physical with redo apply and Logical standby databases with SQL apply.
SQL apply requires more processing than redo applies, the process first read the SRL and “mine” the redo by converting it to logical change records, and then builds SQL transactions before applying the SQL to the standby database. There are more moving parts so it requires more CPU, memory and I/O then redo apply.
The main benefit of “SQL apply” is that the database is open to read-write, while the apply process is active.
You can even create views and local indexes. This makes it ideal for reporting tools. The standby database does not have to be a one to one copy of your primary database, and therefore may not be the best candidate for DR purposes.
The key features of this solution are:
- A standby database that is opened for read-write while SQL apply is active
- Possible modification lock of data that is being maintained by the SQL apply
- Able to execute rolling database upgrades
There are drawbacks. Oracle uses a primary-key or unique-constraint/index supplemental logging to logically recognize a modified row in the logical standby database. When database-wide primary-key and unique-constraint/index supplemental logging is enabled, each UPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database. Oracle Data Guard supports chained replication, which here is called “cascade” however it’s not typical due to the complexity of the setup.
Oracle recommends that you add a primary key or a non-null unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database. This means it doesn’t work on any setup, you may need to modify your application.
Oracle Golden Gate Architecture – How it Works
With Data Guard, as blocks are changed in the database, records are added to the redo log. Then based on the replication mode that you are running, these log records will either be immediately copied to the standby or mined for SQL commands and applied. Golden Gate works in a different way.
Golden Gate only replicates changes after the transaction is committed, so if you have a long running transaction, it can take a while to replicate. The Golden Gate “extract process” keeps transactional changes in memory.
Another big difference is that Oracle Golden Gate enables the exchange and manipulation of data at the transaction level among multiple, heterogeneous platforms. You are not only limited to Oracle database. It gives you the flexibility to extract and replicate selected data records, transactional changes, and changes to DDL (data definition language) across a variety of topologies.
The typical Golden Gate flow shows new and changed database data being captured from the source database. The captured data is written to a file called the source trail. The trail is then read by a data pump, sent across the network, and written to a remote trail file by the Collector process. The delivery function reads the remote trail and updates the target database. Each of the components is managed by the Manager process.
MySQL Logical replication – How it Works
Replication in MySQL has been around for a long time and has been evolving over the years. There are different ways to enable MySQL replication, including Group Replication, Galera Clusters, asynchronous “Master to Slave”. To compare Oracle vs. MySQL architecture, we will focus on replication formats as it is the base for all the various replication types.
First of all, the different replication formats correspond to the binary logging format specified in my.cnf configuration file. Regardless of the format, logs are always stored in a binary way, not viewable with a regular editor. There are three format types: row-based, statement based and mixed. Mixed is the combination of first two. We will take a look at statement and row based.
Statement based – in this case these are the written queries. Not all statements that modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based replication. LOAD_FILE(), UUID(), UUID_SHORT(), USER(), FOUND_ROWS() etc will be not replicated.
Row-based – in this case, these are changes to records. All changes can be replicated. This is the safest form of replication. Since 5.7.7, it’s the default option.
Now let’s take a look what is happening under the hood when replication is enabled.
First of all, the master database writes changes to a file called binary log or binlog. Writing to binary log is usually a lightweight activity because writes are buffered and sequential. The binary log file stores data that a replication slave will be processing later, the master activity doesn’t depend on them. When the replication starts, mysql will trigger three threads. One on the master, two on the slave. The master has a thread, called the dump thread, that reads the master’s binary log and delivers it to the slave.
On the slave, a process called IO thread connects to the master, reads binary log events from the master as they come in and copies them over to a local log file called relay log. The second slave process – SQL thread – reads events from a relay log stored locally on the replication slave and then utilizes them.
MySQL supports chained replication, which is very easy to setup. Slaves which are also masters must be running with –log-bin and –log-slave-update parameters.
To check the status of the replication and get information about threads, you run on the slave:
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 339
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 635
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 339
Relay_Log_Space: 938
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Current_Pos
Gtid_IO_Pos: 0-1-8
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)Setting up Data Guard Logical Replication in Oracle
-
Create a Physical Standby Database
To create a logical standby database, you first create a physical standby database and then transition it to a logical standby database.
-
Stop Redo Apply on the Physical Standby Database
Stopping Redo Apply is necessary to avoid applying changes.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -
Prepare the Primary Database to Support a Logical Standby Database
Change the VALID_FOR attribute in the original LOG_ARCHIVE_DEST_1 and add LOG_ARCHIVE_DEST_3 for logical database.
LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/severalnines/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=severalnines' LOG_ARCHIVE_DEST_3= 'LOCATION=/arch2/severalnines/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=severalnines' LOG_ARCHIVE_DEST_STATE_3=ENABLEBuild a Dictionary in the Redo Data
SQL> EXECUTE DBMS_LOGSTDBY.BUILD; -
Convert to a Logical Standby Database
To continue applying redo data to the physical standby database until it is ready to convert to a logical standby database, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name; -
Adjust Initialization Parameters for the Logical Standby Database
LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/severalnines_remote/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=severalnines_remote' LOG_ARCHIVE_DEST_2= 'SERVICE=severalnines ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=severalnines' LOG_ARCHIVE_DEST_3= 'LOCATION=/arch2/severalnines_remote/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=severalnines_remote' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=ENABLE -
Open the Logical Standby Database
SQL> ALTER DATABASE OPEN RESETLOGS;Verify the Logical Standby Database Is Performing Properly
v$data_guard_stats view
SQL> COL NAME FORMAT A20 SQL> COL VALUE FORMAT A12 SQL> COL UNIT FORMAT A30 SQL> SELECT NAME, VALUE, UNIT FROM V$Data_Guard_STATS; NAME VALUE UNIT -------------------- ------------ ------------------------------ apply finish time +00 00:00:00 day(2) to second(1) interval apply lag +00 00:00:00 day(2) to second(0) interval transport lag +00 00:00:00 day(2) to second(0) intervalv$logstdby_process view
SQL> COLUMN SERIAL# FORMAT 9999 SQL> COLUMN SID FORMAT 9999 SQL> SELECT SID, SERIAL#, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS; SID SERIAL# SPID TYPE HIGH_SCN ----- ------- ----------- ---------------- ---------- 48 6 11074 COORDINATOR 7178242899 56 56 10858 READER 7178243497 46 1 10860 BUILDER 7178242901 45 1 10862 PREPARER 7178243295 37 1 10864 ANALYZER 7178242900 36 1 10866 APPLIER 7178239467 35 3 10868 APPLIER 7178239463 34 7 10870 APPLIER 7178239461 33 1 10872 APPLIER 7178239472 9 rows selected.
These are the necessary steps to create Oracle Data Guard logical replication. Actions will be slightly different if you perform this operation with non default compatibility set or databases running in Oracle RAC environment.
Setting up MySQL replication
-
Configure Master database. Set unique server_id, specify different replication logs –log-basename (MariaDB) , activate binary log. Modify my.cnf file with below information.
log-bin server_id=1 log-basename=master1Login to master database and grant replication user to access master data.
GRANT REPLICATION SLAVE ON *.* TO replication_user -
Start both servers with GTIDs enabled.
gtid_mode=ON enforce-gtid-consistency=true -
Configure the slave to use GTID-based auto-positioning.
mysql> CHANGE MASTER TO > MASTER_HOST = host, > MASTER_PORT = port, > MASTER_USER = replication_user, > MASTER_PASSWORD = password, > MASTER_AUTO_POSITION = 1; -
If you want to add slave to master with data, then you need to take backup and restore it on slave server.
mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --user=root --password=rootpassword > dump_replication.sqlLogin to slave database and execute:
slave> tee dump_replication_insert.log slave> source dump_replication.sql slave> CHANGE MASTER TO MASTER_HOST="host", MASTER_USER=" replication_user ", MASTER_PASSWORD="password ", MASTER_PORT=port, MASTER_AUTO_POSITION = 1;