Password Management:
mysqladmin -u root password
mysqladmin -u root -p
mysqladmin -u -p
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("") where user='root';
flush privileges;
quit;
/etc/init.d/mysql stop
/etc/init.d/mysql start
Create database:
create database ;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, LOCK TABLES on
.* TO ''@'localhost' IDENTIFIED BY '';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, LOCK TABLES on
.* TO ''@'xxx.xxx.xxx.xxx' IDENTIFIED BY '';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, LOCK TABLES on
.* TO '' IDENTIFIED BY '';
flush privileges;
Dump / Restore:
mysqldump -u root -R --password='' > mydb.dump
mysql -h -u -p < mydb.dump
Show Progress
show processlist;
Convert database from latin1 to utf8:
mysqldump -u -p | sed 's/CHARSET=utf8/CHARSET=utf8/' > mydump.sql
mysql -u -p
DROP DATABASE ;
CREATE DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql -u -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 = '' AND TABLE_NAME LIKE '%';
misc commands
show tables;
describe ;
mysql -u -p -e "select * from ;"
# -B to do a select in batch mode and seperate each fields with a tab
# -N to suppress headers