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
depending on which distribution of Linux you have.
The initial settings for the master server are:
These settings you need to add to the
[mysqld] header in your .cnf file
Now the settings of slave server
my.cnf :
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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
Select allOpen in new window
Relay-logs are "buffers" of executed queries; log-bin is for verbose logging.
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';
1:
2:
3:
4:
Select allOpen 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
Here is our example output:
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| bin.log.001 | 29 | test | |
+---------------+----------+--------------+------------------+
1:
2:
3:
4:
5:
Select allOpen 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):
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;
1:
Select allOpen in new window
Now we are ready to execute
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)
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
Select allOpen 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.
by: Maverickerko on 2010-09-13 at 09:46:41ID: 19359