分类目录归档:MySQL

修改innodb_flush_log_at_trx_commit参数提升insert性能

最近,在一个系统的慢查询日志里发现有个insert操作很慢,达到秒级,并且是比较简单的SQL语句,把语句拿出来到mysql中直接执行,速度却很快。

这种问题一般不是SQL语句本身的问题,而是在具体的应用环境中,由于并发等原因导致的。最可怀疑的地方就是在等待表级锁。

加上监控的日志来看,很多SQL是在同一时间完成的,下面的第三列是结束时间,第四列是开始时间:

14:27:30 bizId30905 1355812050  1355812045
14:27:30 bizId28907 1355812050  1355812043
14:27:30 bizId30905 1355812050  1355812047
14:27:30 bizId17388 1355812050  1355812040
14:27:30 bizId40563 1355812050  1355812044
14:27:30 bizId15477 1355812050  1355812048
14:27:30 bizId32588 1355812050  1355812048

但是通过应用的分析来看,并不存在表级锁的地方,而insert自身的操作也只是对要插入的记录本身加锁,不会影响其他并发的insert操作。

没有更好的办法,只能在MySQL写入磁盘的性能上考虑,MySQL有个innodb_flush_log_at_trx_commit参数,用来配置flush log到磁盘的时机,具体点说,是从log buffer写到log file,并写入到磁盘上的时机。这个参数的默认值是1,即每次事务提交的时候会把日志刷到磁盘,而频繁的insert操作就会引起flush log操作的不断积累,进而引发性能问题。在应用数据可接受的前提下,可以把这个值改成0,就是每秒才操作一次。修改后潜在的问题是,在事务已经提交的情况下,如果尚未写入磁盘的时候发生故障,可能丢失数据。

MySQL官网对此参数的描述如下:

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB‘s crash recovery works regardless of the value.

其他角度的优化办法:

如果是MyISAM存储引擎,可以使用insert delay的方式来提高性能,其原理是MySQL自身会在内存中维护一个insert队列,在实际表空闲的时候insert数据。

从应用的角度,批量提交也是解决问题的办法,当然要在应用场景许可的前提下。

管理MySQL数据的一些SQL小技巧

MySQL针对多表数据的DML一般比较低效,特别是in语句,大数据量下基本就不能使用,一般关联多表情况的增删改可以用类似如下的SQL来实现,当然,这种复杂的SQL在应用程序里是应该尽量避免的。列举几个小例子:

delete a from ta a, tb b where a.fid=b.fid and a.fls_id=b.fls_id;

update ta a,tb b set a.name=a.uname where a.uid=b.id;

update ta p,(select feed_id as id,sum(c) as c from ta group by feed_id) c set p.co=c.co where c.id=p.id;

