Database Replication Master-Slave

Database replication is a strategy for maintaining multiple copies of data across different servers to ensure data availability and redundancy. In a master-slave setup, the master server handles all write operations while one or more slave servers handle read operations and act as a backup. This guide will take you through 10 simple steps to set up a master-slave replication in MySQL.

A. Master Server Configuration

Step 1: Configure MySQL Settings

Edit MySQL Configuration: Open /etc/my.cnf in a text editor and enter the following under the [mysqld] section:

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id = 1
log-bin=db-bin
relay-log=db-relay-bin

Step 2: Grant Replication Privileges
2. Grant Slave Access:
Access MySQL (mysql -u root -p) and execute:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'SLAVE_IP' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

Replace SLAVE_IP with your slave server's IP address and password with a secure password.

Step 3: Lock Tables and Note Master Status
3. Lock Tables:

FLUSH TABLES WITH READ LOCK;

4. Check Master Status:

SHOW MASTER STATUS;

Note the File and Position values.

5. Unlock Tables:

UNLOCK TABLES;

B. Slave Server Configuration

Step 4: Configure MySQL Settings

Edit MySQL Configuration:Open /etc/my.cnf in a text editor and enter the following under the [mysqld] section

server-id=2
master-host = 10.10.3.21
master-user = replication
master-password = password
master-port = 3306 

10.10.3.21 is the MASTER server IP. These information will be stored to /var/lib/mysql/master.info

Step 5: Setup Replication on Slave
2. Access MySQL:

mysql -u root -p

Stop Slave Threads:

STOP SLAVE;

Reset Previous Master Info:

RESET SLAVE;

Change Master:

CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='Master_Binlog_File', MASTER_LOG_POS=Master_Log_Position;

Replace MASTER_IP with your master server's IP address, Master_Binlog_File with the file noted earlier, and Master_Log_Position with the position noted earlier.

Start Slave Threads:

START SLAVE;

Step 6: Verify Slave Status Check Replication Status:

SHOW SLAVE STATUS \G;

Ensure Slave_IO_Running and Slave_SQL_Running are both set to Yes, and Seconds_Behind_Master is a non-negative number.


Conclusion
You've now successfully configured a basic master-slave replication setup. This configuration allows the slave to take over in case the master goes down, and it can also help with load balancing by directing read queries to the slave. Regularly check the replication status and backup your data to ensure everything operates smoothly. Remember, replication is just one part of a comprehensive disaster recovery plan.