Setting up replication in MySQL involves configuring a master database server to replicate data changes to one or more slave database servers. This process involves several steps, as outlined below:

1. Set up the master server: The first step is to set up the master server by configuring the MySQL server to allow replication. This involves modifying the MySQL configuration file (my.cnf) to include the following lines:

[mysqld]
log-bin = mysql-bin
server-id = 1

The log-bin parameter specifies the name of the binary log file that will be used to store the replication data, while the server-id parameter identifies the master server uniquely.

2. Create a replication user: The next step is to create a MySQL user that the slave server will use to connect to the master server and replicate the data. This can be done using the following SQL command:

3. Replace replication_user with a username of your choice, and password with a strong password.

4. Set up the slave server: Once the master server is configured, the next step is to set up the slave server by configuring the MySQL server to connect to the master server and replicate the data. This involves modifying the MySQL configuration file (my.cnf) to include the following lines:

[mysqld]
server-id = 2

The server-id parameter identifies the slave server uniquely.

4. Start replication: Once the master and slave servers are configured, you can start the replication process by running the following SQL command on the slave server:

CHANGE MASTER TO
MASTER_HOST='master_hostname',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;
 

Replace master_hostname with the hostname or IP address of the master server, and password with the password you set up for the replication user. MASTER_LOG_FILE and MASTER_LOG_POS specify the binary log file and position on the master server from which the replication should start.

5. Verify replication: Finally, you can verify that replication is working by checking the Slave_IO_State and Slave_IO_Running columns in the output of the SHOW SLAVE STATUS command on the slave server.

Replication in MySQL provides a robust and reliable way to distribute data across multiple servers for improved performance and redundancy. However, it is important to test and monitor the replication process regularly to ensure that it is working correctly.

If you are looking for consultation, fill the Contact Form below.

He who knows when he can fight and when he cannot will be victorious. Sun Tzu