Mysql Replication

MySQL database servers (slave)-

I/O thread and SQL Thread-

Replication I/O threads- 

When a start slave statement is issued on a replica server, the replica creates an I/O thread, which connects to the source and asks it to send the updates recorded in its binary logs. The replication I/O thread reads the updates that the source's Binlog Dump thread sends and copies them to local files that comprise the replica's relay log.

The state of this thread is shown as Slave_IO_running in the output of show slave status.

 

Replication SQL threads-

The replica creates an SQL thread to read the relay log that is written by the replication I/O thread and execute the transactions contained in it.

There are three main threads for each source/replica connection. A source that has multiple replicas creates one binary log dump thread for each currently connected replica, and each replica has its own replication I/O and SQL threads.

 

Binary log dump thread-

The source creates a thread to send the binary log contents to a replica when the replica connects. This thread can be identified in the output of show process list on the source as the Binlog Dump thread.

The binary log dump thread acquires a lock on the source's binary log for reading each event that is to be sent to the replica. As soon as the event has been read, the lock is released, even before the event is sent to the replica.

 

Step 1-

Login to the database and check the global variable gtid.

Step 2-

To Check the server id and binary logfile.

Step 3-  

Open the MySQL configuration file and config.

Gtid_mode=ON

enforce_gtid_consistency=ON

server id=100

log-bin=gtidsource

Step 4 –

Restart MySQL service.

Step 5-

Now check global variable gtid, server_id and binary log.

Step 6-

Create a user in master.

Create user repl@’%’ identified by ‘Rainbow@123’

 

Step 7-

check global variable gtid in replica.

Step 8-

Check server_id and binary_log in replica.

Step 9-

Now check replica status.

Step 10-

Change Master to master user.

 

mysql> change master to  master_user='repl',master_password='Secure@321’, master_host='192.168.0.195',master_auto_position=1,get_master_public_key=1;

Query OK, 0 rows affected, 8 warnings (0.02 sec)

 

mysql> start replica;

Query OK, 0 rows affected (0.01 sec)

 

mysql> show replica status \G

*************************** 1. row ***************************

Replica_IO_State: Waiting for source to send event

Source_Host: 192.168.0.195

Source_User: repl

Source_Port: 3306

Connect_Retry: 60

Source_Log_File: gtidsource.000003

Read_Source_Log_Pos: 157

Relay_Log_File: clota-dbl06-relay-bin.000002

Relay_Log_Pos: 375

Relay_Source_Log_File: gtidsource.000003

Replica_IO_Running: Yes

Replica_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_Source_Log_Pos: 157

Relay_Log_Space: 591

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Source_SSL_Allowed: No

Source_SSL_CA_File:

Source_SSL_CA_Path:

Source_SSL_Cert:

Source_SSL_Cipher:

Source_SSL_Key:

Seconds_Behind_Source: 0

Source_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Source_Server_Id: 100

Source_UUID: 25474780-4a56-11ef-b77c-000c2926dc41

Source_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

Source_Retry_Count: 86400

Source_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Source_SSL_Crl:

Source_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 89ffa9fd-4a4c-11ef-8d4a-000c2995d827:1-4

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Source_TLS_Version:

Source_public_key_path:

Get_Source_public_key: 1

Network_Namespace:

1 row in set (0.00 sec)

 

Step 11-

After enabaling gtid.

Step 12-

Create a database and check the global variable gtid.

Step 13-

Check server_uuid.

Step 14-

Change database and check global variable gtid in replica.

Step 15-

 In replica use f1 database and check the table.