By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,602 Members | 1,507 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,602 IT Pros & Developers. It's quick & easy.

My notes: Hopefully this will help someone

P: n/a
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
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.