Backup and Restore MySQL Database via terminal August 28, 2011Posted 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 .