A list of handy MySQL commands that I use time and time again.

# To connect to a database
$ mysql -h localhost -u root -p

# To backup all databases
$ mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql

# To restore all databases
$ mysql -u root -p  < ~/fulldump.sql

# Create database with utf-8 collation and utf-8 charset
$ mysql> create database <database_name> character set utf8 collate utf8_general_ci;

# Create a table with an auto-incrementing primary key (id), w/ utf-8
$ mysql> create table <table_name> (`id` int unsigned auto_increment, primary key (`id`)) default character set utf8 collate utf8_general_ci;

# List all databases on the sql server.
$ mysql> show databases;

# Switch to a database.
$ mysql> use <database_name>;

# To see all the tables in the db.
mysql> show tables;

# To see database's field formats.
$ mysql> describe <table_name>;

# To delete a db.
$ mysql> drop database <database_name>;

# To delete a table.
$ mysql> drop table <table_name>;

# Show all data in a table.
$ mysql> SELECT * FROM <table_name>;

# Returns the columns and column information pertaining to the designated table.
$ mysql> show columns from <table_name>;

# Show certain selected rows with the value "whatever".
$ mysql> SELECT * FROM <table_name> WHERE <field_name> = "whatever";

# Show all records containing the name "Bob" AND the phone number '3444444'.
$ mysql> SELECT * FROM <table_name> WHERE name = "Bob" AND phone_number = '3444444';

# Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
$ mysql> SELECT * FROM <table_name> WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

# Show all records starting with the letters 'bob' AND the phone number '3444444'.
$ mysql> SELECT * FROM <table_name> WHERE name like "Bob%" AND phone_number = '3444444';

# Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.
$ mysql> SELECT * FROM <table_name> WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

# Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
$ mysql> SELECT * FROM <table_name> WHERE rec RLIKE "^a";

# Show unique records.
$ mysql> SELECT DISTINCT [column name] FROM <table_name>;

# Show selected records sorted in an ascending (asc) or descending (desc).
$ mysql> SELECT [col1],[col2] FROM <table_name> ORDER BY [col2] DESC;

# Return number of rows.
$ mysql> SELECT COUNT(*) FROM <table_name>;

# Sum column.
$ mysql> SELECT SUM(*) FROM <table_name>;

# Join tables on common columns.
$ mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

# To add a user and give rights on the given database
$ mysql> GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;

# To create a new user
# - Login as root
# - Switch to the MySQL db
# - Make the user
# - Update privs
$ mysql -u root -p
$ mysql> use mysql;
$ mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
$ mysql> flush privileges;

# Change a users password from unix shell.
$ mysqladmin -u username -h hostname.blah.org -p password 'new-password'

# To change a users password from MySQL prompt
# - Login as root
# - Set the password
# - Update privs
$ mysql -u root -p
$ mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
$ mysql> flush privileges;

# To recover a MySQL root password
# - Stop the MySQL server process.
# - Start again with no grant tables.
# - Login to MySQL as root.
# - Set new password.
# - Exit MySQL and restart MySQL server.
$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root
$ mysql> use mysql;
$ mysql> update user set password=PASSWORD("newrootpassword") where User='root';
$ mysql> flush privileges;
$ mysql> quit

$ /etc/init.d/mysql stop
$ /etc/init.d/mysql start

# Set a root password if there is on root password
$ mysqladmin -u root password newpassword

# To update the root password
$ mysqladmin -u root -p oldpassword newpassword

# To allow user "bob" to connect to server from localhost using password "passwd"
# - Login as root
# - Switch to the MySQL db
# - Give privs
# - Update privs
$ mysql -u root -p
$ mysql> use mysql;
$ mysql> grant usage on *.* to bob@localhost identified by 'passwd';
$ mysql> flush privileges;

# Give user privilages for a db
# - Login as root
# - Switch to the MySQL db
# - Grant privs
# - Update privs
$ mysql -u root -p
$ mysql> use mysql;
$ mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
$ mysql> flush privileges;
## or
$ mysql> grant all privileges on databasename.* to username@localhost;
$ mysql> flush privileges;

# To update info already in a table
$ mysql> UPDATE <table_name> SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where <field_name> = 'user';

# Delete a row(s) from a table.
$ mysql> DELETE from <table_name> where <field_name> = 'whatever';

# Update database permissions/privilages.
$ mysql> flush privileges;

# Delete a column.
$ mysql> alter table <table_name> drop column [column name];

# Add a new column to db.
$ mysql> alter table <table_name> add column [new column name] varchar (20);

# Change column name.
$ mysql> alter table <table_name> change [old column name] [new column name] varchar (50);

# Make a unique column so you get no dupes.
$ mysql> alter table <table_name> add unique ([column name]);

# Make a column bigger
$ mysql> alter table <table_name> modify [column name] VARCHAR(3);

# Delete unique from table
$ mysql> alter table <table_name> drop index [colmn name];

# Load a CSV file into a table
$ mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE <table_name> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

# Dump all databases for backup
$ mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

# Dump one database for backup
$ mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

# Dump a table from a database
$ mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

# Restore database (or database table) from backup
$ mysql -u username -ppassword databasename < /tmp/databasename.sql

# Create tables
$ mysql> CREATE TABLE <table_name> (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
$ mysql> create table <table_name> (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');


# references
# - http://www.pantz.org/software/mysql/mysqlcommands.html