Password Management:

mysqladmin -u root password <myPassword>

mysqladmin -u root -p <oldpassword> <newpass>

mysqladmin -u <myuser> -p <oldpassword> <newpass>


Reset lost root password:

restart mysql in safe mod:
 /etc/init.d/mysql stop
 mysqld_safe --skip-grant-tables &

mysql -u root
use mysql;
update user set password=PASSWORD("<newpass>") where user='root';
flush privileges;
quit;

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


Create database:

create database <databaseName>;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, LOCK TABLES on
  <databaseName>.* TO '<myuser>'@'localhost' IDENTIFIED BY '<newpass>';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, LOCK TABLES on
  <databaseName>.* TO '<myuser>'@'xxx.xxx.xxx.xxx' IDENTIFIED BY '<newpass>';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, LOCK TABLES on
  <databaseName>.* TO '<myuser>' IDENTIFIED BY '<newpass>';

flush privileges;


Dump / Restore:

mysqldump -u root -R --password='<mypasswd>' <dbname> >  mydb.dump
mysql -h <hostname> -u <username> -p <dbname>  < mydb.dump


Show Progress

show processlist;


Convert database from latin1 to utf8:

mysqldump -u <username> -p <dbName> | sed 's/CHARSET=utf8/CHARSET=utf8/' > mydump.sql

mysql -u <username> -p 
DROP DATABASE <dbname>;
CREATE DATABASE <dbname> CHARACTER SET utf8 COLLATE utf8_general_ci;

mysql -u <username> -p dbName < mydump.sql


Show stored procedures

mysql> SHOW PROCEDURE STATUS;


count # of tables containing keyword in database

use information_schema;
SELECT count(*) FROM TABLES WHERE TABLE_SCHEMA = '<DatabaseName>' AND TABLE_NAME LIKE '<keyword_>%';


misc commands

show tables;
describe <tableName>;
mysql -u <username> -p <dbname> -e "select * from <tableName>;"
# -B  to do a select in batch mode and seperate each fields with a tab
# -N  to suppress headers