mysql tips

TIP 1: Kill已经不使用的进程ID
kill Id 657.
mysql> show processlist;
show processlist;
+-----+------+-----------+---------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+---------+---------+-------+-------+------------------+
| 657 | prog | localhost | weather | Sleep | 28619 | | NULL |
| 782 | prog | localhost | weather | Sleep | 853 | | NULL |
| 785 | prog | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)

mysql>kill 657
也可以直接从shell控制台kill 进程 782
[[e-mail] mysql]# mysqladmin processlist
+-----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+---------+---------+------+-------+------------------+
| 782 | prog | localhost | weather | Sleep | 2676 | | |
| 785 | prog | localhost | | Sleep | 1823 | | |
| 793 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+---------+---------+------+-------+------------------+
[[e-mail] mysql]# mysqladmin kill 782

有用信息:
mysql> show status;
or
mysql> show status\G
also
mysql> show innodb status;
or
mysql> show table status like '%';

TIP 2: 清除二进制日志文件。
默认情况下安装在 /usr/local/var/ 以 -bin.000001,-bin.000002,..命名
mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)

TIP 3: 建立表，列的顺序不一样，情况也会不一样哟
create table blog (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp
);
当插入数据或者更新数据的时候,timeUpdate字段总会自动更新。如果顺序发生变化
timeEnter在timeUpdate之前，那么timeEnter就会更新。记住,第一个timestamp类型
总是会发生变化的。
那么，timeEnter会在什么时候更新呢？答案是，只有插入空数据的时候才会更新

insert into blog (a,b,timeEnter) values (1,2,NULL);

select a,b,DATE_FORMAT(timeUpdate,'%m-%d-%Y %T') as a,
DATE_FORMAT(timeEnter,'%m-%d-%Y %T') as b from blog;

+------+------+---------------------------------------+---------------------+
| a | b | DATE_FORMAT(timeUpdate,'%m-%d-%Y %T') | b
+------+------+---------------------------------------+---------------------+
| 3 | 2 | 09-15-2006 19:14:36 | 09-15-2006 19:15:07 |
| 3 | 2 | 09-15-2006 19:14:39 | 09-15-2006 19:15:07 |
| 5 | 5 | 00-00-0000 00:00:00 | 09-15-2006 19:15:53 |
| 1 | 2 | 00-00-0000 00:00:00 | 09-15-2006 19:20:15 |
+------+------+---------------------------------------+---------------------+
4 rows in set (0.00 sec)

TIP 4: 使用unique字段避免重复数据
CREATE TABLE IF NOT EXISTS blog (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)

);
insert into blog (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);

mysql> select * from blog;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+------+------+------+------+---------------------+
4 rows in set (0.00 sec)

mysql>

id 1 和 id 2 包含了重复的数据，我们使用unique

mysql> ALTER IGNORE TABLE blog ADD UNIQUE INDEX(a,b);

mysql> select * from blog;

select * from blog;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+------+------+------+------+---------------------+
3 rows in set (0.00 sec)

重复的数据没有了。。

TIP 5: 使用\G参数
Show the create statement:
mysql> show create table blog\G
show create table dupTest\G
*************************** 1. row ***************************
Table: blog
Create Table: CREATE TABLE `blog` (
`pkey` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
`b` int(11) default NULL,
`c` int(11) default NULL,
`timeEnter` timestamp NOT NULL,
PRIMARY KEY (`pkey`),
UNIQUE KEY `a` (`a`,`b`)
) TYPE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

TIP 6: 合并表
CREATE TABLE log_01 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE log_02 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE log_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;

mysql> insert into log_01 (a,b) values (1,'log1');
mysql> insert into log_02 (a,b) values (1,'log2');

mysql> select * from log_summary;
select * from log_summary;
+------+------+------+---------------------+
| pkey | a | b | timeEnter |
+------+------+------+---------------------+
| 1 | 1 | log1 | 2004-04-16 11:59:55 |
| 1 | 1 | log2 | 2004-04-16 12:00:08 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)


TIP 7: 监控端口3306:
tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not 192.168.1.102

TIP 8: \S
Quick Status:
mysql> \s
--------------
/usr/local/bin/mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)

Connection id: 642
Current database:
Current user: luzijia@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.1-alpha-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1_swedish_ci
Server characterset: latin1_swedish_ci
UNIX socket: /tmp/mysql.sock
Uptime: 10day 15 hours 24 min 38 sec
Threads: 1 Questions: 4896 Slow queries: 0
Opens: 14 Flush tables: 1 Open tables: 8 Queries per second avg: 0.035
--------------