Slave_ SQL _running: No MySQL synchronization fault solution today I checked the database and found that a MySQL slave was not synchronized with the host. Check the slave status:
Mysql> show slave status \ G
Slave_io_running: Yes
Slave_ SQL _running: No
Last_errno.: 1062
....
Seconds_behind_master: NULL
Cause:
1. The program may write on slave.
2. It may also be caused by transaction rollback after the Server Load balancer instance restarts. solution I:
1. First stop the slave service: slave stop2. check the host status on the master server:
Record the value corresponding to file and position.
Mysql> show Master status;
+ ------------------ + ----------- + -------------- + ------------------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ------------------ + ----------- + -------------- + ------------------ +
| Mysql-bin.000020 | 135617781 |
+ ------------------ + ----------- + -------------- + ------------------ +
1 row in SET (0.00 Sec) 3. perform manual synchronization on the slave server:
Mysql> change master
> Master_host = 'master _ ip ',
> Master_user = 'user ',
> Master_password = 'pwd ',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.20.20 ',
> Master_log_pos = 135617781;
1 row in SET (0.00 Sec)
Mysql> slave start;
1 row in SET (0.00 Sec)
View the slave status again and find:
Slave_io_running: Yes
Slave_ SQL _running: Yes
...
Seconds_behind_master: 0 solution II:
Mysql> slave stop;
Mysql> set global SQL _slave_skip_counter = 1;
Mysql> slave start; usage experience: method 1 is forced to start synchronization from a certain point, and some data that is not synchronized will be lost, the deletion record synchronization on the master server will also have some error messages, which will not affect the usage. method 2 may not be effective. ========================================================== ========================================================== =======] 1, master/Slave cannot be synchronized: Show slave status; error: Error XXX dosn't exist
And show slave status \ G:
Slave_ SQL _running: No
Seconds_behind_master: null solution:
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave; slave will be synchronized with the master later. mainly refer to: slave_io_running: Yes
Slave_ SQL _running: Yes
If seconds_behind_master is 0 or 0, it is already synced with 2. Some optimizations and monitoring are required:
Show full processlist; // view the current MySQL synchronization thread number
Skip-name-resolve // skip DNS name query, which helps speed up connection and Synchronization
Max_connections = 1000 // increase the number of MySQL connections (100 by default)
Max_connect_errors = 100 // increase the number of wrong connections of MySQL. (default: 10) view some log commands.
1. show Master Status \ G;
Here, we mainly check whether the log-bin files are the same.
Show slave status \ G;
Here we mainly look:
Slave_io_running = Yes
Slave_ SQL _running = Yes
If both are yes, the configuration is successful. 2. Enter show processlist \ G on the master;
Mysql> show processlist \ G
* *************************** 1. row ***************************
ID: 2
User: Root
HOST: localhost: 32931
DB: NULL
Command: BINLOG dump
Time: 94
State: has sent all BINLOG to slave; waiting for BINLOG
Be updated
Info: NULL if command: BINLOG dump appears, the configuration is successful. Stop slave # Stop Synchronization
Start slave # Start synchronization and update from the log termination location.
Set SQL _log_bin = 0 | 1 # run on the host. The super permission is required to enable or stop the log. If the log is enabled or disabled at will, the data on the host slave will be inconsistent, resulting in errors.
Set global SQL _slave_skip_counter = N # Run the client to skip several events. It can be executed only when the synchronization process stops when an error occurs.
Reset master # run on the host to clear all logs. This command is the original flush master.
Reset slave # Run from the machine, clear the log synchronization location mark, and regenerate master.info
Although master.info is re-generated, it cannot be used. It is best to restart the MySQL process on the slave machine,
Load table tblname from master # run on the slave machine. The data in the specified table can be re-viewed from the host. Only one table can be read at a time. Due to the time limit of timeout, you need to adjust the timeout time. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Load data from master # Run from the slave machine and read all the data from the host. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Change master to master_def_list # online Change of some host settings. Separate multiple settings with commas (,). For example
Change master
Master_host = 'master2 .mycompany.com ',
Master_user = 'replicase ',
Master_password = 'bigs3cret'
Master_pos_wait () # Run from the slave machine
Show Master Status # Run the host and view the log export information
Show slave hosts # Run the host to check the connected slave machine.
Show slave status (slave)
Show Master logs (master)
Show BINLOG events [IN 'logname'] [from POS] [limit [offset,] rows]
Purge [Master] logs to 'logname'; purge [Master] logs before 'date'
This article is from the "Listen to the future" blog, please be sure to keep this source http://kerry.blog.51cto.com/172631/277414
