生活在別處

xtrabackup 是 percona 的一个开源项目, 可以热备份innodb , XtraDB,和MyISAM(锁表)

下载 Linux binary , 这个还可用.

如果不想麻烦, 可直接将解压后的包里的 bin 下这两个文件全部扔到 /usr/bin 目录下.

[root@localhost bin]# ls -al
total 34108
-rwxr-xr-x 1 root   root      85630 Dec  9 23:15 innobackupex-1.5.1
-rwxr-xr-x 1 root   root    6316740 Dec  9 23:15 xtrabackup_51
 ......

或者将其目录加入到环境变量 $PATH 中, 假设解压后的包放在 /var/tmp/mysql 目录下

[root@localhost bin]PATH="$PATH:/var/tmp/mysql/xtrabackup-1.5/bin"
[root@localhost bin]export $PATH

MySQL bin 同样也需要加入$PATH 中

innobackupex-1.5.1 [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME]
[--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp]
[--ibbackup=IBBACKUP-BINARY] [--slave-info] [--stream=tar]
[--scpopt=OPTIONS-FOR-SCP]
[--defaults-file=MY.CNF]
[--databases=LIST] [--remote-host=HOSTNAME] [--no-lock] BACKUP-ROOT-DIR


innobackupex-1.5.1 --apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]
[--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY] BACKUP-DIR


innobackupex-1.5.1 --copy-back [--defaults-file=MY.CNF] BACKUP-DIR

• --defaults-file=[MY.CNF] 指定 MySQL 的配置文件
• --apply-log 为恢复做准备
• --redo-only --apply-log组, 强制备份日志时只 redo ,跳过 rollback. 这在做增量备份时非常必要
• --copy-back 将数据和日志从备份目录恢复到原始目录
• --remote-host 在指定该参数的情况下, 备份的文件将通过 SSH 连接创建于指定的主机上
• --stream=[tar] 备份文件输出格式, tar 时使用 tar4ibd , 该文件可在 XtarBackup binary 文件中获得
• --tmpdir=DIRECTORY 当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用 MySQL 配置文件中所指定的临时目录tmpdir
• --use-momory=MB 这个用于 ibbackup 子处理 , 即恢复时可用上 MB 的内存
• --parallel=NUMBER-OF-THREADS 这个用于 xtrabackup 子处理, 即备份时可并行运行的传输数据的进程
• --sleep=MS 用于 ibbackup, 指在每 copy 1MB 的数据后sleep 的时间(unit:MS). 可缓和在运行 ibbackup 所导致的磁盘 I/O 问题.
• --compress=[LEVEL] 用于ibbackup, 至在备份数据时压缩的级别. 0-9
• include=REGEXP只备份匹配的表的数据
• --databases=LIST 指定备份的库, 格式如”db_name.[table_name] db_name2[table_name] ….”. 没有指定时, MyISAM 和 InnoDB 的表都会备份

备份

[root@localhost ~]# innobackupex-1.5.1 [--defaults-file=/etc/my.cnf] [--host=127.0.0.1] [--user=root] [--password=password] [--port=3306]
/tmp/backups/  2>/tmp/backups/backup.log

2>/tmp/backups/backup.log, 是将备份过程中的输出信息重定向到 backup.log
备份后的文件

• xtrabackup_binlog_info 存放binlog的信息. (binlog需要另外拷贝备份, 如果需要binlog的话)
• xtrabackup_checkpoints 存放备份的起始位置和结束位置

恢复

停掉数据库, 然后删除数据库目录下的所有数据库文件.

[root@localhost ~]# cd /usr/local/mysql/var/
[root@localhost var]# rm -rf *

开始恢复

[root@localhost ~]# innobackupex-1.5.1 --user=root --apply-log /tmp/backups/2011-03-15_11-16-37  #应用日志
[root@localhost ~]# innobackupex-1.5.1 --user=root --copy-back /tmp/backups/2011-03-15_11-16-37

数据恢复后, 修改其属主

[root@localhost mysql]# chown -R mysql:mysql var/

启动 MySQL 服务

[root@localhost var]# mysqld_safe &

