mysql

=================================================================
MySQL tools (from mysql.com): 
MySQL Migration Toolkit, MySQL Administrator, MySQL Query Browser

3rd party GUI:
http://www.navicat.com - Lite version is free, commercial version is ~$100
http://www.webyog.com ~ free or paid ($130 - $200) (SQLyog & MONyog)
http://www.dbtools.com.br/EN/downloads/downloads_file.php?file_id=4 - DBManager Freeware for MySQL
http://www.scibit.com/products/mascon - FreeMascon
http://www.mysqlfront.de - $35
http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp - WinSQL Lite - free (or pay $99-$249 for dev/pro versions)
http://www.mydb-studio.com
http://www.sqlmanager.net/products/mysql/manager - free version (or ~$100 and more)

3rd party browser-based:
http://www.phpmyadmin.net/home_page - free
http://www.tankado.com/projects/phpMySQLConsole - phpMySQLConsole v0.1 - free, open source
================================================================================
Official reference: http://dev.mysql.com/doc/mysql/en/index.html
Tutorials: on Windows, on Linux
Good concise
useful functions
Useful library: http://savannah.nongnu.org/projects/mysql-sr-lib/
================================================================================
Here are some useful commands for every day use:
================================================================================
login
    mysql -uroot
    mysql -uroot -p
    mysql -umyuser -p
================================================================================

-- this is a comment
/* this is a comment */

default delimiter at the end of every statement is ";" which is equivalent to \g :

You can change the default delimiter:
mysql> delimiter //
and then change back:
mysql> delimiter ;

\G is alternative delimiter which shows the results vertically

select * from mytab\G

================================================================================
create database, user, tables
create database mydb;
drop database mydb;

grant all privileges on mydb.* to myuser@localhost identified by mypass;
grant all privileges on *.* to 'myuser'@'localhost' identified by 'mypass' with grant option;
grant all privileges on *.* to 'myuser'@'%' identified by 'mypass' with grant option;
====================================
insert into mysql.user (host, user, password)
values ('localhost', 'myuser', PASSWORD('mypass'));

flush privileges;

select user, host from mysql.user order by user, host;

grant select, insert, update, delete, create, drop
on mydb.* to 'myuser'@'localhost' identified by 'mypass';

select * from mysql.user where user='myuser'\G
====================================

create user myuser identified by mypass;

revoke all privileges on *.* from user myuser;
revoke grant option on *.* from user myuser;
revoke all privileges, grant option from user1, user2,… ;
drop user myuser;
rename user olduser to newuser;
set password for 'bob'@'%.mydomain.com' = password('newpass');

create table mytab ( col1 int, col2 varchar(20) ) engine=InnoDB; /* default engine=MyISAM */
rename table mytab to mytabnew;
================================================================================
issue commands from the shell script:

mysql -e "create database mydb;" -u dbouser -p dbopass
mysql -e "grant all on mydb.* to  myuser@localhost identified by mypass;"  -u dbouser -p dbopass
================================================================================
look around:

help
help [arg]
Note: help = \h = \? = ?
tee some_file
pager less -n -i -S -F -X
print
system ls -alF

status;
show engine InnoDB status\G
show processlist;
show master status;
show slave status;

show databases;
use mydb;
select database();

show tables;
show table status;
show table status\G
show table status like 'mytab'\G
show table status from mydb like 'user'\G
show table status from crblog like 'wp_%'\G
mysqldump -umyuser -p –no-data mydb mytab > mytab.sql
mysqldump -uroot -p –routines –triggers –no-data mydb > mydb.sql

show create table mytab;
describe mytab;
desc mytab;
show columns from mytab;

show index from mytab;
show index from mytab from mydb;
show index from mydb.mytab;

show procedure status [like ' pattern ' | WHERE expr ]
show function status like 'hello'\G
show create procedure spname;
show create function funcname;

show variables

show grants for myuser;
select user();
select current_user();
select version();
select now();
select current_time();
select current_date();
select unix_timestamp();
================================================================================
Full list of "show" commands: http://dev.mysql.com/doc/refman/5.0/en/show.html

show binary logs
show binlog events
show character set
show collation
show columns
show create database
show create function
show create procedure
show create table
show create view
show databases
show engine
show engines
show errors
show function code
show function status
show grants
show index
show innodb status
show logs
show master status
show mutex status
show open tables
show privileges
show procedure code
show procedure status
show processlist
show profile
show profiles
show slave hosts
show slave status
show status
show table status
show tables
show triggers
show variables
show warnings
================================================================================
Dump definitions of all tables in the database:

