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-
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.
