How To Setup Master-Slave Replication With MariaDB On Centos 7

mariadb-logo

Overview

MariaDB is well known for being a drop in replacement for MySQL. It was a community fork of MySQL that has become default in Centos 7.

In MariaDB master-slave replication the data that gets created on the master server, gets replicated in real time to the slaves. This allows you to create highly available solutions or it can be used as a form of backup.

Install MariaDB

Install Software

Before we can setup the replication, we actually need to install MariaDB. On both your server you are using as the master and the server you are using as the slave run the following commands to install MariaDB:

yum install mariadb-server mariadb -y

Once installed we need to start the software and make sure it starts automatically on boot.

systemctl start mariadb
systemctl enable mariadb

Basic Configuration

By default MariaDB comes without a root password set. To make sure not just anyone can login to it we need to set the root password. Run the command:

mysql_secure_installation

It will then ask you a series of questions to help secure your server.

Set root password? [Y/n]

For this option we do want to set the root password so type Y and then hit enter.

New password: 
Re-enter new password:

It will then prompt you to enter a password. Make sure this is secure but you should also be able to remember it.

Remove anonymous users? [Y/n]

It then asks if you want to remove anonymous users. Type Y for this one, just like you did when choosing the root password option. This removes the ability for people to connect without entering a password.

Disallow root login remotely? [Y/n]

Next it will ask if you want to disallow root to login from an external location. Type Y for this one and do not allow this to be done. This is typically a secure risk to allow it.

Remove test database and access to it? [Y/n]

Since this is going to be a production system there should not be any testing users or databases. Type Y and have them removed.

Reload privilege tables now? [Y/n]

Finally the system will ask you the last option, if you want to reload the privilege tables. Since we made changes to password and users select Y for this to have it done. Once you do this the basic setup is complete.

Configuring Master

Now that the sql server is setup, we need to configure the master server. Login to that server for this next section.

First we should make sure that the firewall allows the mysql ports to be sent through the firewall. Run:

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

After that we need to make a small modification to the MariaDB configuration file:

nano /etc/my.cnf

Look for the section with the [mysqld] header and put these lines:

[mysqld]
server_id=1
log-basename=master
log-bin
binlog-format=row
binlog-do-db=<databasename>

Make sure to replace <databasename> with the name of the database you want to replicate. Save the file with ctrl + o then ctrl + x.

Once you save and close the file we need to restart the server to apply the changes.

systemctl restart mariadb

Now login to MariaDB as we setup the replication.

mysql -u root -p

It will prompt you for your root password that you set earlier for mysql.

Then follow these steps to create a user for the replication.

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

This command will stop the current running slave to make sure it is stopped.

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'securepassword';
Query OK, 0 rows affected (0.00 sec)

This command above will create a new user named “slaveuser” with a password of “securepassword”. Make sure to replace the password with a more secure one.

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Then flush the tables to make sure the changes have taken effect.

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      460 | <db_name>    |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye

Note down the file name and position from the “show master status” command as you will likely need it later.

Now that you have done this we need to backup all the databases on the master to transfer them to the new slave. Run the command:

mysqldump --all-databases --user=root --password --master-data > masterdatabases.sql

This will dump all the data into a file called “masterdatabases.sql”. From there we need to make sure that the databases are unlocked.

Log back into MariaDB and run:

mysql -u root -p
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

Now upload the masterdatabases.sql file you created earlier using scp,sftp,ftp or some other method of your choosing to your slave server.

Configuring Slave

If you have not done it already, make sure you have installed MariaDB, run the secure setup and allowed it through the firewall like you did on the master.

Once this is done open up the MariaDB configuration file:

nano /etc/my.cnf

Just like on the master find the [mysqld] section and put these lines below it:

[mysqld]
server-id = 2
replicate-do-db=<db to replicate>

Make sure to replace “db to replicate” with the name of the database that is being replicated. The server id should also be unique. It should be different from the master and also any other slaves. When you have done this save and close the file with: ctrl + o and then ctrl + x.

Now we need to import the databases that we uploaded to the slave earlier.

mysql -u root -p < masterdatabases.sql

Then we need to apply the configuration file changes we made before.

systemctl restart mariadb

From there we to now login to MariaDB to setup the slave.

mysql -u root -p

Once there you need to stop the slave to make changes

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

Once done with that you need to configure the slave to use the master we setup earlier. Make sure to replace the values with the ones we told you to save from before

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='<your master's ip>', MASTER_USER='slaveuser', MASTER_PASSWORD='securepassword', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=460;
Query OK, 0 rows affected (0.03 sec)

After that is done the slave needs to be started.

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

You can then view the status of the slave and it should look similar to this:

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <master's ip address>
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 460
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: <replicated 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: 460
              Relay_Log_Space: 827
              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
1 row in set (0.00 sec)

Testing

Now that your system is setup for the replicate you need to test that it works. Login to your master and make changes to the database that you setup replication for. Then login back into the slave and see if the changes are also there. If they are then you have successfully setup master-slave replication.

 

CC BY 4.0 This work is licensed under a Creative Commons Attribution 4.0 International License.

Alex Wacker has written 16 articles

I am the founder and owner of Subnet Labs LLC. Impact VPS is one of our VPS brands. Linux, virtualizaton and the internet amaze me and I enjoy learning new things every day about them.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>