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
add compress to your config like so each section needs it to enable default:
[server] compress [client] compress [mysqldump] compress