Mysql three troubleshooting summary

Mysql often encountered during the three failures, in this summary.

MySQl service failed to start

We are in the process of using mysql, often encounter MySQl service can not start, the specific error message: Starting MySQL ERROR.The server quit without updating PID file (/ [FAILED] l / mysql /)

On such a mistake, there are many online analytical explanation, some people analyze the skip-federated this parameter can be masked, and some people think that you can delete the mysql-bin.index this file can start the service. Or check the MYSQL logs, if prompted to configure the parameters of errors, it is easy to cause this error.

Of course, there are many reasons for this error. There are many solutions to this problem. A quick and effective solution is to back up the database and then quickly restore the database as long as the following two steps are taken.

Into the MYSQL installation location under the scripts directory, execute

//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 --datadir = / var / mysql / data & (safe start mode)

This time you can start MYSQL normal.

(Note: parameters can be based on the mysql actual installation directory and data directory to adjust)

2, configure MYSQL synchronization Slave_IO_Running abnormal state

When configuring MYSQL synchronization, the cluster machine shows:

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Make sure both MYSQL host networks are interoperable. When this error occurs, the normal status is not displayed YES, the problem is mainly due to authorization errors or mismatch.

mysql> grant replication slave on *. * to test@192.168.199.119 identified by 'test @ 123456';

Such as authorized users do not match or authorized from the machine IP wrong, will appear Connecting this display.

Through the show slave status \ G command, carefully observe the two display status, all YES is normal.

3, configure MYSQL Based synchronization Slave_SQL_Running abnormal state

When configuring MYSQL synchronization, the cluster machine shows:

Slave_IO_Running: YES

Slave_SQL_Running: No

There are two general reasons for this problem:

A. The program may be written on the slave

B. It may also be slave machine after the restart, the transaction rollback caused.

One solution:

mysql> slave stop;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; (At start slave, skips an event from the current position.)

mysql> slave start;

Solution two,

First stop the Slave service: slave stop

View the host status on the main server:

Record the value of File and Position

Into the 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 need to stop the master write!

Note:

Slave_IO_Running: connect to the main library, and read the main library log locally, generate local log files

Slave_SQL_Running: read the local log file, and execute the SQL command in the log.

MYSQL problem solving ideas:

1: First check the error log to find the most recent error. See where the problem occurred.

2: Modify my.cnf parameters in the configuration, but please note that when the changes must remember that those changes, convenient and make more correct and reasonable changes.

In the configuration parameters must pay great attention to innodb engine parameter configuration, it is extremely easy to lead to the wrong key.

3, the key directory permissions should pay attention

4: Normal start service.

There are many reasons why you can not start MYSQL. Of course, you have to analyze the specific problems, but analyzing the error logs helps us to locate the cause of the problem and solve the problem quickly.