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 a 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!!
