dumping a MySQL table in chunks

Question

I would like to backup one big innodb MySQL table, but unfortunately don't have enough free space to create a full dump of it. So I thought about dividing the backup to two parts. Meaning I will dump half of the table, move that file to an external backup location, and than backup the second half and move it also.

I'm used to doing: mysqldump --user=$user --password=$pass --single-transaction --quick

How can I perform such an operation one half at a time?

Answer 1

You can backup specifics tables like this

Tables t1, t2, and t3 from your DB.

mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

Or you can create a mouting point (unix) or network drive (windows) and make your backup directly on it.

If you want to backup just a table in multiple part

mysqldump --databases X --tables Y --where="1 limit 1000000"
mysqldump --databases X --tables Y --where="myColumn < 1000"