MySQL Master-Slave Replication

hostgliders

Member
Hosting Provider
Registered
Joined
Feb 17, 2017
Messages
51
Points
0
MySQL master slave replication gives you two copies of your database: the live” one and the backup one. You always write your data to your master and read from the master too, but you will always have an up-to-date copy on your slave.


Setting up the master Server

Make sure you have updated packages and MYSQL server isntalled in the server

#
Code:
yum update
#
Code:
yum install install mysql-server


Open the my.cnf file, which contains MYSQL database configuration

Code:
[B]vi   /etc/my.cnf[/B]

Add following lines

Code:
[B][mysqld]
log-bin=mysql-bin
binlog-do-db=mydb1
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1[/B]

Restart mysql service

Code:
[B]service mysqld restart[/B]

Login to MYSQL with MYSQL root password

Code:
[B]mysql -u root -p[/B]
Grand Access to your slave server
Code:
[B]GRANT REPLICATION SLAVE ON *.* to ‘replication'@192.168.1.60 IDENTIFIED BY ‘yourpassword';
mysql> FLUSH PRIVILEGES;
[/B]
Replace the IP address (192.168.1.60)with your slave's IPv4 address and replace ‘yourpassword' with a strong password. Execute the query. It should say ‘Query OK'.


Check the current binary log file name (File) and current offset (Position) value using following command.

Code:
[B]mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000109 |      187 | mydb1         |                  |
+------------------+----------+--------------+------------------+[/B]
Please note the filename (‘File') and number (‘Position'). Remember these or write them down. You will use this to start replication on the slave.


Take a backup of database and copy it to slave mysql server.
Code:
[B] mysqldump -u root -p mydb > mydb1.sql[/B]
# [B]scp mydb1.sql 192.168.1.60:/opt/[/B]


Setup MySQL Slave Server


Make sure you have updated packages and MYSQL server isntalled in the server

Code:
# yum update
#yum install install mysql-server

Edit salve mysql configuration file and add following values under [mysqld] section.
Code:
[mysqld]
server-id=2
replicate-do-db=mydb1

Restart mysql service
Code:
service mysqld restart

Restore database backup taken from master server.
Code:
# mysql -u root -p mydb < mydb.sql

Setup option values on slave server using following command.
Code:
mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.20',
    -> MASTER_USER='replication',
    -> MASTER_PASSWORD='secretpassword',
    -> MASTER_LOG_FILE='mysql-bin.000109',
    -> MASTER_LOG_POS=187;


Finally start the slave thread
Code:
mysql> SLAVE START;

Check the status of slave server.
Code:
mysql> show slave status G


*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.60
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000109
          Read_Master_Log_Pos: 187
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: mydb
          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: 187
              Relay_Log_Space: 187
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>


MySQL Master-slave Replication has been configured successfully on your system . You can test the same by creating a test datatabse in Master server, so it will automatically copied to slave server.
 
Newer Threads
Replies
4
Views
2,924
Replies
13
Views
11,480
Replies
3
Views
3,156
Recommended Threads
Replies
33
Views
9,270
Replies
4
Views
2,127
fiz
Replies
22
Views
8,445

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top