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

From msgwiki
Jump to navigation Jump to search

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 dumping to a file and than cating the file into ssh to the remote host as such. 

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.

Retrieved from "https://msgwiki.msgeducation.com/index.php?title=IT/Software/Database_Applications/mysql_command_line/mysqldump&oldid=426"