2010-08-20 16:48:13 
阿炯
查看当前的查询'进程'
mysql5重置root密码
表操作方法简介
取得库的所有信息
mysql常规优化
在同一mysql服务器上开启多个实例
列出数据库服里的存储过程
修改自增字段的起始值
使用HEX和UNHEX函数处理二进制数据的导入导出
mysqldump备份存储过程、函数及触发器
取得当前会话id
表名忽略大小写
InnoDB的"auto_increment"指定值被重置
将unix timestamp转化为日期时间
在gant时对库表名进行正则匹配
查看主从状态id相关信息
将库表导出为csv格式
导出为XML格式文件
Mysql表修改
批量删除mysql中的库表
mysql skip-相关信息变动
迅速优化MySQL数据库性能
将beta库的视图迁移到正式库中(Migration Views with mysqldump)
MySQL数据类型的使用技巧
---------------
对set类型字段排序
flag 字段里面有多个值，我想让根据这个字段排序。
flag 里有c的优先显示，其他在后面显示。
--
mysql> desc dede_archives;
+-------------+--------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------------------------------+------+-----+---------+-------+
| id | mediumint(8) unsigned | NO | PRI | 0 | |
...
| flag | set('c','h','p','f','s','j','a','b') | YES | | NULL | |
SELECT * FROM `dede_archives` where id0 limit 20;
以下实现了对set类型的字段进行了条件排序：
SELECT id,flag,title,scores FROM `dede_archives` where id<200 order by FIND_IN_SET('c',flag) desc limit 20;
---------------
查看当前的查询'进程'
每隔2秒查看mysql服务器内部正在处理的'进程'列表。
mysql>watch -n 2 mysqladmin --user=<user> --password=<password> processlist
---------------
mysql5重置root密码
操作系统：DEBIAN linux
MYSQL数据库版本：5.0.32
(1)以 系统root权限登陆
(2)停止MYSQL服务器：
mysql.server stop
(3)跳过授权表执行MYSQL服务器：
mysqld_safe --skip-grant-tables --skip-networking &
(注：参数--skip-grant-tables为跳过授权表；--skip-networking为不监听TCP/IP连接)
(4)执行MYSQL客户端：
mysql
(5)使用mysql数据库
use mysql;
(6)更新root密码
update user set password=password('新密码') where user='root';
(7)关闭mysql服务器，用正常方试起动
---------------
表操作方法简介
mysql下创建相关表有若干方式，包括对表的结构与数据。
将创建新表student2，它的结构与数据与表student完全相同。
CREATE TABLE student2 SELECT * FROM student
将student表中的class为'Four'记录复制到表student2，表结构一样。
CREATE TABLE student2 SELECT * FROM student WHERE class='Four'
为阻止错误发生而加上了'if not exists'。
CREATE TABLE IF NOT EXISTS student3 SELECT * FROM student WHERE class='Four'
查看表结构可发现复制过来的只是字段以及相应的属性，关于表原来的索引以及主键信息都没有复制过来。如果想完全复制表结构，应分如下两步完成：
1、create table new_table like dbname.old_table;
2、insert into new_table select * from old_table;
即先将表结构完全复制过来,然后再将数据插入。
仅复制表结构。
create table t1 like student
复制时改变某些属性。
CREATE TABLE student2 (id INT(3) auto_increment primary key) SELECT student.name,student.class, student.mark from student
这将会重建id字段。
表的改名
RENAME TABLE student TO student_new
将表清空
TRUNCATE `student`
将表中的某一字段(dt)删除
ALTER TABLE `content` DROP `dt`;
将表从库中删除
DROP TABLE content
DROP TABLE IF EXISTS `content`;
删除多张表
DROP TABLE `content`, `content_admin`, `content_cat`, `content_cmt_post`;
删除键约束
ALTER TABLE 'content_cat' DROP INDEX 'cat_id'
表数据操作
insert into student2 select * from student;
insert into student2 select * from student where class = 'Four';
The above query will copy four records from student table to student2 table. Please note that if the student2 table is not empty then the unique constraints for the field id will not allow duplicate id so data will not be updated. Here if we want to update student2 table then we have to use replace command in place of insert command.
为避免键冲突的插入语句
replace into student2 select * from student where class = 'Four';---------------
取得库的所有信息
可以从'information_schema'库中取得。
所有库的信息：
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA
所有表及视图的信息：
select * from information_schema.tables
为库表重命名
RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.
这个功能从'5.1.7'开始加入，在'5.1.23'后移除。
RENAME DATABASE db_name TO new_db_name;
# or
RENAME SCHEMA db_name TO new_db_name;
mysql常规优化
在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io 资源，连PHP 的官方都在声称，说 PHP 脚本 80% 的时间都在等待 MySQL 查询返回的结果。由此可见，提高系统的负载能力，降低 MySQL 的资源消耗迫在眉睫。
1、页面缓存功能：
页面缓存功能降低MySQL的资源消耗的(系统本身就已经考虑，采用生成HTML页面，大大降低了数据库的压力)。
2、mysql服务器的优化
2.1、修改全站搜索
修改my.ini(my.cnf) ，在 [mysqld] 后面加入一行“ft_min_word_len=1”，然后重启Mysql，再登录网站后台(模块管理->全站搜索)重建全文索引。
2.2、记录慢查询sql语句，修改my.ini(my.cnf)，添加如下代码：
#log-slow-queries
long_query_time = 1 #是指执行超过多久的 sql 会被 log 下来
log-slow-queries = E:/wamp/logs/slow.log #设置把日志写在那里，可以为空，系统会给一个缺省的文件
#log-slow-queries = /var/youpath/slow.log linux下 host_name-slow.log
log-queries-not-using-indexes
2.3、mysql缓存的设置
mysql>show variables like '%query_cache%'; mysql本身是有对sql语句缓存的机制的，合理设置我们的mysql缓存可以降低数据库的io资源。
#query_cache_type= 查询缓存的方式(默认是 ON)
query_cache_size 如果你希望禁用查询缓存，设置 query_cache_size=0。禁用了查询缓存，将没有明显的开销
query_cache_limit 不缓存大于这个值的结果。(缺省为 1M)
2.4、查询缓存的统计信息
mysql> SHOW STATUS LIKE ‘qcache%’;
Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory 缓存中的空闲内存。
Qcache_hits 每次查询在缓存中命中时就增大。
Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。
Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况)。
Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache 当前缓存的查询(和响应)的数量。
Qcache_total_blocks 缓存中块的数量。通常，间隔几秒显示这些变量就可以看出区别，这可以帮助确定缓存是否正在有效地使用。运行 FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。
2.5、my.ini(my.conf)配置
2.5.1、key_buffer_size = 256M
# key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低。
2.5.2、
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占。如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
2.5.3、
read_buffer_size = 4M
读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
2.5.4、join_buffer_size = 8M
联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
2.5.5、
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
# 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；
#如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；
Qcache_free_blocks，如 果该值非常大，则表明缓冲区中碎片很多
2.5.6、
tmp_table_size = 256M
max_connections = 768
指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提示，则需要增大该参数值。
2.5.7、
max_connect_errors = 10000000
wait_timeout = 10
指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
2.5.8、
thread_concurrency = 8
该参数取值为服务器逻辑CPU数量×2，如果服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8
2.5.9、
skip-networking
开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项，否则将无法正常连接！
---------------
在同一mysql服务器上开启多个实例
创建其数据目录:
mkdir /var/lib/mysql2
chown mysql.mysql /var/lib/mysql2/
复制当前的'my.cnf'文件:
cp /etc/my.cnf /etc/my2.cnf
编辑其'my2.cnf'内容:
[mysqld]
pid-file = /var/lib//mysql2/mysql2.pid
socket = /var/lib/mysql2/mysql2.sock
port = 3308 # (or any other random port of your choice)
datadir = /var/lib/mysql2
log = /var/log/mysql2.log
对其所在的'datadir'目录进行初始化:
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
开启该实例:
mysqld_safe --defaults-file=/etc/my2.cnf &
现可以通过端口或socket来连接服务器:
mysql -h localhost --port=3308
mysql -S=/var/lib/mysql2/mysql2.sock dbase_name
---------------
列出数据库服里的存储过程
List MySQL stored procedures
SHOW PROCEDURE STATUS
SHOW CREATE PROCEDURE mysproc
SHOW PROCEDURE STATUS WHERE Db = 'mydb'
示例：
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mydb | mysproc | PROCEDURE | root@% | 2012-08-18 20:29:53 | 2012-08-18 20:29:53 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
---------------
修改自增字段的起始值
change auto increment Starting number
可以将其increment改为'5',使其从'5'开始。
> ALTER TABLE tbl AUTO_INCREMENT = 5;
通过下面的指令来查看是否生效。
> SHOW CREATE TABLE t \G
---------------
使用HEX和UNHEX函数处理二进制数据的导入导出
在mysql的数据备份恢复的操作中，将备份的数据在另外一个服务器上进行恢复，由于其中一个表中有varbinary字段，导致“’”分隔符被无法使用(由于二进制编码的问题，引号分隔符被当作了数据的一部分)，从而使得数据不能被正常导入。
这时采用导入、出使用十六进制的方式。
(1)导出时采用HEX函数读取数据，把二进制的数据转为16进制的字符串；
select HEX(binField) from testTable;
(2)导入时采用UNHEX函数，把16进制的字符串转为二进制的数据导入库中；
insert into testTable binField values(UNHEX(@hexstr));
有以下的代码可以演示HEX和UNHEX的功能：
SELECT HEX('this is a test')，查询的结果为：7468697320697320612074657374
SELECT UNHEX('7468697320697320612074657374')，查询的结果为：this is a test
还可以直接读取16进制的字符，在字符串前加0x前导：
SELECT 0x7468697320697320612074657374，查询结果为：this is a test
另外，还可以采用二进制的导入导出的方式进行数据的备份恢复操作。
mysqldump备份存储过程、函数及触发器
procedure、function、triggers备份
-E, --events Dump events.
-R, --routines Dump stored routines (functions and procedures).
--triggers Dump triggers for each dumped table
在dump的时候加上上述参数可以将其备份出来。
---------------
取得当前会话id
1)、处于登录终端的情况下
mysql> status
mysql> \s
可以得到如下信息：
Connection id: 93
2)、程序代码中
my $sth=$pdbh->prepare(q{SELECT CONNECTION_ID()});
$sth->execute() or die;
while(@row=$sth->fetchrow_array){
print "$row[0]\n";
}
---------------
表名忽略大小写
这种情况多用于从windows迁移到linux服后出现的现象，可以设置数据库服对表名忽略大小写的选项。
Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux.
to resolve the issue set the lower_case_table_names to 1
lower_case_table_names=1
不建议使用这种方式设置，还是去改代码吧。
---------------
InnoDB的"auto_increment"指定值被重置
出于业务的需要，在新建的表需要对自增列指定初始值，但是有时候会出现明明指定过的初始值却被重置的现象。
表建好后，如果紧接着插数据，那么此现象是不会出现的。目前发现在两种情况下会出现此问题：
1)、表内无记录时，服务器重启后丢失。
2)、表内无记录时，修改表结构后丢失。
如果对某张innodb表指定了auto_increment，那么innodb就会在data dictionary为它维护一个auto_increment计数器，
注意：这个计数器只存储在内存中，不会写在磁盘上。
那么 innodb在DB重启后，如何对这个计数器进行初始化呢。
InnoDB在数据库重新启动后，它会对指定过 auto_increment的表(tab1)做这样一个操作：
SELECT MAX(id) FROM tab1 FOR UPDATE;
然后将这条语句取得的值+1赋给被指定auto_increment的字段和在内存中的这张表的计数器。如果表是空的，那么这个值将会是1。
解决方法：
1、重建完表后，插入数据之前不要重启，保证内存的数据不会释放。
2、插入一条脏数据，占住位置。
---------------
将unix timestamp转化为日期时间
Convert a UNIX timestamp integer to a datetime with MySQL
Dates and times can be stored as an integer value as a UNIX timestamp. This isn't a particularly human readable format but can be converted in MySQL to a datetime value using the FROM_UNIXTIME function.
In my old ecommerce system there was a table called "orders" with a column called "date_ordered" which was a UNIX timestamp stored as an integer. To convert this to a datetime as part of the SELECT query this needs to be done:
SELECT other fields here, FROM_UNIXTIME(date_ordered) FROM orders WHERE ...
The integer field will now be represented in the resultset as a datetime string in the format YYYY-MM-DD HH:MM:SS.
FROM_UNIXTIME(pe_booking_date, '%W %e %M, %Y') 可以对输出结果进行格式化。
---------------
在gant时对库表名进行正则匹配
像对主机地址那样。库名要用`database_name`括起来，即可实现。
grant select on `db_corp_%`.* to mobile@'192.168.9.%' identified by 'web2013';
grant select,insert,update,delete on `db_corp_%`.* to mobile@'192.168.9.%' identified by 'web2013';
GRANT ALL PRIVILEGES ON `jaap\_%` . * TO 'jaap'@'localhost';
其实在官方文档也有对此的说明：
当在GRANT语句中指定数据库名称时，允许使用‘_’和‘%’通配符。这意味着，如果您想要使用‘_’字符作为一个数据库名称的一部分，您应该在GRANT语句中指定它为‘\_’，以防止用户可以访问其它符合此通配符格式的数据库；例如，GRANT ... ON `foo\_bar`.* TO ...。
为了接纳对来自任意主机的用户授权的权利，MySQL支持以user_name@host_name的形式指定user值。如果一个user_name或host_name与一个不加引号的标识符一样是合法的，那么您不需要对它加引号。不过，要指定一个包含特殊字符(如‘-’)的user_name字符串，或一个包含特殊字符或通配字符(如‘%’)，则引号是必要的；例如，'test-user'@'test-hostname'。分别对username和hostname加引号。
您可以在hostname中指定通配符。例如user_name@'%.loc.gov'适用于在loc.gov域中的任何主机的user_name，同时user_name@'144.155.166.%'适用于144.155.166 C级子网中的任何主机的user_name。
这里使用的匹配为标准的sql模式
SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。
但好像没有找到关于表名的规则匹配。
查看主从状态id相关信息
在运用中，需要查询到主从(多从的情况)相关的关系，主要是id信息。
MySQL> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 10 | | 3306 | 1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)
#当然这需要你的mysql版本在5.5以上才可以。不仅在主服执行此命令，在从库也可执行。
> show variables like 'server_id';
---------------
将mysql数据库表导出为csv格式
select * from content into outfile '/tmp/content.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';
要使用这个功能，数据库用户必须要赋予FILE权限，而且貌似只能在数据库服务器上操作(远程没有试验成功)。为了避开以上的限制，使用了变通的方法，就是使用mysql命令将待导出的数据select出来，再使用sed命令配合正则表达式，将结果集修改为需要的csv格式后，重定向到输出文件。
使用mysql命令来实现：
mysql -uuser -ppaswd -h192.168.18.8 --database test -e "select * from tab;"
可以使用-N参数去掉表头。
其它无用的字符如何处理呢，显然-N达不到目的了，这里使用'-s'参数，'-s'参数也能做到的，那就是再加一个"s"，使用"-ss"，也就是更精简的输出。
mysql -S /tmp/mysqld3306.sock -ppasswd -Nsse'select * from site.menu'
数据已经出来了，下一步就是如何格式化为csv格式，这里要用到linux下的sed命令：
sed命令用到了正则表达式，简单说下，我们得到的数据，每个字段之间是用制表符"\t"分隔的，所以第二个表达式s/\t/","/g 把所有的制表符替换为","，然后第三个表达式，将每行的开头加上双引号，第四个表达式将每行的结尾添加一个双引号和一个回车符"\r"（原始数据每行结尾只有一个换行符"\n"，sed命令是处理去掉回车符后的字符串），而第一个则是将'"'转译为'\"'，以免与csv字段界定符相冲突，这样可把得到的文本输出到文件：
mysql -S /tmp/mysqld3306.sock -ppasswd -s -e'select * from foasite.content'|sed 's/\"/\\"/g;s/\t/","/g;s/^/"/g;s/$/"\r/g'>content.csv
---------------
导出为XML格式文件
使用mysql命令行客户端
$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Cars' > /tmp/cars.xml
--xml option, which enables us to dump data in XML format. The -e option executes a statement and quits the cli client.
使用mysqldump工具
The mysqldump is a command tool to create backups for MySQL.
-X, --xml Dump a database as well formed XML.
---------------
Mysql表修改
1、字段类型的修改
MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
alter table tab_name modify ename varchar(64);
change与modify的另一个区别是前者在修改表的的字段类型时，对已经有大量数据的表来说，前者可在1s内完成，而后者就没有那么快了，它取决于你磁盘的i/o性能。即后者不紧修改了表结构，而且还会将数据重新‘灌装’一份，如果数据量很大的话，将会长时间锁表而影响正常的操作。
mysql> alter table wz_contmp change origin_type origin_type enum('wz','lt','bk','wb','jw','tb','mg') NOT NULL DEFAULT 'wz';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table wz_contmp modify origin_type enum('wz','lt','bk','wb','jw','tb') NOT NULL DEFAULT 'wz';
Query OK, 2000000 rows affected (2 min 41.69 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
2、增加字段
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
alter table tab_name add column age int(3);
3、删除字段
DROP [COLUMN] col_name
alter table tab_name drop age;
4、字段改名
CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
alter table tab_name change age rage int(2);
注意：change与modify都可以修改表的定义，不同的是change后面需要写两次列名，它可以修改列的名称，而modify则不能。
5、修改字段排列顺序
在使用add/modify/change时，有一个可选参数'first|after column_name'，可用它来修改字段在表中的位置，默认'add'在新增字段时加在表的最后面，而'change/modify'不会改变字段的位置。
将字段birth添加于age之后
alter table tab_name add birth date after age;
修改字段age，将它放在最前面。
alter table tab_name modify age int(3) first;
6、表名修改
alter table tab_name rename name_tab;
---------------
批量删除mysql中的库表
需要删除数据库的一系列的库表，它们有一定命名规则，相同的前缀，但涉及的数目较多，只能用自动的方法来完成。mysql drop database or table with like。
显示当前库中以'prefix_'为前缀的表，然后删除它们。
show tables like 'prefix\_%';
drop table myprefix_1, myprefix_2, myprefix_3;
方法一
简单的查询并生成语句后，手动执行。对特定库的特定表执行删除操作：
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_schema,'.',table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'smsdb' AND table_name LIKE 'msg_%';
方法二
使用存储过程来完成
SET GROUP_CONCAT_MAX_LEN=10000;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%';
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
这将会生成并执行像如下的一些语句：
DROP TABLE myDatabase.del1, myDatabase.del2, myDatabase.del3;
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'myprefix_%');
PREPARE stmt FROM 'DROP TABLE @tbls';
EXECUTE stmt USING @tbls;
DEALLOCATE PREPARE stmt;
---------------
mysql skip-相关信息变动
skip-innodb / skip-bdb / skip-locking
第一个参数，据说打开后很省内存，文档里有说明：
If InnoDB hasn’t been built, the option –skip-innodb isn’t available. We prefix with ‘loose’ to allow the option to be used at all times.
即skip-innodb已经改成loose-skip-innodb
详细请参考：http://lists.mysql.com/commits/115535
在5.1中的my.cnf中发现一个skip-external-locking参数。
skip-external-locking就是新版本的skip-locking，skip-locking 5.0及之前的了。
skip-bdb这个参数已经废除了！
---------------
迅速优化MySQL数据库性能
(1)、back_log：
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值 对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。在mysql中back_log的设置取决于操作系统，在linux下这个参数的值不能大于系统参数tcp_max_syn_backlog的值。
通过以下命令可以查看tcp_max_syn_backlog的当前值 cat /proc/sys/net/ipv4/tcp_max_syn_backlog
通过以下命令进行修改sysctl -w net.ipv4.tcp_max_syn_backlog=n
深入探讨一点 tcp/ip网络一般会有如下过程，从生成socket到bind端口在listen进而建立连接 具体到listen，就是listen(int fd, int backlog)的调用，这里backlog和mysql中back_log具有一定的关系，即操作系统backlog的要不小于mysql中back_log的值，在linux内核2.6.6中backlog在/include/net/tcp.h中由TCP_SYNQ_HSIZE变量定义。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
(2)、interactive_timeout：
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 MySQL_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。默认数值是28800，我把它改为7200。
(3)、key_buffer_size：
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你 能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为 402649088(400MB)。
(4)、max_connections：
允许的同时客户的数量。增加该值增加 MySQLd 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many connections 错误。 默认数值是100，我把它改为1024 。
(5)、record_buffer：
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，我把它改为16773120 (16M)
(6)、sort_buffer：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，我把它改为 16777208 (16M)。
(7)、table_cache：
为所有线程打开表的数量。增加该值能增加MySQLd要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。
(8)、thread_cache_size：
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可 以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
(10)、wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。
注：参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。
---------------
将beta库的视图迁移到正式库中(Migration Views with mysqldump)
Extracts all of the view names via a query to the INFORMATION_SCHEMA database.
Pipes them to xargs to formulate a mysqldump command. --skip-column-names and --batch are needed to make the output xargs friendly.
查看有哪些视图
mysql -h192.168.20.106 -udba -pyour_paswd --skip-column-names --batch -e "select table_name from information_schema.views where table_schema = 'freeoa_beta'"
将该库的所以视图导出到文件中
mysql -h192.168.20.106 -udba -pyour_paswd --skip-column-names --batch -e "select table_name from information_schema.views where table_schema = 'freeoa_beta'" | xargs --max-args 1 mysqldump --opt -v -h192.168.20.106 -udba -pyour_paswd freeoa_beta $* >> freeoa_beta.views.sql
导入到正式库中
mysql -h192.168.20.106 -udba -pyour_paswd freeoa < freeoa_beta.views.sql
这样查视图也是可以的
mysql -u username INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'database'"
处理当前库中的视图
mysql --skip-column-names --batch -e "select table_name from information_schema.views where table_schema = database()" $* | xargs --max-args 1 mysqldump $*
---------------
MySQL数据类型的使用技巧
更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和cpu缓存，并且处理时需要的cpu周期也更少。
简单就好：简单数据类型的操作通常需要更少的cpu周期，例如整型比字符操作代价更低，因为字符集和校对规则排序规则使字符比较比整型比较更复杂，使用整型存储IP地址。
尽量避免NULL：通常情况下最好制定列为NOT NULL，除非真的需要存储NULL值。如果查询中包含可为NULL的列，对mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。
很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间磁盘和内存空间不同。
关于别名
MYSQL为了兼容性支持很多别名，例如integer、bool、numeric，他们都只是别名，虽然可能令人不解，但是不会影响性能。如果建表的时候采用数据类型的别名，然后用show create table检查，会发现mysql报告的是基本类型，而不是别名。
MySQL可以为整数类型指定宽度，例如int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了mysql的一些交互工具例如mysql命令行客户端用来显示字符的个数。对于存储和计算来说，int(1)和int(20)是相同的。
字符类型
VARCHAR和CHAR是两种最主要的字符串类型。
VARCHAR:
varchar类型选用于存储可变长字符串，需要使用1或者2个额外字节记录字符串的长度，比定长类型更节省空间，因为它仅使用必要的空间例如越短的字符串使用越少的空间。由于VARCHAR行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。varchar的使用场景：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像utf-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR:
CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。char适合于存储很短的字符串，或者所有值都接近同一个长度。例如char非常适合存储密码MD5值，因为这是一个定长的值。对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。
大字段字符类型
blob和text都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。MySQL对blob和text列进行排序于其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串排序。
尽量避免使用text和blob类型，如果实在无法避免，有一个技巧实在所有用到blob字段的地方都是用substring(column,length)将列值转换为字符串在order by 子句中也适用，这样就可以使用内存临时表了。但是要确保截取的子字符串足够短，不会使临时表的大小超过max_heap_table_size或tmp_table_size，超过以后Mysql会将内存临时表转换为MyISAM磁盘临时表。
时间类型
timestamp类型保存了从1970年1月1日午夜以来的秒数，timestamp只使用4个字节的存储空间，因此它的范围比datatime小得多：只能表示从1970年到2038年。提供了from_unixtime函数把unix时间戳转换为日期、unix_timestamp函数把日期转换为时间戳。有时候人们会将unix时间戳存储为整数值，这不会带来任何收益。用整数保存时间戳的格式通常不方便处理，所以我们不推荐这么做。
标识符identifier
整数通常是标识列最好的选择，因为它们很快并且可以使用auto_increment；千万不要使用enum和set类型作为标识列；尽量避免使用字符串类型作为标识列，因为他们很消耗空间，并且通常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心，因为MyISAM默认对字符串使用压缩索引，这会导致查询慢得多。
特殊类型数据：IP地址字段IPv4
人们经常使用varchar(15)来存储ip地址，然而，它们实际上是32位无符号整数，不是字符串。MySQL提供INET_ATON()和INET_NTOA()函数将ip地址在整数和四段表示形式之间进行转换。
物化视图
物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不原生支持物化视图。使用开源工具Flexviews可以自己实现物化视图。它由下面这些部分组成：
1变更数据抓取功能，可以读取服务器的二进制日志并且解析相关行的变更。
2一系列可以帮助创建和管理视图的定义的存储过程
3一些可以应用变更到数据库中的物化视图工具
加快ALTER TABLE操作的速度表结构更改
一般而言，大部分alter table操作将导致MySQL服务中断锁表并重建表。MySQL执行大部分修改表结构的操作方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这个操作可能需要花费很长时间。
不是所有的alter table操作都会引起表重建。理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。比如使用 alter comlum改变列的默认值：
alert table tablename alert column col1 set default 5;
alter table wcm modify column field4_bak int(10) default 1;
alter table wcm modify column field4_bak int(8) default 1;
这个语句会直接修改.frm文件而不涉及表数据，所以这个操作是非常快的。
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------
---------------