mysqldump -umyuser -pmypass --no-data mydb > mydb.sql

Example of how definitions will look in the output file:

DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` varchar(32) NOT NULL default '',
  `a_session` longtext,
  `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

================================================================================
Indexes:

create index i1 on mytab (col1, col2);
create unix index ui1 on mytab (col1 desc);
alter table mytab add primary key (column_list);
alter table mytab add unique i1 (column_list);
alter table mytab add index i2 (column_list);
alter table mytab add fulltext i3 (column_list);

alter table mytab drop index i1

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
================================================================================
select:

SELECT field1, field2,…fieldN
from table_name1, table_name2…
[WHERE Clause]
[OFFSET M] — default 0
[LIMIT N]

select t1.a, t2.b    from tab1 t1, tab2 t2    where t1.id = t2.id

select t1.a, t2.b    from tab1 t1    join tab2 t2    using (id)

select t1.a, t2.b    from tab1 t1    join tab2 t2    on t1.id = t2.id

Note:
= and != operators don't work with null. Use "<=>", "is null", or "is not null" instead.
= operator returns 1 (true) if both sides are equal and not null
<=> operator returns 1 (true) if both sides equal or both a null

select 1 = null; -- null
select 1 is not null; -- 1

select 'a' <=> 'a'; -- 1
select 'a' <=> 'b'; -- 0
select null <=> 'b'; -- 0
select null <=> null; -- 1

Note:
you can also use function IFNULL(arg1,arg2), which returns arg1 if it is not null, otherwise it returns arg2

select
  IFNULL(CONCAT(e2.firstname, ' ', e2.lastname), "[DEPARTMENT HEAD]") AS supervisor,
  CONCAT(e1.firstname, ' ', e1.lastname) AS employee
  ...

================================================================================
left join:

select * from mydb.mytab limit 10;
select * from t1
left join t2 on t1.a=t2.a
left join t3 on t1.b=t3.b
where t3.c > 0

================================================================================
Union:

(SELECT …)
UNION [DISTINCT | ALL]
(SELECT …)
[UNION
(SELECT ...) [...]]
================================================================================
Update 2 tables simultaneously:

update t1, t2
set t1.a = 'str1',
     t2.b = 'str2'
where (t2.c = t1.c)
  and (t1.d = 'str3');

================================================================================
Delete from 2 tables simultaneously:

delete t1,t2
from t1,t2
where t1.c=t2.c and t1.d = 'str4';

================================================================================
http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-10.html

dumping/restoring databases
mysqldump -umyuser -pmypass mydb –r mydb.sql
bash> date;mysqldump -uroot –lock-all-tables –skip-extended-insert –all-databases –r all.txt;date

mysql -umyuser -pmypass -e "source mydb.sql" mydb - (–skip-extended-insert - makes insert slower, but more reliable)

for tables which use transactional engines (like InnoDB), you can achieve much better performance
of the load if you disable autocommit before loading - and restore after the load:

set autocommit = 0; set foreign_key_checks = 0;
source mydb.sql
set foreign_key_checks = 1; commit; set autocommit = 1;

select @@session.foreign_key_checks
select @@session.autocommit

================================================================================
You can dump all databases together:
     mysqldump –all-databases > all_databases.sql
================================================================================
Here is how to copy from one mysql server to another:
     mysqldump –opt db_name | mysql –host=remote_host -C db_name
================================================================================
Example: daily cron job to do backup:

#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c > /var/backup/databasebackup-$date.sql.bz2

================================================================================
mysqldump -uSRC_USER -pSRC_PWD -a –databases db1 db2 db3 | ssh TARGET_USER@TARGET_HOST "mysql -uTARGET_USER -pTARGET_PWD"
================================================================================
See also:
http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
http://worldcommunity.com/opensource/utilities/mysql_backup.html
http://worldcommunity.com/opensource/utilities/mysql_backup.txt - example of a perl script to do backups
  http://dev.mysql.com/doc/refman/5.0/en/faqs-migration.html
  http://dev.mysql.com/doc/refman/5.0/en/upgrade.html
