Backup and Restore MySQL DB using mysqldump command line tool

  • December 7, 2017
  • Posted by: iSummation Team
  • Category: Uncategorized
No Comments

MySQL Workbench and there plenty of great GUI tool and allows you to work with various database related functionality including backup/restore but in a certain case, we may not able to use GUI tool. For an example, Need to perform operation command line server or to make automation like sync my production database to test database before performing test via automation tool)

 

Recently I required copying production database on MySQL server to staging server database and I do not want it to copy entire 100 GB database to my local server and upload it again as this will be very time-consuming. A better option will be I can somehow take backup of Ubuntu Server and restore it back to a Staging Server which is pretty faster option to work with.

 

mysqldump is command line tool come with MySQL Client ToolSet which is used by all GUI tool as well for backup database. 

 

Use Case #1:

Copying database on different server but having the same name.

Backup from source DB

Error when loading gists from https://gist.github.com/.mysqldump –host [hostname] -u [username] -p –databases [dbname] > backup.sql 

–databases will make sure to add "USE [dbname]" in export so it you may not required to choose database while restoring.

Restore on destination db

Error when loading gists from https://gist.github.com/.mysql –host [destinationhostname] -u [username] -p < backup.sql 

Above command will restore it on database with same name.

 

Use Case #2:

Copying database on probably same DB server but with a different name.

Error when loading gists from https://gist.github.com/.mysqldump –host [hostname] -u [username] -p [dbname] > backup.sql 

Note: No –databases option which will make sure it WON’T add "USE [dbname]" in export.

Restore on destination db

Error when loading gists from https://gist.github.com/.mysql –host [destinationhostname] -u [username] -p [dbname2] < backup.sql 

You may need to specify database name in which like to restore if exported without –databases option and this will allow you to restore with different name.