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

MySQL import single database and ignore drop & create database statements

I have a mysql dump that I need to import. The syntax of the file is like this:

DROP DATABASE IF EXISTS db1
CREATE DATABASE db1  
USE db1
CREATE TABLE A  
INSERT INTO TABLE A VALUES(...)  
..  
DROP DATABASE IF EXISTS db2
CREATE DATABASE db2 
USE db2 
CREATE TABLE A  
INSERT INTO TABLE A VALUES(...)  
..

This continues a few more databases

I find that if I run "mysql -D db1 –one-database db1 < master_dump_file.sql".. it will drop & recreate database DB2 (but not create table or rows within db2). How can I avoid deleting data from db2 while loading db1 individually?

Loading the entire file is not an option because there are about 7 more databases which I don’t need, and a full load of the dump file takes too much time.

Tags:

2

Answers


  1. 0

    If you’re clever with a text-processing tool like sed, perl, or awk, you could separate the part of the file you want from the rest, so you end up with only the 1 database you need, omitting the rest.

    Example:

    sed -n -e '1,10000p' master_dump_file.sql > just_one_database.sql

    This outputs lines 1 through 10000 (assuming you know which lines you need).

    You can also use pattern-matching to determine the range of lines.

    You can use patterns to exclude lines too, to remove the DROP TABLE statements.

    An alternative strategy is to restore the whole dump to a test server, and then use mysqldump to produce a new dump file, including only the single database you need.

    You can use mysqldump with the --skip-add-drop-table option to omit the DROP TABLE statements from the dump output.

    Login or Signup to reply.
  2. 0 
      sed '1,/^DROP DATABASE IF EXISTS db2;/ {
  /^DROP DATABASE IF EXISTS db2;/ d ; p ;
  } ; d' master_dump_file.sql | mysql -D db1

    Here sed takes the lines from the start of the file up-to DROP DATABASE IF EXISTS db2; and ignores the rest. Of the lines it takes it emits those that are not DROP DATABASE IF EXISTS db2; .

    The result of that is piped to mysql -D db1

    This relies on the ordering of the data in the dump file and the names used. if that changes it will need to be updated.

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

Back To Top
Search