How to configure MySQL Master-Slave Replication in RHEL 6.X/Centos 6.X

How to configure MySQL Master-Slave Replication in RHEL 6.X/Centos 6.X

1. Working Linux OS like CentOS 6.3, RedHat 6.3 or Fedora 17
2. Master and Slave are CentOS 6.3 Linux Servers.
3. Master IP Address is: 10.65.62.30
4. Slave IP Address is: 10.66.191.232
5. Master and Slave are on the same LAN network.
6. Master and Slave has MySQL version installed.
7. Master allow remote MySQL connections on port 3306.
   

Master side (10.65.62.30):

[root@station1 Desktop]# yum install -y mysql*

[root@station1 Desktop]# service mysqld start

[root@station1 Desktop]# mysqladmin -u root password password ------> setting password for the root user

[root@station1 Desktop]# mysql -u root -ppassword ------> login

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use mysql;

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> DELETE FROM mysql.user WHERE user = ''; ----> to delete anonymous users.

mysql> flush privileges;

mysql> create database Ranjith;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| Ranjith |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use Ranjith;

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> create table contacts ( `first_name` char(20), `last_name` char(20), `mob_no` char(20), `email_id` char(30), PRIMARY KEY (`email_id`));

mysql> show tables;

+-------------------+

| Tables_in_Ranjith |

+-------------------+

| contacts |

+-------------------+

1 row in set (0.00 sec)

mysql> describe contacts;

+------------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+----------+------+-----+---------+-------+

| first_name | char(20) | YES | | NULL | |

| last_name | char(20) | YES | | NULL | |

| mob_no | char(20) | YES | | NULL | |

| email_id | char(30) | NO | PRI | | |

+------------+----------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> INSERT INTO contacts (first_name,last_name,mob_no,email_id) VALUES ('Ranjith','Pandurangan','+918056316316','upload.vellore@licindia.com');

mysql> select * from contacts;

+------------+-------------+---------------+----------------------+

| first_name | last_name | mob_no | email_id |

+------------+-------------+---------------+----------------------+

| Ranjith | Pandurangan | +918056316316 | upload.vellore@licindia.com |

+------------+-------------+---------------+----------------------+

2 rows in set (0.00 sec)

mysql>quit;

[root@station1 ~]# service mysqld stop

Stopping mysqld: [ OK ]

[root@station1 ~]# vim /etc/my.cnf

[mysqld]

server-id = 1

binlog-do-db=Ranjith ----> Name of database to be replicated in slave

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 = /var/lib/mysql/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

[root@station1 ~]# ls /var/lib/mysql

ibdata1 ib_logfile0 ib_logfile1 mysql Ranjith test

[root@station1 ~]# service mysqld start

Starting mysqld: [ OK ]

[root@station1 ~]# ls /var/lib/mysql

ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.index mysql.sock Ranjith test

[root@station1 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, 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> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

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.000001 | 106 | Ranjith | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> quit

Please write down the File (mysql-bin.000001) and Position (106) numbers, we required these numbers later on Slave server. Next apply READ LOCK to databases to export all the database and master database information with mysqldump command.

[root@station1 ~]# mysqldump -u root -p --all-databases --master-data > /root/ranjithdb.db

Enter password:

[root@station1 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, 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> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

[root@station1 ~]# scp -r /root/ranjithdb.db 10.66.191.232:/root/

 

Slave side (10.66.191.232):

[root@vellore ~]# ping 10.65.62.30

PING 10.65.62.30 (10.65.62.30) 56(84) bytes of data.

64 bytes from 10.65.62.30: icmp_seq=1 ttl=59 time=28.2 ms

64 bytes from 10.65.62.30: icmp_seq=2 ttl=59 time=28.2 ms

^C

--- 10.65.62.30 ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 1332ms

rtt min/avg/max/mdev = 28.236/28.237/28.238/0.001 ms

[root@station1 Desktop]# yum install -y mysql*

[root@station1 Desktop]# service mysqld start

[root@station1 Desktop]# mysqladmin -u root password password ------> setting password for the root user

[root@station1 Desktop]# mysql -u root -ppassword ------> login

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use mysql;

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> DELETE FROM mysql.user WHERE user = ''; ----> to delete anonymous users.

mysql> flush privileges;

mysql> create database Ranjith;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| Ranjith |

| mysql |

| test |

+--------------------+

4 rows in set (0.02 sec)

mysql> quit

Bye

[root@vellore ~]# mysql -u root -ppassword Ranjith < /root/ranjithdb.db

[root@vellore ~]# vim /etc/my.cnf

[mysqld]

server-id = 2

master-host=10.65.62.30

master-connect-retry=60

master-user=root

master-password=password

replicate-do-db=Ranjith

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 = /var/lib/mysql/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

[root@vellore ~]# service mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@vellore ~]# mysql -u root -ppassword
 

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='10.65.62.30', MASTER_USER='root', 

MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;

mysql> slave start;
mysql> show slave status\G

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.65.62.30

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 400

Relay_Log_File: mysql-relay-bin.000008

Relay_Log_Pos: 390

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: Ranjith

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

Exec_Master_Log_Pos: 400

Relay_Log_Space: 545

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)

Verifying MySQL Replication on Master and Slave Server

Master Side (10.65.62.30):

[root@station1 ~]# mysql -u root -ppassword

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, 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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| Ranjith |

| mysql |

| test |

+--------------------+

4 rows in set (0.02 sec)

mysql> use Ranjith;

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> show tables;

+-------------------+

| Tables_in_Ranjith |

+-------------------+

| contacts |

+-------------------+

1 row in set (0.00 sec)

mysql> select * from contacts where First_name='Ranjith';

+------------+-------------+---------------+-----------------------------+

| first_name | last_name | mob_no | email_id |

+------------+-------------+---------------+-----------------------------+

| Ranjith | Pandurangan | +918056316316 | upload.vellore@licindia.com |

+------------+-------------+---------------+-----------------------------+

1 row in set (0.02 sec)

mysql> UPDATE contacts SET last_name='' WHERE first_name='Ranjith';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from contacts where First_name='Ranjith';

+------------+-----------+---------------+-----------------------------+

| first_name | last_name | mob_no | email_id |

+------------+-----------+---------------+-----------------------------+

| Ranjith | | +918056316316 | upload.vellore@licindia.com |

+------------+-----------+---------------+-----------------------------+

1 row in set (0.00 sec)

mysql>

Slave Side (10.66.191.232:

[root@vellore ~]# mysql -u root -ppassword

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, 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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| Ranjith |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use Ranjith;

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 contacts where First_name='Ranjith';

+------------+-----------+---------------+-----------------------------+

| first_name | last_name | mob_no | email_id |

+------------+-----------+---------------+-----------------------------+

| Ranjith | | +918056316316 | upload.vellore@licindia.com |

+------------+-----------+---------------+-----------------------------+

1 row in set (0.00 sec)

mysql>quit

From the above example you can see that when i remove my last name in Master database, the same thing gets replicated automatically in my slave server.

Thanks watching. Having any doubts or errors give a comment.