Thingy Ma Jig is the blog of Nicholas Thompson and contains any useful tips, sites and general blog-stuff which are considered interesting or handy!
Posted on 10 October 2006 in
programming
mysql
linux
geek
free
cool
This morning I am faced with a task that will involve repeatedly dropping and reimporting a lot of data. MySQL has DROP TABLE and DROP DATABASE but there is no command to drop all tables or truncate the database.
[adsense:468x60:4496506397]
After finding a reference on the MySQL Lists (http://lists.mysql.com/mysql/193430) I started playing with the idea. I didn't want to have to dump into one file and then run that query. Also - the problem with the above concept is that it drops the table and then recreates it - not what i wanted!
I then looked into using the pipe and grep features in Linux. Now I was getting somewhere! A few tweaks later and this is what I got:
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
In the above, [USERNAME], [PASSWORD] & [DATABASE] are all the details for your database. You might not need the username and password fields - depends on your setup!