IT/Software/Database Applications/mysql command line/mysqldump

Options

1. You can do this from phpMyAdmin or other GUI based apps.
   1. See the Applications page for this.
2. You can perform the dump to various resources.
   1. Dump to a text file.
   2. Dump to a zipped file
   3. Dump to another database
   4. Dump to another Database on another server

Dumping To a Remote Host

Dump directly into a remote database

mysqldump -ulocaluser -plocalpassword localdbname | ssh remoteuser@remotehost.com mysql -uremoteuser -premotepassword remotedbname

Dump to a file on a remote server

mysqldump -ulocaluser -plocalpassword localdbname | ssh remoteuser@remotehost.com 'cat  > /media/vte/backup_from_vte/databases/moodleVteDump.sql'

When running a mysql dump to a remote host directly over ssh the host database will be locked until the sync has finished. (Can be up to 5-10 minutes for lager databases)

The above problem can be easily circumvented by adding --quick and --single-transaction to the mysqldump command.

Tells mysql to only retrieve one row at a time.

--quick

Starts the transaction without locking the entire DB.

--single-transaction

Example dump:

mysqldump --verbose --quick --single-transaction -u<localDbUser> -p<localDbPassord> <localDbName> | ssh user@remote.host mysql -u<remoteDbUser> -p'<remoteDbPassword>' <remoteDbName>