Mysql DBA tools－－monolith

Monolith是一款类似于MySQL Enterprise Monitor的开源工具,使用PHP技术，在很大程度上可以作为替代的MySQL Enterprise Monitor的低成本解决方案。

官方地址：http://sourceforge.net/projects/monolith-mysql

MySQL Enterprise Monitor官方网址：http://www.mysql.com/products/enterprise/monitor.html

Mysql集群安装脚本ndbinstaller

安装Mysql集群会稍微复杂一些，但是现在已经有一个很好安装脚本ndbinstaller。这个脚本提供了各种情况的安装，如：本地安装管理节点、本地数据节点、远程数据节点等。唯一需要注意的是此脚本安装的版本比最新官方的版本略低，而且不可通用，如脚本支持5.1.24那你下载的5.1.26是不能正常安装的。
地址：http://www.jimdowling.info/ndbinstaller-trac
下载：wget http://www.jimdowling.info/ndbinstaller/trunk/ndbinstaller.sh
SVN地址：svn co http://www.jimdowling.info/ndbinstaller/trunk/ ndbinstaller

MySQL Community Replication Monitoring System

Mysql Community Replication Monitoring System是一款监视主从复制的监视工具。需要编译安装。
官方主页：https://launchpad.net/mysql-replication-monitor
下载地址：https://launchpad.net/is-replication-status/+download
文档页面：http://forge.mysql.com/wiki/MySQL_Community_Replication_Monitoring_System
另外一篇介绍：Introducing the MySQL community-driven Replication Monitoring Tools

Configuring Apache for Maximum Performance[转载]

点此阅读原文：原文地址。

Apache is an open-source HTTP server implementation. It is the most popular web server on the Internet. The December 2005 Web Server Survey conducted by Netcraft [1] shows that about 70% of the web sites on Internet are using Apache.

1. Apache server performance

Apache server performance can be improved by adding additional hardware resources such as RAM, faster CPU etc. But, most of the time, the same result can be achieved by custom configuration of the server. This article looks into getting maximum performance out of Apache with the existing hardware resources, specifically on the Linux systems. Of course, it is assumed that there is enough hardware resources, especially enough RAM that the server isn't swapping frequently. First two sections look into various Compile-Time and Run-Time configuration options. Run-Time section assumes that Apache is compiled with prefork MPM. HTTP compression and caching is discussed next. Finally, using separate servers for serving static and dynamic contents are being discussed. Basic knowledge of compiling and configuring Apache, and Linux are assumed.

2 Compile-Time Configuration Options

2.1 Load only the required modules:

The Apache HTTP Server is a modular program where the administrator can choose the functionality to include in the server by selecting a set of modules [2]. The modules can be either statically compiled to the httpd binary or else can be compiled as Dynamic Shared Objects (DSOs). DSO modules can be either compiled when the server is built or else can use the apxs utility to compile and add at a later date. The module mod_so must be statically compiled into the Apache core to enable DSO support.

Run apache with only the required modules. This reduces the memory footprint and hence the server performance. Statically compiling modules will save RAM that's used for supporting dynamically loaded modules, but one has to recompile Apache whenever a module is to be added or dropped. This is where the DSO mechanism comes handy. Once the mod_so module is statically compiled, any other module can be added or dropped using the LoadModule command in httpd.conf file - of course, you will have to compile the modules using apxs if it wasn't compiled when the server was built.

2.2 Choose appropriate MPM:

Apache server ships with a selection of Multi-Processing Modules (MPMs) which are responsible for binding to network ports on the machine, accepting requests, and dispatching children to handle the requests [3]. Only one MPM can be loaded into the server at any time.

Choosing an MPM depends on various factors such as whether the OS supports threads, how much memory is available, scalability versus stability, whether non-thread-safe third-party modules are used, etc.. Linux systems can choose to use a threaded MPM like worker or a non-threaded MPM like prefork:

Worker MPM uses multiple child processes. It's multi-threaded within each child and each thread handles a single connection. Worker is fast and highly scalable and the memory footprint is comparatively low. It's well suited for multiple processors. On the other hand, worker is less tolerant to faulty modules and faulty threads can affect all the threads in a child process.

Prefork MPM uses multiple child processes, each child handles one connection at a time. Prefork is well suited for single or double CPU systems, speed is comparable to that of worker and it's highly tolerant to faulty modules and crashing children. But the memory usage is high, more traffic leads to more memory usage.

How To Repair MySQL Replication [转载]

原文点此阅读：原文地址。

其实文中的方法很简单，就是先确定复制出问题后，再利用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = number 语句来修复一下。

如果同步系统的一些状态参数有变化或长时间未同步后再次同步等情况，请参考我的这篇文章：Mysql Replication Tip。

Version 1.0

Author: Falko Timme <ft [at] falkotimme [dot] com>

Last edited 05/29/2008

If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again.

I do not issue any guarantee that this will work for you!

1 Identifying The Problem

