MySQL most used commands

Started with system database of mysql

$ mysql -uroot -ppassword mysql

Create a database:

mysql> CREATE database mydb
mysql> drop database mydb;
mysql> show databases;


Corresponding Unix system command

$ mysqladmin drop mydb
$ mysqlshow


To add password

$ mysqladmin password xxxx


To export database

$ mysqldump -u myuser -pmypass mydb > exl.sql


To add a user to a database:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on mydb.* to 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

or

mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','mypassword');


To add a user that can access from other client machines, use % as the host

mysql> GRANT SELECT on db1.* to 'myuser'@'%' IDENTIFIED BY 'mypassword';


To change password, use

mysql> update user set password=PASSWORD(“NEW-PASSWORD”) where User=’myuser’;


To change permissions, use

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on mydb.* to myuser;


or

mysql> GRANT ALL on mydb.* to myuser
mysql> FLUSH PRIVILEGES;


— special for loading file

mysql> GRANT FILE on *.* to myuser
mysql> LOAD DATA INFILE '/mypath/myfile' INTO TABLE mytable FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';


To check the error such as the infamous “ERROR 1005 (HY000)”, use

mysql> SHOW ENGINE INNODB STATUS