Mysql master-slave Database Synchronization

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
-> 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 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 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 statusG;
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.

Question?
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 ', '123 ′);
+ ----------------- +
| Master_pos_wait ('mysql-bin.0000011 ', '000000') |
+ ----------------- +
| 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 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 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 the next restart will follow