备份时压缩成 .tar

[root@localhost ~]# innobackupex-1.5.1 [--defaults-file=/etc/my.cnf] [--host=127.0.0.1] [--user=root] [--password=password] [--port=3306] --stream=tar /tmp/backups/tar > /tmp/backups/tar.tar

解压

[root@localhost var]# cd /tmp/backups/tar
[root@localhost tar]# tar ixvf tar.tar  #i 必须

剩下的恢复操作同上.

备份时压缩成 .tar.gz

[root@localhost ~]# innobackupex-1.5.1 [--defaults-file=/etc/my.cnf] [--host=127.0.0.1] [--user=root] [--password=password] [--port=3306] --stream=tar /tmp/backups/tar | gzip > /tmp/backups/tar.tar.gz

解压

[root@localhost var]# cd /tmp/backups/tar
[root@localhost tar]# tar izxvf tar.tar.gz  #i 必须

剩下的恢复操作同上.

–slave-info 参数

[root@localhost ~]innobackupex-1.5.1 [--defaults-file=/etc/my.cnf] [--host=127.0.0.1] [--user=root] [--password=password] [--port=3306] --slave-info /tmp/backup/

备份从库, 加上 --slave-info 备份目录下会多生成一个 xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:

CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

–databases 参数

这个参数用于指定备份的库或者表, 格式如--databases="dbname.[table_name] dbname2.[table_name2] ..."
没指定该参数时, 所有库都会备份(包括 MyISAM 存储引擎). 另外, 若指定的库是 MyISAM 存储引擎, 同样也会备份.

注意点

• xtarbackup bin 下文件所处的目录要在 $PATH 中
• MySQL 配置文件中一定有指定 datadir目录
• 如果备份时有指定--stream=tar, 则tar4ibd 文件所处目录一定要在 $PATH 中(因为使用的是 tar4ibd 去压缩, 在 XtraBackup 的 binary 包中可获得该文件)

MySQL 挂起的调试

1. 附加到mysql

root@gguo-laptop: gdb -p 1232

1232 是 MySQL 的PID
2. 显示当前线程

(gdb) info threads

3.显示所有线程的回溯

(gdb) thread apply all bt

MySQL BINARY

mysql> SELECT * FROM tbl_4;
+----+-------+
| id | title |
+----+-------+
|  1 | 11    |
|  2 | 22    |
|  3 | 33    |
|  4 | aa    |
|  5 | bb    |
|  6 | cc    |
|  7 | AA    |
|  8 | BB    |
|  9 | CC    |
+----+-------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_4 WHERE title LIKE 'a%';
+----+-------+
| id | title |
+----+-------+
|  4 | aa    |
|  7 | AA    |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_4 WHERE BINARY title LIKE 'a%';
+----+-------+
| id | title |
+----+-------+
|  4 | aa    |
+----+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbl_4 WHERE BINARY title LIKE 'A%';
+----+-------+
| id | title |
+----+-------+
|  7 | AA    |
+----+-------+
1 row in set (0.00 sec)

GROUP BY WITH ROLLUP

在 GROUP BY 结果的最后一行，列出所有行的总和,如下

mysql> S ELECT COUNT(*), title FROM tbl_4 GROUP BY title WITH ROLLUP;
+----------+-------+
| COUNT(*) | title |
+----------+-------+
|        1 | 11    |
|        1 | 22    |
|        1 | 33    |
|        2 | aa    |
|        2 | bb    |
|        2 | cc    |
|        9 | NULL  |
+----------+-------+
7 rows in set (0.00 sec)

ORDER BY IF

排序时将某列中特定值排在最前，例如在如下排序中要把 aa 放在最前面：

mysql> SELECT title FROM tbl_4 ORDER BY IF(title = 'aa', 0, 1), title;
+-------+
| title |
+-------+
| aa    |
| AA    |
| 11    |
| 22    |
| 33    |
| bb    |
| BB    |
| cc    |
| CC    |
+-------+
9 rows in set (0.00 sec)

prompt

有很多 MySQL 数据库管理，有时会发生自己忘记了在那台服务器的数据库上，使用 prompt 来重写mysql客户端提示符。

