Setup
Master-Slave Replication In Mysql Server
Mysql:- MySQL is a relational database
management system (RDBMS-Relational DataBase
Management System) based on SQL
(Structured Query Language).
Replication:-Mysql replication allows
you to have multiple copies of data on many systesm
and data on many system and data is
automatically copied from one database (master) to another
databse (slave) .
If one server goes down, the client
still can access the data from another (slave) server database.
mysql master System :- RHEL Server 6.5
192.168.122.10 :-Master Database
Mysql Slave System :- RHEL Server 6.5
192.168.122.20 :-Slave Database
Adjust iptables to allow 3006 port:
-A INPUT -p tcp -m state --state NEW
--dport 3306 -j ACCEPT
-A INPUT -p udp -m state --state NEW
--dport 3306 -j ACCEPT
Save & Restart ipatables;
[root@mysql-1 ~]# service iptables
restart
Now install mysql packages using the
follwing command:-
[root@mysql-1 ~]# yum install
mysql-server mysql -y
Start Mysqld Service
[root@mysql-1 ~]# /etc/init.d/mysqld
restart
[root@mysql-1 ~]# chkconfig mysqld on
Setup mysql root password:-
[root@mysql-1 ~]#
/usr/bin/mysql_secure_installation
configure Mysql Master server
Open /etc/my.cnf file and add the
following lines under
[root@mysql-1 ~]# cat /etc/my.cnf
[mysqld]
server-id = 1
binlog-do-db=replication
[mysqld] section:
expire-logs-days=7
relay-log =
/var/lib/mysql/mysql-relay-bin
relay-log-index =
/var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file =
/var/lib/mysql/mysql-master.info
relay-log-info-file =
/var/lib/mysql/mysql-relay-log.info
log-bin = mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is
recommended to prevent assorted security risks
symbolic-links=0
#
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Here replication is the database name
to be replicated to the Slave system.
Once you are done, restart MySQL
service:
[root@mysql-1 ~]# /etc/init.d/mysqld
restart
Stopping mysqld:
[ OK ]
Starting mysqld:
[ OK ]
Now login to MySQL and create a Slave
user and password. For instance,rj as Slave username and keenable as
password:
[root@mysql-1 ~]# mysql -u root -p
Password:
Welcome to the MySQL monitor. Commands
endYour MySQL connection id is 4
Server version: 5.1.71-log Source
distribution
with ; or \g.
Copyright (c) 2000, 2013, Oracle and/or
its affiliates. All rights reserved.
Oracle is a registered trademark of
Oracle Corporation and/or its
affiliates. Other names may be
trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type
'\c' to clear the current input statement.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning
(0.00 sec)
mysql> GRANT REPLICATION SLAVE ON
*.*TO 'rj'@'%' IDENTIFIED BY 'keenable';
Query OK, 0 rows affected, 1 warning
(0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB |
Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 319 | replication
|
|
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
Note down the file(mysql-bin.000002)
and position number (319), you may need these values
later.
Now Backup Master server database
Enter the following command to dump all
Master databases and save them. We will
transfer these databases to Slave
server later:
[root@mysql-1 test]# mysqldump -u root
-p --all-databases > all_dbs.sql
Enter password:
This will create a file called
all_dbs.sql. This will take some time depending upon the databases
size.
Again login to MySQL as root user and
unlock the tables:
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
Now Copy all_dbs.sql file to Slave mysql server.
Setting up MySQL Slave
We have done Master side installation.
Now we have to start on Slave side. Install MySQL
packages on Slave server:
[root@mysql-2 ~]# yum install
mysql-server mysql -y
Start Mysqld Service
[root@mysql-2 ~]# /etc/init.d/mysqld
restart
[root@mysql-2 ~]# chkconfig mysqld on
Setup mysql root password:-
[root@mysql-1 ~]#
/usr/bin/mysql_secure_installation
Configure MySQL Slave
Open the file /etc/my.cnf and add the
following entries under [mysqld] section as shown below.
Replace the database name and master
server IP Address with your own:
[root@mysql-2 ~]# cat /etc/my.cnf
[mysqld]
server-id = 2
master-host=192.168.122.10
master-connect-retry=60
master-user=rj
master-password=keenable
replicate-do-db=replication
relay-log =
/var/lib/mysql/mysql-relay-bin
relay-log-index =
/var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file =
/var/lib/mysql/mysql-master.info
relay-log-info-file =
/var/lib/mysql/mysql-relay-log.info
log-bin = mysql-bin
Here 192.168.122.10 is Master server IP
address,rj is Master server database user,keenable as
password of user rj,replication is
Master database name.
Import the master database:
[root@mysql-2 ~]# mysql -u root -p <
all_dbs.sql
Password:
[root@mysql-2 ~]# /etc/init.d/mysqld
restart
Now log in to MySQL as root user and
tell the Slave server to where to look for Master log file
which is we have created on Master
server using the command SHOW MASTER STATUS; (File –
mysql-bin.000002 and Position – 319).
Make sure that you changed the Master server IP address,
username and password as your own:
mysql> SLAVE STOP;
Query OK, 0 rows affected(0.01 sec)
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.122.10', MASTER_USER='319',
MASTER_PASSWORD='keenable',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=319;
mysql> SLAVE START;
Query OK, 0 rows affected(0.01 sec)
mysql> show slave status\G
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to
sendMaster_Host: 192.168.122.10
Master_User: rj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 319
Relay_Log_File: mysql-relay-bin.000033
Relay_Log_Pos: 466
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: replication
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
event
Exec_Master_Log_Pos: 319
Relay_Log_Space: 766
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql> bye
Test MySQL Replication
Master side:
[root@mysql-1 ~]# mysql -u root -p
Password:
mysql> create database replication;
Query OK, 1 row affected (0.04 sec)
mysql> use replication;
mysql> create table example (c int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into example (c)
values (1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from example;
mysql> select * from example;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
[root@mysql-2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands
endYour MySQL connection id is 13
Server version: 5.1.71-log Source
distribution
with; or \g.
Copyright (c) 2000, 2013, Oracle and/or
its affiliates. All rights reserved.
Oracle is a registered trademark of
Oracle Corporation and/or its
affiliates. Other names may be
trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type
'\c' to clear the current input statement.
mysql> use replication;
Reading table information for
completion of table and column names
You can turn off this feature to get a
quicker startup with -A
Database changed
mysql> select * from example;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
That’s it. Now the tables created in
the Master server are automatically replicated to the Slave
server.
