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 –

1	yum install mysql-server mysql-client

1) Master server configuration

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

#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