mysql master slave configuration in linux

Configure Master and Slave MySQL Replication On Linux

You need two machines for this setup one for Master and another for slave. I will install the MySQL 5.7 version. you can configure master-slave replication and also show the master configuration in my.cnf file. we can also check MySQL replication status.

This also provides you database high availability for your application if one goes down, data can be fetched from another slave instances.

Master IP: 192.168.223.196

Slave IP: 192.168.223.197

You can find another version from the official site of MySql.

https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html

Check Out: How To Block Ads In Google Chrome Using Group Policy

you have to install on MySQL both servers and the version should be the same. Create a repo file in vim /etc/yum.repos.d/mysql.repo

configure master and slave

Master Configuration

Install MySQL server using yum install mysql mysql-server -y

I will put a few basic and required configuration under my.cnf file. The server-id should not be the same.

Put these code in this file vim /etc/my.cnf and under [mysqld] section.

server-id = 1

#binlog-do-db=testdb

relay-log = /var/lib/mysql/mysql-relay-bin

relay-log-index = /var/lib/mysql/mysql-relay-bin.index

log-error = /var/lib/mysql/mysql.err

master-info-file = /var/lib/mysql/mysql-master.info

relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

log-bin = /var/lib/mysql/mysql-bin

Check Out: Command To Restore And Backup Of MySQL Server On CentOS 7

After putting these code, restart the service 

systemctl restart mysqld

mysql master slave replication

Now grant replication on slave users for any IP address means ‘%’. 

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

FLUSH PRIVILEGES;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

configure master-slave replication

Please note down the file name mysql-bin.000002 and position 417.

Check that the user must be created using the below command. 

select user,host from mysql.user;

mysql show master configuration

Check Out: How To Setup Multi-Factor authentication In Amazon Web Service

Now take the DB dump from your master server using the below command.

mysqldump -u root -p –all-databases –master-data > /root/dump.db

check mysql replication status

Now unlock the tables.

UNLOCK TABLES;

how to check mysql replication databases are in sync

Copy this dumb.db file on the slave server.

scp -r dump.db root@192.168.223.197:/root/

Check Out: How To Install PHPMyAdmin For Accessing Database On CentOS 7

Slave configuration

you need to install MySQL server and make these changes in vim /etc/my.cnf

configure master-slave replication

If you’re not able to start the service with this configuration, you can remove all master variables because it’s not recommended to put a password.

Check Out: Check Logs of Container Running In Docker On CentOS 7

Now it’s time to configure the replication on the slave and run the below commands. You don’t forget to change IP and password.

slave stop;

CHANGE MASTER TO MASTER_HOST='192.168.223.196', MASTER_USER='slave_user', MASTER_PASSWORD='F!fXkv!og26@', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=417;

START SLAVE;

Now check the status of a slave.

show slave status\G 

Master and Slave replication now configured.

Testing whether it’s working or not.

Check Out: MySQL Database Backup In GZ Format On Linux 7

Master DB

I have created a database on master and that database should replica on the slave.

We can also check slave hosts on the Master database.

show slave hosts;

Slave DB

As you can see the database ‘abc’ is automatically replica here.

You’re done check mysql replication status mysql show master configuration configure master-slave replication mysql master replication

Leave a Reply

avatar
  Subscribe  
Notify of