To find out whether replication is/is not working and what has caused to stop it, you can take a look at the logs. On Debian, for example, MySQL logs to /var/log/syslog:

grep mysql /var/log/syslog

server1:/home/admin# grep mysql /var/log/syslog

May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate

May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views

May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146

May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001079' position 203015142

server1:/home/admin#

You can see what query caused the error, and at what log position the replication stopped.

To verify that the replication is really not working, log in to MySQL:

mysql -u root -p

On the MySQL shell, run:

mysql> SHOW SLAVE STATUS \G

If one of Slave_IO_Running or Slave_SQL_Running is set to No, then the replication is broken:

Mysql 管理工具集 maatkit

Maatkit是Mysql的一个高级管理工具集。
官方网站：http://www.maatkit.org/
文档地址：http://maatkit.sourceforge.net/doc/maatkit.html
Google Code 页面：http://code.google.com/p/maatkit/
下载地址：wget http://www.maatkit.org/get/script_name script_name 见下面的列表
SVN地址：http://maatkit.googlecode.com/svn/trunk
其中debian有maatkit的deb包了，地址：http://packages.debian.org/search?keywords=maatkit
Debian下安装deb包也极为方便，先安装 libterm-readkey-perl （ apt-get install libterm-readkey-perl ），再安装maatkit即可（ dpkg -i maatkit.deb ）。
Maatkit包含以下主要工具：
1、mk-table-checksum 检查主从表是否一致及有效的工具
2、mk-table-sync 使表一致的工具，不必重载从表而能够保证一致
3、mk-visual-explain exlpain解释工具
4、mk-heartbeat 主从同步的监视工具，能够给出从落后于主多少
5、mk-parallel-dump 多线程的mysqldump工具
6、mk-parallel-restore 多线程的表回复工具
7、mk-query-profiler 查询检测分析工具
8、mk-deadlock-logger 死锁的记录工具，支持innodb
9、mk-duplicate-key-checker key侦测工具
10、mk-show-grants 权限管理显示工具
11、mk-slave-restart slave的检测和重启工具
12、mk-slave-delay slave delay replication 的工具
13、mk-slave-prefetch This tool implements Paul Tuckfield’s famous “oracle” algorithm to read ahead of the slave SQL thread in the relay logs, rewriting queries as SELECT and executing them to warm the slave’s caches. This can help an I/O-bound slave SQL thread run faster under some conditions, because it doesn’t have to wait for as much I/O to complete.

Mysql优化工具－－MySQLTuner

最近发现一个优化Mysql时可以使用的小工具－－MySQLTuner，这个工具是一个prel写的脚本，运行后会给出当前mysql的各种参数，对进一步优化起到很好的指示。

官方网站：http://rackerhacker.com/

官方wiki：http://wiki.mysqltuner.com/MySQLTuner

下载地址：http://mysqltuner.com/mysqltuner.pl

SVN: svn co http://tools.assembla.com/svn/mysqltuner/

运行的结果：

server1:~# ./mysqltuner.pl



>> MySQLTuner 0.9.8 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

Please enter your MySQL administrative login: <-- root

Please enter your MySQL administrative password: <-- yourrootsqlpassword



-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software! Upgrade soon!

[OK] Operating on 32-bit architecture with less than 2GB RAM



-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster

[--] Data in MyISAM tables: 301M (Tables: 2074)

[--] Data in HEAP tables: 379K (Tables: 9)

[!!] InnoDB is enabled but isn't being used

[!!] ISAM is enabled but isn't being used

[!!] Total fragmented tables: 215



-------- Performance Metrics -------------------------------------------------

[--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)

[--] Reads / Writes: 78% / 22%

[--] Total buffers: 2.6M per thread and 58.0M global

[OK] Maximum possible memory usage: 320.5M (20% of installed RAM)

[OK] Slow queries: 0% (17/1B)

[OK] Highest usage of available connections: 32% (32/100)

[OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M

[OK] Key buffer hit rate: 99.9%

[OK] Query cache efficiency: 99.9%

[!!] Query cache prunes per day: 47549

[OK] Sorts requiring temporary tables: 0%

[!!] Temporary tables created on disk: 28%

[OK] Thread cache hit rate: 99%

[!!] Table cache hit rate: 0%

[OK] Open file limit used: 12%

[OK] Table locks acquired immediately: 99%

[!!] Connections aborted: 20%



-------- Recommendations -----------------------------------------------------

General recommendations:

Add skip-innodb to MySQL configuration to disable InnoDB

Add skip-isam to MySQL configuration to disable ISAM

Run OPTIMIZE TABLE to defragment tables for better performance

Enable the slow query log to troubleshoot bad queries

When making adjustments, make tmp_table_size/max_heap_table_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Your applications are not closing MySQL connections properly

Variables to adjust:

query_cache_size (> 16M)

tmp_table_size (> 32M)

max_heap_table_size (> 16M)

table_cache (> 64)