Check the status of the mysql Master/slave configuration and fix the problem that slave does not start. mysqlslave

Check the status of the mysql Master/slave configuration and fix the problem that slave does not start. mysqlslave

1. view the master Status
Mysql> show master status; // Position should not be 0
Mysql> show processlist;
// The state should be Has sent all binlog to slave; waiting for binlog to be updated

2. view the slave status
Mysql> show slave status;
// The Slave_IO_Running and Slave_ SQL _Running statuses must be Yes
Show processlist;
// There should be two rows of state value:
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

3. error logs
MySQL installation directory/usr/local/mysql
MySQL Log directory/usr/local/mysql/data/, for example, Hostname. err

4. Change master
If the Slave database Slave is not started, Slave_IO_Running is NO.
It may be that the master information of the master database has changed,
View the master database show master status;
Record the File and Position fields, for example, 'mysql-bin.000004 ', 98;
Run the following command in the slave database:

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 of the slave database is NO.
Record in the Err file:
Slave: Error "Duplicate entry '1' for key 1" on query .....
It may be that the master has not successfully synchronized to the slave, but the slave has records. The resulting conflicts can be executed on the slave database.
Mysql> set global SQL _slave_skip_counter = n;
Skip a few steps. Restart slave.

6. synchronization error handling
It is found that the mysql slave server often reports an error in the update statement generated by some special characters or symbols, so the entire synchronization will also be stuck there, the original method was to manually execute the following three SQL statements on the machine with the error, and skip the error.

Mysql> slave stop;
Mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;
Mysql> slave start;
PS: I have encountered the problem that the synchronization process from the database automatically stops. Sometimes it can be solved simply by using slave stop and slave start. Sometimes slave start will automatically stop after it is started. At this time, the change master is used to reset the master database information to solve the problem.

Note:

Slave_IO_Running: connects to the master database and reads logs from the master database to the local computer to generate local log files.

Slave_ SQL _Running: reads the local log file and executes the SQL commands in the log.

 

Source: http://blog.chinaunix.net/uid-24426415-id-77316.html