Create table if not exists from mysqldump

mysql mysqldump

35,267

Solution 1

According to one source, mysqldump does not feature this option.

You could use the --force option when importing the dump file back, where MySQL will ignore the errors generated from attempts to create duplicate tables. However note that with this method, other errors would be ignored as well.

Otherwise, you can run your dump file through a script that would replace all occurrences of CREATE TABLE with CREATE TABLE IF NOT EXISTS.

Solution 2

Try to use this on your SQL file:

sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' <file-path>

or to save

sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' <file-path>

it's not ideal but it works :P

Solution 3

Using sed as described by @Pawel works well. Nevertheless you might not like the idea of piping your data through more potential error sources than absolutely necessary. In this case one may use two separate dumps:

first dump containing table definitions (--no-data --skip-add-drop-table)
second dump with only data (--no-create-info --skip-add-drop-table)

There are some other things to take care of though (e.g. triggers). Check the manual for details.

Solution 4

Not what you might want, but with --add-drop-table every CREATE is prefixed with the according DROP TABLE statement.

Otherwise, I'd go for a simple search/replace (e.g., with sed).

Solution 5

The dump output is the combination of DROP and CREATE, so you must remove DROP statement and change the CREATE statement to form a valid (logical) output:

 mysqldump --no-data -u root <schema> | sed 's/^CREATE TABLE /CREATE TABLE IF NOT EXISTS /'| sed 's/^DROP TABLE IF EXISTS /-- DROP TABLE IF EXISTS /' > <schema>.sql

View more solutions

35,267