Reservation of data stored in MySQL tables is carried out by master-master replication. According to that, all database updates made on the first host are transmitted to the second one in real time. Thus, each copy of Eltex.EMS service has the same virtual address as one of database management system's copies (http://dev.mysql.com/doc/refman/5.5/en/replication.html). That allows having a relevant copy of a database on two hosts simultaneously. If connection is failed, changes are accumulated and then synchronized after reconnection.
1. Replication configuration
Make the following changes in [ mysqld ] section of /etc/mysql/my.cnf configuration file:
Comment out or delete the line:
bind-address = 127.0.0.1
Specify server-id. The servers should be assigned different identifiers, e.g. for the first server:
server-id = 1
for the second server:
server-id = 2
Enable binary logs:
log_bin = /var/log/mysql/mysql-bin.log;
specify auto_increment_increment (increment step) and auto_increment_offset (start point) parameters.
For the first server:
auto_increment_increment= 2
auto_increment_offset = 1
For the second server:
auto_increment_increment= 2
auto_increment_offset = 2
Specify databases for which logs will be generated:
binlog-do-db = eltex_alert
binlog-do-db = eltex_ems
binlog-do-db = wireless binlog-do-db = radius
binlog-do-db = eltex_auth_service
binlog-do-db = ELTEX_PORTAL
binlog-do-db = payments
Specify databases for which logs will not be generated:
binlog-ignore-db = mysql
binlog-ignore-db = Syslog
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
Restart mysql service on each server and create a database for replication.
2. Creating accounts for replication
Create an account for replication on the first server:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip_server2>' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Create an account for replication on the second server:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip_server1>' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
3. Starting slave replication on the second server
3.1 Run the show master status command in MySQL console of the first server and analyze values obtained:
show master status \G
The reply should be as follows:
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 00000107
Binlog_Do_DB: eltex_alert,eltex_ems,eltex_ont,radius,wireless,eltex_auth_service,payments,ELTEX_PORTAL
Binlog_Ignore_DB: mysql,Syslog,performance_schema,information_schema
1 row in set (0.00 sec)
Copy the paramaters File and Position parameters.
3.2 Configure and start the second server replication from the first one (perform the following operations on the second server):
mysql> CHANGE MASTER TO MASTER_HOST='<ip_server1>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; START SLAVE;
where
MASTER_LOG_FILE='mysql-bin.000001' – specify the File value obtained in the previous paragraph.
MASTER_LOG_POS=107 – specify the Position value obtained in the previous paragraph.
3.3 Check replication state:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <ip_server1>
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001783
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 107
Relay_Master_Log_File: mysql-bin.001783
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: 107
Relay_Log_Space: 107
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
If Slave _ IO _ Running and Slave _ SQL _ Running parameters are set to « Yes », replication has been launched successfully.
3.4 Create a table in the database on the first server and add data to it or create a dump.
use eltex_ems; create table test1 (mid int(11) auto_increment, PRIMARY KEY (mid)) Engine=MyISAM;
3.4 On the second server, check if the new table has been copied.
4. Starting replication on the first server
4.1 Run show master status command on the second server:
show master status \G
The reply should be as follows:
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 00000107
Binlog_Do_DB: eltex_alert,eltex_ems,eltex_ont,radius,wireless,eltex_auth_service,payments,ELTEX_PORTAL
Binlog_Ignore_DB: mysql,Syslog,performance_schema,information_schema
1 row in set (0.00 sec)
4.2 Configure and start the first server replication from the second one:
CHANGE MASTER TO MASTER_HOST='<ip_server2>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; START SLAVE;
4.3 Check replication state:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <ip_server2>
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 107
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(the output given is incomplete, since other data is less important)
If the values are correct, replication is performed in either direction. Then the database should be filled with real data.
5. The script to start replication on the servers
Instead of adding all entries manually, the following script can be used:
|
For the script to work, identical values with remote and local root access should be created in mysql.
Automatic replication restart in case of server communication failure
Create /etc/keepalived/revive_mysql_replication.sh file with the following contents on both servers:
|
where
host = 172.16.110.3 – the address of the opposite server.
To make the script run every minute, add the following command to cron:
crontab -l | { cat; echo "*/1 * * * * /etc/keepalived/revive_mysql_replication.sh"; } | crontab
Add users on the first server (specify opposite server's IP address):
GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip_server2>' IDENTIFIED BY 'root'; FLUSH PRIVILEGES;
And on the second server:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip_server1>' IDENTIFIED BY 'root'; FLUSH PRIVILEGES;