WindowsMacSoftwareSettingsSecurityProductivityLinuxAndroidPerformanceConfigurationApple All

How to Set Up MySQL Replication on Debian

Edited 2 days ago by ExtremeHow Editorial Team

DebianMySQLReplicationDatabaseServer SetupSQLLinuxOpen SourceSystem AdministrationCLI

How to Set Up MySQL Replication on Debian

This content is available in 7 different language

MySQL replication is a process that allows you to automatically copy data from one MySQL database server (master) to one or more MySQL database servers (slaves). This setup is very useful for improving performance, reliability, and load balancing. In this guide, we will walk through the steps required to set up MySQL replication on a Debian Linux server. We will cover everything from preparing your server, configuring replication settings, and verifying that replication is working properly. Let us dive into it.

1. Understanding MySQL replication

MySQL replication allows asynchronous copying of data between database servers. The primary server is known as the master, and any secondary servers that receive replicated data are called slaves. This architecture is beneficial in many scenarios, such as read-intensive database environments, data backup and recovery processes, data analysis, and geographically distributed data centers.

2. Preparation

Before you begin setting up MySQL Replication, make sure that you have the following prerequisites in place:

3. Setting up the master server

We will start by configuring the master server, which will hold the original data and send updates to the slave servers.

Step 1: Configure the MySQL master server

Start by opening the MySQL configuration file on the master server. This file is usually located at /etc/mysql/mysql.conf.d/mysqld.cnf. Use a text editor such as nano to edit it:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Look for the [mysqld] section in the configuration file and modify or add the following lines:

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

log-bin directive enables binary logging, which is required for replication as it logs all changes to the database. server-id must be a unique positive integer for each server participating in the replication setup. In this case, we have used 1 as the server ID for the master.

After editing, save and close the file. If you used nano, you can do this by pressing CTRL + X, then Y, and then ENTER.

Step 2: Restart MySQL on the master server

To apply the changes we made, restart the MySQL service:

sudo systemctl restart mysql

Step 3: Create a replication user

Next, log into the MySQL command line on the master server to create a user account that the slave server can use to connect for replication. Use the following command:

mysql -u root -p

After logging in, execute the following SQL commands to create a replication user and grant it privileges. Replace 'replica_user' and 'password' with the username and password of your choice:

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%'; FLUSH PRIVILEGES;

These commands create a new MySQL user with replication privileges. Using '%' as the host allows the slave to connect from any IP address.

Step 4: Get master status

Before proceeding to configure the slave, you need to record the current master log file and status. Use the following command to get this information:

SHOW MASTER STATUS;

Note File and Position values as they will be needed when setting up the slave server. The output may look something like this:

+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 123 | | | +------------------+----------+--------------+------------------+

4. Setting up the slave server

After configuring the master server, the next step is to set up the slave servers.

Step 1: Configure the MySQL slave server

As with the master configuration, open the MySQL configuration on each slave server by editing /etc/mysql/mysql.conf.d/mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Make sure the [mysqld] section has the following settings:

[mysqld] server-id = 2 relay-log = mysql-relay-bin

server-id must be unique and different from any other server in the replication setup. Here, we have used 2 relay-log configuration facilitates the processing of the replication stream from the master server.

After editing, save and close the configuration file as before.

Step 2: Restart MySQL on the slave server

Restart the MySQL server on the Slave to apply the changes:

sudo systemctl restart mysql

Step 3: Set the slave to connect to the master

Log into the MySQL command line client on the slave server:

mysql -u root -p

After logging in, connect the slave to the master using CHANGE MASTER TO command. Make sure to replace 'master_host', 'replica_user', 'password', 'mysql-bin.X', and position_number with the corresponding master server IP address, replica username, password, log file, and position obtained earlier:

CHANGE MASTER TO MASTER_HOST = 'master_host', MASTER_USER = 'replica_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.X', MASTER_LOG_POS = position_number;

After configuring the master information, start the slave process with the following command:

START SLAVE;

Step 4: Verify slave status

To check if the slave server is setup correctly and connected to the master, execute:

SHOW SLAVE STATUS\G;

Look at Slave_IO_Running and Slave_SQL_Running fields in the output. Both should show Yes to indicate that the slave is working correctly. You should also look at the values of Relay_Master_Log_File and Exec_Master_Log_Pos, which confirm replication progress.

5. Testing the replication setup

Once you have set up both the master and slave servers, it is good practice to test the replication setup to make sure everything is working as expected.

Step 1: Test data synchronization

To test replication, try inserting a simple data entry on the Master server and see if it appears on the Slave.

On the master server, log into MySQL and create a simple database and table:

mysql -u root -p
CREATE DATABASE testdb; USE testdb; CREATE TABLE testtable (id INT PRIMARY KEY AUTO_INCREMENT, value VARCHAR(255)); INSERT INTO testtable (value) VALUES ('Replication Test');

Step 2: Verify data on the slave server

Log into the MySQL command line on the slave server and check whether the data has been replicated:

mysql -u root -p
USE testdb; SELECT * FROM testtable;

You should see the row inserted on the master table now present in the slave table. If replication is working correctly, you will see:

+----+------------------+ | id | value | +----+------------------+ | 1 | Replication Test | +----+------------------+

6. General troubleshooting tips

In case problems arise during or after set-up, here are some general troubleshooting steps you can consider:

Conclusion

MySQL replication on Debian, as demonstrated, involves configuring both master and slave servers to handle and reproduce database changes across multiple systems. This setup can substantially increase reliability and data access efficiency. While setting up replication requires some effort and an understanding of the database's architecture, following these instructions should make the process manageable and successful.

Keep in mind that it's always a good idea to regularly monitor your MySQL server and run regular checks for consistency and performance on your replicated data to ensure your systems run smoothly and efficiently.

If you find anything wrong with the article content, you can


Comments 



How to Set Up MySQL Replication on Debian

https://www.extremehow.com/how-to-set-up-mysql-replication-on-debian?ehal=en






Search ExtremeHow (en)