================================================================================
Copying MySQL users:
MYSQL privilege system is based on "host from which you connect" + "user you specify"
1. check that you (user+ host) is allowed to connect
2. check each statement to see if you have privileges
Info is stored in mysql database in tables:
  user
  host
  db
  tables_priv
  columns_priv
  procs_priv
 
show grants for myuser;
flush privileges;
 
A Host value may be a hostname or an IP number, or 'localhost'.
You can use the wildcard characters “%” and “_”

in Host column values.
These have the same meaning as for pattern-matching operations performed
with the LIKE operator. For example,
 '%' - matches any hostname,
 '%.mysql.com' - matches any host in the mysql.com domain.
 
A blank Host value in a db table row means that its privileges should
be combined with those in the row in the host table that matches the
client hostname. The privileges are combined using an AND (intersection)
operation, not OR (union).

Whne there are several lines in mysql.user - they are sorted by host, user (more specific first)

+-----------+---------+----
| Host      | User    | ...
+-----------+---------+----
| localhost | root    | ...
| localhost |         | ...
| %         | jeffrey | ...
| %         | root    | ...
+-----------+---------+----

select current_user();

The wildcard characters “%” and “_” can be used in the Host column.
These have the same meaning as for pattern-matching operations performed with the LIKE operator.
A '%' or blank Host value means “any host.”

Expressed in boolean terms, the description of how a user's privileges are calculated
may be summarized like this:
global privileges
  OR (database privileges AND host privileges)
  OR table privileges
  OR column privileges
  OR routine privileges
 
Note: you can use the host table to specify hosts that are not secure (set all privileges to "N")
 
Table and column privilege changes take effect with the client's next request.
Database privilege changes take effect at the next USE db_name statement.
(change database then return - or use FLUSH PRIVILEGES statement);
Changes to global privileges and passwords take effect the next time the client connects.

http://dev.mysql.com/doc/refman/5.1/en/access-denied.html
http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html
================================================================================
Reasons why can't connect:
  server is not running, or wrong port or pipe
  run this command to see info
    netstat -ln | grep mysql
================================================================================
After original MySQL installation, there are anonymous and root users defined without passwords.
Most often you want to drop the anonymous user(s) - and give password to root users:

shell> mysql -u root
mysql> DROP USER "@'localhost';
mysql> DROP USER "@'you_host_name_or_ip';
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');
mysql> SELECT Host, User FROM mysql.user;

Another way - use direct update in mysql.user table:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
=================================================================
mysql_upgrade -p
/etc/init.d/mysqld restart
mysql_upgrade -p –force
=================================================================
mysqldump -u myuser -p mypass mydb > mydb.sql
mysql -e "create database mydb;" -p
mysql -e "grant all on mydb.* to myuser@localhost identified by 'mypass';" -p
=================================================================
mysqldump -u myuser -p mypass –databases mydb > mydb2.sql
tar cvzf b.tar.gz backup.sql
mysql -u myuser -p mypass -e "source mydb.sql" mydb
# the above command will create a database owned by myuser
=================================================================

#!/usr/local/bin/perl
# script to repair tables one by one - and output the status of repair
use strict;
print"Enter password:";
my $pass = <>;
chomp $pass;
my $cmd = 'mysql -p'.$pass.' -e "show databases;"';
my @databases = grep {;! m/^Database$/} map {;chomp;$_} qx($cmd);
for my $db (@databases) {
  print "=========== $db\n";
  $cmd = 'mysql -p'.$pass.' -D '.$db.' -e "show tables;"';
  my @tables = grep {;! m/^Tables_in/} map {;chomp;$_} qx($cmd);
  for my $tb (@tables) {
    print "  -- $tb -- ";
    $cmd = 'mysql -p'.$pass.' -D '.$db.' -e "repair table '.$tb.';"';
    my @output = grep {;! m/Msg_type/} map {;chomp;$_} qx($cmd);
    my $ss;
    if (scalar(@output) <=0) {
      $ss = "no_output";
    } elsif (scalar(@output) == 1 && $output[0] =~ m/repair/) {
      my @fields = split(/\t/,$output[0]);
      $ss=$fields[3] || "no_status";
    } else {
      $ss = "\n".join("\n",@output);
    }
    print $ss,"\n";
  }
}
exit;

=================================================================
triggers: before/after, insert/update/delete, new/old

Examples:

CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

DROP TRIGGER test.ins_sum;

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

