Dump only the data with mysqldump without any table information

Question

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

Answer 1

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

Answer 2

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]

Answer 3

Just dump the data in delimited-text format.

Answer 4

Try to dump to a delimited file.

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

Answer 5

For example, using -t(--no-create-info), you can export only the data of the tables of apple database to backup.sql with INSERT statement which doesn't have column names as shown below. *My answer explains how to export both the schema and data of the tables of a database:

mysqldump -u john -p -t apple person > backup.sql

Or:

mysqldump -u john -p --no-create-info apple > backup.sql

And, using -c(--complete-insert), you can export only the data of the tables of apple database to backup.sql with INSERT statement which has column names as shown below:

mysqldump -u john -p -t -c apple > backup.sql

Or:

mysqldump -u john -p --no-create-info --complete-insert apple > backup.sql

And, not using or using --tables, you can export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below:

mysqldump -u john -p -t -c apple person animal > backup.sql

Or:

mysqldump -u john -p --no-create-info --complete-insert apple --tables person animal > backup.sql

And, using --ignore-table=., you can export only the data of the tables except person and animal of apple database to backup.sql with INSERT statement which has column names as shown below. *You must use multiple --ignore-table to specify multiple tables and you must specify both a database and a table together <database>.<table> for --ignore-table otherwise there is error:

mysqldump -u john -p -t -c apple --ignore-table=apple.person --ignore-table=apple.animal > backup.sql

Answer 6

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

Answer 7

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

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

Answer 8

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

Answer 9

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

Answer 10

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