mysql>prompt \u@\h(\d) \r:\m:\s>

• \u 连接用户
• \h 连接主机
• \d 连接数据库
• \r:\m:\s: 显示当前时间

有一一劳永逸的方法，就是在 my.cnf 中直接配置

#不是[mysqld]
[mysql]
prompt=\\u@\\d \\R:\\m>

pager

如果 SELECT 出来的结果集超过几个屏幕，那么前面的结果无法看到。使用 pager 可以设置调用 os 的 more 或者 less 显示查询结果，和在 OS 中使用 more 或者 less 查看大文件的效果一样。

mysql> pager more
PAGER set to 'more'
mysql> \P more
PAGER set to 'more'

mysql> pager less
PAGER set to 'less'
mysql> \P less
PAGER set to 'less'

mysql> nopager
PAGER set to stdout

DELIMITER

DELIMITER 就是告诉 MySQL 解释器,命令的结束符是什么。
默认情况下 MySQL 命令结束是以分号(;)，在写过程或者函数等情况下，这会产生不少问题，因为存储过程中有许多语句，所以每一个都需要一个分号。因此你需要选择一个不太可能出现在你的语句或程序中的字符串作为分隔符。

root@localhost(test) 03:27:17>DELIMITER $
root@localhost(test) 03:27:24>SELECT * FROM tbl_4$
+----+-------+
| id | title |
+----+-------+
|  1 | 11    |
|  2 | 22    |
|  3 | 33    |
|  4 | aa    |
|  5 | bb    |
|  6 | cc    |
|  7 | AA    |
|  8 | BB    |
|  9 | CC    |
+----+-------+
9 rows in set (0.00 sec)

LOAD DATA LOCAL INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'

LOAD DATA INFILE and LOAD DATA LOCAL INFILE

在数据库中，LOAD DATA INFILE 和 LOAD DATA LOCAL INFILE都可以导入本地的数据，而 MySQL5.0 版本默认支持上述模式

mysql> LOAD DATA INFILE ‘test.sql’ INTO TABLE test;
mysql> LOAD DATA LOCAL INFILE ‘test.sql’ INTO TABLE test;

启动mysql 加参数可限制使用 LOAD DATA LOCAL INFILE

root@gguo-laptop:/usr/local/mysql/bin/mysqld_safe --local-infile=0 &

mysql> LOAD DATA INFILE ‘test.sql’ INTO TABLE test;

ERROR 1148 (42000): The used command is not allowed with this MySQL version
而对于对于LOAD DATA INFILE, 可由user的 File_priv 对服务器主机上的文件访问进行控制

MySQL 提示符下运行应用程序

mysql>\! cd /home

安装DBI
开始想是采用 cpan方式安装，但是一直提示找不到源，利用o conf init重新配置cpan 源后，还是失败，决。定还是采用源代码安装。参照源代码中的README 如下步骤：

