tips mysql

Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GNU Free Documentation License)
Last Updated: Sun Jan 21 09:44:21 EST 2007

The latest version of this document can be found at:
http://souptonuts.sourceforge.net/readme_mysql.htm


TIP 1:

Find out who is doing what, and kill the process if needed.
This example kills 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

Or, from the command line, to kill process 782

[root@third-fl-71 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 |
+-----+------+-----------+---------+---------+------+-------+------------------+
[root@third-fl-71 mysql]#

[root@third-fl-71 mysql]# mysqladmin kill 782

Note, the following can also be helpful

mysql> show status;
or
mysql> show status\G
also
mysql> show innodb status;
or
mysql> show table status like '%';

The above gives you create time and other information.





TIP 2:

Clean up binary log files. For a default install they may be in

/usr/local/var/
or
/var/lib/mysql/

with names ending in -bin.000001,-bin.000002,.. The following
command may help find out where the logs are located.

mysql> show variables like '%home%';
+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| bdb_home | /var/lib/mysql/ |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
+---------------------------+-----------------+


mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)

See (Tip 24:) details working with binary log files and (Tip 25:) explains
how to setup logging. (Tip 37:) shows have to setup MASTER and SLAVE
replication.



TIP 3:

Can the order of the columns in a create statement make a difference? YES

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the "automatically generated" time. So
if the record is updated, or inserted, this time gets changed. If the
order is changed, "timeEnter" is before "timeUpdate", then, "timeEnter"
would get updated. First timestamp column updates automatically.

Note, in the table above timeEnter will only get updated if passed a null
value.

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

Hints: Need mm-dd-yyyy hh:mm:ss format?

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