How to Setup MariaDB Replication on Centos 7 (Master-Slave)

centos-maria

MariaDB is one of the most popular open source relational database management system (RDBMS). When your traffic grows then you need to increase the instances of your MariaDB server and have to replicate the data on multiple servers. MariaDB provide 2 ways to replicate data : master – master and master – slave.

  • Master – Master
    In this mode data is written to both the servers, so whenever one server gets the write request it will sync data to other server. This mode will be best suited when your project have more write requests and you want best redundancy. You can send write requests to any server.
  • Master – Slave
    In this mode only the master server sync data with other servers, so you will have only 1 server which takes the write requests and other servers will be used for read only purpose. This mode will be best suited for projects which have more read requests than write ones and can be used for data backups.

In this tutorial we will learn how to replicate data using Master – Slave configuration.

Step 1. Install MariaDB in your Machine by running bellow command from your terminal.

# yum install mariadb-server -y

Step 2. Start MariaDB Service.

# service mariadb start

Step 3. Configure MariaDB server by using below command.

# mysql_secure_installation
In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none):

Note: - Press Enter to proceed further.
   
OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] y

New password:                       Specify New Password for MariaDB
Re-enter new password:              Confirm Password by specifying again
 
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a production environment.
   
Remove anonymous users? [Y/n] y
... Success!Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
  
Disallow root login remotely? [Y/n] y
... Success!By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.
 
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
 
Reload privilege tables now? [Y/n] y
... Success!Cleaning up...All done! If you've completed all of the above steps, your MariaDB installation should now be secure.
Thanks for using MariaDB!


Note: Execute upper steps on both servers.

 

Login to Master Server.

 

Step 1. Edit & Modify the Configuration file of Mariadb  Server.

# vi /etc/my.cnf

Add below lines under the [mysqld] section.

server_id = 1
log_bin = /var/log/mariadb/mariadb.log

Step 5. Restart Mariadb Server.

# service mariadb restart

Step 6. Login to Mariadb Server.

# mysql -u root -p

Step 7.  Create a new user for Replication & specify the Password to that user.

MariaDB [(none)]> create user 'replica'@'%' identified by 'password';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
MariaDB [(none)]> FLUSH PRIVILEGES;

Step 5. Execute below command to view the File & Position of Master Server.

MariaDB [(none)]> SHOW MASTER STATUS;

master-status-pic


Login to Slave Server.

 

Step 6. Edit & Modify the Configuration file of Mariadb Server.

# vi /etc/my.cnf

Add below lines under the [mysqld] section.

server_id = 2
log_bin = /var/log/mariadb/mariadb.log

Step 5. Restart Mariadb Server.

# service mariadb restart

Step 8. Login to Mariadb Server.

# mysql -u root -p

Step 9. Specify the following details as given below & make sure to Replace the following settings with your settings.

MASTER_HOST     :   IP Address of Master server
MASTER_USER     :   Replication User of Master server that we had created in previous steps.
MASTER_PASSWORD :   Replication User Password of Master server that we had created in previous steps.
MASTER_LOG_FILE :   Your Value of Master Log File of Master server.
MASTER_LOG_POS  :   Your Value of Master Log Position of Master server.
MariaDB [(none)]> stop slave;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '54.183.17.85', MASTER_USER = 'replica', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mariadb.000001', MASTER_LOG_POS = 551;
MariaDB [(none)]> start slave;


Login to Master Server.

 

Step 10. Login to Mariadb Server

# mysql -u root -p

Step 11. For testing a Replication we need to create a new database, it will automatically replicate on Slave Server.

MariaDB [(none)]> create database demo;


Login to Slave Server.

 

Step 12. Login to Mariadb Server

# mysql -u root -p

Step 13. View your Replicated Database  by using below command.

MariaDB [(none)]> show databases;