in database Linux master mysql replication slave ~ read.

Set Up Master-Slave Replication in MySQL

Introduction

MySQL replication is a process which allows you have a copies data from MySQL and update automatically from a master to a slave database. It is helpful to backup your data or load balancing in your system. You can setup and configure your system to write (Insert, Update, Delete) data by master server , and read data from slave server. All changing data on master server will be updated automatically to slave server.

Today I will show a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and one of the slave.

Setup

This article, I assume that you have user with sudo privileges and have MySQL installed with root account. If you have not MySQL, you can install it by command:

sudo apt-get install mysql-server  

I will use two servers with IP addresses to set up:

– Master : 192.168.1.121

– Slave : 192.168.1.35

Step one – configure the master database server

We will open the configuration file on the master database server:

sudo vim /etc/mysql/my.cnf  

We need to make a few changes it.

– The first, we find the binding section, that bind server to the localhost. If it is commented, then we need to remove comment for it.

bind-address = 127.0.0.1  

– After that, we replace 127.0.0.1 to my IP of the master server (192.168.1.121).

bind-address = 191.168.1.121  

– The next, we configure to have server-id. You can choose any number for it, but it must be unique and can not match with any other server-id in your replication group. If it is commented, then let’s uncomment for it.

server-id = 1  

– The next, uncomment for log_bin line, where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log.

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

– Finally, we need to design the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need. Remember, uncomment it.

binlog_do_db = replication_database  

After you make all of the changes, go ahead and save and exit out of the configuration file. Now, we need to restart MySQL.

sudo service mysql restart  

The next step, we will grant privileges to the slave. we need to go to MySQL shell to do it.

mysql -u root -p

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

FLUSH PRIVILEGES;  

Now, master server should be working properly. You can check by command :

SHOW MASTER STATUS;  

You will see a table that should look something like this:

mysql> SHOW MASTER STATUS;  
+------------------+----------+-----------------------+------------------+
|         File     | Position |      Binlog_Do_DB     | Binlog_Ignore_DB | 
+------------------+----------+-----------------------+------------------+ 
| mysql-bin.000001 |   333    |  replication_database |                  | 
+------------------+----------+-----------------------+------------------+ 
1 row in set (0.00 sec)  

Now, we need to export data in master which will import to slave server. We will run command in terminal:

mysqldump -u root -p replication_database > backup.sql  

Step two – configure the slave database server

We have a master server running, now we will configure for slave server. The first, we need to import master’s data. Log into your slave server and open MySQL shell. We will create database which will be replicated from master server.

CREATE DATABASE replication_database; EXIT;  

Import data by command:

mysql -u root -p replication_database < /path/to/backup.sql  

Now, we will configure slave server in the same way as we did the master:

sudo vim /etc/mysql/my.cnf  

Make some changes:

server-id = 2  
relay-log = /var/log/mysql/mysql-relay-bin.log  
log_bin   = /var/log/mysql/mysql-bin.log  
binlog_do_db = replication_database  

Remember, uncomment for it. If database name of master and database name of slave are difference, then we will have some changes:

server-id = 2  
relay-log = /var/log/mysql/mysql-relay-bin.log  
log_bin   = /var/log/mysql/mysql-bin.log  
binlog_do_db = slave_database_name  
replicate-rewrite-db = master_database_name->slave_database_name  

Restart MySQL:

sudo service mysql restart  

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

CHANGE MASTER TO MASTER_HOST='192.168.1.121', \  
MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', \  
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 333;  

Note:

– MASTER_HOST: IP address of master server

– MASTERUSER and MASTERPASSWORD: that is granted in the master server.

– MASTERLOGFILE and MASTERLOGPOS: that is shown when run “SHOW MASTER STATUS”

Activate the slave server:

START SLAVE;  

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

SHOW SLAVE STATUS\G  

If there is an issue in connecting, you can try starting slave with a command to skip over it:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  
SLAVE START;  

All done and we have a replication for master and slave database.

comments powered by Disqus