Useful MySQL commands

From wiki.network-crawler.de
Jump to: navigation, search

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

mysql -p

or e.g. for parallels:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`
  • Dump all databases:
mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` --all-databases > alldb.sql
  • 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