How to mysqldump WITHOUT dropping any tables

Question

Normally, when I backup the database, I run a command like this:

mysqldump -uuser -p -hhost -Ddatabase > C:\TEMP\db_2018-04-05.sql

Inside that file, there are DROP table statements. This is normally fine, but I've modified my localhost to have a different schema than the production database.

If I execute this file, it will blow away the important changes to the database schema on my localhost.

All I need is the INSERT statements. Is there any flag I can pass mysqldump to achieve this?

score 24 · Accepted Answer · answered Apr 05 '18 at 23:29

24

Include the command for the mysqldump ignore the structure.

mysqldump --no-create-info ...

answered Apr 05 '18 at 23:29

Matheus Sant Anna de Oliveira

900
13
21

2

this one skips both dropping the table and creating the table – Jiechao Wang Jun 17 '22 at 01:35
Also there is the short version `-t` – But those new buttons though.. Nov 29 '22 at 14:50

score 20 · Answer 2 · edited Nov 11 '20 at 07:49

20

All you need is add --skip-add-drop-table option when using mysqldump.

$ mysqldump -uuser -p -hhost -Ddatabase --skip-add-drop-table > C:\TEMP\db_2018-04-05.sql

Now no DROP TABLE IF EXISTS in SQL files.

see docs of mysql on --skip-add-drop-table.

edited Nov 11 '20 at 07:49

Maroun

94,125
30
188
241

answered Apr 05 '18 at 23:32

mcv

1,380
3
16
41

This will still do `CREATE TABLE` statements so if the table exists (as OP mentions), you will get an error like `ERROR 1050 (42S01) at line 24: Table 'my_table' already exists` – But those new buttons though.. Nov 29 '22 at 14:49

score 1 · Answer 3 · answered Apr 15 '23 at 17:46

for dumping only the table data as stated in Dump only the data with mysqldump without any table information? do the following:

mysqldump --no-create-info ...

Also you may use:

--skip-triggers: if you are using triggers

--no-create-db: if you are using --databases ... option

--compact: if you want to get rid of extra comments

How to mysqldump WITHOUT dropping any tables

3 Answers3