Useful MySQL Commands

Suppose if you using a shared host service and u have given only one database to use. Sometime you need to empty the all the tables inside the Database ?

In this scenario,

You have not given an adequate privileges to create a new database. Then use this simple query which will empty the all tables inside a database.

$mysqldump -u[USERNAME] -p[PASSWORD] –add-drop-table –no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

##############################################################

How to set values on MySQL variables
in linux create a file on /etc/my.cnf Or in windows default files found on C:Program FilesMySQLMySQL Server 5.0 named my.ini
2.Edit that file and add the variable ame in to it
Eg :lower_case_table_names=0 ( For making case mysql as case sensitive )

################ how to test it is working ######################

Login as root user in mysql console
mysl>SHOW VARIABLES LIKE ‘lower_case%’;
################### Database dump ###########

$mysqldump –all-databases -r alldump.sql -uroot -p

############ Setting a proper privilages towards a database #####

mysql>GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON <databasename>.* TO ‘<username>’@’localhost’ IDENTIFIED BY ‘<password>’;