<

Mysql LIVE Repplication guide (LINUX)

Published on
8,679 Points
2,679 Views
Last Modified:
Approved
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
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free