=================================================================
User-defined variables:

set @myvar = 1;
select @myvar as 'mama';

Dynamic SQL:

set @c = "c1";
set @s = concat("select ", @c, " from t");
prepare stmt from @s;
execute stmt;

=================================================================
Dump all trigger definitions using the following SELECT statement:

SELECT CONCAT('CREATE TRIGGER ',
    t.TRIGGER_SCHEMA, '.', t.TRIGGER_NAME,
    ' ', t.ACTION_TIMING,
    ' ', t.EVENT_MANIPULATION,
    ' ON ',
    t.EVENT_OBJECT_SCHEMA, '.', t.EVENT_OBJECT_TABLE,
    ' FOR EACH ROW ', t.ACTION_STATEMENT, '//' )
INTO OUTFILE '/tmp/triggers.sql'
FROM INFORMATION_SCHEMA.TRIGGERS AS t;

=================================================================
Working with date and time:
http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-5.html

date - string - CCYY-MM-DD (C = century, Y = Year, M = Month, D = day)
time - string - hh:mm:ss (hours, minutes, seconds) - range (-838:59:59 to 838:59:59)
datetime - string - CCYY-MM-DD hh:mm:ss
timestamp - string - CCYYMMDDhhmmss

=================================================================
Numeric datatypes:

BIT[(M)] - A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
TINYINT[(M)] [UNSIGNED] [ZEROFILL] - A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
BOOL, BOOLEAN - synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true:
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] - (-32768 to 32767) or unsigned (0 to 65535).
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] - (-8388608 to 8388607) or unsigned (0 to 16777215).
INT[(M)] [UNSIGNED] [ZEROFILL] - (-2147483648 to 2147483647) or unsigned (0 to 4294967295).
INTEGER[(M)] [UNSIGNED] [ZEROFILL] - same as INT
BIGINT[(M)] [UNSIGNED] [ZEROFILL] (-9223372036854775808 to 9223372036854775807) or unsigned (0 to 18446744073709551615).
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] - (-1e38 to 1e38), precision is approx 7 digits,
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] (-1e308 to 1e308), presicion is approx 15 digits,
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL] - usually same as DOUBLE
FLOAT(p) [UNSIGNED] [ZEROFILL] - p is precision in bits (0..24 = FLOAT, 25..53 = DOUBLE)
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] - a packed exact fixed-point number (M<=64, D<=30).
synonyms for DECIMAL:
DEC[(M[,D])] [UNSIGNED] [ZEROFILL],
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL],
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

Note - all arithmetic is done using signed BIGINT or DOUBLE values.
Note:
M = total num of decimal digits (not counting [.-])
D = num of digits after decimal point
=================================================================
String Types:

char(M) (M in 0..255) - fixed width
nchar(M) (using predefined national charset)
char byte = binary

varchar(M) - variable length (0..65,535 bytes, or 0..21,844 utf8 chars)
nvarchar(M) - …

binary(M) - similar to char type, stores binary byte strings, M - in bytes
varbinary(M)

TINYBLOB (max 255 (2^8-1) bytes)
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - max 255 (2^8-1) chars

BLOB[(M)] - maximum 65,535 (2^16 – 1) bytes.
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] - max 65,535 (2^16 –

1) chars.

MEDIUMBLOB - 16,777,215 (2^24 – 1) bytes.
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - max 16,777,215 (2^24 –

1) chars.

LONGBLOB - max 4,294,967,295 or 4GB (232– 1) bytes.
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - max 4,294,967,295 or 4GB (2^32 –

1) characters.

enum('value1','value2',…) - up to 65,535 distinct values (internally - integers).
SET('value1','value2',…) - a set = string object that can have zero or more values.
=================================================================
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
you can use up/down arrows to browse history
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line
as new delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing
binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
=================================================================
How to see the command history from mysql prompt:
mysql> system cat ~/.mysql_history
=================================================================
Some tricks of using mysql CLI interface:
from http://www.developer.com/open/article.php/3725901

Change the prompt to show current database:

prompt mysql \d>
prompt mysql (\u@\h)>

add this lines to .my.cnf file:

[mysql]
prompt=mysql \\d>

set permissions on .my.cnf to 0600

Render Query result vertically: use \G instead of ';' at the end:

mysql test>select * from users where id=1\G
*************************** 1. row *********
       id: 1
     name: Jason
    email: jason@example.com
