IT/Software/Database Applications/mysql command line/mysqldump: Difference between revisions

From msgwiki
Jump to navigation Jump to search
Access restrictions were established for this page. If you see this message, you have no access to this page.
mNo edit summary
 
(5 intermediate revisions by the same user not shown)
Line 10: Line 10:


==Dumping To a Remote Host==
==Dumping To a Remote Host==
===Dump directly into a remote database===
<code>mysqldump  -ulocaluser -plocalpassword localdbname | ssh remoteuser@remotehost.com mysql -uremoteuser -premotepassword remotedbname</code>
===Dump to a file on a remote server===
<code>mysqldump -ulocaluser -plocalpassword localdbname | ssh remoteuser@remotehost.com 'cat  > /media/vte/backup_from_vte/databases/moodleVteDump.sql'</code>
===Making the remote dump faster and less resource intensive===
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)
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)



Latest revision as of 07:06, 16 February 2020

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'

Making the remote dump faster and less resource intensive

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>