Mysql dump by query

Command mastery for selective row dumps

Dumping specific rows

mysqldump is handy when backing up part of a table. The --where option exports rows fitting a condition:

mysqldump -u user -p --databases myDatabase --tables myTable --where="myColumn > 200" > selectiveDump.sql


// "// Just like fishing. Don't need the whole sea, just a few specific fish!" 😏

Exclude table structure

For dumps that contain only data use the --no-create-info flag:

mysqldump -u user -p --no-create-info myDatabase myTable > dataDump.sql


// "// Who needs a blueprint when we're moving furniture, am I right?" 😄

Connection essentials

Don't forget to state host, user, and password:

mysqldump -h myHost -u myUser -pMyPassword myDatabase > databaseDump.sql


Limit amount of exported records

Limit the number of rows using --where for data samples:

mysqldump -u user -p myDatabase myTable --where="1 ORDER BY myColumn DESC LIMIT 100" > limitedDump.sql


// "// Slow and steady wins the race, right? Yeah, not in SQL!" 😂

Diving into exporting options and considerations

Emulating phpMyAdmin

Crave for a GUI-like command line? mysqldump can simulate phpMyAdmin's export functionality:

mysqldump -u user -p --no-create-info --complete-insert database table > phpMyAdminStyle.sql


// "// Now who said command lines can't be user-friendly? 🎉"

Exporting data to CSV

Need the data in a spreadsheet format? Use SELECT INTO OUTFILE:

SELECT * INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM myTable;


// "// Dear Excel nerds, we didn't forget about you!" 🤓

Unraveling the command differences

mysqldump is designed to preserve full database integrity, while SELECT outputs focus on data substance.

Dealing with enormous data

For large databases, consider mydumper and myloader, which support multi-threading for fast data processing and restoration. // "// Size really does matter... in databases" 📏

Understand backups for diverse needs

Whole database backup

Keep it simple for complete database backups:

mysqldump -u user -p --databases myDatabase > fullDatabaseDump.sql


Data migration

Migrating between servers? Try this:

mysqldump -u user -p myDatabase | mysql -h otherHost -u user -p -D myDatabase


// "// Who needs a moving truck when you have pipes?" 😎

Secure your data

Working with sensitive data? Run your SQL dumps through gzip to encrypt and compress:

mysqldump -u user -p myDatabase | gzip > databaseDump.sql.gz
