Use this command:

mysqldump -u <username> -p<password> -q <database> | bzip2 -c > filename.sql.bz2

Normally, when importing a mysql database default character set will be UTF8. If not,
we can set it manually with the below methods….

1. mysql -u <username> -p –default_character_set ‘utf8′ databasename < database.sql(exported database).

2. if you are using a compressed file, then use…
1. If bzip2:
bzip2 -cd <database.bz2> | mysql –host=localhost –user=<username> -p<password> –default_character_set ‘utf8′ <database name>

This can be done easily by using phpmyadmin.When importing a database just select UTF8 from the below select box named ‘character set of the file’.

We can drop tables using the query “droptable table name;”. We van do it only one by one.So this is impractical when there is lot of tables. In such situation we can use the below given command:

mysql -u <username> -p<password> <database name> -e “show tables” | grep -v Tables_in | grep -v “+” | \gawk ‘{print “drop table ” $1 “;”}’ | mysql -u <username> -p<password> <database name>

Commonly arising problems:

1. gawk(Gnu awk): you must install this module.gawk is a pattern cheking language.
2. If you give password with this command(in the place of <password> you may avoid the space between -p and password.