Posts Tagged ‘MySQL Tricks’

MySQL Tricks

Posted: June 8, 2014 in MySQL
Tags:
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)



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 mchirico@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 entries.  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, leaves 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)

 

Advertisements