MySQL故障排查实战指南：解决常见问题与疑难诊断

MySQL

需积分: 11 190 浏览量更新于2024-07-18 收藏 7.18MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

《MySQL故障排查指南》是由Svetlana Smirnova撰写的一本专业书籍，作者在MySQL支持团队担任主要技术支持工程师多年，专注于解决用户在使用过程中遇到的问题。这本书于2012年出版，旨在帮助读者深入了解MySQL服务器的工作原理，并提供实用的方法来诊断和修复常见问题。书中指出，尽管存在大量的资源，如众多的技术书籍、博客文章和网页，它们详细解释了MySQL的不同方面，但往往缺乏系统地介绍如何识别服务器故障和不正常行为的技巧。作者观察到，这些资料通常侧重于正常操作，而忽略了在实际应用中遇到问题时所需的具体解决策略。本书的主要内容包括但不限于以下几个方面： 1. **MySQL基础知识回顾**：对于初次接触MySQL或需要巩固基础知识的读者，章节会先回顾数据库的基本概念、架构和查询语言，以便理解故障出现的背景。 2. **故障诊断方法**：书中将深入探讨各种诊断工具和技术，如日志分析（error logs, slow query logs）、性能监控（EXPLAIN, SHOW STATUS）以及使用SQL调试器进行跟踪，这些都是识别问题根源的关键步骤。 3. **问题分类与解决策略**：根据问题类型（如连接错误、性能瓶颈、数据一致性等），作者会提供一套逻辑清晰的故障排除流程，指导读者一步步定位问题。 4. **案例分析**：书中包含多个实际案例，展示了作者在工作中遇到并解决的各种疑难杂症，有助于读者理解和应用到实际场景中。 5. **最佳实践和预防措施**：除了故障处理，书中还会分享防止问题发生的建议，比如定期备份、优化配置参数、维护数据库结构等。 6. **在线资源与社区支持**：作者还会推荐相关的在线资源和MySQL社区，鼓励读者在遇到问题时寻求更广泛的帮助。《MySQL故障排查指南》不仅是一本技术手册，也是一份实践者的指南，它为MySQL使用者提供了实用的故障诊断和解决策略，使他们能够更有效地管理数据库系统，提高工作效率。无论是经验丰富的开发者还是刚入门的学习者，都可以从中受益匪浅。

资源详情

资源推荐

CHAPTER 1

Basics

When troubleshooting, you can generally save time by starting with the simplest pos-

sible causes and working your way to more complicated ones. I work dozens of trouble

tickets at MySQL Support every month. For most of them, we start from trivial requests

for information, and the final resolution may—as we’ll see in some examples—be trivial

as well, but sometimes we have quite an adventure in between. So it always pays to

start with the basics.

The typical symptoms of a basic problem are running a query and getting unexpected

results. The problem could manifest itself as results that are clearly wrong, getting no

results back when you know there are matching rows, or odd behavior in the applica-

tion. In short, this section depends on you having a good idea of what your application

should be doing and what the query results should look like. Cases in which the source

of wrong behavior is not so clear will be discussed later in this book.

We will always return to these basics, even with the trickiest errors or in situations

when you would not know what caused the wrong behavior in your application. This

process, which we’ll discuss in depth in “Localizing the Problem (Minimizing the Test

Case)” on page 205, can also be called creating a minimal test case.

Incorrect Syntax

This sounds absolutely trivial, but still can be tricky to find. I recommend you approach

the possibility of incorrect SQL syntax very rigorously, like any other possible problem.

An error such as the following is easy to see:

SELECT * FRO t1 WHERE f1 IN (1,2,1);

In this case, it is clear that the user just forgot to type an “m”, and the error message

clearly reports this (I have broken the output lines to fit the page):

mysql> SELECT * FRO t1 WHERE f1 IN (1,2,1);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'FRO

t1 WHERE f1 IN (1,2,1)' at line 1

Unfortunately, not all syntax errors are so trivial. I once worked on a trouble ticket

concerning a query like this:

SELECT id FROM t1 WHERE accessible=1;

The problem was a migration issue; the query worked fine in version 5.0 but stopped

working in version 5.1. The problem was that, in version 5.1, “accessible” is a reserved

word. We added quotes (these can be backticks or double quotes, depending on your

SQL mode), and the query started working again:

