377

I am looking for the syntax for dumping all data in my mysql database. I don't want any table information.

Jesse Nickles
  • 1,435
  • 1
  • 17
  • 25
Lizard
  • 43,732
  • 39
  • 106
  • 167
  • [https://dev.mysql.com/doc/refman/5.7/en/mysqldump-definition-data-dumps.html](https://dev.mysql.com/doc/refman/5.7/en/mysqldump-definition-data-dumps.html) – Stack Underflow Dec 19 '19 at 19:50

9 Answers9

671
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
aasmpro
  • 554
  • 9
  • 21
matei
  • 8,465
  • 1
  • 17
  • 15
  • 59
    Here is the full cmd for the copy pasters `mysqldump -u USERNAME -h HOST --no-create-info --skip-triggers YOURDATABASENAME YOURTABLENAME --where='id=12345678'` – Yzmir Ramirez Sep 22 '14 at 21:35
  • 4
    If you get ``Access denied for user ... to database '...' when doing LOCK TABLES``, use ``--single-transaction`` – Rolf Feb 05 '16 at 12:22
  • @YzmirRamirez thanks but FYI on windows I had to use double-quotes for `--where`. For example, `--where="id=2"` – Kip Feb 20 '17 at 19:27
189

This should work:

# To export to file (data only)
mysqldump -u [user] -p[pass] --no-create-info mydb > mydb.sql

# To export to file (structure only)
mysqldump -u [user] -p[pass] --no-data mydb > mydb.sql

# To import to database
mysql -u [user] -p[pass] mydb < mydb.sql

NOTE: there's no space between -p & [pass]

keiththomps
  • 8,005
  • 3
  • 15
  • 17
Ish
  • 28,486
  • 11
  • 60
  • 77
  • 13
    `--no-create-db` is redundant when using `--no-create-info` – James McMahon Jun 13 '12 at 18:30
  • 4
    A space after `-p` is fine – Mulan Mar 14 '14 at 02:28
  • @JamesMcMahon Unless you are using `--databases` as well. – Petah Mar 25 '14 at 00:46
  • 18
    It's generally better to just use -p without supplying the password so that the password isn't stored in your bash history (you'll be prompted to enter the password after you run the command). – Dan Aug 06 '15 at 10:19
  • 2
    In addition to @Dan's advice, it also makes password visible to any user who can list current processes (`ps -ef`) – TMG Aug 08 '15 at 06:55
  • Good explanation – Diego Somar Jun 01 '17 at 10:50
  • 2
    Leaving out password after the `-p` option makes `mysqldump` prompt for password. – Melle Jun 09 '17 at 14:52
  • Regarding password argument I prefere to use the explicit version `--password='MyOddlyLongPAssword2018'` This way I don't have mistakes about spaces between -p and password and if I use single quotes it usually protects specific characters like `<` from screwing up the command. – JDuarteDJ Mar 28 '18 at 09:54
  • unfortunately, mysql allocates a fixed buffer for -p input which is exceeded by a moderately strong password. unless this issue has been fixed in recent years. – Vector Gorgoth Sep 11 '20 at 00:12
  • @Thankyou A space after `-p` will prompt a password. – Maroun Nov 12 '20 at 14:24
29

If you just want the INSERT queries, use the following:

mysqldump --skip-triggers --compact --no-create-info

Jonathan
  • 13,947
  • 17
  • 94
  • 123
24
 >> man -k  mysqldump [enter in the terminal]

you will find the below explanation

--no-create-info, -t

Do not write CREATE TABLE statements that re-create each dumped table. Note This option does not not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

--no-data, -d

Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).

# To export to file (data only)
mysqldump -t -u [user] -p[pass] -t mydb > mydb_data.sql

# To export to file (structure only)
mysqldump -d -u [user] -p[pass] -d mydb > mydb_structure.sql
Angelin Nadar
  • 8,944
  • 10
  • 43
  • 53
5

Best to dump to a compressed file

mysqldump --no-create-info -u username -hhostname -p dbname | gzip > /backupsql.gz

and to restore using pv apt-get install pv to monitor progress

pv backupsql.gz | gunzip | mysql -uusername -hhostip -p dbname
mikoop
  • 1,981
  • 1
  • 18
  • 18
3

Would suggest using the following snippet. Works fine even with huge tables (otherwise you'd open dump in editor and strip unneeded stuff, right? ;)

mysqldump --no-create-info --skip-triggers --extended-insert --lock-tables --quick DB TABLE > dump.sql

At least mysql 5.x required, but who runs old stuff nowadays.. :)

wuzer
  • 97
  • 1
  • 4
  • I ran into a situation recently, where had to connect to a system that was using 4.x in production. They had spaces in table and column names, and in my opinion, they were doing basically everything wrong. – Elkvis Apr 26 '18 at 17:37
1

When attempting to export data using the accepted answer I got an error: 

ERROR 1235 (42000) at line 3367: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

As mentioned above: 

mysqldump --no-create-info

Will export the data but it will also export the create trigger statements. If like me your outputting database structure (which also includes triggers) with one command and then using the above command to get the data you should also use '--skip-triggers'.

So if you want JUST the data: 

mysqldump --no-create-info --skip-triggers
Ben Waine
  • 1,650
  • 3
  • 21
  • 34
1

Just dump the data in delimited-text format.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
1

Try to dump to a delimited file.

mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-enclosed-by=\" --fields-terminated-by=,
ed209
  • 228
  • 1
  • 7