Mysql LIVE Repplication guide (LINUX)

Published:
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment).

We need to replicate EVERY executed SQL query on server 1 to server 2.

The main configuration files are located at
/etc/my.cnf or /etc/mysql/my.cnf 
                      

Open in new window

depending on which distribution of Linux you have.

The initial settings for the master server are:

1

server-id = 1 
                      log-bin = /var/log/mysql/bin.log
                      

Open in new window

These settings you need to add to the [mysqld] header in your .cnf file

Now the settings of slave server my.cnf :

2

server-id = 2 
                      
                      master-host = 192.168.0.1 
                      master-port = 3306 
                      master-user = root 
                      master-password = passwod 
                      log-bin = /var/log/mysql/bin.log 
                      log-bin-index = /var/log/mysql/log-bin.index 
                      log-error = /var/log/mysql/error.log 
                      
                      relay-log = /var/log/mysql/relay.log 
                      relay-log-info-file = /var/log/mysql/relay-log.info 
                      relay-log-index = /var/log/mysql/relay-log.index
                      

Open in new window


Relay-logs are "buffers" of executed queries; log-bin is for verbose logging.

3

on the Master server you need to execute MySQL command
GRANT REPLICATION SLAVE, REPLICATION CLIENT 
                          ON *.* 
                          TO 'root'@'192.168.0.2 
                          IDENTIFIED BY 'password';
                      

Open in new window

The IP address is that of the slave server and password is password identified in my.cnf on the slave server.

Now our first configuration is ready to use. We can check the configuration by
mysql>SHOW MASTER STATUS;
                      

Open in new window


Here is our example output:

+---------------+----------+--------------+------------------+ 
                      | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
                      +---------------+----------+--------------+------------------+ 
                      |  bin.log.001 | 29       | test         |                 | 
                      +---------------+----------+--------------+------------------+
                      

Open in new window


This represents that the log file is bin.log.001 and the last position in file is 29 . You need this option set up on the slave server (MySQL command):

4

CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='root', MASTER_PASSWORD='password', MASTER_LOG_FILE=bin.log.001', MASTER_LOG_POS=29;
                      

Open in new window


5

Now we are ready to execute
mysql>[b]SLAVE START[/b]; a mysql>[b]SHOW SLAVE STATUS\G[/b];
                      

Open in new window



This is the example's output:
*************************** 1. row *************************** 
                                   Slave_IO_State: Waiting for master to send event 
                                      Master_Host: 192.168.0.1 
                                      Master_User: root 
                                      Master_Port: 3306 
                                    Connect_Retry: 60 
                                  Master_Log_File: bin.log.001 
                              Read_Master_Log_Pos: 29 
                                   Relay_Log_File: mysql-relay-bin.000002 
                                    Relay_Log_Pos: 558 
                            Relay_Master_Log_File: mysql-bin.000075 
                                 Slave_IO_Running: Yes 
                                Slave_SQL_Running: Yes 
                                  Replicate_Do_DB: 
                              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: 47514905 
                                  Relay_Log_Space: 558 
                                  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 
                      1 row in set (0.00 sec)
                      

Open in new window


This is now our live replication. However, you must have the same databases and tables ready on the slave server because it dies when you try to insert some data to a nonexistent table.

I hope it is helpful post.
jjo9pd.jpg
0
2,878 Views

Comments (2)

Author

Commented:
yes i wil  review it but im little busy now :) i will check it wednesdey :)
CERTIFIED EXPERT

Commented:
Maverickerko,

Congratulations! Your article has been published.

ericpete
Page Editor

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.