Mysql master-slave Database Synchronization (centos)

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: 1. master server 1.1, create a copy user, has the replication slave permission. Mysql> grant replication slave on *. * to 'repl' @ '192. 168.1.22 'identified by 'repl'; 192. 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 and start 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 mysqldump test> test to be synchronized. SQL 1.7, unlock mysql> unlock tables; 2. Slave Server 2.1, edit my. cnf file vi/etc/my. cnf
Add
Server-id = 2 Note: remove the default server-id = 1 from 2.2, start mysql from the database mysqld_safe -- user = mysql & 2.3, and set the slave database accordingly. 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; execute 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 starts to accept and execute log 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 whether the configuration is correct and execute show slave status \ G on the slave server;
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 the user table mysql> use test in the master server test database;
Mysql> create table user (id int); 2. view the user table mysql> use test on the slave server;
Mysql> show tables like 'user ';
+ ---------------------- +
| Tables_in_test (user) |
+ ---------------------- +
| User |
+ ---------------------- +
1 row in set (0.00 sec) indicates that the Master/Slave Data is successfully synchronized. Question? 1. the slave I/O thread stops because master and slave have equal MySQL server ids appear when you view The slave status from The database; 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) instruction side from the server my. the server-id in cnf has the same. Solution: Modify the server-id in my. cnf and restart the database service. 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 lines 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 = test name of the database to be backed up
# Replicate-ignore-db = databases ignored by mysql
# Master-connect-retry = 60 if the master server is disconnected from the server, you can directly modify the following settings for the time difference (in seconds) of the reconnection. cnf configuration file log-bin = mysql-bin
Master-host = 192.168.1.22master-user = replmaster-password = repl
Master-port = 3306

Synchronization and maintenance of the master and slave servers may result in inconsistent master and slave data due to various reasons. manual synchronization is performed when the load is low. run mysql> flush tables with read lock; Query OK on the master server, rows affected (0.01 sec) mysql> show master status; + -------------------- + ---------- + ------------ + -------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.0000011 | 260 |
+ ------------------ + ---------- + -------------- + ------------------ + Run the command on the slave server to obtain the binary file name and offset of the current master server, run the command to synchronize mysql> select master_pos_wait ('mysql-bin.0000011', '123') from the slave server to the master server ');
+ -------------------------------------------------- +
| Master_pos_wait ('mysql-bin.0000011 ', '123') |
+ -------------------------------------------------- +
| 0 |
+ -------------------------------------------------- +
1 row in set (0.01 sec) after synchronization is completed, unlock mysql> unlock tables on the master server. switch between the master and slave servers when the master server fails, the slave server can be used as the master server. the steps are as follows: 1. Ensure that all slave databases have performed all updates in the relay log, execute stop slave io_thread in the slave server, and check with show processlist, 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: NULL2. 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.