MariaDB master & Slave replication.

We are configuring a MariaDB Master-Slave replication on Linux machine. 

172.31.7.219 -- master 

172.31.47.223 - slave 

Install the DB software on both master and slave server. 

Note: We can do this from a repository as well 

# yum install update 

# yum install mariadb-server mariadb 

Start and enable the mariadb service 

# systemctl start mariadb 

# systemctl enable mariadb 

Set root password for Mariadb on both servers 

# mysql_secure_installation 

 --> Configure the MariaDB master node: 

Edit config file andd append the following entries under the [mysqld] section as shown 

vi /etc/my.cnf 

server_id=1

log-basename=master

log-bin binlog-format=row

binlog-do-db=replica_db

Restart the service 

# systemctl restart mariadb 

Now, create replica 

DB CREATE DATABASE replica_db; 

Next, create a Slave user and password.

STOP SLAVE; 

GRANT REPLICATION SLAVE ON *.* TO 'vinay_slave'@'%' IDENTIFIED BY 'vinay_slave'; 

FLUSH PRIVILEGES; 

FLUSH TABLES WITH READ LOCK; 

SHOW MASTER STATUS; --> Please note the position. 

Now, take a full back of the database and move the file to slave server

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

Then, unlock the table lock: 

UNLOCK TABLES; 

 --> Configure the MariaDB slave node: 

Edit config file andd append the following entries under the [mysqld] section as shown 

server-id = 2

replicate-do-db=replica_db 

Now, import the data using the file moved from master. 

mysql -u root -p < /home/masterdatabase.sql

restart the service 

systemctl restart mariadb 

Now, Stop the Slave. Instruct the Slave where to find the Master Log file and start the Slave. 

STOP SLAVE; 

CHANGE MASTER TO MASTER_HOST='172.31.7.219', MASTER_USER='vinay_slave', MASTER_PASSWORD='vinay_slave', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=473;

""Here we using portion under MASTER_LOG_POS""" START SLAVE; 

Now, check slave status under slave. 

SHOW SLAVE STATUS\G; 

--> The replication is over. Now you may verify the slave server data by making any DMP, DDL on master.