SETUP MASTER – SLAVE REPLICATION BETWEEN TWO MYSQL SERVERS IN UBUNTU

  Uncategorized

ABOUT

MySQL Master – Slave replication is a process that enables data from one MySQL database server (the master) to be copied automatically to another MySQL database server (the slave). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.

Here I will show you how to setup Master – Slave replication with two mysql servers running on Ubuntu.

REQUIREMENTS

Two servers with pre-installed MySQL server.

Root Access to the servers.

A database on the master which you want to replicate.

Configuring the Master Server

Open the mysql configuration file in a text editor.

vi /etc/mysql/my.cnf

Find the following line in the file:

bind-address            = 127.0.0.1

and change it as follows:

bind-address = <slave_ip>

Un-comment the following lines:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

Add the name of the DB which you want to replicate:

binlog_do_db            = <db_name>

Save and exit the file.

Refresh MySQL.

service mysql restart

Open up the MySQL shell.

mysql -u root -p

Grant privileges to a mysql user on slave server.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'<slave_ip>' IDENTIFIED BY 'password';

Following that, lock the database to prevent any new changes:

FLUSH TABLES WITH READ LOCK;

Then type in:

SHOW MASTER STATUS;

It will show something like below, you have to note these details we will need these on the slave server:

 

 

Now by holding read lock dump the database into a .sql file from the command line.

mysqldump -u root -p dbname > dbname.sql

Now login back to mysql and unlock the databases:

UNLOCK TABLES;
QUIT;

Configure the Slave Server

Login to mysql and create a new database.

CREATE DATABASE dbname;
EXIT;

Import the database that you previously exported from the master server.

mysql -u root -p dbname < /path/to/dbname.sql

Now we need to configure the slave server.

vi /etc/mysql/my.cnf

Here server id must be a different one from the master server.

server-id               = 2

Un-comment the following line:

log_bin                 = /var/log/mysql/mysql-bin.log

Add the following lines to the configuration:

relay-log               = /var/log/mysql/mysql-relay-bin.log
replicate_do_db         = <dbname>

Save and exit from the configuration file.

Restart mysql service.

service mysql restart

The next step is to enable the replication from within the MySQL shell.

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='<master_ip>',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='<File_name>', MASTER_LOG_POS=  <position>;

<File_name> and <position> are those we got from the “SHOW MASTER STATUS;” on the master.

Activate the slave server:

START SLAVE;

You be able to see the details of the slave replication by the below command. The \G rearranges the text to make it more readable.

SHOW SLAVE STATUS\G

ALL DONE!

LEAVE A COMMENT