How to Setup MySQL (Master-Slave) Replication in linux

Here i’m going to explain, step’s to configure MySQL (Master-Slave) Replication to secure your Database from hackers. For this requirement mysql has been installed on both server.

Two way of configuration is must be handle, so that it will not causes any issues while configuring.



  • Master Server Configuration
  • Slave Server Configuration
  • Testing

Tested Environment.

Both Remote Server OS CentOS release 6.5 Server (Final)
Both Remote Server MySQL Version 5.1.73
Master Server IP 83.170.117.116
Slave Server IP 83.170.117.139

1) Master Server Configuration

First i’m going to configure the master server for Replication.

Configure a MySQL in Master Server

Just open the my.cnf file on your favourite editor and add the below coloured lines then save and exit the file. Instead of daygeek_daygeek add your DB.

[email protected] [~]# nano /etc/my.cnf

  GNU nano 2.0.9                                  File: /etc/my.cnf                                                                            

[mysqld]
innodb_file_per_table=1
open_files_limit=20000
local-infile=0

server-id = 1
binlog-do-db=daygeek_daygeek
log-bin = /var/lib/mysql/mysql-bin

Restarting the mysql service

After exiting the file, restart your mysql service to take effect.

[email protected] [~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

Slave user creation

Login to mysql server and create the slave user and grant privileges for replication.

[email protected] [~]# mysql -u root -p
Enter Password : *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.1.73-cll MySQL Community Server (GPLv2)

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.

In this tutorial i’m using slave username as slave_daygeek and password as daygeek123. You should replace this for your convenient.

Use the below command to create the slave user.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_daygeek'@'%' IDENTIFIED BY 'daygeek123';
Query OK, 0 rows affected (0.00 sec)

Flush the privileges to take effect the modification.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Apply READ LOCK to on database to export the database and master database information using mysqldump command.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Note the below file name and position, it will be required on slave configuration.

mysql> SHOW MASTER STATUS;
+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000006 |      336 | daygeek_daygeek |                  |
+------------------+----------+-----------------+------------------+
1 row in set (0.00 sec)

mysql> quit;

Use the below command to dump mysql database and master database information.

[email protected] [~]# mysqldump -u root daygeek_daygeek --master-data > /root/onlydaygeek.db

After dumped the database unlock the tables and quit.

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

Copy the dumped database to slave server.

[email protected] [~]# rsync -avz -e "ssh -p 11021" /root/onlydaygeek.db 83.170.117.139:/root
stdin: is not a tty
sending incremental file list
onlydaygeek.db

sent 122110947 bytes  received 31 bytes  10618345.91 bytes/sec
total size is 130098213  speedup is 1.07

Now, you have successfully configured Master server configuration.

2) Slave Server Configuration

Here i’m going to configure the slave server for Replication.

Configure a MySQL in Slave Server

Just open the my.cnf file on your favourite editor and add the below coloured lines then save and exit the file.

[email protected] [~]# nano /etc/my.cnf

 GNU nano 2.0.9                                  File: /etc/my.cnf                                                                            

[mysqld]
innodb_file_per_table=1
open_files_limit=11350
local-infile=0

server-id=2
master-host=83.170.117.116
master-connect-retry=60
master-user=slave_daygeek
master-password=daygeek123
replicate-do-db=daygeek_daygeek
log-bin = /var/lib/mysql/mysql-bin

Restarting the mysql service

After exiting the file, restart your mysql service to take effect.