telephone: (614)999-9999
     city: Columbus
1 row in set (0.00 sec)

=================================================================
Bulk extract:
SELECT … INTO OUTFILE 'myfile';
Bulk Load:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

target="blank">http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Example:

select Host, User
from mysql.user
into outfile '/tmp/users2.csv'
fields terminated by '\t'
lines terminated by '\n';

Inputing tab-delimited file:

load data infile '/tmp/somefile'
into table mytab
fields terminated by '\t'
lines terminated by '\n';

Note:
to speed up the load you can disable indexes during the load

alter table mytab disable keys; -- do this before the load
alter table mytab enable keys; -- to re-create the indexes after loading the file

Note:
mysqldump will add these disable/enable statements for you by default

Note:
mysqlimport - internally uses LOAD DATA INFILE statement

Note:
If you run 'status" command from the mysql prompt - it will show you character set
for server, db, client, and connection. For example:

...
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1

If you are exporting/loading a table with a different character set - make sure to
control the character set of your "mysql" or "mysqldump" tool with option "–default-character-set"
=================================================================
MySQL root password recovery:

1. stop server
/etc/init.d/mysql stop (or: service mysql stop)
2. start in safe mode and skip the "grant tables":
/usr/bin/mysqld_safe –user=mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid
–datadir=/var/lib/mysql –skip-grant-tables –skip-networking &
3. reset mysql root password:
mysqladmin -u root flush-privileges password newpassword
4. stop mysql:
kill `cat /var/run/mysqld/mysqld.pid`
5. start normally:
/etc/init.d/mysql start
=================================================================
Disable connection from other boxes (only allow connection from local processes on the same box):

Edit shell script /usr/bin/safe_mysqld ( or /usr/bin/mysqld_safe).
Add the flag "–skip-networking" - see below:

...
  if test -z "$args"
  then
    $NOHUP_NICENESS $ledir/$MYSQLD $defaults \
       --basedir=$MY_BASEDIR_VERSION \
       --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file \
       --skip-networking \
       --skip-locking >> $err_log 2>&1
  else
    eval "$NOHUP_NICENESS $ledir/$MYSQLD $defaults \
       --basedir=$MY_BASEDIR_VERSION \
       --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file \
       --skip-networking \
       --skip-locking $args >> $err_log 2>&1"
  fi
...

For MySQL 5 networking may be disabled by default on some installations.
To enable remote database access, comment out (or remove) the following
line with a "#" in the file: /etc/mysql/my.cnf

...
bind-address = 127.0.0.1
...

Restart the database after making changes.
=================================================================
pager less
show databases;
pager
pager vi
show databases;
nopager
=================================================================
$ cat index_size.sh
perl -lane ‘$i+= $F[17];END{print $i}’
mysql> pager ./index_size.sh
PAGER set to ‘./index_size.sh’

mysql> show table status;
71680

more examples
=================================================================
You can keep a list of your commands in a separate file and execute them
as a batch from the prompt like this:

cat filename | mysql -u someuser -password=somepassword somedatabase
or cat file | mysql > result

It is convenient to define these 3 aliases:

alias con="mysql -h myhost.com -u myuser –password=mypasswd mydatabase"
alias vv="vi test.sql"
alias rr="cat test.sql | con"

con is a conection string command.

Here are examples of commands:
con
vv
rr
rr > out.txt
con < test.sql
con < test.sql > out.txt

using 'limit' to limit the output:
select * from ABC limit 5,10; # Retrieve rows 6-15
select * from ABC limit 5; # retrieve first 5 rows, equivalent to: limit 0,5
=================================================================
- autoincrement

