CODEWITHSUNDEEP
mysql
khelll
khelll

Reputation: 24020

Create table if not exists from mysqldump

I'm wondering if there is any way in mysqldump to add the appropriate create table option [IF NOT EXISTS]. Any ideas?

Upvotes: 35

Views: 43228

Answers (8)

Artistan
Artistan

Reputation: 2037

Create a bash script with this. E.g. dump.sh touch dump.sh

Make sure you make it executable: chmod 0750 dump.sh

dump.sh:

#!/bin/bash

name="mysqldump_${HOSTNAME}_$(date +'%Y-%m-%d_%H-%M').sql"
echo "Using filename: ${name}"
mysqldump --replace --skip-add-drop-table --skip-comments izon -p > "${name}"
sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' "${name}"

Upvotes: 4

Mostafa Barmshory
Mostafa Barmshory

Reputation: 2039

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

Upvotes: 6

Celepine
Celepine

Reputation: 151

The sed will be much faster without the 'g' (global) at its end:

eg:

mysqldump -e <database> | sed 's/^CREATE TABLE /CREATE TABLE IF NOT EXISTS /' > <database>.sql

Upvotes: 2

Franc
Franc

Reputation: 988

To Find and replace the text On Windows 7 Using PowerShell

Open command prompt and use the command below

powershell -Command "(gc E:\map\map_2017.sql) -replace 'CREATE TABLE', 'CREATE TABLE IF NOT EXISTS' | Out-File E:\map\map_replaced.sql"

  • First param is the filepath

  • Second param is 'find string'

  • Third param is 'replace string'

This command will create a new file with the replaced text. Remove the Command starting from '|' (pipe) if you want to replace and save contents on the same file.

Upvotes: 0

Pawel
Pawel

Reputation: 361

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

Upvotes: 36

sfussenegger
sfussenegger

Reputation: 36115

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.

Upvotes: 19

Daniel Vassallo
Daniel Vassallo

Reputation: 344571

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.

Upvotes: 22

Boldewyn
Boldewyn

Reputation: 82814

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).

Upvotes: 9

Related Questions