Mysql master-slave database synchronization

Environment:
Main server: centos 5.2 mysql 5.1.35 Source IP: 192.168.1.22
Slave server: centos 5.2 mysql 5.1.35 Source IP: 192.168.1.33
Configuration:
1. Main server
1.1. Create a replication user , with replication slave permissions.
mysql>grant replication slave on *.* to 'repl'@'192.168.1.22′ identified by 'repl';
1.2. Edit my.cnf file
vi /etc/my.cnf
Add
server-id=1
And open the log-bin binary log file
log-bin=mysql-bin
Note: You need to 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, backup the database to be synchronized
mysqldump test > test.sql
1.7, unlock
mysql>unlock tables;

2. From the server
2.1, edit the my.cnf file
vi /etc/my.cnf
Add
server-id=2
Note: You need to remove the default server-id=1
2.2 , start the slave database
mysqld_safe –user=mysql &
2.3, set the slave database accordingly
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 slave thread
mysql>start slave;
Execute the show processlist command to display the following processes:
mysql>show processlistG
****************************** 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 means that the slave has connected to the master and started to accept and execute logs
2.5, check 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 whether the configuration is correct
Execute on the slave server
show slave statusG;
Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If the above two lines are both Yes means the configuration is successful

Test
1. Create the user table in the main server test database
mysql>use test;
mysql>create table user(id int);
2. View the user table in the slave server
mysql>use test;
mysql> show tables like 'user';
+————————-+
| Tables_in_test(user) |
+————————-+
| user |
+———— ————-+
1 row in set (0.00 sec)
indicates that the master-slave data synchronization is successful.

Problem?
1. Appears when checking slave status from 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)
It means that the server-id in my.cnf in the slave server has the same.
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=The name of the database that needs to be backed up, you can write multiple lines
#binlog-ignore-db=The name of the database that does not need to be backed up, you can write multiple lines
Slave server my .cnf
# replicate-do-db=test The name of the database that needs to be backed up
# replicate-ignore-db=mysql The ignored database
# master-connect-retry=60 If the slave server finds that the master server is disconnected, the time difference between reconnection (Seconds)
The following settings can also be modified directly in the my.cnf configuration file
log-bin=mysql-bin
master-host=192.168.1.22
master-user=repl
master-password=repl
master-port=3306

Master-slave server synchronization maintenance
Due to various reasons, the master-slave data is inconsistent. When the load is low, manual synchronization is performed.
Executed on the master server

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| | |
+——————+————- +————–+——————+
Execute on the slave server
First get 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 the synchronization is completed, perform unlocking on the main server
mysql>unlock tables;

Switch the master-slave server

When the master server fails, the slave server can be used as the master server. The steps are as follows:
1. Ensure that all All updates in the relay log have been executed from the database. Execute
stop slave io_thread in the slave server. Check with show processlist to see if the status is Has read all relay log, which means the update is completed.
mysql>stop slave io_thread;
Query OK,0 affected (0.00 sec)
mysql>show processlistG;
**************************** 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 to reset 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 restart next time It will also be started from the server.