Question 112784 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Remove Records From a Dumped SQL file

I have a dumped sql file named my_dump.sql.

Within that dump file there are various tables. I’m interested in the table named foo_bars which has a bunch of records. I would like to modify this sql file by doing the following:

  • keep the foo_bars table structure, but remove all it’s records from the sql file.

The outcome: when I create a new database via this sql file, It properly has the foo_bars table, but all the records are gone.

Note: I realize I could create a new database with all the foo_bars table records intact and then issue a delete statement once the database is created. I do not want to do this though because there are tons of records. I’d rather remove the records beforehand from the sql file.

How might I do this?

Tags:

3

Answers


  1. 0

    Do you still have access to the source MySQL instance where you got the dump file? You could create a new dump file with the --no-data option. That dumps the table definition without any INSERT statements following.

    Another tricky alternative: edit your dump file and change the table options to add ENGINE=BLACKHOLE (replacing the existing engine option on that table, if there is one). Then all the following INSERT statements will be processed, but will result in no storage.

    After you finish processing the dump file, run ALTER TABLE foo_bars ENGINE=InnoDB; to change the table back to InnoDB (or whatever engine you intend it to use, but I’d prefer InnoDB).

    Read more about BLACKHOLE here: https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html

    You haven’t shown the table you want to handle in this way, so I can’t guarantee that your table will be compatible with the blackhole engine. I can’t think of any potential incompatibilities off the top of my head, but I haven’t tried it so I can’t be sure.

    Login or Signup to reply.
  2. 0

    I’m not sure if this method will help you, because as you said, all the records are gone.
    Search the my_dump.sql file for all INSERT INTO foo_bars commands, and replace with INSERT IGNORE INTO foo_bars, so that all duplicate records are not inserted into your table.
    Let me know if it worked

    Login or Signup to reply.
  3. 0

    I think Bill Karwin solution is the best and the fastest. You can also do:

    Edit your dump file to add before inserting into foo_bars

    START TRANSACTION;
SET autocommit = 0;

    After the end of the last insert statement into foo_bars

    ROLLBACK;
SET autocommit = 1;

    You can read more about autocommit mode, ROLLBACK an transactions here: https://dev.mysql.com/doc/refman/8.0/en/commit.html

    If the dump it’s too big to edit, you can also drop the table and create it again, it’s faster than deleting the records.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search