1. View the status of Master
Show master status; Position should not be 0
show Processlist;
//state status should be have sent all binlog to slave; waiting for Binlog to be updated
2. View slave status
show slave status;
//slave_io_running and slave_sql_running states must be Yes
show Processlist;
//There should be two rows with the state value:
Have read all relay logs; Waiting for the slave I/O thread to update it
waiting for Master to send event
3. Error log
MySQL installation directory/usr/local/mysql
MySQL log directory/usr/local/mysql/data/shape, Hostname.err
4. Change Master to
if the Slave from the library is not started, slave_io_running is no.
may be the Master library is the information of master is changed,
View Main Library show master status;
Record the File,position field, assuming as ' mysql-bin.000004 ', 98;
in the From Library execution:
Mysql>stop slave;
Mysql>change Master to master_log_file= ' mysql-bin.000004 ', master_log_pos=98;
Mysql>start slave;
|
5, SET global sql_slave_skip_counter=n;
if the slave_sql_running from the library is no.
The Err file records:
slave:error "Duplicate entry ' 1 ' for key 1" on query ... ..
It is possible that master did not synchronize successfully to slave, but there is already a record in slave. The resulting conflict can be executed from the vault.
set global sql_slave_skip_counter=n;
skip a few steps. Then restart slave.
6. Synchronous Error Handling
found that MySQL slave server often because of some special characters or symbols generated by the UPDATE statement error, the entire synchronization will be stuck there, the original method is only manually go to the wrong machine to execute the following three SQL statements, skip the error.
Mysql>slave stop;
Mysql>set GLOBAL sql_slave_skip_counter=1;
Mysql>slave start;
|
PS: I have repeatedly encountered the synchronization process from the database automatically stop the problem, sometimes simple through slave stop,slave start can be solved. Sometimes slave start automatically stops and then the change master resets the master database information in such a way that it solves the problem.
Description
Slave_io_running: Connect to the main library and read logs from the main library to local, generate local log files
Slave_sql_running: Reads the local log file and executes the SQL command in the log.
From for notes (Wiz)
View MySQL master and slave configuration status and fix slave not start problem
