Mysql LIVE Repplication guide (LINUX)

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 

Select all 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

Select all 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

Select all 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';

Select all 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;

Select all Open in new window

Here is our example output:

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

Select all 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;

Select all Open in new window

5

Now we are ready to execute

mysql>[b]SLAVE START[/b]; a mysql>[b]SHOW SLAVE STATUS\G[/b];

Select all 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)

Select all 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