MySQL Commands, Tips and Theory

Commands

mysql -u <user> -p<password> # login, without prompt

Note: Password is in plain text, no space between flag and value

mysql -u <user> -p # login, with password prompt

### Tables

Quick Show All Databases

mysqlshow -u username -p

### Import/Export

mysql -u <user> -p <db_name> <file> # e.g. my_datbase.sql

Tip: If you're SQL file is in a compressed format such as .sql.gz you can unzip and import in one command:

gunzip < <file.sql.gz> | mysql -u <user> -p <db_name>

### Users

List users

SELECT User from mysql.user

Add a user

CREATE USER '<user>'@'localhost' identified by '<password>';

Grant user access to a database

GRANT ALL privileges on <db_name>.* to '<user>'@'localhost';

Sometimes you need to reload the privileges TODO: Find out when

FLUSH privileges;