How to Setup MariaDB (Master-Slave) Replication in CentOS/RHEL 7/8

 

 MariaDB Master-to-Slave Replication Setup                  Guide on CentOS/RHEL 7/ 8

Project: MariaDB Master-Slave Replication Setup

Objective:

To create a Master-Slave replication setup with MariaDB on CentOS/RHEL 8, ensuring data consistency, load distribution for read operations, and a backup solution for the master database.

Introduction:

• Briefly introduce what replication is and its benefits, particularly focusing on Master-to-Slave replication in MariaDB.
• Mention that the guide is specifically for RHEL systems.

 Prerequisites:

•  Two RHEL servers with MariaDB installed.
•  Network connectivity between the master and slave servers.
•  Sudo or root access on both servers.
•  Basic knowledge of MariaDB/MySQL administration.

Step 1: Prepare the Environment

Update the System:

Ensure both CentOS/RHEL 8 servers are updated.

Install MariaDB on Both Servers:
Install MariaDB using the package manager.




Start and Enable MariaDB Service:



Secure MariaDB Installation:

 Run the security script on both servers and follow the prompts.

Step 2: Configure the Master Server

STEP 1: Edit the /etc/my.cnf file. Under the [mysqld] section, add the following four lines:

[mysqld]
log-bin
server_id=1
replicate-do-db=employees
bind-address=192.168.175.134

Setting Up a Sample MySQL Database on Master
tar -xvzf test_db.tgz -C /home/local
cd test_db
mysql < employees.sql

Enable and restart MariaDB:
sudo systemctl restart mariadb

 

STEP 2: Log on to the MariaDB server as root, create the user slave and assign the necessary grants:

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'Password@123';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'Password@123' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;

The last command (SHOW MASTER STATUS) returns the current position in the binary log (the exact coordinates that indicate at exactly
which point the slave should start replicating from:

screenshot:

 STEP 3: Exit the MariaDB prompt (with exit;) and use the following command to take a snapshot of the employees database. When you hit Enter, you will be prompted to type the password for root you set up earlier through mysql_secure_installation:

 mysqldump -u root -p employees > employees-dump.sql
After the dump is completed, connect to the database server again to unlock the tables and then exit:

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;



STEP 4: Copy the dump to the slave:

# scp employees-dump.sql root@192.168.175.133:/root/
STEP 5: Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

# mysql_upgrade -u root -p

 

Step 3: Configure the Slave Server

 Now let’s configure the slave.similar way using of config.sh

Configuring MySQL Server on Slave
To configure the slave, follow these steps:

STEP 1: Create the account to perform the replication tasks. Connect to the local MariaDB server with:

# mysql -u root –p

and enter the password you set up earlier.

STEP 2: Once connected to the database server, create the user and an empty database, and grant permissions:

MariaDB [(none)]> CREATE DATABASE employees;
MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'Password@123';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
STEP 3: Exit the MariaDB prompt and load the dump created in the master server:

# mysql -u root -p employees < employees-dump.sql
STEP 4: Edit the /etc/my.cnf file to assign a server ID to the slave under the [mysqld] section. Note that it needs to be a different integer than 1, as we used 1 in the master:

[mysqld]
server_id=2
replicate-do-db=employees
Restart the database server:

# systemctl restart mariadb

STEP 5: Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

# mysql_upgrade -u root -p

STEP 6: Once the dump has been imported to the slave, we are only a few steps to begin replicating. Log on to the database and run the following commands in the MariaDB prompt. Pay special attention to the MASTER_LOG_FILE and MASTER_LOG_POS variables, which should match the values returned by SHOW MASTER STATUS in STEP 2 of “Configuring the master” above.

MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.175.134',
  MASTER_USER='slave',
  MASTER_PASSWORD='SlavePassword',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=824,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID=current_pos;

STEP 7: Start the slave and check its status without exiting the MariaDB prompt:

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;



Not that you need this now, but note that you can stop the slave with:

MariaDB [(none)]> STOP SLAVE;
if the SHOW SLAVE STATUS\G; command returns any errors. Use those errors to troubleshoot and then run START SLAVE; to test again.

Step 4: Test MySQL/MariaDB Database Replication

Let’s add a record to the employees table in the master server:

MariaDB [(none)]> USE employees;
MariaDB [(none)]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (500000, '1983-07-12', 'Dave', 'Null', 'M', '2014-12-12');


Then verify that this change was replicated in the slave:

MariaDB [(none)]> USE employees;
MariaDB [(none)]> SELECT * FROM employees WHERE emp_no=500000;

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 192.168.175.134
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 2351
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 2563
         Relay_Master_Log_File: mariadb-bin.000001
              Slave_IO_Running: No
             Slave_SQL_Running: No
               Replicate_Do_DB: employees
           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: 2351
               Relay_Log_Space: 2874
               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: NULL
 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_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 0-1-6
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 5
Slave_Non_Transactional_Groups: 1
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

ERROR: No query specified

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [employees]> SELECT * FROM employees WHERE emp_no=500000;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1983-07-12 | Dave       | Null      | M      | 2014-12-12 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.006 sec)

MariaDB [employees]>


As you can see, replication is working correctly from master to slave.

Summary
In this article we have explained how to install the latest version of MariaDB in CentOS/RHEL 7 and Debian 8/9,and discussed how to set up master-slave replication with GTIDs. For more information, you may want to refer to the MariaDB Replication Guide,and don’t hesitate to contact us using the form below if you have questions or comments.

2. **Verify Slave Status:**
   - Use `SHOW SLAVE STATUS\G;`.
   - Ensure `Slave_IO_Running` and `Slave_SQL_Running` states are `Yes`.

### Post-Configuration:
- Test replication by creating a database or a table on the master and check if it replicates to the slave.
- Set up monitoring for replication status to catch any issues early.

### Tips and Best Practices:
- Regularly back up both master and slave databases.
- Monitor the replication delay.
- Secure your replication setup, especially the replication user credentials.
- Test fail-over scenarios.

### Troubleshooting:
- Address common issues such as replication lag or errors in the `SHOW SLAVE STATUS` output.

 

Conclusion:

You have now set up MariaDB Master-Slave replication on CentOS/RHEL 8. This setup ensures that your data is synchronized from the master to the slave, providing a foundation for load distribution, scalability, and redundancy. Regularly monitor the replication status and perform backups to safeguard your data.

• Recap the importance of replication for data redundancy and high availability.
• Encourage further reading on advanced replication setups and performance tuning.