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.
VisualWikitext
No edit summary
No edit summary
Line 12: Line 12:
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 dumping to a file and than cating the file into ssh to the remote host as such.
The above problem can be easily circumvented by adding --quick and --single-transaction to the mysqldump command.
mysqldump --verbose -u<localDbUser> -p<localDbPassord> <localDbName> > /tmp/<dbName>.sql && cat /tmp/<dbname>.sql | ssh user@remote.host mysql -u<remoteDbUser> -p'<remoteDbPassword>' <remoteDbName>
Note: The above should work without issue but has not undergone much testing yet.


Note2: When running a dump using the above method the remote database differed in files size from the source by about 20-30M on a 100M database.
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> > /tmp/<dbName>.sql | ssh user@remote.host mysql -u<remoteDbUser> -p'<remoteDbPassword>' <remoteDbName>

Revision as of 18:21, 6 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

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> > /tmp/<dbName>.sql | ssh user@remote.host mysql -u<remoteDbUser> -p'<remoteDbPassword>' <remoteDbName>
Retrieved from "https://msgwiki.msgeducation.com/index.php?title=IT/Software/Database_Applications/mysql_command_line/mysqldump&oldid=428"