Dump a mysql table without locking

If you want to keep your site/application live, you can dump mysql tables online like so:

mysqldump --single-transaction --quick -hhostname -uuser -ppassword database > /path/to/destination-sqlbackup.sql

--compress = as it says, enabled compression on the connection to the mysql server. This will always speed up bulk loads 10x vs. using uncompressed connections.

--single transaction = Issue a BEGIN SQL statement before dumping data from server

--quick = This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.



-p password = password

Importing your dump is pretty easy too:

Inserting your backup back to the Mysql server is simple too:

cat destination-sqlbackup.sql | mysql -hhostname -uuser -ppassword database
enabling compression by default via /etc/my.cnf

add compress to your config like so each section needs it to enable default: