How Replication transfer events from Master to Slave in MySQL

-
In MySQL the events (write operations) are transferred from master to slave with the help of threads.

Thread Name Runs On
Binlog Dump Master
IO Slave
SQL Slave

and in the following chronological order.
  • The slave connects to the master (as a client) using the credentials that were given during setup. This credentials are set by executing CHANGE MASTER TO at slave side. It might be possible that MySQL is storing the credentials in a plain form. Hence it is advisable to create a separate user in Master with privileges that grant it the power of serving the purpose of replication only.
  • IO thread from the slave asks for new events in the master if any.
  • Binlog Dump thread from master checks this and send contents to IO thread if new events are do present.
  • IO thread then writes the new events to the relay logs on slave.
  • SQL thread on the slave then reads these events from relay logs and apply them to the database.

MySQL Master Slave Replication Diagram

This is how slave keeps up with master and maintain a copy of the master database. It is even recommended to set read_only server variable to TRUE on slave to prevent writes outside from replication.

Since the replication process is asynchronous, hence we have the flexibility to stop and start the IO and SQL thread independently in situations required.

Also it is possible for slaves to lag behind master within few events up to a bigger extent.

Please note that it is possible to setup MySQL master slave replication by two methods:
  • Binary Log Coordinates → Each replica keeps a record of the binary log coordinates: the binary log file name and position within the file that it has read and processed from the source.
  • GTID → A GTID (Global Transaction IDentifier) is represented as a pair of coordinates (GTID = source_id:transaction_id). The source_id identifies the originating server. Normally, the server's server_uuid is used for this purpose. The transaction_id is a sequence number determined by the order in which the transaction was committed on this server; for example, the first transaction to be committed has 1 as its transaction_id, and the tenth transaction to be committed on the same originating server is assigned a transaction_id of 10. It is not possible for a transaction to have 0 as a sequence number in a GTID.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more