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>’;
Leave a Reply