| |
Fault description:
Enter from the database
mysql> show slave status \ G;
The results are as follows:
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
...
the reason:
1. The program may be a write operation on the slave
2. After the slave machine may be restarted, things caused rollback
Most are caused by the second
Solution one: Continue to ignore the error after synchronization
The method is applicable to the main data from the library or less, or require data may not be completely uniform, the data requirements are not stringent conditions
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> slave start;
Solution two: Mandatory start synchronization from a certain point
This method will be some loss of data is not synchronized, delete records on a subsequent synchronization master server will have some error messages, it will not affect the use of
View the host to the primary server status:
Record File Position and the corresponding value.
mysql> show master status;
+ ------------------ + ----------- + -------------- + --- --------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ----------- + -------------- + --- --------------- +
| Mysql-bin.000021 | 135617782 | | |
+ ------------------ + ----------- + -------------- + --- --------------- +
1 row in set (0.00 sec)
3. to the slave server to perform manual synchronization:
mysql> change master to
> Master_host = 'master_ip',
> Master_user = 'user',
> Master_password = 'pwd',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.000021',
> Master_log_pos = 135617782;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
See slave status again found:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0
Solution three: Re-shots from fully synchronized
The method is applicable to the case of a larger difference from the main library data, or require a fully integrated data
1. Go to the main library, data backup
mysqldump -u root -p --opt --master-data --single-transaction -B> mysql.bak.sql
2. Backup files to mysql from the library, import from library
mysql> stop slave;
mysql -u root -p < mysql.bak.sql
3. Locate the file and position from the backup files
grep -i "CHANGE MASTER TO" mysql.bak.sql will be similar to the following contents:
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000021', MASTER_LOG_POS = 135617782;
4. Set the synchronization from the library
mysql> change master to
> Master_host = 'master_ip',
> Master_user = 'user',
> Master_password = 'pwd',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.000021',
> Master_log_pos = 135617782;
mysql> slave start;
1 row in set (0.00 sec)
See slave status again found:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0 |
|
|
|