iDog's MySQL Quick Reference

Account & Password

Login

shell> mysql -u<user> [-p[<password>]]

Log out

mysql> exit

mysql> quit

Change Password

After installation, set root's password to 'mypassword':

shell> mysqladmin -uroot password mypassword

Change password to 'newpassword':

shell> mysqladmin -uroot -pmypassword password newpassword

Add user

grant create, select, insert, update, delete on my_database.* to user1@localhost identified by "the_password";

Here we add a user called 'user1', he/she has rights of create/select/insert/update/delete on all tables in database 'my_database', the password of this account is 'the_password', and this user can only login MySQL from 'localhost' (this makes it very secure).

Show Information

List databases

mysql> show databases;

Use a database

mysql> use <database_name>;

List tables in database

mysql> use <database_name>;
mysql> show tables;

Database Manipulation

Create database

mysql> create database <database_name>;

Backup database

shell> mysqldump --opt mydatebase > mydatabase.txt

Execute .sql file

In mysql prompt:

mysql> source /path/to/file.sql

In shell:

shell> mysql <db_name> -u<usr> -p<pwd> < /path/to/file.sql