[email protected] [~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

Want to create the database to import the dumped database which was taken from master server.

[email protected] [~]# mysql -u -p 
Enter Password : ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.1.73-cll MySQL Community Server (GPLv2)

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.

Use the below command to create the database.

mysql> create database daygeek_daygeek;
Query OK, 1 row affected (0.00 sec)

Use the below command to create the user.

mysql> CREATE USER 'daygeek_daygeek'@'localhost' IDENTIFIED BY 'daygeek123';
Query OK, 1 row affected (0.00 sec)

Use the below command to assign the privilege the database.

mysql> GRANT ALL PRIVILEGES ON daygeek_daygeek.* TO 'daygeek_daygeek'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Flush the privileges to take effect the modification and quit to exit from database server.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

Use the below command to import the database which was dumped from master server.

[email protected] [~]# mysql -u root -p daygeek_daygeek < /root/onlydaygeek.db
Enter password: *****

Login to mysql server and do the below necessary changes.

[email protected] [~]# mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 138
Server version: 5.1.73-cll MySQL Community Server (GPLv2)

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.

/*Use the below command to stop the slave

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

*/

Run the below command so that slave get the details from master.(give the required details whcih you assigned in master)

mysql> CHANGE MASTER TO MASTER_HOST='83.170.117.116', MASTER_USER='slave_daygeek', MASTER_PASSWORD='daygeek123', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=336;
Query OK, 0 rows affected (0.12 sec)

Use the below command to start slave.

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

Now, check the slave server status.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 83.170.117.116
                  Master_User: slave_daygeek
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1354
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1269
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: daygeek_daygeek
          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: 1354
              Relay_Log_Space: 1424
              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)

Now, you have successfully configured Slave server configuration.

3) Testing

Now, i’m going to test whether its working properly or not.

3a) Testing on Master Server

Just login to master and check the corresponding database and how many tables are there. Then create new table and check at slave server wheter its reflected or not.

[email protected] [~]# mysql -u -p
Enter Password : ******

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 282
Server version: 5.1.73-cll MySQL Community Server (GPLv2)

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.

Navigate the corresponding database

mysql> use daygeek_daygeek;
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

List out the tables which is present on the database.

mysql> show tables;
+------------------------------------+
| Tables_in_daygeek_daygeek          |
+------------------------------------+
| TMPSBIQBE_biuser                   |
| daygeek_bug_file_table             |
| .				     |
| .				     |
| daygeek_project_user_list_table    |
| daygeek_project_version_table      |
| daygeek_sponsorship_table          |
| daygeek_tag_table                  |
| daygeek_tokens_table               |
| daygeek_user_pref_table            |
| daygeek_user_print_pref_table      |
| daygeek_user_profile_table         |
| daygeek_user_table                 |
+------------------------------------+
33 rows in set (0.00 sec)

The output clearly shows the master server database is having 33 tables and i’m going to create the new one fore testing purpose, so that i can check whether its working properly or not.

mysql> create table test_mirror (c int);
Query OK, 0 rows affected (0.11 sec)

Inserting some values.

mysql> INSERT INTO test_mirror (c) values (15);
Query OK, 1 row affected (0.00 sec)

Verifying the tables value whcih is created by me.

mysql> SELECT * FROM test_mirror;
+------+
| c    |
+------+
|   15 |
+------+
1 row in set (0.00 sec)

3b) Testing on Slave Server

Now, i’m going to check the slave server whether its replected or not.

[email protected] [~]# mysql -u -p
Enter Password : ******

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 282
Server version: 5.1.73-cll MySQL Community Server (GPLv2)

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.

Navigate the corresponding database

mysql> use daygeek_daygeek;

Database changed

List out the tables which is present on the database.

mysql> show tables;
+------------------------------------+
| Tables_in_daygeek_daygeek          |
+------------------------------------+
| TMPSBIQBE_biuser                   |
| daygeek_bug_file_table             |
| .				     |
| .				     |
| daygeek_project_user_list_table    |
| daygeek_project_version_table      |
| daygeek_sponsorship_table          |
| daygeek_tag_table                  |
| daygeek_tokens_table               |
| daygeek_user_pref_table            |
| daygeek_user_print_pref_table      |
| daygeek_user_profile_table         |
| daygeek_user_table                 |
| test_mirror                        |
+------------------------------------+
34 rows in set (0.00 sec)

Yes its reflected now.
I’m checking the value.

mysql> SELECT * FROM test_mirror;
+------+
| c    |
+------+
|   15 |
+------+
1 row in set (0.00 sec)

Its also clearly shows, Wow we have successfully done the (Master-Slave) Replication.

We are preparing all articles in-depth to understand by all level/stage Linux administrators. If the article is useful for you, then please spend less than a minute to share your valuable comments in our commenting section.

Please stay tune with us…Good Luck.

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...