下载  >  数据库  >  MySQL  > mysql_troubleshooting

mysql_troubleshooting 评分

When troubleshooting, you can generally save time by starting with the simplest possible 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
MySQL Troubleshooting Sveta smirnova O REILLY Beijing· Cambridge. Farnham·Koln· Sebastopol· Tokyo MySQL Troubleshooting by Sveta smirnova Copyright@ 2012 Sveta Smirnova. All rights reserved Printed in the United States of america Published by O Reilly Media, Inc, 1005 Gravenstein Highway North, Sebastopol, CA 95472 O'Reilly books may be purchased for educational, business, or sales promotional use. Online editions arealsoavailableformosttitles(http://my.safaribooksonline.com).Formoreinformationcontactour corporate/institutionalsalesdepartment:(800)998-9938orcorporate@oreilly.com Editor: Andy Oram Indexer: Angela Howard Production Editors: Jasmine Perez and Teresa Elsey Cover Designer: Karen montgomery Copyeditor: Genevieve d'Entremont Interior Designer: David Futato Proofreader: Jasmine perez Illustrator: Robert romano February 2012: First Edition Revision History for the First Edition: 201202-03 clease Seehttporeillycom/catalog/errata.cspisbn=9781449312008forreleasedetails Nutshell Handbook, the Nutshell Handbook logo, and the O'Reilly logo are registered trademarks of O'Reilly Media, Inc. MySQL Troubleshooting, the image of a Malayan badger, and related trade dress are trademarks of o'reilly media, Inc Many of the designations used by manufacturers and sellers to distinguish their products are claimed as rademarks. Where those designations appear in this book, and O Reilly Media, Inc, was aware of a trademark claim, the designations have been printed in caps or initial caps While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information con tained herein ISBN:978-1-449-31200-8 1328280258 Table of contents Foreword Preface 1. Basics Incorrect syntax Wrong Results from a SEleCt When the Problem May Have Been a Previous Update Getting Information About a Query 16 Tracing Back Errors in Data eries 24 Tuning a Query with Information from EXPLAIN 24 Table Tuning and Indexes 30 When to Stop optimizing 34 Effects of options 35 Queries That Modify data 36 No Silver bullet 39 When the Server does not answer Issues with Solutions Specific to Storage Engines 44 ISAM Corruption 45 InnoDB corruption 47 Permission issues 2. You Are not Alone: Concurrency Issues..................53 Locks and transactions 54 54 Table locks Row Locks 57 Transaction 63 Hidden Q eries 63 padlocks 69 Implicit commits Metadata Locking 73 Metadata Locking Versus the Old Model 75 How Concurrency Affects Performance 76 Monitoring InnoDB Transactions for Concurrency Problems Monitoring Other Resources for Concurrency Problems 78 Other Locking Issues 79 Replication and concurrency Statement-Based Replication Issues Mixing transactional and nontransactional tables 91 Issues on the Slave Effectively Using MySQL Troubleshooting Tools 94 SHOW PROCESSLIST and the INFORMATION SCHEMA PROCESSLIST Table 95 SHOW ENGINE INNODB STATUS and innoDB monitors INFORMATION SCHEMA Tables PERFORMANCE SCHEMA Tables 100 og Files 102 3. Effects of Server Options........................ 107 Service options 108 Variables That are Supposed to Change the server Behavior 111 Options That Limit hardware resources 112 Using the --no-detaults Option Performance Options 114 Haste makes waste 114 The set Statement 115 How to Check whether Changes had an Effect 115 Descriptions of variables Options that Affect server and client behavior 117 Performance-Related Options 132 Calculating safe values for Options 142 4. MySQL's Environment 147 Physical hardware Limits 147 RAM 147 Processors and Their Cores 149 Disk i/o 149 Network Bandwidth 151 Example of the Effect of latencies 151 Operating System Limits 152 Effects of other Software 153 iv Table of Contents 5. Troubleshooting Replication ,,155 Displaying slave statu 157 Problems with the I/O Thread 159 Problems with the SQL Thread 166 When Data Is Different on the Master and Slave 167 Circular replication and nonreplication Writes on the slave 168 Incomplete or Altered SQL Statements 170 Different errors on the master and slave 170 Configuration 171 When the Slave Lags Far Behind the master 171 6. Troubleshooting Techniques and Tools The o Slow Query Log 174 Tools That Can be cl The MySQL Command-Line Interface 177 Effects of the environment 181 Sandb 181 Errors and logs 185 Error Information, Again 185 Crash 186 Information-Gathering tools 189 Information schema 189 InnoDB Information Schema tables 191 Innodb monitors 192 Performance Schema 201 SHOW IGLOBALI STATUS 203 Localizing the problem (minimizing the Test case 205 General Steps to Take in Troubleshooting 206 Testing Methods 208 Try the query in a Newer Version 209 Check for Known Bugs 209 Workarounds 210 pecial esting 211 Benchmarking tools 211 ypS 215 MySQL Test Framework 216 Maintenance tools 218 7. Best practices 221 Backups 221 Planning Backups 222 Types of backups ) Table of Contents v Tools Gathering the Information You Need 224 What does it all mean? 225 Testing 225 Prevention 226 Privileges Environment 226 Think about it! 227 Appendix: Information resources....................... 229 Index 233 I Table of Contents Foreword Solving a system problem can be one of the most frustrating experiences a systems expert can encounter. Repair of the problem or the execution of the solution is typically the easy part. Diagnosing the cause of the problem is the real challenge Experienced administrators have learned--some by doing and others by trial and error that the best way to solve a problem is to use a standardized process for defining the problem, forming a list of possible causes, and then testing each until the solution is found. This may sound naive, but it generally works(although it is not sufficient for specialIzed systems, MySQL is a specialized, complex, mature, and powerful database system capable of meeting the needs of a vast number of organizations. MySQL is also very easy to install and configure. Indeed, most default installations do not need to be configured or tuned at all. However, MySQL is also a system with many layers of functionality that can sometimes go awry and produce a warning or error Sometimes the warning or error is specific enough (or has been seen and documented enough)that a solution can be implemented immediately. Other times, and thankfull infrequently, a problem is encountered that does not have a known solution or is spe- cific to your application, database, or environment Finding a solution for such a warn ing, error, or other problem with MySQL can be a daunting task When encountering such an issue, database professionals typically search various resources looking for clues or at least documentation that describes a similar problem and solution. Most will find that there are simply too many references to problems that are somewhat similar or that contain suggested solutions that simply don' t work or don t apply to your situation A fine example of this is searching the Internet using the error message as search criteria More often than not you will find all manner of hits, varying from archived email logs to blogs and similar commentary that may or may not refer to the error message. This often leads to a lot of wasted time and frustration. What is needed is a reference guide for how to solve problems with MySQL not only does this book fulfill that need, it also establishes a protocol for solving problems that can be applied to almost any system. The methods presented are well structured, thorough, and repeatable. Combined with real-world examples, the text becomes a watershed work that defines the proper way to diagnose and repair MysQ Sveta uses her firsthand experiences and in-depth knowledge of mysQl and diagnostic skills to teach the reader fundamental skills to diagnose and repair almost any problem you may encounter with MySQL--making this book a must have for any MySQL professional I consider myself a MySQL expert, and while my skills are backed by much experience, I wont claim to know everything there is to know about MySQL. After reading this book, I can say that I've broadened my skills even further. If a seasoned professional like myself can benefit from reading this book, every mysQL user should read this ok. More to the point, it should be considered required reading for all MySQL database administrators, consultants, and database developers Dr Charles Bell, Oracle Corporation Author of Mysql High Availability(O'Reilly) and Expert MysQL (Apress) ⅶ ii Foreword

...展开详情
所需积分/C币:9 上传时间:2013-04-15 资源大小:7.1MB
举报 举报 收藏 收藏
分享 分享
navicat8_mysql_cs.exe和navicat8_mysql_ct.exe(简体与繁体 含注册机)

集合简体与繁体的Navicat强大的MySQL数据库管理和开发工具。

立即下载
MYSQL软件navicat8lite_mysql_cs

MYSQLMYSQL软件navicat8lite_mysql_cs

立即下载
mysql 客户端navicat8_mysql_cs

mysql 客户端navicat8_mysql_cs,内有注册码

立即下载
navicat8_mysql_cs,mysql数据库界面

navicat8_mysql_cs,mysql数据库界面。很好用的直观的mysql数据库的界面显示

立即下载
mysql_navicat120_mysql_en_x64下载

windows下 navicat120_mysql_en_x64数据库可视化工具

立即下载
MySQL_Cluster集群配置方案MySQL_Cluster集群配置方案

MySQL_Cluster集群配置方案MySQL_Cluster集群配置方案MySQL_Cluster集群配置方案

立即下载
navicat8lite下载 navicat8lite_mysql_en下载 navicat8lite_mysql_en mysql客户端下载

navicat8lite下载 navicat8lite_mysql_en下载 navicat8lite_mysql_en mysql客户端下载 非常不错的mysql客户端

立即下载
mysql 监控 Monitoring_MySQL mysql_en mysql

mysql 监控 Monitoring_MySQL mysql_en mysql

立即下载
MySQL_5.1_zh.chm & MySQL_5.5_en.chm文档

内含 MySQL_5.1_zh.chm & MySQL_5.5_en.chm两份文档!!

立即下载
mysql+mysql_gui_tools汉化

mysql+mysql_gui_tools汉化

立即下载
mysql设置max_allowed_packet_解决_MySQL_Error_2006

mysql设置max_allowed_packet_解决_MySQL_Error_2006

立即下载
专家教你安装_MySQL与MySQL_GUI_Tools.

专家教你安装_MySQL与MySQL_GUI_Tools.

立即下载
mysql_Workbench_3

mysql_Workbench_3mysql_Workbench_3mysql_Workbench_3mysql_Workbench_3mysql_Workbench_3mysql_Workbench_3mysql_Workbench_3mysql_Workbench_3

立即下载
mysql_Workbench_6

mysql_Workbench_6mysql_Workbench_6mysql_Workbench_6mysql_Workbench_6mysql_Workbench_6mysql_Workbench_6mysql_Workbench_6

立即下载
mysql_Workbench_4

mysql_Workbench_4mysql_Workbench_4mysql_Workbench_4mysql_Workbench_4mysql_Workbench_4mysql_Workbench_4

立即下载
mysql安装教程 linux安装mysql Windows安装mysql_Mysql教程

mysql安装教程 linux安装mysql Windows安装mysql_Mysql教程

立即下载
Beginning_MySQL_Database_Design__Optimization

Beginning_MySQL_Database_Design__Optimization

立即下载
MySQL_on_Windows_WWH

MySQL_on_Windows_WWH

立即下载
navicat10_mysql_oracle_mssql

navicat10_mysql_oracle_mssql 安装 navicat10_mysql_oracle_mssql

立即下载
mysql_linux_64

mysql_linux_64

立即下载