Shulou(Shulou.com)05/31 Report--

This article introduces to you what are the solutions to the three faults of mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Here is a brief summary of the three failures that you often encounter in the use of mysql.

1. The MySQl service cannot be started

In the process of using mysql, we often encounter that the MySQl service cannot be started. Specific error message: Starting MySQL ERROR.The server quit without updating PID file (/ [FAILED] l/mysql/)

There are many online analyses and explanations for such errors. Some analysts just mask the parameter skip-federated, while others think that deleting the mysql-bin.index file can start the service. Or check the MYSQL log, if prompted for parameter configuration errors, it is also easy to cause this error.

Of course, there are many reasons for this error, and there are also many solutions. The faster and effective solution is to back up the database, and then only need the following two steps to quickly restore the database to normal.

Go to the scripts directory under the MYSQL installation location and 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 startup mode)

At this point, you can start MYSQL normally.

(note: the parameters can be adjusted according to the actual installation directory and data directory of mysql)

2. Abnormal Slave_IO_Running status when configuring MYSQL synchronization

When configuring MYSQL synchronization, the cluster machine displays:

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Ensure that the networks of the two MYSQL hosts are interoperable. When this error occurs, the normal YES is not displayed, which is mainly due to an authorization error or mismatch.

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

For example, if the authorized user does not match or the authorized slave IP is incorrect, the Connecting display will appear.

Through the show slave status\ G command, carefully observe the two display states, both of which are normal when they are YES.

3. Abnormal Slave_SQL_Running status when configuring MYSQL synchronization

When configuring MYSQL synchronization, the cluster machine displays:

Slave_IO_Running: YES

Slave_SQL_Running: No

There are generally two reasons for this problem:

a. The program may have written on slave.

b. It may also be caused by the rollback of the transaction after the slave machine is restarted.

Solution 1:

Mysql > slave stop

Mysql > set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; (in the case of start slave, skip an event from the current position.)

Mysql > slave start

Solution II.

First stop the Slave service: slave stop

Go to the primary server to view the host status:

Record the values 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 needs to stop the write operation of master first!

Note:

Slave_IO_Running: connect to the main library and read the log of the main library locally to generate a local log file

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

Ideas to solve the MYSQL problem:

1: first check the error log to find the most recent error. See where the problem is.

2: modify the parameter configuration in my.cnf, but please note that you must remember those changes clearly when you modify them, so that you can make more correct and reasonable changes later.

When configuring parameters, we must attach great importance to the configuration of innodb engine parameters, which is the key to easily lead to errors.

3. Pay attention to the permissions of key directories

4: start the service normally.

There are many reasons why MYSQL cannot be started normally, of course, the specific problems should be analyzed in detail, but the analysis of error log will help us to locate the cause of the problem and solve the problem quickly.

What is the solution to the three faults of mysql is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.