<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Mysql LIVE Repplication guide (LINUX)

Published on
8,637 Points
2,637 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
2 Comments
LVL 2

Author Comment

by:Maverickerko
yes i wil  review it but im little busy now :) i will check it wednesdey :)
0
LVL 15

Expert Comment

by:Eric AKA Netminder
Maverickerko,

Congratulations! Your article has been published.

ericpete
Page Editor
0

Featured Post

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month