If you like to delete all tables from database using unix command then below is the solution


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

If you get error on above code

ERROR 1217 (23000) at line 1: Cannot delete or update a parent row:
a foreign key constraint fails

Then you can follow this step

(echo 'SET foreign_key_checks = 0;';
(mysqldump -u[USERNAME] -p[PASSWORD] -h [HOSTNAME] --add-drop-table --no-data [DATABASE] |
grep ^DROP);
echo 'SET foreign_key_checks = 1;') | \
mysql -u[USERNAME] -p[PASSWORD] -h [HOSTNAME] -b [DATABASE]

This will ignore foreign key constraint

Leave a Reply

Your email address will not be published. Required fields are marked *