用布尔术语表示的用户权限：
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
但是我们在Database Level新建授权时，在mysql.db表新建相应记录时，向上在mysql.user中也新建一个所有权限都是’N’的记录。Host大部分情况下不使用。
文章关键字 ‘MySQL’
权限级别Database Level
2010年01月27日，星期三MySQL的日志模块的一些总结
2010年01月26日，星期二日志记录模块：用于将Query和Command两种请求记入日志。需要Full Query logging开启。
日志处理模块：各种处理模块调用该日志处理模块，将相应的变更语句以更新事件的形式记录到bin-log中。需要开启bin-log功能。
各种存储引擎层的WAL：如innodb的redo日志。无需开启。
欢迎拍砖！
mysqldump,flush tables?
2010年01月26日，星期二mysqldump –lock-all-tables = mysqldump using flush tables with read lock.
搭建MySQL Replication
2010年01月20日，星期三今天搭建MySQL Replication碰到一些问题，总结一下。基本的步骤是按照这样(最基本通用的一种)：
http://forums.mysql.com/read.php?26,148922,196831#msg-196831
注意的是：主服务器要开启二进制日志，从不用。
然后还是碰到了这样的问题：
Slave_IO_State: Connecting to master
Slave_IO_Running: No
Slave_SQL_Running: Yes
使用了reset方法：
http://forums.mysql.com/read.php?26,148922,151236#msg-151236
还是不行，然后看到一篇博文中的这句话：
Slave_SQL_Running: No的情况，可通过如下解决：
(1)、确定分配置给从的账号可以远程登陆主mysql服务器,这是关键。
http://www.joinphp.cn/blog/?p=70
然后(要确保bind-address=127.0.0.1已经被注释掉了)执行：
mysql> GRANT ALL PRIVILEGES ON *.* TO rep@"%" IDENTIFIED BY "yourpasswd";
mysql> flush privileges;
OK了。
还找到一篇比较详细的文章，虽然没有帮到自己，不过有问题查阅一下也是很好的。http://liuyu.blog.51cto.com/183345/64077
毕竟没有自己的东西，所以还是都用链接的好，尊重版权，呵呵。
MySQL-Max去哪儿了？
2010年01月5日，星期二我的MySQL版本是5.0.75，因为一直看的文档是中文那一版的，很老了的，提到了MySQL-Max的这个扩展模式，但我怎么感觉自己用的时候虽然不是这个模式，是标准模式MySQL-server就有了扩展模式的东东了呢，然后又发现没有MySQL-Max了，历经一些周折找到了问题所在，供大家参考：
· MySQL 5.0.27 is the last version in MySQL 5.0 for which MySQL-Max binary distributions are provided, except for RPM distributions. For RPMs, MySQL 5.0.37 is the last release. After these versions, the features previously included in the mysqld-max server are included in mysqld.
If you previously installed a MySQL-Max distribution that includes a server named mysqld-max, and then upgrade later to a non-Max version of MySQL, mysqld_safe still attempts to run the old mysqld-max server. If you perform such an upgrade, you should remove the old mysqld-max server manually to ensure that mysqld_safe runs the new mysqld server.
以上的内容摘自MySQL的官网的5.1的在线帮助文档，大概的意思就是说5.0.27是二进制分发版中有MySQL-Max的最后一版，5.0.37是RPM分发版中有MySQL-Max的最后一版，以后的版本中MySQL-Max已经包含到mysqld中了。
MySQL中的Bit类型详解
2009年12月29日，星期二多的不说，用实际例子举例：
mysql> CREATE TABLE test(id int,a bit(8));
mysql> INSERT INTO test VALUES(1,b‘00111010′);
mysql> INSERT INTO test VALUES(2,b‘00111000′);
mysql> INSERT INTO test VALUES(3,’00111010′);
mysql> INSERT INTO test VALUES(4,’1′);
mysql> SELECT id,a, a + 0 FROM test order by id;
+——+——-+——+
| id | a | a + 0 |
+——+——-+——+
| 1 | : | 58 |
| 2 | 8 | 56 |
| 3 | ? | 255 |
| 4 | 1 | 49 |
+——+——-+——+
b表示输入的字符串按binary转换，没有b标示的按ASCII码值转换。默认bit的值是转换成ASCII码的值的。a+0表示得到binary对应的数字的值。不在ASCII码范围的就用11111111(只针对本例)。
MySQL监控工具和日志分析工具汇总
2009年12月28日，星期一监控工具：innotop(安装及介绍)，mysqlsniffer (http://hackmysql.com/mysqlsniffer) 和tcpdump(see http://forge.mysql.com/snippets/view.php?id=15) , mysql profiles.
mysql profiles监控sql语句(转载)
2009年12月28日，星期一要使用该功能，mysql的版本必须在5.0.37版本以上。
profiling 功能可以了解到cpu io 等更详细的信息。
show profile 的格式如下：
SHOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
默认方式下该功能是关闭的：
mysql>select @@profiling;
+————-+
| @@profiling |
+————-+
| 0 |
+————-+
1 row in set (0.00 sec)
打开功能
mysql>set profiling=1;
+————-+
| @@profiling |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)
输入需要执行的sql 语句：
mysql>select count(*) from table;
mysql> show profiles\G;
通过指定的Query_ID 来查询指定的sql语句的执行信息：
mysql> show profile for query 1;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| starting | 0.000028 |
| checking query cache for query | 0.000008 |
| checking privileges on cached | 0.000009 |
| sending cached result to clien | 0.000023 |
| logging slow query | 0.000004 |
| cleaning up | 0.000003 |
+——————————–+———-+
6 rows in set (0.00 sec)
mysql> show profile cpu,block io for query 1;
+——————————–+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+——————————–+———-+———-+————+————–+—————+
| starting | 0.000028 | NULL | NULL | NULL | NULL |
| checking query cache for query | 0.000008 | NULL | NULL | NULL | NULL |
| checking privileges on cached | 0.000009 | NULL | NULL | NULL | NULL |
| sending cached result to clien | 0.000023 | NULL | NULL | NULL | NULL |
| logging slow query | 0.000004 | NULL | NULL | NULL | NULL |
| cleaning up | 0.000003 | NULL | NULL | NULL | NULL |
+——————————–+———-+———-+————+————–+—————+
6 rows in set (0.00 sec)
如果不带for 参数则指列出最后一条语句的profile 信息：
mysql> show profile cpu,block io for query 1;
+——————————–+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+——————————–+———-+———-+————+————–+—————+
| starting | 0.000028 | NULL | NULL | NULL | NULL |
| checking query cache for query | 0.000008 | NULL | NULL | NULL | NULL |
| checking privileges on cached | 0.000009 | NULL | NULL | NULL | NULL |
| sending cached result to clien | 0.000023 | NULL | NULL | NULL | NULL |
| logging slow query | 0.000004 | NULL | NULL | NULL | NULL |
| cleaning up | 0.000003 | NULL | NULL | NULL | NULL |
+——————————–+———-+———-+————+————–+—————+
6 rows in set (0.00 sec)
关闭参数：
mysql> set profiling=0
+—————+——-+
| Variable_name | Value |
+—————+——-+
| profiling | OFF |
+—————+——-+
1 row in set (0.00 sec)
一些局部及整体压力测试(基准确定)的工具Benchmarking Tools
2009年12月28日，星期一
Full-Stack Tools：测试整个Web应用的一些工具。
ab
ab is a well-known Apache HTTP server benchmarking tool. It shows how many
requests per second your HTTP server is capable of serving. If you are bench-
marking a web application, this translates to how many requests per second the
entire application can satisfy. It’s a very simple tool, but its usefulness is also lim-
ited because it just hammers one URL as fast as it can. More information on ab
is available at http://httpd.apache.org/docs/2.0/programs/ab.html.
http_load
This tool is similar in concept to ab; it is also designed to load a web server, but
it’s more flexible. You can create an input file with many different URLs, and
http_load will choose from among them at random. You can also instruct it to
issue requests at a timed rate, instead of just running them as fast as it can. See
http://www.acme.com/software/http_load/ for more information.
JMeter
JMeter is a Java application that can load another application and measure its
performance. It was designed for testing web applications, but you can also use
it to test FTP servers and issue queries to a database via JDBC.
JMeter is much more complex than ab and http_load. For example, it has fea-
tures that let you simulate real users more flexibly, by controlling such parame-
ters as ramp-up time. It has a graphical user interface with built-in result
graphing, and it offers the ability to record and replay results offline. For more
information, see http://jakarta.apache.org/jmeter/.
Single-Component Tools：数据库端的压力测试工具。
mysqlslap
mysqlslap (http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html) simulates
load on the server and reports timing information. It is part of the MySQL 5.1
server distribution, but it should be possible to run it against MySQL 4.1 and
newer servers. You can specify how many concurrent connections it should use,
and you can give it either a SQL statement on the command line or a file con-
taining SQL statements to run. If you don’t give it statements, it can also auto-
generate SELECT statements by examining the server’s schema.
sysbench
sysbench (http://sysbench.sourceforge.net) is a multithreaded system benchmark-
ing tool. Its goal is to get a sense of system performance, in terms of the factors
important for running a database server. For example, you can measure the per-
formance of file I/O, the OS scheduler, memory allocation and transfer speed,
POSIX threads, and the database server itself. sysbench supports scripting in the
Lua language (http://www.lua.org), which makes it very flexible for testing a vari-
ety of scenarios.
Database Test Suite
The Database Test Suite, designed by The Open-Source Development Labs
(OSDL) and hosted on SourceForge at http://sourceforge.net/projects/osdldbt/,isa
test kit for running benchmarks similar to some industry-standard benchmarks,
such as those published by the Transaction Processing Performance Council
(TPC). In particular, the dbt2 test tool is a free (but uncertified) implementation
of the TPC-C OLTP test. It supports InnoDB and Falcon; at the time of this writ-
ing, the status of other transactional MySQL storage engines is unknown.
MySQL Benchmark Suite (sql-bench)
MySQL distributes its own benchmark suite with the MySQL server, and you
can use it to benchmark several different database servers. It is single-threaded
and measures how quickly the server executes queries. The results show which
types of operations the server performs well.
The main benefit of this benchmark suite is that it contains a lot of predefined
tests that are easy to use, so it makes it easy to compare different storage engines
or configurations. It’s useful as a high-level benchmark, to compare the overall
performance of two servers. You can also run a subset of its tests (for example,
just testing UPDATE performance). The tests are mostly CPU-bound, but there are
short periods that demand a lot of disk I/O.
The biggest disadvantages of this tool are that it’s single-user, it uses a very small
dataset, you can’t test your site-specific data, and its results may vary between
runs. Because it’s single-threaded and completely serial, it will not help you
assess the benefits of multiple CPUs, but it can help you compare single-CPU
servers.
Perl and DBD drivers are required for the database server you wish to bench-
mark. Documentation is available at http://dev.mysql.com/doc/en/mysql-
benchmarks.html/.
Super Smack
Super Smack (http://vegan.net/tony/supersmack/) is a benchmarking, stress-
testing, and load-generating tool for MySQL and PostgreSQL. It is a complex,
powerful tool that lets you simulate multiple users, load test data into the data-
base, and populate tables with randomly generated data. Benchmarks are con-
tained in “smack” files, which use a simple language to define clients, tables,
queries, and so on.