Useful MySQL commands

From wiki.network-crawler.de

First of all you'll have to login. As root I use:

mysql -p

• List databases:

mysql> show databases;

Now you know what databases exist. Though you sure want connect to one.

• Connect to a database:

mysql> use itsmeyourfavouritedatabase;
Database changed

This is the right time to list the tables included in your database:

• List tables:

mysql> show tables;

Often it's likely you want to create a brand new database:

• Create new database:

create database brandnewdatabase;

If you messed up with creating a database you can easyly delete it again

• Delete database:

drop database messeddatabase;

To create new users which are only allowed to connect from localhost:

• Add new user:

use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'newusername'@'localhost' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

To create new users which are allowed to connect from anywhere:

• Add new user:

use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'newusername'@'%' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

To grand both (logically):

use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'newusername'@'localhost' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'newusername'@'%' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

To repair your database e.g. after session crack (damn script kiddies) use:

REPAIR TABLE <table_name> QUICK;

References:

http://www.pantz.org/database/mysql/mysqlcommands.shtml http://dev.mysql.com/doc/refman/5.0/en/adding-users.html