MYSQL MASTER-SLAVE REPLICATION

<SERVER>

Install MySql Server Package and Edit the configuration file.

# yum install mysql-*

# /etc/init.d/mysqld start

# chkconfig mysqld on

# vim /etc/my.cnf                                        (Configuration file)

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-bin=mysql-bin                          (Important for Replication)

server-id=1                                       (Indicates Master Server)

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

:wq

 

Login into SQL Server and give permission.

# mysql -u root -p

mysql> show database;

 

mysql> grant replication slave on *.* to <Username>@’<Slave_IP>’ identified by ‘<Master_Pasword>’;

i.e.

mysql> grant replication slave on *.* to repl@’192.168.137.132’ identified by ‘hello123’;

(If we face any error then type below command)

 

 

mysql> grant all privileges on *.* to <Username>@’<Slave_IP>’ identified by ‘<Master_Pasword>’;

i.e.

mysql> grant all privileges on *.* to repl@’192.168.137.132’ identified by ‘hello123’;

 

Take backup with dump command.

# mysqldump -u root -p --all-databases > /opt/all_db.sql

 

Now copy this dump file to slave server

#scp /opt/all_db.sql <username>@<Slave_IP>:/opt/                       (From Server end)

                                                                                          OR

#scp <username>@<Server_IP>:/opt/all_db.sql /opt/                    (From client end)

 

 

<CLIENT>

Install MySQL and edit configuration file.

# yum install mysql-*

# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=2

master_host=192.168.137.95                            (Master Server)

master_user=repl                                                   (Authenticated User)

master_password=hello123                               (Password)

master_port=3306                                                 (Port Number)

log-bin=mysql-bin

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

:wq

 

Login in SQL server

#mysql -u root -p

mysql> show slave status \G;            (Looks for Second Behind Master)

                              if, Second Behind Master = 0 then Communication is OK

                                   Second Behind Master = NULL then Communication is not OK

(if you didn’t get any output then restart the “mysqld” service)

* If you get “NULL” then follow the below command:

 

               a) mysql> stop slave;

 

               b) mysql> change master to master_host=’192.168.137.95’,master_user=’repl’,master_password=’hello123’,master_log_file=’<Get-it-from-master-server>’,master_log_pos=<Get-it-from-master-server>;

 

               c) Go to master server

                              mysql> show master status;              (Take output of the ‘File & Position’)

 

              d) mysql> start slave;