In this tutorial we will learn what is Mysql Replication,How to setup mysql replication.
Let's start with what is MySQL replication is.
MySQL replication
MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons :
- Scale-out solutions
- Data Security
- Analytics
- data distribution
- Backup
we will Setup a very simple Mysql Replication with One Master and One slave.let's take two machines for achieving the same. lets take two machines for setting up the Mysql Replication
Machine One:10.33.76.78- Master Database
Machine Two:10.22.43.46- Slave Database
Setup
In this article i am using ubuntu to setup the replication,So first thing which needs to be done for replication is installation of mysql on the system
Once you are done with the installation part,lets start the real fun of replication.
Step One—Setup the Master Database
On Master database we need to make couple changes in the configuration file on Mysql.
The first step is to find the section that looks like this, binding the server to the local host:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabase
service mysql restart
We need to grant privileges to the slave. The command should be in this format:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
'slave_user' : Denotes the name of user used for replication,It can be anything.
'%' : Denotes that with above user and password,Any host can become slave of this master,I would recommend that instead of % you should use the ip-address of the slave host.
'password': it should be combination of character numeric.
Last thing to do :
FLUSH PRIVILEGES;
Now,We need to note down the position of master and take the backup of databases for which we want to setup the Mysql Replication,Which can be done with following sequence of commands:-
USE newdatabase;
FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
The position noted above will be helpful while setting up the Slave.
export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
mysqldump -u root -p --opt newdatabase > newdatabase.sql
Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
The first step is to find the section that looks like this, binding the server to the local host:
bind-address = 127.0.0.1
Replace the standard IP address with the IP address of server.
bind-address = 0.0.0.0
Next we need to un-comment the line which has server-id:
server-id = 1
Now,Search for the line that refers to log_bin and uncomment the same,I will make the bin logs on default location but you can change the location of bin logs as well.
log_bin = /var/log/mysql/mysql-bin.log
Next option is used if you want to have Mysql Replication on the basis of databases i.e if you have multiple databases and you want Replication for only selective database
binlog_do_db = newdatabase
We are done with all the configuration changes on Master and now it's time to restart the mysql.
service mysql restart
We need to grant privileges to the slave. The command should be in this format:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
'slave_user' : Denotes the name of user used for replication,It can be anything.
'%' : Denotes that with above user and password,Any host can become slave of this master,I would recommend that instead of % you should use the ip-address of the slave host.
'password': it should be combination of character numeric.
Last thing to do :
FLUSH PRIVILEGES;
Now,We need to note down the position of master and take the backup of databases for which we want to setup the Mysql Replication,Which can be done with following sequence of commands:-
USE newdatabase;
FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
The position noted above will be helpful while setting up the Slave.
export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
mysqldump -u root -p --opt newdatabase > newdatabase.sql
Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;
Step Two—Configure the Slave Database
On Slave Machine,open up the MySQL shell and create the new database that you will be replicating from the master (then exit):
CREATE DATABASE newdatabase;
EXIT;
After creating the database, Now Import that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql
server-id = 2
Activate the slave server:
After creating the database, Now Import that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql
Now we need to configure the slave configuration :
make sure that your have the following Four criteria appropriately filled out:
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabases
Server-id needs to be unique as and we have already used server-id = 1 on master,lets setup server-id=2 here on slave.
You might not find the rest of three values in my.cnf by default,So if the values are missing please add it and restart the mysql.
service mysql restart
Let's enable the replication from within the MySQL shell.Open up the the MySQL shell and type in the details :
CHANGE MASTER TO MASTER_HOST='ip-address of master',MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='', MASTER_LOG_POS= ;
This command accomplishes several things at the same time:
It designates the current server as the slave of our master server.
It provides the server the correct login credentials
Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.
With that—you have configured a master and slave server.
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
Show slave status will give you complete details which you have entered while setting up the slave.like master server,user name and etc.You can test t
he replication by simplying createing a table in the database for which you have enabled the replication and see if the same table exists on slave.If
you table is present on the slave machine as well then your Mysql Replication is working fine,Else investagte further to check what has went worng.
Written by Unknown
Experience running high-traffic web services, service configuration, monitoring,troubleshooting and change management.Expertise in Linux/Unix system administration, including configuration, troubleshooting, Python scripting language.
Posts
0 comments:
Post a Comment