Hopefully this will help someone...
Helpful Things to Know about MySQL
Mike Chirico (mc******@users.sourceforge.net)
Last Updated: Fri Apr 16 11:47:34 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/s...l.txt?download
Format is better on the above link. I'm looking for suggestions and
examples...
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
TIP 2:
Clean up binary log files. For a default install they may be in
/usr/local/var/
with names ending in -bin.000001,-bin.000002,..
mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)
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.
Not, in the table above timeEnter will only get updated if passed a null
value.
insert into t (a,b,timeEnter) values (1,2,NULL);
TIP 4:
Connect, create table and select with Perl (Linux). First the DBI module
is needed, which
can be installed from the system prompt as follows:
# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
The following is an example program:
#! /usr/bin/perl -w
# Copyright (GPL) Mike Chirico mc******@users.sourceforge.net
#
# Program does the following:
# o connects to mysql
# o creates perlTest if it doesn't exist
# o inserts records
# o selects and displays records
#
# This program assumes DBI
#
# perl -MCPAN -e shell
# cpan> install DBI
# cpan> install DBD::mysql
#
#
#
#
#
use strict;
use DBI;
# You will need to change the following:
# o database
# o user
# o password
my $database="yourdatabase";
my $user="user1";
my $passwd="hidden";
my $count = 0;
my $tblcreate= "
CREATE TABLE IF NOT EXISTS perlTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) ";
my $insert= "
insert into perlTest (a,b,c)
values (1,2,3),(4,5,6),(7,8,9)";
my $select="
select a,b,c from perlTest ";
my $dsn = "DBI:mysql:host=localhost;database=${database} ";
my $dbh = DBI->connect ($dsn, $user, $passwd)
or die "Cannot connect to server\n";
my $s = $dbh->prepare($tblcreate);
$s->execute();
$s = $dbh->prepare($insert);
$s->execute();
$s = $dbh->prepare($select);
$s->execute();
while(my @val = $s->fetchrow_array())
{
print " $val[0] $val[1] $val[2]\n";
++$count;
}
$s->finish();
$dbh->disconnect ( );
exit (0);
TIP 5:
Remove duplicate entires. Assume the following table and data.
CREATE TABLE IF NOT EXISTS dupTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);
mysql> select * from dupTest;
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>
Note, the first two rows contains duplicates in columns a and b. It
contains
other duplicates; but, leave the other duplicates alone.
mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| 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 6:
Show status information on a table. Note, if the database was started
with --safe-show-database or --skip-show-database some of these
commands
may not work. Note the "\G" option my provide a nicer format.
Show the create statement:
mysql> show create table dupTest\G
show create table dupTest\G
*************************** 1. row ***************************
Table: dupTest
Create Table: CREATE TABLE `dupTest` (
`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)
More examples can be found at:
http://prdownloads.sourceforge.net/s...l.txt?download
Regards,
Mike Chirico
