MySQL master-Slave synchronization error troubleshooting results and Repair process: slave_sql_running:no

Cause investigation:

Receive a large number of email alerts Presumably, ask colleagues what happened? Colleague login from the library view, found the following error prompt, indicating that synchronization with the main library failed, always stuck in the place, watching him get two hours, more and more problems, to solve a recovery calm a two minutes and not .... So the alarm of the mail is another letter ... So he asked me for help. Log in from the Vault and discover:

One of the error content from the library is:

1Mysql>show slave status\g;2 3 Slave_io_running:yes4 Slave_sql_running:no5 6Last_errno:10087 8Last_error:error'Can'T drop database'LCP'; Database doesn'T exist'On query. Default Database:'LCP'. Query:'drop Database LCP'

Failed to create library,

View Logs

1 mysql> SHOW  '%log%'; 2 3 | Log_error                               |/application/mysql/data/db03.err log path does not move, default   45 | log_warnings                            1 Fortunately enable the log ...

Cut a window to view the log:

Error report found, unable to synchronize with main Library, previous report LCP library already exists. Wondering what you do with my name as a library name ... A hearty batch of meals. (later learned to feel my name has a deterrent, frighten frighten server, laugh and cry ...) ）

Anyway

I looked at his history operation record, want to create "LCP" library but found that the library already exists, want to delete "LCP" library, but found that the system error this library does not exist. Then I was very depressed to ask him what you did to the server!!!

Really not afraid of the team-mates, afraid of .... Haha, of course, open a joke, wrong is the process of growth, I like my teammates, he often give me the space to grow. (Ps: Do not imitate easily!!) Especially the production environment!!! ）

1  160524 Ten: -: A[Warning] Slave:can'T CREATE database'Min'; database exists error_code:10072  160524 Ten: -: A[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with"SLAVE START". We stopped at log'Mysql-b3  inch.000005'Position 1074  160524  One: -: -[ERROR] Error Reading Packet fromServer:lost connection to MySQL server during query (server_errno= -)

So I went up to the wrong log and found a similar hint: min Library already exists, cannot complete creation. What seemed to come to mind at the time. Asked, did you create a min library on two MySQL servers at the same time? The pig-like teammate gave me the answer that was: "Yes!" ”！！！！！ Well, forgive him for being a novice. Since the problem found a way to naturally simple, fortunately, one from the library, will not cause great impact, so choose the simplest way:

Move the binglog pointer down one position. Solution:

mysql> slave stop;
Query OK, 0 rows affected (0.01 sec)

Mysql> set GLOBAL sql_slave_skip_counter=1;
Query OK, 0 rows Affected (0.00 sec)

mysql> slave start;
Query OK, 0 rows Affected (0.00 sec)

Mysql> show Slave status\g;

Slave_io_running:yes
Slave_sql_running:yes

last_io_errno:0
Last_io_error:
last_sql_errno:0

At this point to solve the completion, although using the method to solve a bit less aboveboard, but the wrong way of thinking must be clear, although not the production environment, if the production environment beyond 5 minutes of the row is not a qualified ops should do things, here to send a blog post also to save a file, by the way to help the people in need.

MySQL master-Slave synchronization error troubleshooting results and Repair process: slave_sql_running:no