极湖

以下脚本用于本机 mysql 数据库的备份。每个数据库保存一周的7个备份。

#!/bin/bash

# 备份路径
BACKDIR=/var/backup/mysql
mkdir -p $BACKDIR

# MySQL的root用户密码
ROOTPASS=password

# 取得数据库名列表
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# 取得星期几
WEEKDAY=`date +'%w'`

# 对各数据库进行备份
for dbname in $DBLIST
do
    [ $dbname = "mysql" ] && continue
    mysqldump -u root -p$ROOTPASS $dbname | gzip > $BACKDIR/$dbname.sql.$WEEKDAY.gz
done

MySQL 有 AUTO_INCREMENT 可以很方便的实现字段自增长。不过 AUTO_INCREMENT 有以下缺点：

• 一个表只能有一个自增长字段
• 自增长字段必须绑定到某一个表
• 实现自增长，须在插入记录时，用 NULL，或是不指定该字段的值

为此，有必要仿照 PostgreSQL，增加 Sequence 管理用的表格和函数。

代码：

-- Sequence 管理表
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
	name VARCHAR(50) NOT NULL,
	current_value INT NOT NULL,
	increment INT NOT NULL DEFAULT 1,
	PRIMARY KEY (name)
) ENGINE=InnoDB;

-- 取当前值的函数
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
	RETURNS INTEGER
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	DECLARE value INTEGER;
	SET value = 0;
	SELECT current_value INTO value
		FROM sequence
		WHERE name = seq_name;
	RETURN value;
END
$
DELIMITER ;

-- 取下一个值的函数
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
	RETURNS INTEGER
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	UPDATE sequence
		SET current_value = current_value + increment
		WHERE name = seq_name;
	RETURN currval(seq_name);
END
$
DELIMITER ;

-- 更新当前值的函数
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
	RETURNS INTEGER
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	UPDATE sequence
		SET current_value = value
		WHERE name = seq_name;
	RETURN currval(seq_name);
END
$
DELIMITER ;

/*
-- 测试
INSERT INTO sequence VALUES ('TestSeq', 0, 1);
SELECT SETVAL('TestSeq', 10);
SELECT CURRVAL('TestSeq');
SELECT NEXTVAL('TestSeq');
*/

以上内容翻译整理自：
http://ronaldbradford.com/blog/sequences-in-mysql-2006-01-26/

修改 Zend/Db/Select.php， 增加以下代码

const FOUND_ROWS     = 'foundrows'; 
const SQL_FOUND_ROWS = 'SQL_CALC_FOUND_ROWS'; 

protected static $_partsInit = array(
    self::FOUND_ROWS   => false,  
    ... ...
);

/**
 * Makes the query SELECT SQL_CALC_FOUND_ROWS.  
 *
 * @param bool $flag Whether or not add SQL_CALC_FOUND_ROWS to SELECT.
 * @return Zend_Db_Select This Zend_Db_Select object.
 */
public function foundRows($flag = true)
{
    $this->_parts[self::FOUND_ROWS] = (bool) $flag;
    return $this;
}

/**
 * Render FOUND_ROWS clause  
 *
 * @param string   $sql SQL query
 * @return string
 */
protected function _renderFoundRows($sql)
{
    if ($this->_parts[self::FOUND_ROWS]) {
        $sql .= ' ' . self::SQL_FOUND_ROWS;
    }

    return $sql;
}

以上代码的具体位置不再详述。

调用方法如下:

$select = $db->select()->foundRows()->from(
  ... ...
)->where(
  ... ...
);

$data = $db->fetchAll($select);

$stmt = $db->query("SELECT FOUND_ROWS() as cnt");
list($rec) = $stmt->fetchAll();
$count = $rec['cnt']；

原文: http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html
翻译: 小白（又名 风子）

MySQL优化
关于优化MySQL应对高访问网站的讨论
Tim,
首席技术长官，DCS

MySQL设置，高并发连接
本人有一网站每天需要负责大约3000用户，每天长达5到7小时的在线工作。所以在繁忙时段，我们要为2000多并发用户提供服务。在PHP和MySQL的设置比较合理的前提下，即使是入门级的Intel平台的服务器，MySQL一样可以应对的很好。

