How to Setup MySQL (Master-Slave) Replication in RHEL, CentOS, Fedora
The following tutorial aims to provide you a simple step-by-step guide for setting up MySQL (Master-Slave) Replication in RHEL 6.3/6.2/6.1/6/5.8, CentOS 6.3/6.2/6.1/6/5.8 and Fedora 17,16,15,14,13,12 using latest MySQL version. This guide is specially written for Redhat 6.6 Operating System, but also work with older version of Linux distributions with MySQL 5.x.
The MySQL Replication is very useful in terms of Data Security, Fail-over Solution, Database Backup from Slave, Analytics etc. We use the following things to carry the replication process. In your scenario it would be different.
The MySQL Replication is very useful in terms of Data Security, Fail-over Solution, Database Backup from Slave, Analytics etc. We use the following things to carry the replication process. In your scenario it would be different.
- Working Linux OS like CentOS 6.3, RedHat 6.3 or Fedora 17
- Master and Slave are Redhat 6.6 Linux Servers.
- Master IP Address is: 192.168.1.1.
- Slave IP Address is: 192.168.1.2.
- Master and Slave are on the same LAN network.
- Master and Slave has MySQL version installed.
- Master allow remote MySQL connections on port 3306.
[[email protected] ~]# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
[[email protected] ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)
[[email protected] ~]#
We have two servers, one is Master with IP (192.168.1.1) and other is Slave as (192.168.1.2). We have divided the setup process in two phases to make things easier for you, In Phase I we will configure Master server and in Phase II with Slave server. Let’s start the replication setup process.
Phase I: Configure Master Server (192.168.1.1) for Replication
In Phase I, we will see the installation of MySQL, setting up Replication and then verifying replication.
Install a MySQL on Master Server
First, proceed with MySQL installation using YUM command. If you already have MySQL installation, you can skip this step.[[email protected] ~]# yum install yum install mysql mysql-server
If you need detail about mysql 5.1 installation on Redhat 6 you can find in here
Configure a MySQL on Master Server
Open
my.cnf
with vi editor[[email protected] ~]# vi /etc/my.cnf
Configuration before edit shown on following figure[[email protected] ~]# cat /etc/my.cnf [mysqld] 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 [[email protected] ~]#
Add the following entries under
[mysqld]
section and don’t forget to replaced db_wachid
with database name that you would like to replicate on Slave.server-id = 1
binlog-do-db=db_wachid
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
Save
my.cnf
and the configuration will be like following figure [[email protected] ~]# cat /etc/my.cnf
[mysqld]
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
server-id = 1
binlog-do-db=db_wachid
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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[[email protected] ~]#
Restart MySQL service
[[email protected] ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[[email protected] ~]#
Login into MySQL as root user and create the slave user and grant privileges for replication. Replace
slave_user
with user and p4ssw0rd
with your password.[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73-log Source distribution
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>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'p4ssw0rd';
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 | 331 | db_wachid | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> quit
Please write down the File (mysql-bin.000001) and Position (331) 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.
[[email protected] ~]# mysqldump -u root -p --all-databases --master-data > /root/dbdump.db
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[[email protected] ~]#
Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.
[[email protected] ~]# 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.73-log Source distribution 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> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [[email protected] ~]#
Transfer the database dump file on Slave Server (192.168.1.2) using SCP command.
[[email protected] yum.repos.d]# scp /root/dbdump.db [email protected]:/root/
[email protected]'s password:
dbdump.db 100% 514KB 514.4KB/s 00:00
[[email protected] yum.repos.d]#
That’s it we have successfully configured Master server, let’s proceed to Phase II section..
Phase II: Configure Slave Server (192.168.1.2) for Replication
In Phase II, we do the installation of MySQL, setting up Replication and then verifying replication.
Restart MySQL service
You cannot import the dump file while mysql server running as slave, you will get this error
Install a MySQL on Master Server
Install MySQL server, if you don't have then perform installation with following command[[email protected] ~]# yum install yum install mysql mysql-server
Configure a MySQL on Slave Server
[[email protected] ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[[email protected] ~]#
Now import the dump file to slave server with following command
[[email protected] ~]# mysql -u root -p < /root/dbdump.db Enter password: [[email protected] ~]#
[[email protected] ~]# mysql -u root -p < /root/dbdump.db
Enter password:
ERROR 1198 (HY000) at line 22: This operation cannot be performed with a running slave; run STOP SLAVE first
[[email protected] ~]#
Open my.cnf
with vi editor[[email protected] ~]# vi /etc/my.cnf
Configuration before edit shown on following figure[[email protected] ~]# cat /etc/my.cnf [mysqld] 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 [[email protected] ~]#
Add the following entries under
[mysqld]
section and don’t forget to replaced db_wachid
with database name that you would like to replicate with master.server-id = 2
master-host=192.168.1.2
master-connect-retry=60
master-user=slave_user
master-password=p4ssw0rd
replicate-do-db=db_wachid
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
Save
my.cnf
and the configuration will be like following figure [[email protected] ~]# cat /etc/my.cnf
[mysqld]
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
server-id = 2
master-host=192.168.1.2
master-connect-retry=60
master-user=slave_user
master-password=p4ssw0rd
replicate-do-db=db_wachid
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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[[email protected] ~]#
Restart MySQL service again to reload new configuration
[[email protected] ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[[email protected] ~]#
Login into MySQL as root user and stop the slave. Then tell the slave to where to look for Master log file, that we have write down on master with SHOW MASTER STATUS; command as File (mysql-bin.000001) and Position (331) numbers. You must change 192.168.1.1 to the IP address of the Master Server, and change the user and password accordingly.[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73-log Source distribution
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>
mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='slave_user', MASTER_PASSWORD='p4ssw0rd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=331;
Query OK, 0 rows affected (0.03 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 331
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_wachid
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: 331
Relay_Log_Space: 406
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
mysql>
mysql> exit
Bye
Verifying MySQL Replication on Master and Slave Server
It’s really very important to know that the replication is working perfectly. On Master server create table and insert some values in it.
On Master
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
mysql>
mysql> create database db_wachid;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| db_wachid |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql> use db_wachid;
Database changed
mysql>
mysql> create table test_tab (col1 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_tab (col1) values (10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_tab;
+------+
| col1 |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql>
mysql> exit
Bye
On Slave
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 631
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 551
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_wachid
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: 631
Relay_Log_Space: 706
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>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_wachid |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql> use db_wachid;
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>
mysql> show tables;
+---------------------+
| Tables_in_db_wachid |
+---------------------+
| test_tab |
+---------------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from test_tab;
+------+
| col1 |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql>
That’s it, finally you’ve configured MySQL Replication in a few simple steps. More information can be found at MySQL Replication Guide.
Tidak ada komentar :
Posting Komentar