How MySQL replication works with Oracle logical standby

MySQL replication and Oracle logical standby are both SQL apply. What is the difference in implementation?

Binary log and redo transmission principles

MySQL replication can be easily used for application read expansion, and can also help MySQL implement a certain level of HA solution
In fact, the whole replication process is that slave captures binary logs from the master end and then executes the operations recorded in the logs in full order on itself.

(I) How to synchronize?

The master database writes all update operations to binary logs.
Run the "Io thread" (slave Io thread) from the database to read the binary logs of the master database.
Run the "SQL thread" (slave SQL thread) from the database to execute the SQL statements in the logs read by the I/O thread (slave Io thread), so as to maintain the consistency with the transactions in the master database.

(Ii) How do I allocate requests?

Currently, this part must be implemented at the application layer.
When an update SQL statement (Update, insert, delete) is executed, the master database is requested.
When querying SQL (select), the request is from the database
Therefore, when your application select request accounts for a larger proportion, the more effective the relication will be.


(3) How does MySQL transmit binary logs? Is it a master database push or a slave database pull? What is the real-time performance of MySQL Log transmission?

In the MySQL replication structure, the slave database completes the replication configuration by changing master to for the first time, and then uses the start slave command to start replication.
More detailed:
① The IO thread on slave connects to the master and initiates a request to the master to Read Binary logs (com_binlog_dump command ),
Log content after the specified location (or initial log) of the specified Log File
② After the master receives the com_binlog_dump request, it uses the dump thread to continuously send binary logs to the slave Io thread (the log information after the specified location of the log file)
Besides the information contained in the log, the returned information also includes the file name of the binary log on the master node and the location of the binary log.
③ Once a new log is generated by the master, a broadcast is sent immediately. After receiving the broadcast, the dump thread reads the binary log and transmits the log to the slave over the network.
④ After the slave Io thread receives the information, it writes the received log content to the end of the slave relay log file (mysql-relay-bin.xxxxxx) in turn. And read
The file name and location of the binary log of the Master are recorded in the master-info file, so that the master can be clearly told during the next read:
"Please send me the log Content starting from the location of a binary log"
⑤ After the slave SQL thread detects the newly added content in the relay log, it will immediately parse the content in the log file into the executable query statements in the real execution of the master,
And execute these queries on your own. In this way, the same query is actually executed on the master and slave, so the data at both ends is exactly the same.
Therefore, this is a process in which the master database keeps pushing data to the slave database.

After a new log is generated, only one broadcast and network is sent to the standby database immediately (<1 ms). If the network between the active and standby databases is good (such as RTT <1 ms ), the log on the slave database is less than 2 ms.
Therefore, in general (depending on RTT), the Standby database is very real-time.
Note:
RTT (round-trip time): round-trip latency, which is also an important performance indicator in computer networks,
It indicates the total latency from the time when the sender sends data to the time when the sender receives the confirmation from the receiver (the receiver sends the confirmation immediately after receiving the data ).



For Oracle, in primary, mongouard can use the archive process (arcn) or log write process (lgwr) to collect redo data and transmit it to standby.

(I) Use arcn to archive redo data
By default, RTS uses the arcn process to archive redo logs. However, the arcn archiving process only supports the highest-performance protection mode.
Archiving will be started when the primary log is switched:
● In primary (assuming there are two archiving processes), once the arc0 Process completes redolog archiving, The arc1 process begins to transmit the archive to the specified path of standby.
● In STANDBY, The RFS process writes redo data to the standby log in turn, and then the arcn process in standby writes it to the archive, and then applies the data to the standby database through SQL apply.

(Ii) Use lgwr to archive and synchronize redo data in real time
The process of using lgwr is significantly different from that of using arcn. lgwr does not have to wait for log switching and archiving.
● In primary, lgwr submits redo data to lnsn (lgwr network server process) process (n> 0), and lnsn initiates network transmission.
● The RFS (Remote File Server) of standby writes the received redo data to the standby log. During this period, the primary transactions will remain until all the log_archive_dest_n specified paths with the lgwr sync attribute have been received.

(Iii) Use lgwr to archive asynchronous redo data
The general steps are the same as synchronous transmission. The difference is only in the lnsn process. lgwr writes data to online redolog.
The lnsn process accesses the online redolog and transmits data to the Rfs of the remote standby instead of being associated with the local lgwr.
The processing logic of standby databases remains unchanged.

 

Replication implementation level and data protection mode

MySQL replication can be based on statement level or row level.
Different replication levels will affect the binary log records of the master to different forms.
① Row level
Binary log records each row of data in the modified form, and then slave modifies the same data.
Advantages:
The log content at the lower row level records the details of each row's data modification, which is easy to understand.
In some cases, the stored procedure, function, and trigger call and trigger cannot be correctly copied.
Disadvantages:
Generate amazing log volumes
② Statement level
Each query that modifies the data is recorded in the binary log of the master. When the Server Load balancer replicates data, the SQL thread parses the same query as the original one executed by the master to execute the statement again.
Advantages:
Reduces binary log volumes, saves Io costs, and improves performance
Disadvantages:
Begin must cascade the context information of each statement during execution.
In some cases, the call and trigger of stored procedures, functions, and triggers cannot be correctly copied.

Data Guard provides three data protection Modes

① Maximum protection ):
All transactions are written to at least one standby database redo data before submission.
If any fault occurs that causes the standby database to be unavailable, the primary database will be shut down.
② Maximum performance (maximum performance ):
Transactions can be committed at any time. The redo data of the current primary database also needs to be written to at least one standby database, but such writing can be non-synchronous.
③ Maximum availability (maximum availability ):
Like maximum protection, at least one standby database redo data is written synchronously.
However, if the Import fails and cannot write the standby database redo log at the same time, the primary database will not shutdown.
Instead, the Standby database is automatically converted to the highest availability mode.