MySQL Three troubleshooting summary

MySQL is often encountered in the process of three failures, here a summary.

1. mysql service cannot start

In the process of using MySQL, we often encounter the MySQL service can not start, the specific error message: Starting MySQL error. The server quit without updating PID file (/[failed]l/mysql/)

To such a mistake, the analysis of the online interpretation of a lot, someone analyzed the skip-federated This parameter screen can be, also some people think that delete mysql-bin.index this file can start the service. Or check the MySQL log, if prompted with a parameter configuration error, it is also easy to cause this error.

Of course, there are many reasons for this error, there are many solutions, a relatively fast and effective solution is to do a good job of database backup, and then just two steps, you can quickly restore the database to normal.

Enter the scripts directory under MySQL installation location to perform

./mysql_install_db--defaults-file=/etc/my.cnf--basedir=/usr/local/mysql--datadir=/var/mysql/data--user=mysql (Initialize MySQL database)

Then execute./mysqld_safe--user=mysql & (Safe boot mode)

This is the time to start MySQL normally.

2. Slave_io_running state exception when configuring MySQL synchronization

When configuring MySQL synchronization, Cong displays:

Slave_io_running:connecting

Slave_sql_running:yes

When this error occurs, the normal state of Yes is not displayed, mainly because of an error or mismatch in authorization. For example, authorized users do not match or authorize the slave IP is not correct, will appear connecting this display.

With the show slave status\g command, it is OK to observe the two display states carefully.

3. Slave_sql_running state exception when configuring MySQL synchronization

When configuring MySQL synchronization, Cong displays:

Slave_io_running:yes

Slave_sql_running:no

There are generally two reasons for this problem:

A. The program may have been written on slave

B. It may also be the result of a transaction rollback after the slave machine is reset.

Solution One:

mysql> slave stop;

Mysql> set GLOBAL sql_slave_skip_counter=1; ( at start slave, an event is skipped from the current position.) )

mysql> slave start;

Solution II,

First stop the slave service: Slave stop

To view host status on the primary server:

Record the value corresponding to file and position

Enter Master

Mysql> Show master status;

+----------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+----------------------+----------+--------------+------------------+

| localhost-bin.000009 |     33622483 |  Weichat | Mysql,test |

+----------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Then perform a manual synchronization on the slave server:

Mysql> Change Master to

> master_host= ' master_ip ',

> master_user= ' user ',

> master_password= ' pwd ',

> master_port=3306,

> master_log_file=localhost-bin.000009 ',

> master_log_pos=326;

1 row in Set (0.00 sec)

mysql> slave start;

1 row in Set (0.00 sec)

Mysql> Show Slave Status\g

1. Row ***************************

........

master_log_file:localhost-bin.000009

read_master_log_pos:326

relay_log_file:localhost-relay-bin.000027

relay_log_pos:1014014

relay_master_log_file:localhost-bin.000009

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:weichat

Manual synchronization requires you to stop the master write operation first!

Note:

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.

Ideas for solving MySQL problems:

1: Check the error log first to find the most recent error. See where the problem occurs.

2: Modify the configuration of the parameters in the MY.CNF, but please note that the changes must be made aware of those places modified, convenient after making more correct and reasonable changes.

When configuring parameters, it is important to attach great importance to the configuration of InnoDB engine parameters, which is very easy to lead to errors.

3: Normal start service.

There are many reasons why you can not start MySQL properly, but the specific problem is specific analysis, but the analysis error log will help us locate the cause of the problem, so as to quickly resolve the problem.

This article is from the "Hollows Jie Sun" blog, be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1439199