jump to navigation

Backup and Restore MySQL Database via terminal August 28, 2011

Posted by Tournas Dimitrios in Mysql.

Backing up your database is a very important system administration task , and should generally be run from a cron job at scheduled intervals . We will use the mysqldump utility included with mysql to dump the contents of the database to a text file that can be easily re-imported .  For those users that prefer to use the GUI-interface , they can use phpMyadmin  . This article will present the terminal alternative :

mysqldump -h localhost -u root -p databasename >> dumpfile.sql 
 If you ommit the -h switch it will default to “localhost”. After you enter the command  and press enter, you will be prompted for a password because you did not enter the password after the -p switch . For security reasons , don’t include the password at the command-request  . If  the password is  correct  , a text file containing all the sql-commands required to recreate the database and all the content will be exported into the specified file .
Dumping only the database-table_structure is also possible through the terminal , just include the “-d” option :
mysqldump  -d  -h  localhost  -u  root  -p databasename >> dumpfile.sql

Importing data into the database via terminal :

  • Once you have the sql file , log into mysql and create a database or “use” an existing database – again , ommiting the -h will default to localhost and you will be prompted for a password  
    mysql   -u<username>  -p
  • After logging in ,  you will need to either create a new database and select the database for use with the import , or use an existing database . For this example , we’ll create a new database. Remember to use backticks and not apostrophe’s to create your new MySQL database .
    mysql> create database  ‘newdatabasename’
  • Step into the database :  use  database-name
  • Remember where you had stored the SQL file and issue a “source” command :
    mysql> source  dumpfile.sql
  • The query is executed and if you didn’t mess around with the SQL produced by mysqldump , it should run things flawlessly .


No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s