MySQL master-slave Database Synchronization enhances System Security

Test environment:

Master server: CentOS 5.2 Mysql 5.1.35 source code IP: 192.168.1.22

Slave server: CentOS 5.2 Mysql 5.1.35 source code IP: 192.168.1.33

Configuration:

I. master server

1.1 create a replication user with the replication slave permission.

 
 

  
  
mysql>grant replication slave on *.* to ‘repl’@’192.168.1.22′ identified by ‘repl’; 
 
 

1.2 edit the my. cnf File

Vi/etc/my. cnf

Add

Server-id = 1

And enable the log-bin binary log file.

Log-bin = mysql-bin

Note: remove the default server-id = 1.

1.3 start the mysql database

 
 

  
  
mysqld_safe –user=mysql & 
 
 

1.4 set the read lock

 
 

  
  
mysql>flush tables with read lock; 
 
 

1.5. Get the binlog Log File Name and offset.

 
 

  
  
mysql>show master status;  
  
  
+——————+———-+————–+——————+  
  
  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
  
  
+——————+———-+————–+——————+  
  
  
| mysql-bin.0000010 | 106| | |  
  
  
+——————+———-+————–+——————+ 
 
 

1.6 back up the database to be synchronized

 
 

  
  
mysqldump test > test.sql 
 
 

1.7 unlock

 
 

  
  
mysql>unlock tables; 
 
 

Ii. slave server

2.1 edit the my. cnf File

Vi/etc/my. cnf

Add

Server-id = 2

Note: remove the default server-id = 1.

2.2 start slave Database

 
 

  
  
mysqld_safe –user=mysql & 
 
 

2.3 configure slave Database

 
 

  
  
mysql> change master to  
  
  
-> master_host=’192.168.1.22′  
  
  
-> master_user=’repl’  
  
  
-> master_password=’repl’  
  
  
-> master_log_file=’mysql-bin.0000010′  
  
  
-> master_log_pos=106; 
 
 

2.4 start the slave server slave thread

 
 

  
  
mysql>start slave; 
 
 

Run the show processlist command to display the following processes:

 
 

  
  
mysql>show processlist\G  
  
  
 
  
  
*************************** 2. row ***************************  
  
  
Id: 2  
  
  
User: system user  
  
  
Host:  
  
  
db: NULL  
  
  
Command: Connect  
  
  
Time: 2579  
  
  
State: Has read all relay log; waiting for the slave I/O thread to update it 
 
 

Info: NULL indicates that the slave has been connected to the master, and the log is accepted and executed.

2.5 view the slave thread status

 
 

  
  
mysql>show slave status;  
  
  
*************************** 1. row ***************************  
  
  
Slave_IO_State: Waiting for master to send event  
  
  
Master_Host: 192.168.1.22  
  
  
Master_User: repl  
  
  
Master_Port: 3306  
  
  
Connect_Retry: 60  
  
  
Master_Log_File: mysql-bin.0000010  
  
  
Read_Master_Log_Pos: 106  
  
  
Relay_Log_File: centos-relay-bin.000002  
  
  
Relay_Log_Pos: 529  
  
  
Relay_Master_Log_File: mysql-bin.0000010  
  
  
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: 106  
  
  
Relay_Log_Space: 830  
  
  
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:  
  
  
1 row in set (0.00 sec) 
 
 

Verify that the configuration is correct

Run

 
 

  
  
show slave status\G;  
  
  
Waiting for master to send event  
  
  
Slave_IO_Running: Yes  
  
  
Slave_SQL_Running: Yes 
 
 

If the preceding two rows are both "Yes", the configuration is successful.

Test:

1. Create a user table in the master server test database

 
 

  
  
mysql>use test;  
  
  
mysql>create table user(id int); 
 
 

2. view the user table on the slave server

 
 

  
  
mysql>use test;  
  
  
mysql> show tables like ‘user’;  
  
  
+———————-+  
  
  
| Tables_in_test(user) |  
  
  
+———————-+  
  
  
| user |  
  
  
+———————-+  
  
  
1 row in set (0.00 sec) 
 
 

The Master/Slave Data is successfully synchronized.

FAQs:

1. It appears when you view the slave status from the database

The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the-replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

The server-id in my. cnf is the same from the server.

Solution:

Modify the server-id in my. cnf and restart the database service. The my. cnf file has server-id = 1 by default.

Other Instructions

Master server my. cnf

# Binlog-do-db = Name of the database to be backed up. Multiple rows can be written.

# Binlog-ignore-db = Name of the database that does not need to be backed up. Multiple rows can be written.

Slave server my. cnf

# Replicate-do-db = Name of the database to be backed up by test

# Replicate-ignore-db = databases ignored by mysql

# Master-connect-retry = 60 if the master server is disconnected from the server, the time difference between the reconnection (seconds)

You can also directly modify the my. cnf configuration file using the following settings.

 
 

  
  
log-bin=mysql-bin  
  
  
master-host=192.168.1.22  
  
  
master-user=repl 
  
  
master-password=repl 
  
  
master-port=3306 
 
 

Synchronous maintenance of Master/Slave servers

Due to various reasons, the Master/Slave Data is inconsistent. When the load is low, manual synchronization is performed.

Run

 
 

  
  
mysql>flush tables with read lock;  
  
  
Query OK,rows affected (0.01 sec)  
  
  
mysql>show master status;  
  
  
+——————+———-+————–+——————+  
  
  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
  
  
+——————+———-+————–+——————+  
  
  
| mysql-bin.0000011 | 260| | |  
  
  
+——————+———-+————–+——————+ 
 
 

Run

First obtain the binary file name and offset of the current master server, and execute the command to synchronize the slave server with the master server.

 
 

  
  
mysql>select master_pos_wait(‘mysql-bin.0000011′,’260′);  
  
  
+————————————————–+  
  
  
| master_pos_wait(‘mysql-bin.0000011′,’260′) |  
  
  
+————————————————–+  
  
  
| 0 |  
  
  
+————————————————–+  
  
  
1 row in set (0.01 sec) 
 
 

After synchronization, unlock the master server

 
 

  
  
mysql>unlock tables; 
 
 

Switch Master/Slave servers

When the master server fails, use the slave server as the master server. The steps are as follows:

1. Ensure that all updates in the relay log have been executed in all slave databases and executed on the slave server.

Stop slave io_thread, use show processlist to check whether the status is Has read all relay log, indicating that the update is complete.

 
 

  
  
mysql>stop slave io_thread;  
  
  
Query OK,0 affected (0.00 sec)  
  
  
mysql>show processlist\G;  
  
  
*************************** 2. row ***************************  
  
  
Id: 2  
  
  
User: system user  
  
  
Host:  
  
  
db: NULL  
  
  
Command: Connect  
  
  
Time: 4757  
  
  
State: Has read all relay log; waiting for the slave I/O thread to update it  
  
  
Info: NULL 
 
 

2. Execute the stop slave and reset master commands on the slave server and reset them to the master database.

 
 

  
  
mysql>stop slave;  
  
  
Query OK,0 affected (0.00 sec)  
  
  
mysql>reset master;  
  
  
Query OK,0 affected (0.00 sec) 
 
 

3. Delete the master.info and relay-log.info files in the new master server database directory, otherwise it will start according to the slave server next restart.

Edit recommendations]