BUILDING

    perl Makefile.PL
    make
    make test
    make test TEST_VERBOSE=1   (if any of the t/* tests fail)
    make install (if the tests look okay)

试过很多次，但每次都是以出现如下错误而失败而告之：

30 tests and 225 subtests skipped.
Failed 56/178 test scripts, 68.54% okay. 1428/5109 subtests failed, 72.05% okay.
make: *** [test_dynamic] Error 255

再看README,里面提到

2. The output of perl -V

3. If you get a core dump, try to include a stack trace from it.
   (Try installing the Devel::CoreStack module to get a stack trace.)
   If the stack trace mentions XS_DynaLoader_dl_load_file then rerun
   make test after setting the environment variable PERL_DL_DEBUG to 2.

依照上面提示，从中得知，perl 5以上版本可以安装 Devel::CoreStack这个模块跟踪，因为之前 cpan 安装都失败，所以直接到 perl module 这下载源代码。 Devel::CoreStack 编译很顺利，之后，设置好环境变量 PERL_DL_DEBUG。重新执行安装 DBI 的命令， 失败，跟踪错误——某个文件里变量有重复定义。

不得以，问一同事，才得知我此时使用的测试服务器的 DNS 设置有问题。修改 DNS， 再次使用 cpan 方式安装，失败。后来，想到有可能是 cpan 配置的 source 有问题，于是，将另外一台机器上 cpan 的 source 完全 copy 过来，再使用

cpan>reload index

重新加载源的索引。这之后

cpan>install DBI

执行成功。

安装DBD::mysql

cpan>install DBD::mysql

失败，错误提示是关于sql-bench 执行时测试的参数没有设置有关。因为不太了解这里要怎么设置，所以转而采用源代码编译安装。执行 perl MakeFile.PL，还是报跟采用 cpan 方式时的错误。求助于README后，试着加上如下参数编译

perl Makefile.PL --testhost='127.0.0.1' --testuser='test' --testpassword='123456' testdb='test'

这次编译算是提示半成功，还是有提示

libs   -rdynamic -L/usr/local/mysql/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lmygcc

libs 中的 -rdynamic 标识无法识别，为了避免这个对于之后的编译有影响。于是，重新用如下参数设置

perl Makefile.PL --libs='-L/usr/local/mysql/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lmygcc' --testhost='127.0.0.1' --testuser='test' --testpassword='123456' testdb='test'

这次终于没报任何错误，顺利编译。
这里有点疑问，不知道是我理解错误还是参数设置的问题，为什么一定要设置好测试的主机，用户及其库才能编译成功呢？源代码正确安装步骤如下:

tar xvzf DBD-mysql-4.018.tar.gz
cd DBD-mysql-4.018
perl Makefile.PL --libs='-L/usr/local/mysql/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lmygcc' --testhost='127.0.0.1' --testuser='test' --testpassword='123456' testdb='test'
make
make install

安装Data::ShowTable
这个我采用的还是源代码安装，一路下来，很顺利。

tar xvzf Data-ShowTable-3.3.tar.gz
cd Data-ShowTable-3.3
perl Makefile.PL
make
make install

这番准备后，应该可以正常运行sql-bench。

这里测试时有点小问题，bench-init.pl 是在 sql-bench 目录下，同各测试程序是在同一目录，但是，好像加载这个配置文件时使用的目录却是当前命令行所在的目录，所以，开始时一度总是报找不到测试配置文件的错误。等把当前目录转到 sql-bench 所在的目录时，再执行，OK。

机器信息

MySQL 5.1.17
A. 192.168.0.2
B. 192.168.0.9

创建用户并授权

A.

mysql> GRANT REPLICATION SLAVE, FILE on *.* TO 'slave_2'@'192.168.0.9' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
B.
mysql> GRANT REPLICATION SLAVE, FILE on *.* TO 'slave_9'@'192.168.0.2' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

修改配置文件

A.

[mysqld]
server-id									= 1
user											= mysql
log_bin										= mysql-bin
binlog_do_db							= example
binlog_ignore_db					= mysql
binlog_ignore_db					= test
replicate_do_db						= example
replicate_ignore_db				= mysql
replicate_ignore_db				= test
log_slave_updates
slave_skip_errors 				= all
sync_binlog								= 1
auto_increment_increment	= 2
auto_increment_offset			= 1
master_host								= 192.168.0.9
master_port								= 3306
master_user								= slave_9
master_password						= 123456
master_connect_retry			= 60
report_host								= 192.168.0.5

B.

[mysqld]
server-id									= 2
user											= mysql
log_bin										= mysql-bin
binlog_do_db							= example
binlog_ignore_db					= mysql
binlog_ignore_db					= test
replicate_do_db						= example
replicate_ignore_db				= mysql
replicate_ignore_db				= test
log_slave_updates
slave_skip_errors 				= all
sync_binlog								= 1
auto_increment_increment	= 2
auto_increment_offset			= 1
master_host								= 192.168.0.2
master_port								= 3306
master_user								= slave_2
master_password						= 123456
master_connect_retry			= 60
report_host								= 192.168.0.5

启动MySQL服务，在A和B上执行如下相同的步骤

[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/log/localhost.localdomain.pid --skip-external-locking &

查看备份是否成功

mysql> SHOW SLAVE STATUS \G

当看到Slave_IO_Running，Slave_SQL_Running 都是Yes，就说明备份成功 ：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如下是手动指定Master
A.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.000004
        Position: 520
    Binlog_Do_DB: example
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

B.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 519
    Binlog_Do_DB: example
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

A.

mysql> CHANGE MASTER TO
    -> master_host='192.168.0.9',
    -> master_user='slave_9',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=519;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

B.

mysql> CHANGE MASTER TO
    -> master_host='192.168.0.2',
    -> master_user='slave_2',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=520;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

查看各自机器上的IO进程和 SLAVE进程是否都开启。

mysql> SHOW SLAVE STATUS \G

释放掉各自的锁

mysql> UNLOCK TABLES;

PS: All version since MySQL 3.23

打开慢查询日志对系统性能的整体影响不是很大，但因为系统需要计算每一条查询的执行时间，所以，CPU方面还是有些损耗。在CPU资源不够时，在大部分时候可关闭这个，在可能存在的慢查询时，间断性的打开来定位,系统默认没有打开慢查询日志。

mysql> SHOW VARIABLES LIKE '%log_slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
+------------------+-------+

mysql> SHOW VARIABLES LIKE '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

long-query-time: 当期系统记录执行时间的超过这个时间的query,MySQL < 5.1, long-query-time = 1(unit: s)，。

[mysqld]
#enable the slow query log, default 10 seconds
log_slow_queries
#log queries taking longer than 5 seconds
long_query_time = 5
#log queries that don’t use indexes even if they take less than long_query_time
#MySQL 4.1 and newer only
log_queries_not_using_indexes
#define slow log file directory
#log_slow_queries = /new/path/to/file

这 4 个设置一起使用，可以记录执行时间超过 5 秒和没有使用索引的查询。但需要注意log-queries-not-using-indexes 必须是使用 MySQL 4.1 或更高版本。

另外，在 mysqld 进程启动时, 指定--log-slow-queries[=file_name]选项，也可以开启慢查询日志

root@gguo-laptop:~# /usr/local/mysql/bin/mysqld_safe \
>--log-slow-queries=/user/local/mysql/log/slow-log-query.sql &

若需要进一步缩短慢查询时间限制，可使用 Percona 提供的 microslow-patch(msl Patch)。msl Patch不仅能将满朝性时间减小到毫秒级别，还能通过一些特定的规则来过滤记录的SQL，如只记录某个表的慢查询。

阅读慢速查询日志可通过 mysqldumpslow 命令进行。指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是 mysqldumpslow 在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次;这可以帮助找出需要工作量最多的查询。

MySQL的连接不仅有网络方式，还可以通过命名管道的方式，但不管哪种方式，在MySQL中都是通过线程的方式管理所有客户端请求的。

max_connections = 100

max-connections 整个MySQL允许的最大连接数，这个参数主要影响的是整个MySQL应用的并发处理能力，当系统中实际需要的连接数大于max-connections时，由于MySQL的设置限制，应用中必然会产生连接请求的的等待，从而限制了相应的并发量。这个参数默认是100，最大是16384。一般来说500到800左右是一个比较适合的参考值。

mysql> SHOW  VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

网络连接交互相关的参数

• max-user-connections 每个用户允许的最大连接数
• net-buffer-length 网络包传输中，传输消息之前的 net buffer初始化大小
  该参数主要影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大小，所以造成的影响主要是当每次的消息都很大时，MySQL 总是需要多次申请扩展缓冲区的大小。default 16MB,一般来说可以满足大多数场景，如果查询返回的数据量非常小，每次网络传输量都很少，而且系统内存又比较紧缺，可以适当将该值调整到 8MB。
• max-allowed-packet 网络传输中，一次消息传输量的最大值
  该参数与 net-buffer-length 相对应，只不过是net buffer的最大值，当消息传输量大于net-buffer-length的设置时，MySQL 会自动增大net buffer的大小，直到缓冲区大小达到 max-allowed-packet 所设置的值。系统默认值为 1MB,最大值是1GB，必须设定为1024的倍数，单位为字节。
• back-log 在 MySQL 的连接请求等待队列中允许存放的最大连接请求数
  连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大，MySQL 主线程没办法及时给每一个新的连接请求分配(或创建)连接线程时，还没有分配到连接线程的所有请求将存放到一个等待队列中，这个队列就是MySQL 的连接请求队列。当系统存在瞬时的大量连接请求时，则应该注意 back-log 的设置时。default: 50,最大可以设置为 65535 。当增大 back-log 参数的设置时，同时还需注意 OS 级别对网络监听队列的限制，因为如果 OS 的网络监听设置小于 MySQL 的back-log设置，加大back-log设置是没有意义的。

连接线程优化
MySQL中，为了尽可能提高 客户端请求创建连接 这个过程的性能，实现了一个Thread Cache池，将空闲的连接线程存放其中，而不是完成请求后就销毁。这样，当有新的连接请求时，MySQL首先会检查Thread Cache 池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的连接线程。

• thread-cache-size Thread Cache池中应该存放的连接线程数
  如果应用程序使用的是短连接，Thread Cache 池的功效是最明显的。因为在短连接的数据库应用中…
• thread-stack 每个连接线程被创建时，MySQL 给它分配的内存大小

线程相关的系统变量

mysql> SHOW VARIABLES LIKE 'thread%';
+-------------------+---------------------------+
| Variable_name     | Value                     |
+-------------------+---------------------------+
| thread_cache_size | 8                         |
| thread_handling   | one-thread-per-connection |
| thread_stack      | 196608                    |
+-------------------+---------------------------+
3 rows in set (0.00 sec)

系统被连接的次数及当前系统中连接线程的状态值

mysql> SHOW STATUS LIKE 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 45    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 1     |
| Threads_created   | 2     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

通过上面的命令，可看出，系统设置了Thread Cache 池中最多将缓存8个连接线程，每个连接创建之初，系统分配 192KB 的内存堆栈给它。系统启动到目前共接收到客户端的连接 40 次，共创建了2个连接线程，当前有1个连接线程处于和客户端连接的状态，有1个连接的线程处于active状态，即正在处理客户端提交的请求。而在Thread Cache缓冲池共缓冲了1个连接线程。

根据Connections 和 Threads_created 这两个系统状态值，还可以计算出系统新建连接的Thread Cache 命中率，即:

Thread-cache-hit = (Connections - Threads-created) / Connections; (>=90%)

系统不能连接数据库,可看两个数据：
A. max_connections
B. threads_connected

当 threads_connected = max_connections 时，就不能提供更多的连接数了，这时，如果程序还想新建连接线程，数据库系统就会拒绝。

因为创建和销毁数据库连接，都会消耗系统资源。而且为了避免在同一时间同时打开过多的连接线程，一般都需要用到数据库连接池技术。但数据库连接池技术，并不能避免程序错误导致连接资源消耗殆尽，这种情况通常发生在程序未能及时释放连接资源或其他原因造成连接资源不能释放。

该错误有个简便的检查办法，刷新页面时，不断监视 threads_connected 的变化。 当 threads_connected 值不断增加以至达到 max_connections，如果采用数据库连接池技术，threads_connected 增长到数据库连接池的最大连接线程数时，就不再增长了。

FROM: MySQL数据库Query的优化系列均来自51CTO读书频道

在 MySQL 中有一个专门负责优化 SELECT 语句的优化器模块 —— MySQL Query Optimizer，其主要的功能是通过计算分析系统中收集的各种统计信息，为客户端请求的 Query 给出最优的执行计划，也就是最优的数据检索方式。 当 MySQL Query Optimizer 接收到从 Query Parser (解析器) 过来的 Query 时，会根据 MySQL Query 语句的相应语法对该 Query 进行分解分析，同时还会做很多其他的计算转化工作，如常量转化, 无效内容删除, 常量计算等。所有这些工作都是为了 Optimizer 分析出最优的数据检索方式，也就是常说的执行计

在分析 MySQL Query Optimizer 的工作原理之前，先了解一下 MySQL 的 Query Tree。MySQL 的Query Tree是通过优化实现 DBXP 的经典数据结构 和 Tree构造器 而生成的，是指导完成一个 Query 语句的请求须要处理的工作步骤，我们可以简单地认为就是一个的数据处理流程，只是以 Tree 的数据结构存放而已。通过 Query Tree可以很清楚地知道一个 Query 的完成须要经过哪些步骤，每一步的数据来源在哪里，处理方式是怎样的。在整个DBXP 的Query Tree 生成过程中，MySQL 使用了LEX 和 YACC 这两个功能非常强大的语法 (词法) 分析工具。MySQL Query Optimizer的所有工作都是基于这个Query Tree进行的。各位朋友如果对MySQL Query Tree 实现生成的详细信息比较感兴趣，可以参考 Chales A. Bell 的《Expert MySQL》这本书，里面有比较详细的介绍。

MySQL Query Optimizer 并不是一个纯粹的CBO (Cost Base Optimizer) ，而是在CBO的基础上增加了一个被称为Heuristic Optimize (启发式优化) 的功能。也就是说，MySQL Query Optimizer在优化一个Query认为的最优执行计划时，并不一定完全按照系数据库的元信息和系统统计信息，而是在此基础上增加了某些特定的规则。 其实就是在CBO的实现中增加了部分RBO (Rule Base Optimizer) 的功能，以确保在某些特殊场景下控制 Query 按照预定的方式生成执行计划。

当客户端向MySQL 请求一条Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息 (如果有) ，看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有 Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
Query 语句的优化思路和原则主要体现在以下几个方面：

• 优化更需要优化的Query;
• 定位优化对象的性能瓶颈;
• 明确优化目标;
• 从 Explain 入手;
• 多使用Profile;
• 永远用小结果集驱动大的结果集;
• 尽可能在索引中完成排序;
• 只取自己需要的Columns;
• 仅仅使用最有效的过滤条件;
• 尽可能避免复杂的Join和子查询。

上面所列的几点信息，前面4点可以理解为Query优化的一个基本思路，后面部分则是优化的基本原则。

SELECT user_id, (
    SELECT COUNT(1) FROM user_scores WHERE score >= (
        SELECT score
            FROM user_scores
            WHERE user_id = '" . $user_id . "' AND " . $condition . " ORDER BY score DESC LIMIT 1
    )
    AND " . $condition . "
) AS rank
FROM user_scores
WHERE user_id = '" . $user_id . "'  AND " . $condition;

《高性能 MySQL》第二版，介绍用户自定义变量时，有列举一排序，笔记于这里

SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
SELECT actor_id,
    @curr_cnt := AS cnt,
    @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
    @prev_cnt := @curr_cnt AS dummy
FROM (
    SELECT actor_id, COUNT(*) AS cnt,
    FROM sakia.film_actor
    GROUP BY actor_id
    ORDER BY cnt DESC
    LIMIT 10
) AS der;

UNIX_TIMESTAMP(date)
如果没有参数调用，返回一个Unix时间戳记(从’1970-01-01 00:00:00′GMT开始的秒数)。如果 UNIX_TIMESTAMP( ) 用一个date参数被调用，它返回从 ’1970-01-01 00:00:00′ GMT 开始的秒数值。date可以是一个DATE字符串, 一个 DATETIME字符串, 一个TIMESTAMP或以 YYMMDD 或 YYYYMMDD 格式的本地时间的一个数字。

mysql> SELECT UNIX_TIMESTAMP();
    -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
    -> 875996580

当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的 string-to-unix-timestamp 变换。

FROM_UNIXTIME(unix_timestamp)
以 YYYY-MM-DD HH:MM:SS 或 YYYYMMDDHHMMSS 格式返回 unix_timestamp 参数所表示的值，取决于函数是在一个字符串还是或数字上下文中被使用。

mysql> SELECT FROM_UNIXTIME(875996580);
    -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
    -> 19971004222300

FROM_UNIXTIME(unix_timestamp, format)
返回表示 Unix 时间标记的一个字符串，根据 format 字符串格式化。format 可以包含与DATE_FORMAT( )函数列出的条目同样的修饰符。

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');