MySQL replication is a process of copying data changes from a primary server (master) to one or more secondary servers (slaves). This creates a redundant and scalable setup for your database.
- Master Server: (192.168.42.128) The main server that holds the original, authoritative copy of the data. All write operations occur on the master.
- Slave Server: (192.168.42.129) A secondary server that receives updates (replicates) from the master. Slaves are typically used for read-only operations (e.g., reports, analytics) or load balancing read traffic.
Replication Process:
- Binary Logging: The master server enables binary logging, which records all data modifications (INSERT, UPDATE, DELETE) as binary events in a file called the binary log.
- Slave Configuration: The slave server is configured to connect to the master and specify replication settings. This includes details like the master’s hostname, port, username, and password.
- I/O Thread (Slave): The slave runs an I/O thread that continuously reads binary events from the master’s binary log.
- Relay Log (Slave): The slave stores the received binary events in its own local file called the relay log.
- SQL Thread (Slave): The slave runs a separate SQL thread that processes the events from the relay log. It applies these changes (CREATE, UPDATE, DELETE) to the slave’s database, keeping it synchronized with the master.
Benefits of Replication:
- High Availability: If the master server fails, a slave can be promoted to become the new master, minimizing downtime.
- Read Scalability: Slaves can handle read-only traffic, reducing the load on the master server and improving performance.
- Data Backup and Offloading: Slaves can be used for backups or running long-running queries without impacting the master’s performance.
Replication Types:
- Asynchronous Replication (Default): The most common type. The slave applies changes eventually, potentially lagging slightly behind the master.
- Semi-synchronous Replication: The master waits for at least one slave to acknowledge receiving the event before committing the transaction. Offers a balance between availability and consistency.
step-by-step process for setting up MySQL replication:
Master Server Configuration:
-
Enable Binary Logging:
Edit the master server’s configuration file (my.cnf). Within the [mysqld] section, add or uncomment the option log_bin = mysql-bin (or a desired filename). This enables binary logging, which records data changes for replication.
| 1 2 3 4 | sudo vi /etc/my.cnf bind-address =192.168.42.128 server-id=1 log_bin =mysql-bin |
-
Configure Replication User:
Create a dedicated user on the master server with the necessary permissions for replication. Grant this user privileges like REPLICATION SLAVE to allow slave servers to connect and access the binary log.
| 1 2 3 4 5 | sudo mysql -u root -p CREATE USER 'replica'@'192.168.42.129' IDENTIFIED BY '*****@123'; CREATE USER replica'@'192.168.42.129' IDENTIFIED BY 'mypassword'; GRANT REPLICATION SLAVE ON *.*TO 'replica'@'192.168.42.129'; FLUSH PRIVILEGES; |
Slave Server Configuration:
-
Edit Slave Configuration:
Edit the slave server’s configuration file (my.cnf). Similar to the master, ensure binary logging is enabled using log_bin (though it’s not used for replication on the slave).
| 1 2 3 4 | sudo vi /etc/my.cnf bind-address =192.168.42.129 server-id=2 log_bin =mysql-bin |
-
Specify Master Information:
Within the [mysqld] section, add the following options:
| 1 2 3 4 5 6 | CHANGE MASTER TO MASTER_HOST='192.168.42.128' , MASTER_USER='replica' , MASTER_PASSWORD='P****@123' , MASTER_LOG_FILE='binlog.0000013' , MASTER_LOG_POS=156; |
-
Start Slave (Optional):
You can optionally start the slave SQL thread using the command START SLAVE on the slave server. This will initiate the connection to the master and begin reading the binary log.
| 1 2 3 4 5 | sudo systemctl restart mysqld mysql -p STOP SLAVE; reset slave; START SLAVE; |
Identify Master Log File and Position:
On the master server, use the command SHOW MASTER STATUS to get the current binary log filename and position. You’ll need this information for the next step.
| 1 2 3 4 5 6 7 8 | mysql> show master status\G *************************** 1. row *************************** File: binlog.000013 Position: 897 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.01 sec) |
Verification and Monitoring:
-
Start Slave SQL Thread:
Once the slave’s position is set, use the command START SLAVE to initiate the replication process.
-
Monitor Replication Status:
Use the command SHOW SLAVE STATUS on the slave server to view the current replication status. Look for indicators like Slave_IO_Running: Yes and Slave_SQL_Running: Yes to confirm successful replication.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.42.128 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000013 Read_Master_Log_Pos: 897 Relay_Log_File: Slave-relay-bin.000017 Relay_Log_Pos: 321 Relay_Master_Log_File: binlog.000013 Slave_IO_Running: Yes Slave_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_Master_Log_Pos: 897 Relay_Log_Space: 1436 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a3cb60af-cc36-11ee-bbbf-000c29c89eea Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) |
Conclusion: By following these steps we can create a basic MySQL replication setup to create a redundant and scalable database environment.