首先是硬件需求
1. 最好可以将Apache/PHP和MySQL分开跑在两台独立的服务器上，Linux版本及分发不限
2. 尽量减少服务器的其他负载，将最多的资源留给Apache/PHP和MySQL服务

我正在使用的服务器配置如下：
1. Apache/PHP: 奔腾III, 600MHz, 512M内存
2. MySQL: 双奔腾III, 750MHz, 2G内存

如此有偏向的配置主要是因为数据库的负荷非常重。我们的网站为会员制的在线教学系统，只有登录以后才能使用其中的功能，而且内容高度自订化。学生可以在系统中访问属于本人的一整套服务，譬如课程，成绩卡，时间表等等。教师可在网站上在线创建课程，包括课文，语音材料（包括文本生成的音频材料）等等。

1) PHP 程序编写： 一定要使用持续连接！
从Apache/PHP到MySQL的通讯中，建立和关闭连接是一个非常大的开销。通过使用持续连接，大型的数据访问网站可以共享连接来交换数据，避免了载入每页时都要重复的连接请求。有鉴于用户每次点击都可能会产生一次连接，持续连接的左右是相当重要的。
确定使用mysql_pconnect来取代mysql_connect，同时在php.ini或者程序文件中的ini_set行应当做出相应的调整。

2) Apache设置(httpd.conf)。我们的网站进行了很多针对性的优化，经过多次试验对比，我们最终确定了一组参数。最终的结果使我们在top可以得到最大的CPU空闲百分比。
MinSpareServers 10
MaxSpareServers 20
StartServers 70
MaxClients 255

3) Mysql设置(my.cnf)。在MySQL的配置文件重，我们在[mysqld]模块中加入了如下内容
set-variable = max_connections = 300
(这个数字必须大于Apache的MaxClients，否则Apache将无法充分利用连接)
set-variable = max_user_connections = 300
set-variable = table_cacle = 1200
(在数据库查询中可能发生的最多join表数量乘以max_user_connections)
在如上的参数重，max_connections和max_user_connections为我们提供稳定的服务提供了巨大的帮助。当Apache/PHP连接MySQL服务器时会被作为单用户来对待，MySQL默认并发连接数为1（译者注，鉴于原文撰写时间较早，当时的数据库默认配置较为保守。译者特地检查MySQL 5.0.32的默认位置文件，发现默认并发连接数已经提高到100，对于中小型网站来说已经游刃有余。不过网管对于这个参数的作用还是应当了然于胸的）。通过修改max_connections，使得Apache/PHP可以产生大量的持续连接。当Apache启动时，大量的并发连接已经就绪，之后的访问都可以利用这条通路来交换数据，从而大大的节约了建立连接时的系统开销。

一些MySQL的其他技巧：
set-variable = max_allowed_packet = 1M (数据健壮性检查，防止失控查询）
set-variable = max_connect_errors = 999999 (防止mysqld因为过多的连接错误而重启）

译者注: 本文原创于2003年，当时的网络条件和硬件条件和如今已经有了比较大的区别，软件配置也有了相当的变化，譬如set-variable在MySQL 4.0.2以后已经可以省略。所以本文仅供参考，为有志于深入了解Apache MySQL的管理员新手们提供一点辅助的参考资料，切莫生搬硬套。

MySQL性能调优
参考：
http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html

PostgreSQL性能调优
参考：
http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://php.y-110.net/wiki/index.php?PostgreSQL%A1%A7%A5%C1%A5%E5%A1%BC%A5%CB%A5%F3%A5%B0%B4%AA%BD%EA
http://h2np.net/tips/tips12.htm
http://www.asahi-net.or.jp/~aa4t-nngk/pgsql5.html

[附]
SQL优化
http://www.geocities.jp/mickindex/database/db_optimize.html

Linux上shmmax参数的设置及含义
http://www.eygle.com/archives/2005/11/whats_mean_linux_shmmax.html