MySQL

Show command history

cat ~/.mysql_history

Clear cache

reset query cache;

Stop on Ubuntu

sudo /etc/init.d/mysql stop

Users

List users

SELECT user, host FROM mysql.user;

Tables

Show table structure

DESCRIBE <table_name>;
+----------------+------------------+------+-----+---------------------+----------------+
| Field          | Type             | Null | Key | Default             | Extra          |
+----------------+------------------+------+-----+---------------------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| username       | varchar(255)     | NO   |     | NULL                |                |
| password       | varchar(255)     | NO   |     | NULL                |                |
| remember_token | varchar(255)     | YES  |     | NULL                |                |
| created_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+----------------+------------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

Indexes

List indexes of one table

SHOW INDEX FROM <table_name>;
+-------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_system |          0 | PRIMARY                     |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| user_system |          1 | user_system_user_id_foreign |            1 | user_id     | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

List indexes of all tables

SELECT DISTINCT table_name, index_name
FROM information_schema.statistics
WHERE table_schema = '<database_name>';
+-------------------------------------------+-----------------------------------------------------------------+
| TABLE_NAME                                | INDEX_NAME                                                      |
+-------------------------------------------+-----------------------------------------------------------------+
| subject                                   | PRIMARY                                                         |
| subject                                   | country_origin                                                  |
| subject                                   | country_residence                                               |
| subject_type                              | PRIMARY                                                         |
| user                                      | PRIMARY                                                         |
| user_system                               | PRIMARY                                                         |
| user_system                               | user_system_user_id_foreign                                     |

Create index

ALTER TABLE <table_name>
ADD INDEX (<column_name>);

Note: if executed multiple times, multiple indexes will be created for the column.

Drop index

DROP INDEX <index_name> ON <table_name>;

Profiling

SET profiling = 1;
 
-- ... execute your queries
 
SHOW profiles;
+----------+------------+---------------------------+
| Query_ID | Duration   | Query                     |
+----------+------------+---------------------------+
|        1 | 0.00008175 | select * from user        |
|        2 | 0.00040450 | select count(*) from user |
+----------+------------+---------------------------+

Note: profiling mode will automatically be turned off when exiting mysql

Disabling caching

Add SQL_NO_CACHE after the select clause:

SELECT SQL_NO_CACHE * FROM ...

Or to affect just the current session:

SET SESSION query_cache_type=0;

Reset password on Mac OS X Snow Leopard

  • stop mysql server
  • launch mysql server:
sudo /usr/local/mysql/bin/mysqld --user=mysql --skip-grant-tables
  • in another terminal, connect to the database
mysql
  • and execute
UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
  • kill mysql server
  • launch mysql server your usual way

Ref: http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html

Dump

To be redirected into a file, of course.

Schema and data

mysqldump --skip-extended-insert -u user -ppassword dbname [tablename]

Schema only

mysqldump --no-data --skip-extended-insert -u user -ppassword dbname [tablename]

Data only

mysqldump --no-create-info --skip-extended-insert -u user -ppassword dbname [tablename]

XML

mysqldump --xml -u user -ppassword dbname [tablename]

Python script which dumps tables in separate files

Empty a database (shell script)

#!/bin/bash
MUSER=""
MPASS=""
MDB=""
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
 
 
TABLES=$($MYSQL -u $MUSER $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
 
for t in $TABLES
do
        echo "Deleting $t table from $MDB database..."
        $MYSQL -u $MUSER $MDB -e "drop table $t"
done

Ref: http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/

Truncate a table with foreign keys

To empty a table with foreign keys and avoid the error ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint:

SET FOREIGN_KEY_CHECKS = 0;

Feedback