mysqldump data only
Solution 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
Solution 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]
Solution 3
If you just want the INSERT queries, use the following:
mysqldump --skip-triggers --compact --no-create-info
Solution 4
>> 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
Solution 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
Comments
-
Lizard over 3 years
I am looking for the syntax for dumping all data in my mysql database. I don't want any table information.
-
James McMahon almost 12 years
--no-create-db
is redundant when using--no-create-info
-
Mulan about 10 yearsA space after
-p
is fine -
Petah about 10 years@JamesMcMahon Unless you are using
--databases
as well. -
Yzmir Ramirez over 9 yearsHere is the full cmd for the copy pasters
mysqldump -u USERNAME -h HOST --no-create-info --skip-triggers YOURDATABASENAME YOURTABLENAME --where='id=12345678'
-
Dan over 8 yearsIt'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).
-
TMG over 8 yearsIn addition to @Dan's advice, it also makes password visible to any user who can list current processes (
ps -ef
) -
Rolf about 8 yearsIf you get
Access denied for user ... to database '...' when doing LOCK TABLES
, use--single-transaction
-
Kip about 7 years@YzmirRamirez thanks but FYI on windows I had to use double-quotes for
--where
. For example,--where="id=2"
-
Diego Somar almost 7 yearsGood explanation
-
Melle almost 7 yearsLeaving out password after the
-p
option makesmysqldump
prompt for password. -
erwan about 6 yearsis there a reason for using twice the '-t' and '-d' option ?
-
JDuarteDJ about 6 yearsRegarding 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. -
Elkvis about 6 yearsI 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.
-
Ken Ingram over 4 yearsThis was a brilliant saver for me. Thanks.
-
chiliNUT about 4 yearsthank you for
--compact
which gets rid of the extra comment statements -
Vector Gorgoth over 3 yearsunfortunately, 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.
-
Maroun over 3 years@Thankyou A space after
-p
will prompt a password.