update user u,tc c set u.username=substring(u.username,1,instr(u.username,’(‘)-1)
where u.id=c.id

在Oracle中,表连接有NL(Nested Loop)、HJ(Hash Join)和SMJ(Sort Merge Join)等方式,而在MySQL中,只有NL一种方式,所以就是拿驱动表的结果集做循环,去另外一个表里查数据。

另外,保证被驱动表的条件有索引很重要,一般被驱动表比较大,没有索引的查询会非常慢。

在MySQL的存储过程里使用动态SQL

MySQL的存储过程不好用,一般在应用开发过程中不建议使用。但有时候在数据迁移等环境中,特别涉及到复杂点的逻辑处理,就需要存储过程的配合了,当然,用其他语言写程序实现也是可以的,不过效率估计没有直接在数据库中操作高。

有时候数据所在的表是不确定的,需要根据实际情况临时决定表名,这时候就是动态SQL的用武之地了,以下示例展示了在一次数据导出的过程中使用动态SQL的存储过程例子:

delimiter //
CREATE PROCEDURE `proc_rt_imp_group`()
BEGIN
DECLARE done INT DEFAULT FALSE;
declare v_tid,v_ptable int;
declare v_tablename VARCHAR(32);
DECLARE cur_t CURSOR FOR select t.tid,t.ptable from pw_threads_rt t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_t;
read_loop: LOOP
FETCH cur_t INTO v_tid,v_ptable;
IF done THEN
LEAVE read_loop;
END IF;
if v_ptable=0 then
set v_tablename = ‘pw_posts’;
else
set v_tablename = concat(‘pw_posts’,v_ptable);
end if;
set @updatesql =CONCAT(‘insert into pw_posts_rt select * from ‘,v_tablename,’ where tid= ‘,v_tid);
PREPARE sqltext from @updatesql;
execute sqltext;
END LOOP;
CLOSE cur_t;
END//
delimiter ;

MySQL的wait_timeout参数太小导致表级锁失效

如果想让某个表只读不能被修改,可以在MySQL Server层面加表级锁:

mysql> lock tables sms read;
Query OK, 0 rows affected (0.01 sec)

然后其他的连接去更新或插入数据是不会成功的:

mysql> insert into sms(id) values (555);

因为要等待表级锁的释放,可以通过processlist看到这一点:

mysql> show processlist;
+——–+————-+———————+—————+———+——+——————————————-+—————————————–+
| Id | User | Host | db | Command | Time | State | Info |
+——–+————-+———————+—————+———+——+——————————————-+—————————————–+ |
| 364733 | root | localhost | banpingdb | Query | 0 | NULL | show processlist |
| 364734 | root | localhost | banpingdb | Query | 148 | Waiting for table level lock | insert into sms(id) values (555) |
+——–+————-+———————+—————+———+——+——————————————-+—————————————–+
2 rows in set (0.00 sec)

当然,更详细的信息也可以看到:

mysql> show engine innodb status G
————
TRANSACTIONS
————
Trx id counter 5AA0D8A
Purge done for trx’s n:o < 5AA0D77 undo n:o < 0
History list length 1063
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started, process no 21967, OS thread id 1286076736
mysql tables in use 1, locked 1
MySQL thread id 364734, query id 3527582 localhost root Waiting for table level lock
insert into sms(id) values (555)
—TRANSACTION 0, not started, process no 21967, OS thread id 1284471104
mysql tables in use 1, locked 1
MySQL thread id 364733, query id 3527604 localhost root
show engine innodb status

但是,今天奇怪的发现,在等待了一段时间后,insert语句竟然莫名其妙的成功了,分析了一下原因。并没有什么地方控制表级锁的超时,就是因为表级锁被释放了,才能使得insert成功,而表级锁被释放的源头就是wait_timeout参数,这个参数的意思是:

The number of seconds the server waits for activity on a noninteractive connection before closing it.

如果这个值设置的小,比如说一分钟,那么一分钟后,不活动的连接就会被释放,而连接的释放会导致这个连接发出的表级锁被解锁。如果你的系统里这个参数很小,下面的这些信息一定是你经常看到的:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 364743
Current database: banpingdb

这个错误提示的就是连接超时了重新连的意思。
还有另外一个参数是interactive_timeout,是针对interactive connection的超时时间,但是在global层面设置这个值会引起wait_timeout的连带改变。

MySQL主从复制出错案例一则

有时候为了方面的导入数据,可以使用MyISAM存储引擎的表,直接把数据文件Copy一份放到对应的目录下,这样MySQL就能直接识别到,但是在主从复制的环境里这样做是有问题的,因为在从库上并没有这个表,从而导致后续的一些操作失败,比如在主库上执行导入数据的SQL语句,则在从库会报错如下:

mysql> show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1146
Last_Error: Error ‘Table ‘banpingdb.blog_score’ doesn’t exist’ on query. Default database: ‘banpingdb’. Query: ‘replace INTO blog_score_result(id,score,avg)
SELECT b.id, min(s.comment), min(s.price) FROM blog_score s,a_blog b where s.name=b.biz_name
group by b.id’

同样的信息通过日志也一样会看到:

111215 19:17:54 [ERROR] Slave SQL: Error ‘Table ‘banpingdb.blog_score’ doesn’t exist’ on query. Default database: ‘banpingdb’. Query: ‘replace INTO blog_score_result(id,score,avg)
SELECT b.id, min(s.comment), min(s.price) FROM blog_score s,a_blog b where s.name=b.biz_name
group by b.id’, Error_code: 1146
111215 19:17:54 [Warning] Slave: Table ‘banpingdb.blog_score’ doesn’t exist Error_code: 1146
111215 19:17:54 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ’1-bin.000001′ position 130482476

这种错误处理也很简单,可以直接跳过,当然要评估可能产生的影响:

mysql> slave stop;
Query OK, 0 rows affected (0.22 sec)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

然后blog_score_result这个表的数据肯定不准确了,可以重新从主库导入数据,视应用的繁忙程度,要保证数据的一致性,先在主库上lock这个表,不让其他用户更新:

mysql> lock tables blog_score_result read;
Query OK, 0 rows affected (0.00 sec)

然后导出数据:

[root@banping 3306]# bin/mysqldump –default-character-set=utf8 banpingdb blog_score_result -u root -p>/tmp/blog_score_result.sql;

去从库导入数据:

[root@banping 3306]# bin/mysql –default-character-set=utf8 banpingdb -u root -p</tmp/blog_score_result.sql;
Query OK, 0 rows affected (0.00 sec)

到主库解锁这个表:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

这样就完成了数据一致性的操作。主从复制的同步也正常了。这种事情的处理要结合实际应用情况,不可照搬就是了。

Xtrabackup 1.6.3安装及恢复数据

Xtrabackup是备份MySQL的很好的工具,目前最新的版本是1.6.3,和之前出过的1.6版本改进了不少,官方Percona目前对1.6.3的文档也改进了很多。安装包不再包含MySQL源码文件了,要根据需要从Percona在Amazon aws的服务器上下载,当然也可以自己下载需要的文件。

安装Xtrabackup,可以通过源码安装,也可以拿二进制程序过来直接使用,以下是源码安装的过程:

[root@data banping]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6.3/source/xtrabackup-1.6.3.tar.gz
[root@data banping]# tar zxf xtrabackup-1.6.3.tar.gz
[root@data banping]# cd xtrabackup-1.6.3
[root@data xtrabackup-1.6.3]# AUTO_DOWNLOAD=”yes” ./utils/build.sh 5.5

自动下载的是libtar-1.2.11.tar.gz和mysql源码文件,mysql的版本根据给定的参数识别,可选的有以下版本:

Value Alias Server
innodb51_builtin 5.1 build against built-in InnoDB in MySQL 5.1
innodb51 plugin build agsinst InnoDB plugin in MySQL 5.1
innodb55 5.5 build against InnoDB in MySQL 5.5
xtradb51 xtradb build against Percona Server with XtraDB 5.1
xtradb55 xtradb55 build against Percona Server with XtraDB 5.5

默认libtar并不会安装,如果需要使用stream功能,需要手动安装这个程序。安装完成后,会生成一个xtrabackup_innodb55文件,可以用它恢复innodb数据,文件的路径如下:

Target Location
innodb51_builtin mysql-5.1/storage/innobase/xtrabackup
innodb51 mysql-5.1/storage/innodb_plugin/xtrabackup
innodb55 mysql-5.5/storage/innobase/xtrabackup
xtradb51 Percona-Server-5.1/storage/innodb_plugin/xtrabackup
xtradb55 Percona-Server-5.5/storage/innobase/xtrabackup

恢复的方法如下:

[root@data recover]# /banping/xtrabackup_innodb55 –prepare –target-dir=/banping/recover/

prepare就是recover的过程,也就是通过前滚和回滚使数据一致,target-dir就是备份出来的文件路径。这一步的操作和MySQL Server是没有任何关系的。官方还建议执行两次prepare以便生成新的redo文件,个人认为是没什么必要的。

关于1.6版本的安装及备份和Xtrabackup的原理,请参见我以前的文章:

http://www.banping.com/2011/04/14/xtrabackup-mysql55/

http://www.banping.com/2011/05/24/xtrabackup-stream-mysql/

http://www.banping.com/2011/07/01/xtrabackup-process-backgroud/

 

HandlerSocket性能测试

引言

NoSQL是伴随着web2.0的迅猛发展而在2009年被提出的一个概念,一般可以通俗的理解为高性能的Key Value存储结构的数据库,当然也有其他更广泛的类型。它基于CAP和BASE理论,强调最终一致性,具有数据结构灵活、扩展方便、大数据量下读写性能高效等特点,在互联网行业被广泛采用。本系列文章将评测广受关注的几个NoSQL数据库产品。本文关注的是HandlerSocket Plugin for MySQL。

介绍

HandlerSocket是日本DeNA公司的架构师Yoshinori开发的一个NoSQL产品,以MySQL Plugin的形式运行。其主要的思路是在MySQL的体系架构中绕开SQL解析这层,使得应用程序直接和Innodb存储引擎交互,通过合并写入、协议简单等手段提高了数据访问的性能,在CPU密集型的应用中这一优势尤其明显。其架构图如下:

image001

图片来源:HandlerSocket作者博客

另外,HandlerSocket还帮我们解决了缓存的问题,因为Innodb已经有了成熟的解决方案,通过参数可以配置用于缓存数据的内存大小,这样只要我们分配合理的参数,就能在应用程序无需干涉的情况下实现热点数据的缓存,降低缓存维护的开发成本。

因为HandlerSocket是MySQL的一个Plugin,集成在mysqld进程中,对于NoSQL无法实现的复杂查询等操作,仍然可以使用MySQL自身的关系型数据库功能来实现。在运维层面,原来广泛使用的MySQL主从复制等经验可以继续发挥作用,相比其他或多或少存在一些bug的NoSQL产品,数据安全性更有保障。

可以说这是一个很有创意的产品,因此HandlerSocket的作者在2010年10月在博客上宣称这一产品能达到75K QPS的时候,在业界引起了广泛的关注,包括MySQL官方在新的5.6版本中推出的Memcached API,相信也是受了HandlerSocket的启发。

安装

一、安装MySQL

通过编译源码的方式安装MySQL,这里选择的版本是5.5.8版本。由于这不是本文的重点,这里只简单的提及一下要点。

首先到MySQL的官网http://dev.mysql.com/downloads/mysql/下载源码,要注意的是从MySQL 5.5版本开始需要Cmake编译工具和bison。然后开始安装:

[root@localhost handlersocket]# tar zxvf mysql-5.5.8.tar.gz

[root@localhost handlersocket]# cd mysql-5.5.8

[root@localhost mysql-5.5.8]#  cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/home/handlersocket/mysql558/ -DSYSCONFDIR=/home/handlersocket/mysql558/ -DWITH_EMBEDDED_SERVER=on -DWITH_READLINE=on -DWITH_SSL=yes -DENABLED_LOCAL_INFILE=on -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1

[root@localhost mysql-5.5.8]# make

[root@localhost mysql-5.5.8]# make install

安装完成后配置my.cnf文件,最重要的参数是分配给innodb存储引擎的内存大小,也就是innodb_buffer_pool_size参数,这里设置为2G。

[root@localhost mysql558]# vi my.cnf

……

innodb_buffer_pool_size = 2G

……

二、安装HandlerSocket

首先到以下地址下载代码:

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

开始安装HandlerSocket:

[root@localhost handlersocket]# tar -zxvf ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-67-g25f4957.tar.gz

[root@localhost handlersocket]# cd handlersocket/

[root@localhost handlersocket]# ./autogen.sh

[root@localhost handlersocket]# ./configure –with-mysql-source=/home/handlersocket/mysql-5.5.8 –with-mysql-bindir=/home/handlersocket/mysql558/bin  –with-mysql-plugindir=/home/handlersocket/mysql558/lib/plugin

[root@localhost handlersocket]# make

[root@localhost handlersocket]# make install

三、修改MySQL配置

在my.cnf配置文件增加以下内容:

[root@localhost mysql558]# vi my.cnf

[mysqld]

……

loose_handlersocket_port = 9998

# the port number to bind to (for read requests)

loose_handlersocket_port_wr = 9999

# the port number to bind to (for write requests)

loose_handlersocket_threads = 16

# the number of worker threads (for read requests)

loose_handlersocket_threads_wr = 16

# the number of worker threads (for write requests)

open_files_limit = 65535

# to allow handlersocket accept many concurrent connections

#make open_files_limit as large as possible.

这里增加的这些主要是针对handlersocket的配置,它有2个端口,9998用来读数据,9999读写均可,但是通过9998来读的效率更高。这里我们设置处理读写的线程数均为16个,另外为了处理更多的并发连接,设置能打开的文件描述符个数为65535。

四、安装MySQL授权表并启动数据库

[root@localhost mysql558]# scripts/mysql_install_db –user=mysql

[root@localhost mysql558]# bin/mysqld_safe –user=mysql &

五、登录MySQL安装HandlerSocket Plugin:

[root@localhost mysql558]# bin/mysql -u root -p

mysql> install plugin handlersocket soname ‘handlersocket.so’;

Query OK, 0 rows affected (0.01 sec)

通过以下命令查看也可见handlersocket已经成功安装到MySQL中:

mysql> show processlist;

mysql> show plugins;

测试说明

一、测试环境

Handlersocket部署在一台PC 服务器上,配置如下:

CPU为Xeon 2.80GHz *4

内存为4G

硬盘为一块400G SATA盘

操作系统为64位CentOS 5.3版本

二、测试方法

Handlersocket的客户端几乎涵盖了各种语言,都实现了通过以上两个端口对数据读写的操作,其中查询操作必须通过建立有索引的列来进行。

考虑到NoSQL在互联网行业应用较为广泛,采用PHP实现客户端程序的做法,通过一定的并发去读写数据,观测每秒读写的记录数作为主要的衡量指标。当然也可以利用Java的多线程进行并发测试,但由于篇幅和时间所限,本文不再涉及此方面的内容。

这里采用第三方实现的一个PHP客户端,网址为http://code.google.com/p/php-handlersocket/,可以编译到PHP运行环境中。

为了不对测试服务器产生额外的影响,测试客户端部署在另外一台独立的服务器上,运行的PHP的版本是5.3.5,web server是Nginx 0.8.54,通过fastcgi的方式调用PHP服务。使用apache ab工具实现多个请求和并发操作。

测试分为两个步骤,首先是写操作,通过500个请求,每个请求写入10000条记录,并发度为2来共写入500万条数据,数据的key为数字1到5000000,value大小为100个字节。然后是读操作,也是用500个请求,每个请求随机根据key值读出10000条记录,并发度为10共读出500万条记录,评测的重点是写入和读出数据的时间,以及在此过程中服务器的资源使用情况。

测试结果及总结

一、写操作

成功写入500万条记录,共耗时4300秒,平均每秒写入数据1163笔。数据文件大小761M。写入过程中,服务器内存、CPU和磁盘等资源使用情况如下图所示:

image003

image005

image007

可见,CPU使用率平稳,Idle值稳定在69到74之间,wait值稳定在15到19之间。内存分配上的变化较大,主要用来缓存数据,cache部分上升了1.4G,但是没有交换区到内存的换入换出。磁盘IO表现平稳,每秒有70%到80%的时间用于处理IO操作。

二、读操作

成功读出500万条记录,共耗时193秒,平均每秒读出数据25906笔。

image009

读数据过程中没有发生磁盘IO。CPU较繁忙,Idle在39到53之间,等待CPU资源的进程一直在1到6个之间。内存表现平稳没有波动。

通过以上测试结果可以说明,写入的数据完全缓存到了文件系统中,所以cache部分占用的内存大量增加,这也是读取数据的时候没有发生磁盘IO的原因。

要说明的是,虽然这是一份真实的测试数据,但是并不一定具有普遍意义。因为应用场景是千差万别的,服务器的资源配置、数据记录的多少、单条数据的大小、读写的比例、客户端程序的质量等因素都会影响测试结果,甚至差别会非常大,对我们来说,更重要的是了解这个NoSQL产品的特性,知道它的适用场景,并且能够根据自己实际的应用场景针对性的进行测试,这样才能做到针对性的选型,只有最适合自己需求的产品才是最好的产品。

备注:本文发表在IT168网站上,版权所有,转载请注明出处。

使用XtraBackup备份MySQL的一个经典错误

本文开头提到的问题就是在这个锁表的过程中不成功,造成等待而最终超时退出。
今天凌晨2点50分,一台MySQL服务器的备份遭遇了一个错误,信息如下:
innobackupex: Error: Connection to mysql child process (pid=20080) timedout. (Time limit of 900 seconds exceeded. You may adjust time limit by editing the value of parameter “$mysql_response_timeout” in this script.) while waiting for reply to MySQL request: ‘FLUSH TABLES WITH READ LOCK;’ at /home/mysql/backup/xtrabackup-1.6/innobackupex line 336.
这是使用XtraBackup容易引起的一个经典错误,引起这个错误的原因也很简单,就是XtraBackup在备完innodb表的时候,要获得对所有表的锁定来备份MyISAM等其他表,来保证数据的一致性,如果数据库不断的有DML操作,XtraBackup就可能迟迟无法获得对所有表的锁定,最后超时。
让我们先了解一下XtraBackup备份MySQL的原理和过程。

前几天一台MySQL服务器的备份遭遇了一个错误,信息如下:

innobackupex: Error: Connection to mysql child process (pid=20080) timedout. (Time limit of 900 seconds exceeded. You may adjust time limit by editing the value of parameter “$mysql_response_timeout” in this script.) while waiting for reply to MySQL request: ‘FLUSH TABLES WITH READ LOCK;’ at /home/mysql/backup/xtrabackup-1.6/innobackupex line 336.

这是使用XtraBackup容易引起的一个经典错误,引起这个错误的原因也很简单,就是XtraBackup在备完innodb表的时候,要获得对所有表的锁定来备份MyISAM表和其他文件,来保证数据的一致性,如果在此过程中数据库不断的有DML操作,XtraBackup就可能迟迟无法获得对所有表的锁定,最后超时。

要避免这个问题,一个是加大超时的时间限制,但这显然不是一个好的解决办法,另外就是在备份的时候加上–no-lock参数,就是在复制MyISAM表和.frm等文件的时候不锁表,但要保证这时没有对MyISAM表的DML操作和Innodb表的DDL操作,这一般通过应用端权衡时间是容易办到的。

但是要注意的是,如果备份出来的文件要用于主从复制,那么不锁表会导致没有输出 slave_info文件和binlog_info文件,这对于一些人的应用场景可能会有影响。这时可以加一个–safe-slave-backup参数,使得在从库上备份的时候停止SQL THRED,这样即使从库能从主库接收binlog文件,但是不会应用,relay log position就不会移动了。但是单纯的停掉slave SQL Thread是不会影响binlog position的。

因为临时表的原因,–safe-slave-backup需要在SHOW STATUS的输出中slave_open_temp_tables为0的时候才停止slave SQL Thread,否则会导致复制出问题。因为临时表是基于用户session的,因此如果正在操作临时表的数据的时候停止slave SQL Thread,会导致可能后续的数据不一致,在其他停用slave SQL Thread的场景也要注意这个问题。

解析XtraBackup备份MySQL的原理和过程

XtraBackup是percona公司提供的开源工具,以热备Innodb表著称而被广泛采用。

XtraBackup对Innodb的备份之所以是热备,无需锁表,是基于Innodb自身的崩溃恢复机制,它首先复制所有的Innodb数据文件,这样复制出来的文件肯定是不一致的,然后对每个文件进行崩溃恢复处理,最终达到一致。就和MySQL在启动Innodb的时候一样,会通过比较数据文件头和redo log文件头信息来检查数据是否是一致的,如果不一致就尝试通过前滚(把redo log中所有提交的事务写入数据文件)和回滚(从数据文件中撤销所有redo log中未提交的事务引起的修改)来使数据达到最终一致。

This works because InnoDB maintains a redo log, also called the transaction log. This contains a record of every change to InnoDB’s data. When InnoDB starts, it inspects the data files and the transaction log, and performs two steps. It applies committed transaction log entries to the data files, and it performs an undo operation on any transactions that modified data but did not commit.
XtraBackup works by remembering the log sequence number (LSN) when it starts, and then copying away the data files. It takes some time to do this, so if the files are changing, then they reflect the state of the database at different points in time. At the same time, XtraBackup runs a background process that watches the transaction log files, and copies changes from it. XtraBackup needs to do this continually because the transaction logs are written in a round-robin fashion, and can be reused after a while. XtraBackup needs the transaction log records for every change to the data files since it began execution.
The above is the backup process. Next is the prepare process. During this step, XtraBackup performs crash recovery against the copied data files, using the copied transaction log file. After this is done, the database is ready to restore and use.
The above process is implemented in the xtrabackup compiled binary program. The innobackupex program adds more convenience and functionality by also permitting you to back up MyISAM tables and .frm files. It starts xtrabackup, waits until it finishes copying files, and then issues FLUSH TABLES WITH READ LOCK to prevent further changes to MySQL’s data and flush all MyISAM tables to disk. It holds this lock, copies the MyISAM files, and then releases the lock.
The backed-up MyISAM and InnoDB tables will eventually be consistent with each other, because after the prepare (recovery) process, InnoDB’s data is rolled forward to the point at which the backup completed, not rolled back to the point at which it started. This point in time matches where the FLUSH TABLES WITH READ LOCK was taken, so the MyISAM data and the prepared InnoDB data are in sync.
The xtrabackup and innobackupex tools both offer many features not mentioned in the preceding explanation. Each tool’s functionality is explained in more detail on its manual page. In brief, though, the tools permit you to do operations such as streaming and incremental backups with various combinations of copying the data files, copying the log files, and applying the logs to the data.

XtraBackup在启动的时候会记录一个LSN(log sequence number),然后就把所有的Innodb数据文件复制出来,这样复制出来的数据文件是不一致的,但是XtraBackup会在后台运行一个进程把所有对redo log file的修改记录下来,只要有了这个数据,就能进行崩溃恢复。只所以要额外记录下来,是因为MySQL自身的redo log file是可重用的。

以上的操作是由xtrabackup二进制程序(比如xtrabackup_55)完成的,如果使用innobackupex 脚本,刚才的步骤完成以后,innobackupex就会去备份MyISAM表和.frm文件,这时要保证数据的一致性就会先锁表了,通过FLUSH TABLES WITH READ LOCK命令锁表然后把文件复制出来,再释放掉这个锁。

在恢复数据的时候,要经过prepare(recovery)和restore两个步骤。在prepare结束以后,Innodb的表恢复到了复制Innodb文件结束的时间点,这个时间点也就是锁表复制MyISAM表的起点,所以最终数据是一致的。一般我们在恢复的时候执行两次prepare,是因为第二次prepare会帮助我们生成redo log文件,从而加快MySQL数据库启动的速度。

我们再来看一下实际备份的日志来理解这个过程:

……

110701 03:29:13  innobackupex: Starting ibbackup with command: xtrabackup_55  –defaults-file=”/home/mysql/3306/my.cnf” –backup –suspend-at-end –log-stream –target-dir=./

innobackupex: Waiting for ibbackup (pid=22334) to suspend

innobackupex: Suspend file ‘/home/mysql/3306/data/xtrabackup_suspended’

xtrabackup: suspend-at-end is enabled.

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/mysql/3306/data

xtrabackup: Target instance is assumed as followings.

xtrabackup:   innodb_data_home_dir = /home/mysql/3306/data

xtrabackup:   innodb_data_file_path = ibdata1:512M:autoextend

xtrabackup:   innodb_log_group_home_dir = /home/mysql/3306/redolog

xtrabackup:   innodb_log_files_in_group = 3

xtrabackup:   innodb_log_file_size = 134217728

110701  3:29:13 InnoDB: Using Linux native AIO

110701  3:29:13  InnoDB: Warning: allocated tablespace 268, old maximum was 0

xtrabackup: Stream mode.

>> log scanned up to (2371741708)

110701 03:29:15  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes

innobackupex: from original InnoDB data directory ‘/home/mysql/3306/data’

innobackupex: Backing up as tar stream ‘ibdata1′

>> log scanned up to (2371741708)

>> log scanned up to (2371742105)

>> log scanned up to (2371742105)

innobackupex: Backing up file ‘/home/mysql/3306/data/test/t.ibd’

>> log scanned up to (2371742115)

innobackupex: Backing up files ‘/home/mysql/3306/data/banping/*.ibd’ (16 files)

……

110701 03:29:35  innobackupex: Connected to database with mysql child process (pid=22630)

>> log scanned up to (2371742526)

110701 03:29:39  innobackupex: Starting to lock all tables…

>> log scanned up to (2371742526)

>> log scanned up to (2371742526)

110701 03:29:51  innobackupex: All tables locked and flushed to disk

110701 03:29:51  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectories of ‘/home/mysql/3306/data’

innobackupex: Backing up files ‘/home/mysql/3306/data/banping/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (17 files)

innobackupex: Backing up file ‘/home/mysql/3306/data/test/t.frm’

……

110701 03:29:53  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): ’2371742526′

>> log scanned up to (2371742526)

xtrabackup: Transaction log of lsn (2371741708) to (2371742526) was copied.

110701 03:29:55  innobackupex: All tables unlocked

110701 03:29:55  innobackupex: Connection to database server closed

innobackupex: Backup created in directory ‘/home/mysql/backup/data/3306′

innobackupex: MySQL binlog position: filename ‘bin.000014′, position 309836330 mysql,information_schema,performance_schema

innobackupex: MySQL slave binlog position: master host ”, filename ”, position

innobackupex: You must use -i (–ignore-zeros) option for extraction of the tar stream.

110701 03:29:55  innobackupex: completed OK!

使用xtrabackup的stream方式备份MySQL

在使用xtrabackup备份MySQL的时候,可以用stream参数指定输出流的格式,目前只能选择tar方式。使用这个tar4ibd程序进行打包。这个程序包含在xtrabackup的源码中,需要单独安装,如果没有安装,指定stream=tar参数会报以下错误:

innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory ‘/home/mysql/3306/data’
innobackupex: Backing up as tar stream ‘ibdata1′
sh: tar4ibd: command not found
innobackupex: tar returned with exit code 127.
innobackupex: Error: Failed to stream ‘/home/mysql/3306/data/ibdata1′:  at xtrabackup-1.6/innobackupex line 336.

innobackupex: Starting to backup InnoDB tables and indexes

innobackupex: from original InnoDB data directory ‘/home/mysql/3306/data’

innobackupex: Backing up as tar stream ‘ibdata1′

sh: tar4ibd: command not found

innobackupex: tar returned with exit code 127.

innobackupex: Error: Failed to stream ‘/home/mysql/3306/data/ibdata1′:  at xtrabackup-1.6/innobackupex line 336.

到xtrabackup-1.6的源码目录下有个libtar-1.2.11文件目录,安装即可:

[root@web-04 xtrabackup-1.6]# cd libtar-1.2.11

./configure

make

make install

然后就可以备份MySQL了:

xtrabackup-1.6/innobackupex –stream=tar –socket=/home/mysql/3306/mysql.sock –defaults-file=/home/mysql/3306/my.cnf –user=root –password=banping –slave-info /home/mysql/backup/data/3306 1>/home/mysql/backup/data/3306/mysql20110518.tar 2>/home/mysql/backup/data/3306/tar.log

也可以在备份的时候进行压缩:

xtrabackup-1.6/innobackupex –stream=tar –socket=/home/mysql/3306/mysql.sock –defaults-file=/home/mysql/3306/my.cnf –user=root –password=banping –slave-info /home/mysql/backup/data/3306 2>/home/mysql/backup/data/3306/tar.gz.log | gzip >/home/mysql/backup/data/3306/mysql20110518.tar.gz

需要注意的是,在解包备份文件的时候必须使用-i参数。

源码安装XtraBackup备份MySQL5.5数据库

XtraBackup是Percona公司搞的一个开源的备份工具,是目前开源软件里做的最好的一款MySQL备份工具,在全球有着广泛的用户群体。

前天(2011年4月12日)刚刚发布的版本是1.6,官方页面显示的链接地址还有点问题,实际的下载的地址是:

http://www.percona.com/downloads/XtraBackup/

我下载的是源码:http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz

[root@localhost backup]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz
–16:30:34–  http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz
Resolving www.percona.com… 65.49.72.130
Connecting to www.percona.com|65.49.72.130|:80… connected.
HTTP request sent, awaiting response… 302 Found
Location: /downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz [following]
–16:30:35–  http://www.percona.com/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz
Reusing existing connection to www.percona.com:80.
HTTP request sent, awaiting response… 200 OK
Length: 49867645 (48M) [application/x-gzip]
Saving to: `xtrabackup-1.6.tar.gz’
100%[=====================>] 49,867,645  61.5K/s   in 16m 8s
16:46:45 (50.3 KB/s) – `xtrabackup-1.6.tar.gz’ saved [49867645/49867645]

[root@localhost backup]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz

–16:30:34–  http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz

Resolving www.percona.com… 65.49.72.130

Connecting to www.percona.com|65.49.72.130|:80… connected.

HTTP request sent, awaiting response… 302 Found

Location: /downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz [following]

–16:30:35–  http://www.percona.com/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz

Reusing existing connection to www.percona.com:80.

HTTP request sent, awaiting response… 200 OK

Length: 49867645 (48M) [application/x-gzip]

Saving to: `xtrabackup-1.6.tar.gz’

100%[=============>] 49,867,645  61.5K/s   in 16m 8s

16:46:45 (50.3 KB/s) – `xtrabackup-1.6.tar.gz’ saved [49867645/49867645]

之所以这么大,是因为这个包里打包了编译需要的两个MySQL Source, 从方便用户的角度看这样也无可厚非,比如上一个1.5版本就必须和MySQL5.5.8版本的源码编译,我在5.5.9下打patch的时候会报错。

[root@localhost backup]# tar zxvf xtrabackup-1.6.tar.gz

[root@localhost backup]# cd xtrabackup-1.6

[root@localhost xtrabackup-1.6]# ll

total 48112

-rw-r–r– 1 banping banping      658 Apr 12 21:32 BUILD.txt

-rw-r–r– 1 banping banping    19070 Apr 12 21:32 COPYING

drwxr-xr-x 2 banping banping     4096 Apr 12 21:32 doc

-rwxr-xr-x 1 banping banping    91146 Apr 12 21:32 innobackupex

lrwxrwxrwx 1 banping banping       12 Apr 14 17:12 innobackupex-1.5.1 -> innobackupex

-rw-r–r– 1 banping banping   145354 Apr 12 21:34 libtar-1.2.11.tar.gz

-rw-r–r– 1 banping banping     7976 Apr 12 21:32 Makefile

-rw-r–r– 1 banping banping 24795624 Apr 12 21:34 mysql-5.1.56.tar.gz

-rw-r–r– 1 banping banping 23877968 Apr 12 21:34 mysql-5.5.10.tar.gz

drwxr-xr-x 2 banping banping     4096 Apr 12 21:32 patches

drwxr-xr-x 6 banping banping     4096 Apr 12 21:32 test

drwxr-xr-x 4 banping banping     4096 Apr 12 21:32 utils

-rw-r–r– 1 banping banping   174073 Apr 12 21:32 xtrabackup.c

没有文档说怎么安装,官方的文档都还没更新,自己摸索了一下,搞明白怎么用了。这里可见包里已经封装了innobackupex程序,这是个可执行程序,可以备份包括MyISAM、InnoDB和XtraDB在内的一个完整数据库实例,同时它封装了对xtrabackup的调用,其实就是一个perl写的脚本。而xtrabackup就是备份innodb和xtradb的程序。那么接下来我们要做的就是编译生成这个xtrabackup可执行文件。

[root@localhost xtrabackup-1.6]# utils/build.sh innodb55

……

[root@localhost xtrabackup-1.6]# ll

total 48140

-rw-r–r–  1 banping banping      658 Apr 12 21:32 BUILD.txt

-rw-r–r–  1 banping banping    19070 Apr 12 21:32 COPYING

drwxr-xr-x  2 banping banping     4096 Apr 12 21:32 doc

-rwxr-xr-x  1 banping banping    91146 Apr 12 21:32 innobackupex

lrwxrwxrwx  1 banping banping       12 Apr 14 17:12 innobackupex-1.5.1 -> innobackupex

drwxr-xr-x  9    2000 users       4096 Apr 14 20:03 libtar-1.2.11

-rw-r–r–  1 banping banping   145354 Apr 12 21:34 libtar-1.2.11.tar.gz

-rw-r–r–  1 banping banping     7976 Apr 12 21:32 Makefile

-rw-r–r–  1 banping banping 24795624 Apr 12 21:34 mysql-5.1.56.tar.gz

drwxr-xr-x 32    7161 wheel       4096 Apr 14 20:01 mysql-5.5.10

-rw-r–r–  1 banping banping 23877968 Apr 12 21:34 mysql-5.5.10.tar.gz

drwxr-xr-x  2 banping banping     4096 Apr 12 21:32 patches

-rw-r–r–  1 root    root           0 Apr 14 19:58 stderr

-rw-r–r–  1 root    root         339 Apr 14 19:58 stdout

drwxr-xr-x  6 banping banping     4096 Apr 12 21:32 test

drwxr-xr-x  4 banping banping     4096 Apr 14 17:18 utils

-rw-r–r–  1 banping banping   174073 Apr 12 21:32 xtrabackup.c

这个脚本会根据我们的参数选择解包5.5.10版本的mysql代码,然后在对应的目录生成xtrabackup程序:

[root@localhost xtrabackup-1.6]# cd mysql-5.5.10/storage/innobase/xtrabackup/

[root@localhost xtrabackup]# ll

total 11448

-rw-r–r– 1 root root     7976 Apr 14 20:03 Makefile

-rw-r–r– 1 root root   174073 Apr 14 20:03 xtrabackup.c

-rwxr-xr-x 1 root root 11064287 Apr 14 20:03 xtrabackup_innodb55

-rw-r–r– 1 root root   430024 Apr 14 20:03 xtrabackup.o

这个xtrabackup_innodb55文件就是我们备份innodb的工具了。

由于我的MySQL是源码安装的,可以建立2个软链接:

[root@localhost bin]# ln -s /home/mysql/3306/bin/mysql /usr/bin/mysql

[root@localhost xtrabackup-1.6]# ln -s /home/mysql/backup/xtrabackup-1.6/xtrabackup_innodb55 /usr/bin/xtrabackup_55

然后就可以用innobackupex程序备份数据库了,注意的是my.cnf里datadir这个参数是必须要指定的,xtrabackup_55根据它去定位innodb数据文件的位置。当然如果直接用xtrabackup_55这个程序可以通过参数来指定。

[root@localhost xtrabackup-1.6]# ./innobackupex –host=192.168.0.35 –port=3306 –user=banpingdbuser –password=mypassword –databases=banpingdb –defaults-file=/home/mysql/3306/my.cnf /tmp/mysqlbackup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona Inc 2009-2011.  All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

110414 20:18:55  innobackupex: Starting mysql with options:  –defaults-file=’/home/mysql/3306/my.cnf’ –password=’mypassword’ –user=’banpingdbuser’ –host=’192.168.0.35′ –port=’3306′ –unbuffered –

110414 20:18:55  innobackupex: Connected to database with mysql child process (pid=21752)

110414 20:19:01  innobackupex: Connection to database server closed

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

prints “completed OK!”.

innobackupex: Using mysql  Ver 14.14 Distrib 5.5.9, for Linux (x86_64) using readline 5.1

innobackupex: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /tmp/mysqlbackup/2011-04-14_20-19-01

110414 20:19:01  innobackupex: Starting mysql with options:  –defaults-file=’/home/mysql/3306/my.cnf’ –password=’mypassword’ –user=’banpingdbuser’ –host=’192.168.0.35′ –port=’3306′ –unbuffered –

110414 20:19:01  innobackupex: Connected to database with mysql child process (pid=21783)

110414 20:19:05  innobackupex: Connection to database server closed

110414 20:19:05  innobackupex: Starting ibbackup with command: xtrabackup_55  –defaults-file=”/home/mysql/3306/my.cnf” –backup –suspend-at-end –target-dir=/tmp/mysqlbackup/2011-04-14_20-19-01

innobackupex: Waiting for ibbackup (pid=21809) to suspend

innobackupex: Suspend file ‘/tmp/mysqlbackup/2011-04-14_20-19-01/xtrabackup_suspended’

xtrabackup_55  Ver undefined Rev undefined for 5.5.10 Linux (x86_64)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/mysql/3306/data

xtrabackup: Target instance is assumed as followings.

xtrabackup:   innodb_data_home_dir = /home/mysql/3306/data

xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:   innodb_log_group_home_dir = /home/mysql/3306/redolog

xtrabackup:   innodb_log_files_in_group = 3

xtrabackup:   innodb_log_file_size = 5242880

110414 20:19:05 InnoDB: Using Linux native AIO

110414 20:19:06  InnoDB: Warning: allocated tablespace 286, old maximum was 0

>> log scanned up to (188888442)

[01] Copying /home/mysql/3306/data/ibdata1

to /tmp/mysqlbackup/2011-04-14_20-19-01/ibdata1

[01]        …done

[01] Copying ./banpingdb/sys_post_type.ibd

to /tmp/mysqlbackup/2011-04-14_20-19-01/banpingdb/sys_post_type.ibd

[01]        …done

[01] Copying ./banpingdb/w_board.ibd

to /tmp/mysqlbackup/2011-04-14_20-19-01/banpingdb/w_board.ibd

[01]        …done

[01] Copying ./test/t.ibd

to /tmp/mysqlbackup/2011-04-14_20-19-01/test/t.ibd

[01]        …done

110414 20:19:15  innobackupex: Continuing after ibbackup has suspended

110414 20:19:15  innobackupex: Starting mysql with options:  –defaults-file=’/home/mysql/3306/my.cnf’ –password=’mypassword’ –user=’banpingdbuser’ –host=’192.168.0.35′ –port=’3306′ –unbuffered –

110414 20:19:15  innobackupex: Connected to database with mysql child process (pid=21827)

>> log scanned up to (188888855)

110414 20:19:19  innobackupex: Starting to lock all tables…

>> log scanned up to (188889267)

>> log scanned up to (188889267)

110414 20:19:29  innobackupex: All tables locked and flushed to disk

110414 20:19:29  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectories of ‘/home/mysql/3306/data’

innobackupex: Backing up files ‘/home/mysql/3306/data/banpingdb/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (129 files)

110414 20:19:30  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): ’188889267′

>> log scanned up to (188889267)

xtrabackup: Stopping log copying thread.

xtrabackup: Transaction log of lsn (188887635) to (188889267) was copied.

110414 20:19:32  innobackupex: All tables unlocked

110414 20:19:32  innobackupex: Connection to database server closed

innobackupex: Backup created in directory ‘/tmp/mysqlbackup/2011-04-14_20-19-01′

innobackupex: MySQL binlog position: filename ‘bin.000011′, position 45557372           mysql,information_schema,performance_schema

110414 20:19:32  innobackupex: completed OK!

参考文档:

http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual

MySQL查询返回随机记录

很多人使用order by rand()的方式随机返回一些记录,这其实是非常低效的,示例一下:

mysql> explain select id from u_user where id> round(rand()*(select max(id) from u_user)) limit 5;
+—-+————-+——–+——-+—————+———+———+——+——+——————————+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |
+—-+————-+——–+——-+—————+———+———+——+——+——————————+
|  1 | PRIMARY     | u_user | index | NULL          | PRIMARY | 4       | NULL |   88 | Using where; Using index     |
|  2 | SUBQUERY    | NULL   | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Select tables optimized away |
+—-+————-+——–+——-+—————+———+———+——+——+——————————+
2 rows in set (0.00 sec)
mysql> explain select id from u_user order by rand() limit 5;
+—-+————-+——–+——-+—————+———+———+——+——+———————————————-+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+—-+————-+——–+——-+—————+———+———+——+——+———————————————-+
|  1 | SIMPLE      | u_user | index | NULL          | PRIMARY | 4       | NULL |   88 | Using index; Using temporary; Using filesort |
+—-+————-+——–+——-+—————+———+———+——+——+———————————————-+
1 row in set (0.00 sec)

mysql> explain select id from users order by rand() limit 5;

+—-+————-+——–+——-+—————+———+———+——+——+———————————————-+

| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |

+—-+————-+——–+——-+—————+———+———+——+——+———————————————-+

|  1 | SIMPLE      | u_user | index | NULL          | PRIMARY | 4       | NULL |   88 | Using index; Using temporary; Using filesort |

+—-+————-+——–+——-+—————+———+———+——+——+———————————————-+

1 row in set (0.00 sec)

可以改用子查询的方式:

mysql> explain select id from users where id> round(rand()*(select max(id) from users)) limit 5;

+—-+————-+——–+——-+—————+———+———+——+——+——————————+

| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |

+—-+————-+——–+——-+—————+———+———+——+——+——————————+

|  1 | PRIMARY     | u_user | index | NULL          | PRIMARY | 4       | NULL |   88 | Using where; Using index     |

|  2 | SUBQUERY    | NULL   | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Select tables optimized away |

+—-+————-+——–+——-+—————+———+———+——+——+——————————+

2 rows in set (0.00 sec)

关键是消除了临时表排序,记录一下。

搭建MySQL Dual Master环境

搭建Dual Master其实和Msater Slave是一样的,就是反过去再做一遍而已,互为主从。
1、修改配置文件,一般要注意以下几个参数:
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = information_schema
binlog_format=mixed
server-id       = 1
其中replicate开头的几个参数只影响从库。
2、建立一个专门的用于复制的用户,锁主库表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to ‘repl’@'%’ identified by ‘slave’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
3、导数据
mysql> show master status;
+————+———-+————–+———————————————+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+————+———-+————–+———————————————+
| bin.000010 |   190034 |              | mysql,information_schema,performance_schema |
+————+———-+————–+———————————————+
1 row in set (0.00 sec)
[root@localhost 3306]# bin/mysqldump rtdb –master-data -u root -p>/tmp/rtdb3306.db;
这个master-data参数就是在导出的文件里显示CHANGE MASTER TO的信息,可以注释掉以后手工执行,也可以看到这里的信息和刚才show master status的输出是一致的:
[root@localhost 3306]# vi /tmp/rtdb3306.db
– CHANGE MASTER TO MASTER_LOG_FILE=’bin.000010′, MASTER_LOG_POS=190034;
4、释放表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
5、启动从库
[root@localhost 3308]# bin/mysqld_safe –user=mysql –skip-slave-start &
[1] 21925
[root@localhost 3308]# 110319 15:11:08 mysqld_safe Logging to ‘/home/mysql/3308/mysql_error.log’.
110319 15:11:08 mysqld_safe Starting mysqld daemon with databases from /home/mysql/3308/data
6、导入数据
[root@localhost 3308]# bin/mysql rtdb -u root -p</tmp/rtdb3306.db
7、登入从库修改master信息,如果报错,先删掉data目录下的master.info和relay-log.info
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.0.35′,
-> MASTER_PORT = 3308,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’rtslave’,
-> MASTER_LOG_FILE=’bin.000010′,
-> MASTER_LOG_POS=190034;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
8、查看show slave statusG输出,注意2个线程的状态
9、反过来再做一遍
10、在两边insert数据进行测试

如何通过show slave status的输出使用change master to命令

在MySQL的master-slave或dual master的架构中,要重新开始复制可以使用change master to命令,而参数的选择可以来自简单的show slave status命令,举例说明如下:

mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.2.216
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 2218353
Relay_Log_File: relaylog.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2218353
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master ‘repl@192.168.2.216:3306′ – retry-time: 60  retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

mysql> show slave statusG

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.2.216

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: bin.000001

Read_Master_Log_Pos: 2218353

Relay_Log_File: relaylog.000003

Relay_Log_Pos: 4

Relay_Master_Log_File: bin.000001

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,performance_schema,information_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2218353

Relay_Log_Space: 107

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master ‘repl@192.168.2.216:3306′ – retry-time: 60  retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

1 row in set (0.00 sec)

我们看到这个库无法和Master数据库连接了,这时可以先停掉复制:

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

我们都知道目前MySQL的版本slave端是通过2个线程实现复制的,一个是I/O Thread负责连接到master接收binlog信息,在从服务器端这部分信息叫做relay log。另一个SQL Thread负责读取relay log并执行其中的event(在statement format的时候其实就是SQL语句)。这从以上输出的信息也能看出来,IO线程在连接但是连不上,而SQL线程正在执行:

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

接下来我们再用change master to命令修改连接参数和复制起始点,其中重要的MASTER_LOG_FILE和MASTER_LOG_POS参数,这两个参数对应的是IO线程生成relay log的信息,因此和Relay_Master_Log_File和Exec_Master_Log_Pos是对应的,具体命令如下:

mysql> CHANGE MASTER TO

-> MASTER_HOST=’192.168.0.35′,

-> MASTER_PORT = 3306,

-> MASTER_USER=’banping_repl’,

-> MASTER_PASSWORD=’slavepassword’,

-> MASTER_LOG_FILE=’bin.000001′,

-> MASTER_LOG_POS=2218353;

Query OK, 0 rows affected (0.00 sec)

然后再开始复制:

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

再查看slave的状态:

mysql> show slave statusG

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.35

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: bin.000002

Read_Master_Log_Pos: 1819899

Relay_Log_File: relaylog.000003

Relay_Log_Pos: 76625

Relay_Master_Log_File: bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,performance_schema,information_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 76485

Relay_Log_Space: 1820328

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 50236

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

可见已经恢复了复制。完整的命令如下:

CHANGE MASTER TO option [, option] ...

option:
    MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)

MySQL的字符集小结

正确了解MySQL的字符集问题,能够从根本上解决乱码的困扰。

首先,MySQL的字符集问题主要是两个概念,一个是Character Sets,一个是Collations,前者是字符内容及编码,后者是对前者进行比较操作的一些规则。这两个参数集可以在数据库实例、单个数据库、表、列等四个级别指定。

对于使用者来说,一般推荐使用utf8编码来存储数据。而要解决乱码问题,不单单是MySQL数据的存储问题,还和用户的程序文件的编码方式、用户程序和MySQL数据库的连接方式都有关系。

首先,MySQL有默认的字符集,这个是安装的时候确定的,在编译MySQL的时候可以通过DEFAULT_CHARSET=utf8和DEFAULT_COLLATION=utf8_general_ci这两个参数(MySQL5.5版本,5.1版本用–with-charset=utf8 –with-collation=utf8_general_ci)来指定默认的字符集为utf8,这也是最一劳永逸的办法,这样指定后,客户端连接到数据库的编码方式也默认是utf8了,应用程序不需要任何处理。

但是遗憾的是,很多人编译安装MySQL的时候没有指定这两个参数,大多数人更是通过二进制程序的方式安装,那么这时候MySQL的默认字符集是latin1。而这时候我们仍然可以指定MySQL的默认字符集,通过my.cnf文件增加两个参数:

character_set_server=utf8
collation_server=utf8_general_ci
init_connect = ‘SET NAMES utf8′
default-character-set = utf8
character_set_server=utf8
collation_server=utf8_general_ci
这样我们建数据库建表的时候就不用特别指定utf8的字符集了。配置文件里的这种写法解决了数据存储和比较的问题,但是对客户端的连接是没有作用的,客户端这时候一般需要指定utf8方式连接才能避免乱码。也就是传说总的set names命令。
事实上,set names utf8命令对应的是服务器端以下几个命令:
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = xutf8;
但这三个参数是不能写在配置文件my.cnf里的。只能通过set命令来动态修改。我们需要的是在配置文件里写好一劳永逸的办法。
那么这时候,是否有在服务端解决问题的办法呢,可行的思路是在init_connect里设置。这个命令在每个普通用户连接上来的时候都会触发执行,可以在[mysqld]部分增加以下一行设置连接字符集:
init_connect = ‘SET NAMES utf8′
这样就简单的解决了问题,但是要注意的是,这个命令对具有super权限的用户是不生效的,可以简单的测试如下:
[root@localhost init.d]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.1.49-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
mysql> show variables like ‘%collation%’;
+———————-+——————-+
| Variable_name        | Value             |
+———————-+——————-+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+———————-+——————-+
3 rows in set (0.00 sec)
mysql> show variables like ‘%character%’;
+————————–+—————————-+
| Variable_name            | Value                      |
+————————–+—————————-+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
我们看到root用户看到的connection相关参数仍然是latin1,而普通用户看到的却是utf8。实际上,对于mysql, mysqladmin, mysqlcheck, mysqlimport和mysqlshow这些客户端命令程序,可以设置以下参数在配置文件的[client]部分,为这些程序指定默认用utf8编码和连接:
default-character-set = utf8
网上很多资料(包括一些大公司的所谓牛人)说加上这一行就能彻底解决乱码问题,这是不正确的,这个参数只对以上几个官方的客户端程序有用,对普通的第三方程序,比如php代码是无效的。
当然,这个参数可以方便DBA的管理,还是很有用的,而这个参数也能解释为什么DBA看到的内容和普通用户看到的内容有时会有差异。
除了数据库的存储,连接数据库的方式,还有就是程序本身的编码也要是utf8方式来保存,当然这与MySQL数据库已经无关了,而是客户端显示的问题。
总结一下就是:
1、首选在编译安装MySQL的时候指定两个参数使用utf8编码。
2、次选在配置文件my.cnf设定两个参数,同时设置init_connect参数。
3、第三在配置文件my.cnf设定两个参数,同时客户端的连接指定set names命令。
4、在配置文件my.cnf里加入default-character-set参数方便管理。

源码安装mysql5.5+HandlerSocket的过程

一、下载相关软件:

MySQL(目前的最新版本是5.5.8)源码:

http://dev.mysql.com/downloads/mysql/

Cmake(MySQL5.5开始使用的编译工具):

http://www.cmake.org/cmake/resources/software.html

bison(一个替代yacc的语法分析程序生成器,用来generate sql_yacc.cc from sql_yacc.yy):

http://www.gnu.org/software/bison/

HandlerSocket Plugin for MySQL:

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

php-handlersocket(php客户端,测试用):

http://code.google.com/p/php-handlersocket/

二、安装MySQL5.5:

[root@localhost handlersocket]# tar zxvf mysql-5.5.8.tar.gz

[root@localhost handlersocket]# tar zxvf cmake-2.8.3-Linux-i386.tar.gz

[root@localhost bin]# ln -s /home/zoudp/handlersocket/cmake-2.8.3-Linux-i386/bin/ccmake /usr/bin/ccmake

[root@localhost handlersocket]# tar zxvf bison-2.4.tar.gz

[root@localhost handlersocket]# cd bison-2.4

[root@localhost bison-2.4]# ./configure

[root@localhost bison-2.4]# make

[root@localhost bison-2.4]# make install

[root@localhost handlersocket]# cd mysql-5.5.8

[root@localhost mysql-5.5.8]#  cmake . -DCMAKE_INSTALL_PREFIX=/home/zoudp/handlersocket/mysql558/ -DSYSCONFDIR=/home/zoudp/handlersocket/mysql558/ -DWITH_EMBEDDED_SERVER=on -DWITH_READLINE=on -DWITH_SSL=yes -DENABLED_LOCAL_INFILE=on -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1

[root@localhost mysql-5.5.8]# make

[root@localhost mysql-5.5.8]# make install

这里要注意的是,如果之前编译失败过,官方的文档说执行以下代码清理:

shell> make clean
shell> rm CMakeCache.txt

shell> make clean

shell> rm CMakeCache.txt

而实际上这样是清理不完整的,建议直接删除源码,重新解压一个新鲜的源码出来编译。否则会遇到很多莫名其妙的错误。

还有DSYSCONFDIR这个参数会害死人的,mysqld仍然优先去找/etc/my.cnf而不是这里设置的路径,这是一直以来的一个bug.

三、配置MySQL:

[root@localhost handlersocket]# cd mysql558

[root@localhost mysql558]# vi my.cnf

# The following options will be passed to all MySQL clients

[client]

#password       = your_password

port            = 3316

socket          = /home/zoudp/handlersocket/mysql558/mysql.sock

default-character-set=utf8

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port            = 3316

socket          = /home/zoudp/handlersocket/mysql558/mysql.sock

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

character-set-server=utf8

collation-server=utf8_general_ci

basedir=/home/zoudp/handlersocket/mysql558

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# binary logging format – mixed recommended

binlog_format=mixed

server-id       = 1

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /home/zoudp/handlersocket/mysql558/data

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /home/zoudp/handlersocket/mysql558/data

# You can set .._buffer_pool_size up to 50 – 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 16M

innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

四、安装MySQL授权表、启动数据库测试并设置root用户密码:

[root@localhost mysql558]# chown -R mysql .

[root@localhost mysql558]# chgrp -R mysql .

[root@localhost mysql558]# scripts/mysql_install_db –defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf –user=mysql

[root@localhost mysql558]# bin/mysqld_safe –defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf –user=mysql &

[root@localhost mysql558]# bin/mysqladmin version

bin/mysqladmin  Ver 8.42 Distrib 5.5.8, for Linux on i686

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Server version          5.5.8-log

Protocol version        10

Connection              Localhost via UNIX socket

UNIX socket             /home/zoudp/handlersocket/mysql558/mysql.sock

Uptime:                 2 min 46 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.6

[root@localhost mysql558]# bin/mysqladmin -u root shutdown

[root@localhost mysql558]# bin/mysqld_safe –defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf –user=mysql &

[root@localhost mysql558]# bin/mysqlshow

+——————–+

|     Databases      |

+——————–+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+——————–+

[root@localhost mysql558]# bin/mysql -u root

mysql> set password for ‘root’@'localhost’=password(‘banping’);

Query OK, 0 rows affected (0.00 sec)

五、安装HandlerSocket Plugin:

[root@localhost handlersocket]# tar -zxvf ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-67-g25f4957.tar.gz

[root@localhost handlersocket]# cd handlersocket/

[root@localhost handlersocket]# ./autogen.sh

[root@localhost handlersocket]# ./configure –with-mysql-source=/home/zoudp/handlersocket/mysql-5.5.8 –with-mysql-bindir=/home/zoudp/handlersocket/mysql558/bin  –with-mysql-plugindir=/home/zoudp/handlersocket/mysql558/lib/plugin

[root@localhost handlersocket]# make

[root@localhost handlersocket]# make install

在my.cnf配置文件增加以下内容:

[root@localhost mysql558]# vi my.cnf

# The MySQL server

[mysqld]

……

loose_handlersocket_port = 9998

# the port number to bind to (for read requests)

loose_handlersocket_port_wr = 9999

# the port number to bind to (for write requests)

loose_handlersocket_threads = 16

# the number of worker threads (for read requests)

loose_handlersocket_threads_wr = 1

# the number of worker threads (for write requests)

open_files_limit = 65535

# to allow handlersocket accept many concurrent

# connections, make open_files_limit as large as

# possible.

登录mysql安装这个plugin:

[root@localhost mysql558]# bin/mysqladmin -u root shutdown -p

[root@localhost mysql558]# bin/mysqld_safe –defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf –user=mysql &

[root@localhost mysql558]# bin/mysql -u root -p

mysql> install plugin handlersocket soname ‘handlersocket.so’;

Query OK, 0 rows affected (0.01 sec)

mysql> show processlist;

+—-+————-+—————–+—————+———+——+——————————————-+——————+

| Id | User        | Host            | db            | Command | Time | State                                     | Info             |

+—-+————-+—————–+—————+———+——+——————————————-+——————+

|  1 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | show processlist |

|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 17 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |

| 18 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |

+—-+————-+—————–+—————+———+——+——————————————-+——————+

18 rows in set (0.00 sec)

mysql> show plugins;

+———————–+——–+——————–+——————+———+

| Name                  | Status | Type               | Library          | License |

+———————–+——–+——————–+——————+———+

| binlog                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| mysql_native_password | ACTIVE | AUTHENTICATION     | NULL             | GPL     |

| mysql_old_password    | ACTIVE | AUTHENTICATION     | NULL             | GPL     |

| CSV                   | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| MyISAM                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| MEMORY                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| MRG_MYISAM            | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| PERFORMANCE_SCHEMA    | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| BLACKHOLE             | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| InnoDB                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| INNODB_TRX            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| INNODB_LOCKS          | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| INNODB_LOCK_WAITS     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| INNODB_CMP            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| INNODB_CMP_RESET      | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| INNODB_CMPMEM         | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| INNODB_CMPMEM_RESET   | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |

| partition             | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |

| handlersocket         | ACTIVE | DAEMON             | handlersocket.so | BSD     |

+———————–+——–+——————–+——————+———+

19 rows in set (0.00 sec)

六、安装php扩展包测试

[root@localhost handlersocket]# tar zxvf php-handlersocket-0.0.7.tar.gz

[root@localhost php-handlersocket]# /usr/local/php533/bin/phpize

[root@localhost php-handlersocket]# ./configure –with-php-config=/usr/local/php533/bin/php-config

[root@localhost php-handlersocket]# make

[root@localhost php-handlersocket]# make install

[root@localhost php-handlersocket]# cd /usr/local/php533/etc

[root@localhost etc]# vi php.ini

extension = “handlersocket.so”

[root@localhost etc]# ps -ef|grep fpm

nobody     394 24696  0 Jan14 ?        00:00:32 /usr/local/php533/sbin/php-fpm

nobody     419 24696  0 Jan14 ?        00:00:31 /usr/local/php533/sbin/php-fpm

nobody     485 24696  0 Jan14 ?        00:00:32 /usr/local/php533/sbin/php-fpm

nobody     486 24696  0 Jan14 ?        00:00:32 /usr/local/php533/sbin/php-fpm

nobody     520 24696  0 Jan14 ?        00:00:39 /usr/local/php533/sbin/php-fpm

nobody     546 24696  0 Jan14 ?        00:00:31 /usr/local/php533/sbin/php-fpm

nobody     591 24696  0 Jan14 ?        00:00:32 /usr/local/php533/sbin/php-fpm

nobody     824 24696  0 Jan14 ?        00:00:30 /usr/local/php533/sbin/php-fpm

nobody     829 24696  0 Jan14 ?        00:00:31 /usr/local/php533/sbin/php-fpm

nobody     928 24696  0 Jan14 ?        00:00:30 /usr/local/php533/sbin/php-fpm

nobody    8274 24696  0 Jan14 ?        00:00:28 /usr/local/php533/sbin/php-fpm

nobody    8568 24696  0 Jan14 ?        00:00:28 /usr/local/php533/sbin/php-fpm

nobody    9083 24696  0 Jan14 ?        00:00:28 /usr/local/php533/sbin/php-fpm

nobody    9421 24696  0 Jan14 ?        00:00:27 /usr/local/php533/sbin/php-fpm

nobody   11369 24696  0 Jan14 ?        00:00:27 /usr/local/php533/sbin/php-fpm

root     22589 28942  0 14:45 pts/3    00:00:00 grep fpm

root     24696     1  0  2010 ?        00:00:33 /usr/local/php533/sbin/php-fpm

nobody   28846 24696  0 Jan19 ?        00:00:02 /usr/local/php533/sbin/php-fpm

[root@localhost etc]# kill -SIGINT 24696

[root@localhost etc]# /usr/local/php533/sbin/php-fpm

建立测试表:

mysql> create table t (id int(10) unsigned primary key,k varchar(10),v varchar(10));

Query OK, 0 rows affected (0.03 sec)

mysql> desc t;

+——-+——————+——+—–+———+——-+

| Field | Type             | Null | Key | Default | Extra |

+——-+——————+——+—–+———+——-+

| id    | int(10) unsigned | NO   | PRI | NULL    |       |

| k     | varchar(10)      | YES  |     | NULL    |       |

| v     | varchar(10)      | YES  |     | NULL    |       |

+——-+——————+——+—–+———+——-+

3 rows in set (0.00 sec)

mysql> insert into t values(1,’k1′,’v1′);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,’k2′,’v2′);

Query OK, 1 row affected (0.01 sec)

mysql> create index inx_k on t(k);

Query OK, 0 rows affected (0.70 sec)

Records: 0  Duplicates: 0  Warnings: 0

用php程序测试:

[root@localhost banping]# vi test.php

<?php

$host = ‘localhost’;

$port = 9998;

$port_wr = 9999;

$dbname = ‘test’;

$table = ‘t’;

//GET

$hs = new HandlerSocket($host, $port);

if (!($hs->openIndex(1, $dbname, $table,’inx_k’, ‘k,v’)))

{

echo $hs->getError(), PHP_EOL;

die();

}

$retval = $hs->executeSingle(1, ‘=’, array(‘k1′), 1, 0);

var_dump($retval);

$retval = $hs->executeMulti(

array(array(1, ‘=’, array(‘k1′), 1, 0),

array(1, ‘=’, array(‘k2′), 1, 0)));

var_dump($retval);

unset($hs);

?>

运行这个php查看输出结果:

array(1) { [0]=> array(2) { [0]=> string(2) “k1″ [1]=> string(2) “v1″ } } array(2) { [0]=> array(1) { [0]=> array(2) { [0]=> string(2) “k1″ [1]=> string(2) “v1″ } } [1]=> array(1) { [0]=> array(2) { [0]=> string(2) “k2″ [1]=> string(2) “v2″ } } }


至此测试完成,这个功能可用了,但是个人认为,用PHP程序不能最大限度的发挥HandlerSocket for MySQL的性能,因为建立一个HandlerSocket的openIndex后,这个openIndex最好能够不断的重用,这样能最大限度的减少开销,那么客户端的数据库连接池就是实现这一功能的最好方式。但php由于程序架构上的设计,单个请求完成就释放掉全部资源,并不支持连接池,所以只能每个请求就open一个index.所以和java等其他语言比,效率会低一些。

解析MySQL与连接数相关的几个参数

MySQL的variables和status是管理维护的利器,就类似Oracle的spfile和v$表。

MySQL通过系统变量记录很多配置信息,比如最大连接数max_connections:

mysql> show variables like ‘%connect%’;
+————————–+—————–+
| Variable_name | Value |
+————————–+—————–+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| init_connect | SET NAMES utf8 |
| max_connect_errors | 10 |
| max_connections | 200 |
| max_user_connections | 0 |
+————————–+—————–+
7 rows in set (0.00 sec)

这个参数是指同时连接上来的客户端数量,在5.1版本里默认的值是151,那么实际支持的连接数是这个值加一,也就是152,因为要为系统管理员登录上来查看信息保留一个连接。这个参数的大小要综合很多因素来考虑,比如使用的平台所支持的线程库数量(windows只能支持到2048)、服务器的配置(特别是内存大小)、每个连接占用资源(内存和负载)的多少、系统需要的响应时间等。一般Linux系统支持到几百并发是没有任何问题的。可以在global或session范围内修改这个参数:

mysql> set global max_connections=151;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘%connect%’;
+————————–+—————–+
| Variable_name | Value |
+————————–+—————–+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| init_connect | SET NAMES utf8 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_user_connections | 0 |
+————————–+—————–+
7 rows in set (0.00 sec)

但是要注意的是,连接数的增加会带来很多连锁反应,需要在实际中避免由此产生的负面影响。

首先我们看一下status的输出:

mysql> status
————–
mysql Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1

Connection id: 255260
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.1.49-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 161 days 3 hours 42 min 38 sec

Threads: 14 Questions: 160655492 Slow queries: 71 Opens: 8124 Flush tables: 3 Open tables: 64 Queries per second avg: 11.538
————–

这里有个Open tables输出时64,这就是说当前数据库打开的表的数量是64个,要注意的是这个64并不是实际的64个表,因为MySQL是多线程的系统,几个不同的并发连接可能打开同一个表,这就需要为不同的连接session分配独立的内存空间来存储这些信息以避免冲突。因此连接数的增加会导致MySQL需要的文件描述符数目的增加。另外对于MyISAM表,还会建立一个共享的索引文件描述符。

那么在MySQL数据库层面,有几个系统参数决定了可同时打开的表的数量和要使用的文件描述符,那就是table_open_cache、max_tmp_tables和open_files_limit.

mysql> show variables like ‘table_open%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| table_open_cache | 64    |
+——————+——-+
1 row in set (0.00 sec)
这里的table_open_cache 参数是64,这就是说所有的MySQL线程一共能同时打开64个表,我们可以搜集系统的打开表的数量的历史记录和这个参数来对比,决定是否要增加这个参数的大小。查看当前的打开表的数目的办法一个是用上边提到过的status命令,另外可以直接查询这个系统变量的值:
mysql> show status like ‘open%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| Open_files               | 3     |
| Open_streams             | 0     |
| Open_table_definitions   | 8     |
| Open_tables              | 8     |
| Opened_files             | 91768 |
| Opened_table_definitions | 0     |
| Opened_tables            | 0     |
+————————–+——-+
7 rows in set (0.00 sec)
mysql> show global status like ‘open%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| Open_files               | 3     |
| Open_streams             | 0     |
| Open_table_definitions   | 10    |
| Open_tables              | 11    |
| Opened_files             | 91791 |
| Opened_table_definitions | 1211  |
| Opened_tables            | 8158  |
+————————–+——-+
7 rows in set (0.00 sec)
这里有Open_tables就是当前打开表的数目,通过flush tables命令可以关闭当前打开的表。而全局范围内查看的Opened_tables是个历史累计值。 这个值如果过大,并且如果没有经常的执行flush tables命令,可以考虑增加table_open_cache参数的大小。
接下来看max_tmp_tables 参数:
mysql> show variables like ‘max_tmp%’;
+—————-+——-+
| Variable_name  | Value |
+—————-+——-+
| max_tmp_tables | 32    |
+—————-+——-+
1 row in set (0.00 sec)
这个参数指定的是单个客户端连接能打开的临时表数目。查看当前已经打开的临时表信息:
mysql> show global status like ‘%tmp%table%’;
+————————-+——-+
| Variable_name           | Value |
+————————-+——-+
| Created_tmp_disk_tables | 10478 |
| Created_tmp_tables      | 25860 |
+————————-+——-+
2 rows in set (0.00 sec)
也可以对比这两个值来判断临时表的创建位置,一般选取BLOB和TEXT列、Group by 和 Distinct语句的数据量超过512 bytes,或者union的时候select某列的数据超过512 bytes的时候,就直接在磁盘上创建临时表了,另外内存中的临时表变大的时候,也可能被MySQL自动转移到磁盘上(由tmp_table_size和max_heap_table_size参数决定)。

继续原来的讨论,增加table_open_cache或max_tmp_tables 参数的大小后,从操作系统的角度看,mysqld进程需要使用的文件描述符的个数就要相应的增加,这个是由open_files_limit参数控制的。但是这个参数是OS限制的,所以我们设定的值并不一定总是生效。如果OS限制MySQL不能修改这个值,那么置为0。如果是专用的MySQL服务器上,这个值一般要设置的尽量大,就是没有报Too many open files错误的最大值,这样就能一劳永逸了。当操作系统无法分配足够的文件描述符的时候,mysqld进程会在错误日志里记录警告信息。

mysql> show variables like ‘open_files%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| open_files_limit | 1024  |
+——————+——-+
1 row in set (0.00 sec)

mysql> show variables like ‘open_files%’;

+——————+——-+

| Variable_name    | Value |

+——————+——-+

| open_files_limit | 1024  |

+——————+——-+

1 row in set (0.00 sec)

对应的,有两个状态变量记录了当前和历史的文件打开信息:
mysql> show global status like ‘%open%file%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_files    | 3     |
| Opened_files  | 91799 |
+—————+——-+
2 rows in set (0.01 sec)
MySQL为每个连接分配线程来处理,可以通过threads_connected参数查看当前分配的线程数量:

mysql> show status like ‘%thread%’;
+————————+——–+
| Variable_name | Value |
+————————+——–+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 14 |
| Threads_created | 255570 |
| Threads_running | 2 |
+————————+——–+
6 rows in set (0.00 sec)

比较这个threads_connected参数和前面提到的max_connections参数,也可以作为目前的系统负载的参照,决定是否需要修改连接数。

如果查看每个thread的更详细的信息,可以使用processlist命令:

mysql> show processlist;
+——–+———–+——————–+———-+————-+———-+—————————————————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+——–+———–+——————–+———-+————-+———-+—————————————————————-+——————+
| 8293 | repl | 192.168.0.33:47208 | NULL | Binlog Dump | 11574424 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 140991 | mogile | 192.168.0.33:41714 | mogilefs | Sleep | 0 | | NULL |
| 140992 | mogile | 192.168.0.33:41715 | mogilefs | Sleep | 3 | | NULL |
| 140993 | mogile | 192.168.0.33:41722 | mogilefs | Sleep | 2 | | NULL |
| 140994 | mogile | 192.168.0.33:41723 | mogilefs | Sleep | 1 | | NULL |
| 140995 | mogile | 192.168.0.33:41724 | mogilefs | Sleep | 3 | | NULL |
| 254914 | mogile | 192.168.0.33:43028 | mogilefs | Sleep | 11074 | | NULL |
| 254915 | mogile | 192.168.0.33:43032 | mogilefs | Sleep | 11091 | | NULL |
| 255144 | mogile | 192.168.0.33:47514 | mogilefs | Sleep | 11090 | | NULL |
| 255157 | mogile | 192.168.0.33:47535 | mogilefs | Sleep | 11087 | | NULL |
| 255162 | mogile | 192.168.0.33:47549 | mogilefs | Sleep | 11074 | | NULL |
| 255260 | root | localhost | mysql | Query | 0 | NULL | show processlist |
| 255352 | maopaodev | 192.168.0.78:55399 | maopaodb | Sleep | 3172 | | NULL |
| 255353 | maopaodev | 192.168.0.78:55400 | NULL | Sleep | 8926 | | NULL |
+——–+———–+——————–+———-+————-+———-+—————————————————————-+——————+
14 rows in set (0.00 sec)

执行这个命令需要有Process_priv权限,具体的权限分配信息可以查看mysql.user表。

对于影响系统运行的thread,可以狠一点,用kill connection|query threadid的命令杀死它。

MySQL的Create Table and Alter Table语句

MySQL可以利用其他的已有的表结构创建一个新表:

mysql> desc u_mail_receive;
+——————-+———————+——+—–+———————+—————————–+
| Field | Type | Null | Key | Default | Extra |
+——————-+———————+——+—–+———————+—————————–+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| create_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| update_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sender_uid | int(10) unsigned | NO | | NULL | |
| sender_username | varchar(32) | YES | | NULL | |
| receiver_uid | int(10) unsigned | NO | | NULL | |
| receiver_username | varchar(32) | YES | | NULL | |
| content | varchar(1000) | NO | | NULL | |
| status | tinyint(1) unsigned | NO | | 0 | |
| title | varchar(100) | NO | | NULL | |
+——————-+———————+——+—–+———————+—————————–+
10 rows in set (0.01 sec)

mysql> create table u_message as select * from u_mail_receive where 1=2;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc u_message;
+——————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+———————+——+—–+———————+——-+
| id | int(10) unsigned | NO | | 0 | |
| create_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| update_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| sender_uid | int(10) unsigned | NO | | NULL | |
| sender_username | varchar(32) | YES | | NULL | |
| receiver_uid | int(10) unsigned | NO | | NULL | |
| receiver_username | varchar(32) | YES | | NULL | |
| content | varchar(1000) | NO | | NULL | |
| status | tinyint(1) unsigned | NO | | 0 | |
| title | varchar(100) | NO | | NULL | |
+——————-+———————+——+—–+———————+——-+
10 rows in set (0.00 sec)

可见新表继承了原表的数据结构,但是主键、自增、on update等属性并没有继承,要手动来自行添加:

mysql> alter table u_message add primary key (id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc u_message;
+——————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+———————+——+—–+———————+——-+
| id | int(10) unsigned | NO | PRI | 0 | |
| create_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| update_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| sender_uid | int(10) unsigned | NO | | NULL | |
| sender_username | varchar(32) | YES | | NULL | |
| receiver_uid | int(10) unsigned | NO | | NULL | |
| receiver_username | varchar(32) | YES | | NULL | |
| content | varchar(1000) | NO | | NULL | |
| status | tinyint(1) unsigned | NO | | 0 | |
| title | varchar(100) | NO | | NULL | |
+——————-+———————+——+—–+———————+——-+
10 rows in set (0.00 sec)

mysql> alter table u_message modify id int(10) unsigned auto_increment;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc u_message;
+——————-+———————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+——————-+———————+——+—–+———————+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| create_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| update_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| sender_uid | int(10) unsigned | NO | | NULL | |
| sender_username | varchar(32) | YES | | NULL | |
| receiver_uid | int(10) unsigned | NO | | NULL | |
| receiver_username | varchar(32) | YES | | NULL | |
| content | varchar(1000) | NO | | NULL | |
| status | tinyint(1) unsigned | NO | | 0 | |
| title | varchar(100) | NO | | NULL | |
+——————-+———————+——+—–+———————+—————-+
10 rows in set (0.00 sec)

mysql> alter table u_message modify update_time timestamp on update current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc u_message;
+——————-+———————+——+—–+———————+—————————–+
| Field | Type | Null | Key | Default | Extra |
+——————-+———————+——+—–+———————+—————————–+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| create_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| update_time | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
| sender_uid | int(10) unsigned | NO | | NULL | |
| sender_username | varchar(32) | YES | | NULL | |
| receiver_uid | int(10) unsigned | NO | | NULL | |
| receiver_username | varchar(32) | YES | | NULL | |
| content | varchar(1000) | NO | | NULL | |
| status | tinyint(1) unsigned | NO | | 0 | |
| title | varchar(100) | NO | | NULL | |
+——————-+———————+——+—–+———————+—————————–+
10 rows in set (0.00 sec)

另外还有注意的是MySQL的timestamp这种类型,默认的情况下MySQL会把第一个timestamp列的默认值置为CURRENT_TIMESTAMP并且自动添加on update CURRENT_TIMESTAMP属性。如果想让非第一列timestamp有这种特性,那么第一列必须给一个常量作为Default值,例如0.以下是个示例:

mysql> CREATE TABLE ttt (ts1 TIMESTAMP,ts2 TIMESTAMP default 0);
Query OK, 0 rows affected (0.09 sec)

mysql> desc ttt;
+——-+———–+——+—–+———————+—————————–+
| Field | Type | Null | Key | Default | Extra |
+——-+———–+——+—–+———————+—————————–+
| ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts2 | timestamp | NO | | 0000-00-00 00:00:00 | |
+——-+———–+——+—–+———————+—————————–+
2 rows in set (0.00 sec)

MySQL Create Table 和 Alter Table链接:

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

MySQL的varchar能存多少个字符?

MySQL 5.1.X的版本中,官方文档对字符类型的存储空间描述如下:

Data Type Storage Required
CHAR(M) M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set
BINARY(M) M bytes, 0 <= M <= 255
VARCHAR(M)VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOBTINYTEXT L + 1 bytes, where L < 28
BLOBTEXT L + 2 bytes, where L < 216
MEDIUMBLOBMEDIUMTEXT L + 3 bytes, where L < 224
LONGBLOBLONGTEXT L + 4 bytes, where L < 232
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

可见在存储255字节以内时,用1个字节来存储数据长度信息,超过255个字节,则用2个字节来存储长度信息。

而具体能存多少个字符,是和数据库的字符集有关的,简单测试一下在UFT8字符集下能存储的容量:

mysql> select version();
+————+
| version() |
+————+
| 5.1.49-log |
+————+
1 row in set (0.00 sec)

mysql> show create table a;
+——-+———————————————————————————————————+
| Table | Create Table |
+——-+———————————————————————————————————+
| a | CREATE TABLE `a` (
`vv` varchar(5) DEFAULT NULL,
`ww` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+———————————————————————————————————+
1 row in set (0.00 sec)

mysql> alter table a modify vv varchar(10);
Query OK, 8 rows affected (0.10 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> desc a;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| vv | varchar(10) | YES | | NULL | |
| ww | char(10) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set (0.00 sec)

mysql> alter table a modify vv varchar(21834);
Query OK, 8 rows affected (0.12 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> desc a;
+——-+—————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————-+——+—–+———+——-+
| vv | varchar(21834) | YES | | NULL | |
| ww | char(10) | YES | | NULL | |
+——-+—————-+——+—–+———+——-+
2 rows in set (0.00 sec)

长度声明为21834时候是正常的,下面继续测试:

mysql> alter table a modify vv varchar(21835);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> desc a;
+——-+—————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————-+——+—–+———+——-+
| vv | varchar(21834) | YES | | NULL | |
| ww | char(10) | YES | | NULL | |
+——-+—————-+——+—–+———+——-+
2 rows in set (0.00 sec)

这时候修改失败了,可见当前21834个字符是varchar能容纳的最大容量。这个值在不同的环境下可能稍微有差别,因为一个字符在utf8编码下占用的字节是不一样的。继续测试:

mysql> alter table a modify vv varchar(21845);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> alter table a modify vv varchar(21846);
Query OK, 8 rows affected, 2 warnings (0.10 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> show warnings;
+——-+——+———————————————+
| Level | Code | Message |
+——-+——+———————————————+
| Note | 1246 | Converting column ‘vv’ from VARCHAR to TEXT |
| Note | 1246 | Converting column ‘vv’ from VARCHAR to TEXT |
+——-+——+———————————————+
2 rows in set (0.00 sec)

mysql> desc a;
+——-+————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+———+——-+
| vv | mediumtext | YES | | NULL | |
| ww | char(10) | YES | | NULL | |
+——-+————+——+—–+———+——-+
2 rows in set (0.00 sec)

从21835到21845的修改都是失败的,而从21846开始,MySQL会自动把它转化为Text类型来存储。这是有点奇怪的设计。为什么留10个字符的窗口不能修改呢?

MySQL的ERROR 1030 (HY000): Got error 28 from storage engine

有时候操作MySQL的时候会碰到以下错误提示:

ERROR 1030 (HY000): Got error 28 from storage engine

这是因为临时空间不够造成的,增大临时空间就可以解决。

MySQL的临时空间目录是通过–tmpdir参数指定的,默认一般是系统的/tmp目录。临时空间对一个数据库系统来说是很重要的,最好手动指定这个参数,并分配足够大的空间,最好是指定不同盘的多个空间,以提高IO性能。MySQL建立的临时文件都是隐藏的,所以一般不容易在文件系统层面直观的看到。

排序、分组、可能使用临时表的查询和修改表定义都可能使用临时空间:

When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

The row pointer size is usually four bytes, but may grow in the future for really big tables.

For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form SQL_*.

ALTER TABLE creates a temporary table in the same directory as the original table.