useful mysql commands and snippets

For most of my toy projects i work with mysql. It is easy to setup on windows and came bundled with my mac OS X iBook. There are few tasks that I keep doing time and again. Like creating users , doing a mysql dump and analyzing the tables. First let me list the often-repeated tasks

• How to create a mysql database
• How to create a mysql database user
• How to load a SQL file in MySQL Database(basically a batch file)
• How to dump mysql database using mysqldump
• How to dump mysql schema
• How to dump mysql tables data
• How to load data from previous mysql dump
• How to analyze mysql tables

Create MySQL Database

 
$mysql/> create database gloo;
 

Create MySQL local database user

 
mysql> grant all privileges on gloo.*
-> to 'snoopy'@'localhost' identified by 'peanuts'
-> with grant option
-> ;
Query OK, 0 rows affected (0.00 sec)
 

Create MySQL remote database user

 
mysql> grant all privileges on gloo.*
-> to 'snoopy'@'%' identified by 'peanuts'
-> with grant option
-> ;
Query OK, 0 rows affected (0.00 sec)
 

Load a SQL file into MySQL database

 
$mysql -u gloo -p < /usr/rjha94/schema.sql
 

Dump MySQL database using mysqldump utility 

 
syntax is mysqldump -u {user} -p {database} > {file_name}
$ mysqldump -u snoopy -p gloo > gloo.full.sql
 

Dump MySQL schema

 
(useful for creating schema across sites)
$mysqldump --no-data --add-drop-table -u snoopy -p gloo > gloo.schema.sql
[ This will not load the row information. ]
 

Dump MySQL database tables

 
$mysqldump --no-create-info -u snoopy -p gloo > gloo.data.sql
 

Load data from a dump file into MySQL

 
$ mysql -u snoopy -p < gloo.sql