Step by Step Mysql Replication on Centos

Overview on Mysql Replication:

Mysql Replication is the process to copy your main Mysql server data to the many more servers. Or we can say that to keep data as backup then we can enable this mysql replication and have copy of the databases to slave servers for any recovery or backup.

Here we are going to create Master Slave Mysql Replication. So we will be using 2 servers where 1 will be the Master and 2nd will work as Slave.

Master Server: 192.168.1.2
Slave Server: 192.168.1.3

Steps to Start Mysql Replication:-

1: Need to create a backup on the Master Server
2: Copy the backed up data file to the Slave.
3: Need to configure the master mysql server.
4: Now configure the slave server.
5: Then allow the privileges to the slave server on master server
6: Start mysql replication

Steps for setup :

Mysql installtion –

yum install mysql-server mysql-client

1) Master server configuration

vi  /etc/my.cnf
	
	#Here we need to do some changes as like there is one option to bind the server 
	
	bind-address  =127.0.0.1 
	
	#change it to the server ip address
	
	bind-address  =192.168.1.2

	#Now there is one more option named as server id which you can set to any uniq number which should not be used by any other server.
	
	server-id  =1     #here we are using 1 as server id

        #Now we need to uncomment the line showing bin log path which actually store the replication logs in it.
   
        log_bin    = /var/log/mysql/mysql-bin.log

	#Now we need to mention the db name which needs to replicate to the slave server below
	
	binlog_do_db    = database_name    # for more database we can repeat this line as required
	
	#Save this file now. And restart the mysql server
	
	service mysql restart
	
	#Below commands to provide slave server the privileges
	
	mysql -u root -p
	
	GRANT REPLICATION SLAVE ON *.*  TO 'slave_username'@'%'  IDENTIFIED BY  'PASSWORD';
	
	FLUSH PRIVILESGES;
	
	#Now the most important step which is to lock the tables on the same database which is going to be replicated to avoid the further changes on the same.
	
	use database_name;
	
	FLUSH TABLES WITH RED LOCK;   # Locking tables to avoid changes
	
	SHOW MASTER STATUS;
	
	+------------------+----------+--------------+------------------+
	| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
	+------------------+----------+--------------+------------------+
	| mysql-bin.000001 |      103 | database_name|                  |
	+------------------+----------+--------------+------------------+
	1 row in set (0.00 sec)
	
	#This shows the position from where the slave will start replicating the database. Please note these details.
	
	#Do remember always lock the tables in a seperate tab and meanwhile take the latest dump of the same database as below 
	
	mysqldump -u root -p  database_name >  database_name.sql
	
	#Now go to other tab and UNLOCK the tables
	
	UNLOCK TABLES;
	
	EXIT.

Configuration done on Master.

2) Configuration on Slave Server

        #On this slave server firstly we need to install the mysql server same as on master.
	
	#then we have to create the database named as same as which we are going to replicate from master.
	
	mysql -u root -p
	
	create database database_name;
	
	quit;
	
	#Now just import the backup file taken from the master server for the same database;
	
	mysql -u root -p  database_name < database_name.sql

        #Edit file my.cnf :
	
	vi /etc/my.cnf
	
	server-id =2    # Same as master but it should be differnt and unique

	relay-log = /var/log/mysql/mysql-relay-bin.log
	
        log_bin  =  /var/log/mysql/mysql-bin.log
	
        binlog_do_db = database_name
	
	#Save and Exit
	
	#Just restart mysql now !
	
	service mysql restart
	
	#Now some configuration to make this server understand the details about master server to communicate
	
	Mysql -u root -p 
	
	CHANGE  MASTER TO  MASTER_HOST='192.168.1.2', MASTER_USER='slave_username', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  103;
	
	#Above command will make this server as slave server and also provide it the position from where it needs to start replication.
	
	#Now activate the Slave Server 
	 
	 START SLAVE;
	 
	 #To check slave server status:
	 
	 SHOW SLAVE STATUS\G

All done.  Enjoy !  You have done the Mysql Replication Setup.

@Vinod –  This is for you. As you requested us to post this.

🙂 🙂

 

One thought on “Step by Step Mysql Replication on Centos

Leave a Reply

Your email address will not be published. Required fields are marked *