Aug 1, 2024

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.



Jul 29, 2024

Install MySQL 8 on Oracle Linux

Installing MySQL on Oracle Linux is simple and straightforward. Download the latest version of MySQL based on your OS version.

Download via Command Line (optional):

Use wget or curl to download the file directly if you prefer using the command line

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz






Change to the directory where you downloaded the tarfile:

cd  /test

Extract the tarfile using tar:

tar -xvf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz













Now Please install all the package Step by Step :

Step 1 :Install mysql-commercial-common

yum install mysql-commercial-common-8.0.32-1.1.el9.x86_64.rpm

Step 2 :Install mysql-commercial-client-plugins

yum install mysql-commercial-client-plugins-8.0.32-1.1.el9.x86_64.rpm

Step 3 :Install mysql-commercial-libs

yum install mysql-commercial-libs-8.0.32-1.1.el9.x86_64.rpm

Step 4 :Install mysql-commercial-client

yum install mysql-commercial-client-8.0.32-1.1.el9.x86_64.rpm

Step 5 :Install mysql-commercial datafile

yum install mysql-commercial-icu-data-files-8.0.32-1.1.el9.x86_64.rpm

Step 6 :Install MySQL server:

yum install mysql-commercial-server-8.0.32-1.1.el9.x86_64.rpm


Start and enable the MySQL service:

 systemctl start MySQL

 systemctl enable MySQL

Run the Mysql_secure_installation script to secure your installation:

mysql_secure_installation

Check MySQL Status verify that MySQL is running:

systemctl status MySqld








Access MySQL connect to MySQL using the root account:

mysql -u root –p

Check the temporary password for MySQL:

grep 'temporary password' /var/log/mysqld.log

/usr/bin/mysql_secure_installation

Securing the MySQL server deployment:

Enter password for user root: <enter temp password>

The existing password for the user account root has expired. Please set a new password.

New password: <give new password>

Re-enter new password: <re-enter new password>

Connect to MySQL and check the databases:

mysql  -u root  -p

password ******









mysql> show databases;



 


Jul 28, 2024

Network actual bandwidth between servers.

Iperf:

Download the tool from https://iperf.fr/iperf-download.php

Keep it on both source and target server.











  • On one server (server), run:

Copy code

iperf3 -s








  • On server (server2){Cline machine}, run:

Copy code

iperf3 -c server1_IP



Note: make sure to open port 5201 on the server.