IT/Software/Database Applications/mysql command line/mysqldump: Difference between revisions
Access restrictions were established for this page. If you see this message, you have no access to this page.
(added note) |
Walttheboss (talk | contribs) |
||
| (9 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
==Options== | ==Options== | ||
#You can do this from phpMyAdmin or other | #You can do this from phpMyAdmin or other GUI based apps. | ||
##See the Applications page for this. | ##See the Applications page for this. | ||
#You can perform the dump to various resources. | #You can perform the dump to various resources. | ||
| 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) | ||
The above problem can be easily circumvented by | The above problem can be easily circumvented by adding --quick and --single-transaction to the mysqldump command. | ||
mysqldump --verbose -u<localDbUser> -p<localDbPassord> <localDbName> | |||
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> | |||
Latest revision as of 07:06, 16 February 2020
Options
- You can do this from phpMyAdmin or other GUI based apps.
- See the Applications page for this.
- You can perform the dump to various resources.
- Dump to a text file.
- Dump to a zipped file
- Dump to another database
- 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>