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 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. On the 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 the slave Database

Mysqld_safe -- user = mysql &

2.3 set the slave database to 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

It indicates that the slave has been connected to the master, and the Server Load balancer starts to accept and execute log 2.5 and check the Server Load balancer thread status. mysql> show Server Load balancer 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: If Yes is the same as the preceding two rows, the configuration is successfully tested. 1. Create the user table mysql> use test on 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 the slave server with the master server.

Mysql> select master_pos_wait ('mysql-bin.0000011 ', '123 ');
+ -------------------------------------------------- +
| Master_pos_wait ('mysql-bin.0000011 ', '123') |
+ -------------------------------------------------- +
| 0 |
+ -------------------------------------------------- +
1 row in set (0.01 sec)

After synchronization, unlock mysql> unlock tables on the master 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 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.