SELECT `id` FROM `t1` WHERE `accessible`=1;

The actual query looked a lot more complicated, with a large JOIN and a complex

WHERE condition. So the simple error was hard to pick out among all the distractions.

Our first task was to reduce the complex query to the simple one-line SELECT as just

shown, which is an example of a minimal test case. Once we realized that the one-liner

had the same bug as the big, original query, we quickly realized that the programmer

had simply stumbled over a reserved word.

■ The first lesson is to check your query for syntax errors as the first troubleshooting

step.

But what do you do if you don’t know the query? For example, suppose the query was

built by an application. Even more fun is in store when it’s a third-party library that

dynamically builds queries.

Let’s consider this PHP code:

$query = 'SELECT * FROM t4 WHERE f1 IN(';

for ($i = 1; $i < 101; $i ++)

$query .= "'row$i,";

$query = rtrim($query, ',');

$query .= ')';

$result = mysql_query($query);

Looking at the script, it is not easy to see where the error is. Fortunately, we can alter

the code to print the query using an output function. In the case of PHP, this can be

the echo operator. So we modify the code as follows:

…

echo $query;

//$result = mysql_query($query);

Once the program shows us the actual query it’s trying to submit, the problem jumps

right out:

$ php ex1.php

SELECT * FROM t4 WHERE f1 IN('row1,'row2,'row3,'row4,'row5,'row6,'row7,'row8,

'row9,'row10,'row11, 'row12,'row13,'row14,'row15,'row16,'row17,'row18,'row19,'row20)

If you still can’t find the error, try running this query in the MySQL command-line

client:

2 | Chapter 1: Basics

mysql> SELECT * FROM t4 WHERE f1 IN('row1,'row2,'row3,'row4,'row5,'row6,'row7,'row8,

'row9,'row10,'row11,'row12,'row13,'row14,'row15,'row16,'row17,'row18,'row19,'row20);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'row2,

'row3,'row4,'row5,'row6,'row7,'row8,'row9,'row10,'row11, 'row12,'row13,'row' at

line 1

The problem is that the closing apostrophe is missing from each row. Going back to

the PHP code, I have to change:

$query .= "'row$i,";

to the following:

$query .= "'row$i',";

■ An important debugging technique, therefore, consists of this: always try to view

the query exactly as the MySQL server receives it. Don’t debug only application

code; get the query!

Unfortunately, you can’t always use output functions. One possible reason, which I

mentioned before, is that you’re using a third-party library written in a compiled

language to generate the SQL. Your application might also be using high-level abstrac-

tions, such as libraries that offer a CRUD (create, read, update, delete) interface. Or

you might be in a production environment where you don’t want users to be able to

see the query while you are testing particular queries with specific parameters. In such

cases, check the MySQL general query log. Let’s see how it works using a new example.

This is the PHP application where the problem exists:

private function create_query($columns, $table)

{

$query = "insert into $table set ";

foreach ($columns as $column) {

$query .= $column['column_name'] . '=';

$query .= $this->generate_for($column);

$query .= ', ';

}

return rtrim($query, ',') . ';';

}

private function generate_for($column)

