MySQL recipes

To perform any of these, log in to the server with credentials that are authorized to perform the commands on the databases you want to look at:
mysql -u username -p

• show databases on a server:
  

show databases;

• show status of a database server:

show status;

• get user account info for a database server:

select User,Host,Grant_Priv,Super_Priv from mysql.user;

• show tables in a database:

show tables in database_name;

• get information about a table in a database:

describe database_name.table_name;

• get all contents of a table in a database:

select * from database_name.table_name;

• get the number of rows of a table in a database:

select count(*) from database_name.table_name;

• create an account on database with a given password:

grant all on database_name.* to username@hostname identified by 'password';

• Change user's password

SET PASSWORD FOR foo@localhost=PASSWORD('rubberchicken');
SET PASSWORD FOR foo=PASSWORD('rubberchicken');

• Flush / reload grant tables (after changing table permissions):

flush privileges;

• Conditionally drop a table, if it exists:

drop table if exists database_name.table_name;

• Create a database:

create database database_name;

• Copy a table from one database to another:

CREATE TABLE new_table_name LIKE old_database_name.table_name;
INSERT new_table_name SELECT * FROM old_database_name.table_name;

• Rename a table name:

RENAME TABLE old_table_name TO new_table_name;

• Eliminate duplicate results from a SELECT:

SELECT DISTINCT party FROM presidents;

• Use Regular Expressions in a SELECT:

SELECT selection FROM table_name WHERE column_name REGEXP 'regularexpression'

• Drop a column from a table:

ALTER TABLE 'table_name' DROP 'column_name'

• Check consistency of MyISAM database tables (must be in data dir), first flush the tables, then make sure nobody is accessing the tables, then run the command below (see http://www.lampspot.net/linux/how-to-guide-database-table-corruption-recognise-repair-and-prevent-by-jeng/):
  

myisamchk --update-state --key_buffer_size=256M --sort_buffer_size=256M --read_buffer_size=1M --write_buffer_size=1M *.MYI

• Flush database tables:

flush tables

• Check database table status:

check table database_name.table_name

• Switch MyISAM tables to InnoDB tables (or switch to MyISAM if you have problems with InnoDB). (You must of course start mysqld with InnoDB support, which can be done by adding innodb options from the distributed my-medium.cnf file to your my.cnf file. This can be done with your table as long as you are not using two part autoincrement or FULLTEXT indexes.):
  

ALTER TABLE problem_table TYPE=INNODB

Get mysql version:
mysql -V

Dump all contents of a database to a .sql file; will prompt for password:
mysqldump -u username -p database_name > /tmp/backup.sql

Restore all contents of a database from a .sql file (user must have access to do so; database must exist but be empty, I think; if tables already exist, this will not overwrite them):
mysql -u username -p database_name < /tmp/backup.sql

Change your mysql password (hostname matters?), will prompt for old password:
mysqladm -u username -p password newpassword