March 30, 2017

Setup MySQL Replication Server

MySQL Replication combined two server of MySQL server Database to allow replication process to easily manage one or more databases. replicate from the Master Database server to Slave Database Server. One of the reason you need setup MySQL Replication is to load balance traffict to databases and you need keep the backup of MySQL DB.

In this toutorial i will capture my recent configuration using Ubuntu 17.04 beta 2 server amd64. Using two server called Mysql01 and Mysql02 :
Mysql01 = 10.3.99.72
Mysql02 = 10.3.99.73

Setup Master Mysql01

1. Install Mysql-server mysql-client

MySQL Server Installation

Using apt-get Install mysql-server mysql-client, you should connect your server to the internet and you need to update package using apt-get update

Root Set Password


Setup Mysql root password for login to Mysql Console.

2. Login to Mysql Server, change bind Address

Change bind-address

For Master Database, change the parameter bind-address = 10.3.99.72, it will be make your MySQL Database listen on IP 10.3.99.72.

3. Edit Server ID 

set server-id
For Master Database, change parameter server-id = 1, and uncomment parameter log_bin, binlog_do_db. in parameter include_database_name, put your target database to create a database replica. After change the Parameter, restart the mysql service using service mysql restart.

4. Grant Slave Permission.

Grant Replication

Next, login to Mysql using command mysql -u root -p type your password. Create a slave user using sql query 
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'mysqlpass';
This command explain to create user with name slave to all source IP Address  with password mysqlpass

5. Show Master Status

Show master Status

Show Master status explain a DB with binlog_do_DB employees.

6. Export employees database
Transfer File
after you export the database using mysqldump -u root -p --opt emplyees > employees.sql, send this file via scp to slave server using scp command.


Related Article : SQL Server 2016


Setup MySQL Slave Mysql02

1. Change Bind Address and Server ID
Slave Server

Change the server-id parameter value 2, uncomment log_bin, binlog_do_db.

2. Import employees.sql

Import sql file
Import employees.sql in to Mysql02 Server to actived Slave Server using command mysql -u root -p employees < home/db/employees.sql

3. Active Slave Server

Active Slave Server
Set the master server on MySQL02 using command 
"CHANGE MASTER TO MASTER_HOST='10.3.99.72',MASTER_USER='slave', MASTER_PASSWORD='mysqlpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  154;"

4. Check The Slave Server

Check Slave Server

Check the slave status using SHOW SLAVE STATUS\G


This is a simple way to setup MySQL replication between two server using Ubuntu 17.04.


Thank you. 


Related Article : Linux System Logging


Previous Post
Next Post

post written by:

0 comments: