How to Drop All Tables in SQL Dump File
I am currently moving a WordPress blog from one server to another. In the process, I messed up the MySQL database transportation, resulting in some tables being created, and some rows being inserted. It would not be possible to simply run the SQL dump files again, since that would result in duplicate rows. This was easy to fix with some commandline and perl magic, though. The file containing all SQL statements is called wordpress-dump.sql (in this example).
First, find the names of all tables:
$ grep -E '^CREATE TABLE' wordpress-dump.sql CREATE TABLE `cat_visibility` ( CREATE TABLE `comments` ( CREATE TABLE `links` ( CREATE TABLE `movie_ratings` ( CREATE TABLE `options` ( CREATE TABLE `photopress` ( CREATE TABLE `postmeta` ( CREATE TABLE `posts` ( CREATE TABLE `pp_cats` ( CREATE TABLE `sk2_logs` ( CREATE TABLE `sk2_spams` ( CREATE TABLE `term_relationships` ( CREATE TABLE `term_taxonomy` ( CREATE TABLE `terms` ( CREATE TABLE `usermeta` ( CREATE TABLE `users` (
Next, reformat those lines into DROP queries.
$ grep -E '^CREATE TABLE' wordpress-dump.sql | perl -pe 's/CREATE/DROP/; s/ *\($/;/;'
Now, you won’t have to change those by hand!
Använder FireStats