CREATE TABLE animals (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO animals (name)
VALUES ("dog"),("cat"),("penguin"),("lax"),("whale"); 

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
+----+---------+

to get last id of the last insert:

mysql>last_insert_id();

from perl:
  my $lastid =  $dbh->{mysql_insertid};
from php:
  $lastid = mysql_insert_id ($conn_id);

How to renumber - and start with 100:

mysql> alter table mytab DROP id;
mysql> alter table mytab
    -> add id int unsigned not null auto_increment = 100,
    -> add primary key (id);

=================================================================
Dealing with Duplicates:

insert ignore into mytab (col1,col2) values (1,2)

replace into mytab (col1,col2) values (1,2)

insert into mytab (col1, col2) values (1,2) on duplicate key update col1 - '3'

distinct:

select distinct name from contacts;

Finding duplicates:

select name, count(1) as reps from contacts group by name having reps > 1;

Removing duplicates in existing table by adding primary key

 alter ignore table mytab add primary key (name);

Removing duplicate by switching the table:

create table newtab select ... from mytab ... ;
drop table mytab;
alter table newtab rename to mytab;

=================================================================
Creating procedures and functions - change delimiter temporarily to
something like // or $$ - and then restore:

mysql>delimiter //
mysql>drop procedure if exists myproc //
mysql>create procedure myproc ( )
    -> begin
    -> select count(*) from test.tab1;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>delimiter ;
mysql>call myproc();
mysql>call test.myproc();
mysql>show create procedure test.myproc;
===========================
mysql> create function hello (s char(20))
    -> returns char(50) deterministic
    -> return concat('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> select hello('world');
mysql> show create function hello;

mysqldump -uroot -p --routines --triggers --no-data mydb > mydb.sql

=================================================================
3.
How to show a list of tables with their sizes (and size of the whole database)?
For one table:

show table status like 'mytab'\G

For many tables - use "show table status", and parse the result with a Perl or PHP script:
http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

mysqlshow -u myuser -p mypass --status mydb

=================================================================
Regular expressions

Pattern  What the pattern matches
^        Beginning of string
$        End of string
.        Any single character
[...]    Any character listed between the square brackets
[^...]   Any character not listed between the square brackets
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
*        Zero or more instances of preceding element
+        One or more instances of preceding element
{n}      n instances of preceding element
{m,n}    m through n instances of preceding element

Example:

mysql> SELECT name FROM contacts WHERE name REGEXP '^[aeiou]|ok$';

=================================================================
Temporary tables - exists until the end of the session.
You create them the same way as regular tables - just add the word "temporary":

create temporary table mytabb ( ... );
insert into mytab select ... from ...;

=================================================================

mysqldump generates many commands wrapped into something like this:
/*!40101 … */
Those are code-containing comments. The number at the beginning is the server version. The code will execute if the server is at this version or newer. Read more here.

=================================================================
Frequent task - to generate and maintain a unique Id.
Below is a solution.
It consists of a table "sequences" and a function "next_id"
When you do:

select next_id(tabname);

it will give you next integer id.
Note: this version of function will reset the id on every date change.

drop table if exists mydb.sequences;

CREATE TABLE mydb.sequences (
  name varchar(30) NOT NULL,
  id int unsigned DEFAULT '0',
  iddate date DEFAULT '2000-01-01' NULL,
  PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

delimiter //

drop function if exists mydb.get_next_id //

create function mydb.next_id(p_name varchar(30)) returns int
  deterministic
  sql security invoker
begin
  declare current_id integer;

  UPDATE mydb.sequences
  SET id = if( iddate <> current_date, \@current_id:=0, \@current_id:=(id+1) ),
      iddate = if(iddate <> current_date,current_date,iddate)
  WHERE name = p_name;

  return \@current_id;
end//

delimiter ;

=================================================================
Here is a set of simple perl procedures which I find useful:

# ---------------------------------------------------------------
# mysql_connect()
# my $dbh = $self->mysql_connect($server,$database,$user,$passwd, $label)
# ---------------------------------------------------------------
sub mysql_connect {
  my $self     = shift;
  my $server   = shift || $self->param('mysql_server');
  my $db       = shift || $self->param('mysql_db');
  my $user     = shift || $self->param('mysql_user');
  my $password = shift || $self->param('mysql_password');
  my $label    = shift || "";
  $self->mydebug("connecting to the database");
  my $dbh = DBI->connect("DBI:mysql:host=$server;database=$db", $user, $password)
    or die("Error connecting to the mysql server $server $label : " . $DBI::errstr);
  $dbh->{RaiseError}=0; # 1 - exit on error, we use 0 to capture and log the error
  $dbh->{PrintError}=1; # 1 = print error to STDERR via warn()
  $dbh->{AutoCommit}=1; # 1 = AutoCommit is actually default setting, so this is not necessary
  $self->mydebug("connected to the database [$db] successfully");
  # $dbh->do("use $db"); - this is not necessary
  return $dbh;
}

# ------------------------------------------------------------------
# mysql_close()
# $self->mysql_close($dbh)
# $self->mysql_close($dbh,$label)
# ------------------------------------------------------------------
sub mysql_close {
  my $self  = shift;
  my $dbh   = shift;
  my $label = shift || "";
  return if not defined $dbh;
  $self->mydebug("closing msql database connection $label");
  $dbh->disconnect()
    or die("Error closing msql database connection $label : " . $DBI::errstr);
}

# ---------------------------------------------------------------
# $self->mydebug($sql)
# ---------------------------------------------------------------
sub mydebug {
  my $self = shift;
  my $sql = shift;
  if($self->param('mydebug')) {
    $self->logerr("\n".$sql."\n");
  }
}

# ---------------------------------------------------------------
# mysql_do()
# $self->mysql_do($dbh, $sql)
# ---------------------------------------------------------------
sub mysql_do {
  my ($self, $dbh, $sql) = @_;
  $self->mydebug($sql);
  my $rows = $dbh->do($sql)
    or die("ERROR running this SQL: \n$sql\nError : " . $DBI::errstr);
  return $rows;
}

# ---------------------------------------------------------------
# mysql_tran()
# $self->mysql_tran($dbh, @arr_sql)
# this is just a template showing how to do transactions
# ---------------------------------------------------------------
sub mysql_tran {
  my $self = shift;
  my $dbh = shift;
  my @arr_sql = @_;
  return unless(scalar(@_));
  my $old_pe = $dbh->{PrintError}; # save and reset
  my $old_re = $dbh->{RaiseError}; # error-handling
  $dbh->{PrintError} = 0;    # attributes
  $dbh->{RaiseError} = 1;
  $dbh->{AutoCommit} = 0;    # disable auto-commit mode eval
  if ($dbh->{'AutoCommit'}) {
    die("ERROR turning AutoCommit off");
  } else {
    for my $sql (@arr_sql) {
      # ... perform queries here ...
      # if this is reached, queries succeeded; commit them
    }
    $dbh->commit ();
  };
  $dbh->rollback () if $@;    # rollback if transaction failed
  $dbh->{AutoCommit} = 1;    # restore auto-commit mode
  $dbh->{PrintError} = $old_pe; # restore error attributes
  $dbh->{RaiseError} = $old_re;
}

# ---------------------------------------------------------------
# mysql_query()
# my $result = $self->mysql_query($dbh,$sql); # $result is a ref to an array
#   if ( $self->{err_num} ) { $self->mydebug("err_num = $dm->{err_num}, err_str = " . $dm->{err_str} ); }
# ---------------------------------------------------------------
sub mysql_query {
  my ($self, $dbh, $sql) = @_;
  $self->mydebug($sql);
  my $sth = $dbh->prepare($sql)
    or die("ERROR preparing SQL statement : " . $DBI::errstr);
  $sth->execute
    or die("ERROR executing SQL statement : " . $DBI::errstr);
  my @data = ();
  while (my $row_hashref = $sth->fetchrow_hashref()) {
    push @data, $row_hashref;
  }
  $sth->finish();
  $self->mydebug("SQL query returned " . scalar(@data) . " rows");
  return \@data;
}

# ---------------------------------------------------------------
# $self->mysql_tab_columns($dbh, $db, $table)
# ---------------------------------------------------------------
sub mysql_tab_columns {
  my ($self, $dbh, $db, $table) = @_;
  my $sql = qq{ select ordinal_position, column_name from information_schema.columns
               where table_schema = '$db' and table_name = '_$table' order by ordinal_position };
  my $sth = $dbh->prepare($sql)
    or die("ERROR preparing SQL statement : " . $DBI::errstr);
  $sth->execute
    or die("ERROR executing SQL statement : " . $DBI::errstr);
  my @data = ();
  while (my $row = $sth->fetchrow_hashref()) {
    push @data, $row->{column_name};
  }
  $sth->finish;
  return \@data;
}

=================================================================

Questions:

1.
How to find the status of the connection and status of the server from mysql prompt?
For example, how to find out on which port the server is listenning (default port is 3306) ??
Or how much memory it is using?

2.
How to show if tables are locked? And what kind of locks?

3.
When moving several databases from one server to another, how do I
handle the mysql database, and especially mysql.user table.
If I dump everything (mysqldump –all-databaes) and then restore it,
will this work (scary, as it drops/recreates tables).
=================================================================