{

switch ($column['data_type']) {

case 'int':

return rand();

case 'varchar':

case 'text':

return "'" . str_pad(md5(rand()), rand(1,$column['character_maximum_length']),

md5(rand()), STR_PAD_BOTH) . "'";

default:

return "''";

}

Incorrect Syntax | 3

This code updates a table defined in Example 1-1.

Example 1-1. Sample table of common troubleshooting situations

CREATE TABLE items(

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

short_description VARCHAR(255),

description TEXT,

example TEXT,

explanation TEXT,

additional TEXT

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now is time to start using the general query log. This log contains every single query

the MySQL server receives. Many production applications don’t want to use it on a

day-to-day basis, because it grows extremely fast during a high load, and writing to it

can take up MySQL server resources that are needed for more important purposes.

Starting with version 5.1, you can turn the general query log on temporarily to record

just the query you need:

mysql> SET GLOBAL general_log='on';

Query OK, 0 rows affected (0.00 sec)

You can also log into a table, which lets you easily sort logfile entries because you can

query a log table like any other MySQL table:

mysql> SET GLOBAL log_output='table';

Query OK, 0 rows affected (0.00 sec)

Now let’s run the application. After an iteration that executes the problem code, query

the table containing the general log to find the problem query:

mysql> SELECT * FROM mysql.general_log\G

*************************** 1. row ***************************

event_time: 2011-07-13 02:54:37

user_host: root[root] @ localhost []

thread_id: 27515

server_id: 60

command_type: Connect

argument: root@localhost on collaborate2011

*************************** 2. row ***************************

event_time: 2011-07-13 02:54:37

user_host: root[root] @ localhost []

thread_id: 27515

server_id: 60

command_type: Query

argument: INSERT INTO items SET id=1908908263,

short_description='8786db20e5ada6cece1306d44436104c',

description='fc84e1dc075bca3fce13a95c41409764',

example='e4e385c3952c1b5d880078277c711c41',

explanation='ba0afe3fb0e7f5df1f2ed3f2303072fb',

additional='2208b81f320e0d704c11f167b597be85',

*************************** 3. row ***************************

event_time: 2011-07-13 02:54:37

user_host: root[root] @ localhost []

4 | Chapter 1: Basics

thread_id: 27515

server_id: 60

command_type: Quit

argument:

We are interested in the second row and query:

INSERT INTO items SET id=1908908263,

short_description='8786db20e5ada6cece1306d44436104c',

description='fc84e1dc075bca3fce13a95c41409764',

example='e4e385c3952c1b5d880078277c711c41',

explanation='ba0afe3fb0e7f5df1f2ed3f2303072fb',

additional='2208b81f320e0d704c11f167b597be85',

The error again is trivial: a superfluous comma at the end of the query. The problem

was generated in this part of the PHP code:

$query .= ', ';

}

return rtrim($query, ',') . ';';

The rtrim function would work if the string actually ended with a comma because it

could remove the trailing comma. But the line actually ends with a space character. So

rtrim does not remove anything.

Now that we have the query that caused the error in our application, we can turn off

the general query log:

mysql> SET GLOBAL general_log='off';

Query OK, 0 rows affected (0.08 sec)

In this section, we learned a few important things:

• Incorrect syntax can be the source of real-life problems.

• You should test exactly the same query that the MySQL server gets.

• Programming language output functions and the general query log can help you

quickly find the query that the application sends to the MySQL server.

Wrong Results from a SELECT

This is another frequent problem reported by users of an application who don’t see the

updates they made, see them in the wrong order, or see something they don’t expect.

There are two main reasons for getting wrong results: something is wrong with your

SELECT query, or the data in database differs from what you expect. I’ll start with the

first case.

When I went over examples for this section, I had to either show some real-life examples

or write my own toy cases. The real-life examples can be overwhelmingly large, but the

toy cases wouldn’t be much help to you, because nobody writes such code. So I’ve

chosen to use some typical real-life examples, but simplified them dramatically.

Wrong Results from a SELECT | 5

剩余257页未读，继续阅读

nn123456789

粉丝: 14
资源: 128

MySQL故障排查实战指南：解决常见问题与疑难诊断

MySQL Troubleshooting

MySQLTroubleshooting.pdf 英文原版

can't connect to local mysql server through socket '/tmp/mysql.socket'

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/omm/proj/mysql/mysql.sock' (2)

Can't connect to MySQL server on 'localhost:3306' (10061)

mysql1045 - access denied for user

2003-can‘t connect to MySQL server on“127.0.0.1”

Starting MySQL . ERROR! The server quit without updating PID file (/usr/local/mysql/data/macdeMacBook-Pro.local.pid).

mysql error:event Scheduler: Unable to schedule event

troubleshooting是什么意思

unable to verify your captcha response. please visit https://docs.github.com/articles/troubleshooting-connectivity-problems/#troubleshooting-the-captcha for troubleshooting information.

Failed to start mysqld.service: Unit mysqld.service not found.

--skip-grant-tables

matlab2015b报错troubleshooting

troubleshooting ip routing protocols

2059 - Authentication plugin 'caching sha2 password' cannot be loaded:

Unable to verify your captcha response. Please visit https://docs.github.com/articles/troubleshooting-connectivity-problems/#troubleshooting-the-captcha for troubleshooting information.

No package zabbix-server-mysql available. No package zabbix-web-mysql available. No package zabbix-agent available.

Linux服务搭建参考文献

https://github.com/git-lfs/git-lfs/wiki/Troubleshooting

最新资源