MySQL database backup and restore tutorial for beginners

MySQL is the database behind popular content management systems like WordPress,Joomla and Drupal.All data of these content management systems are stored in MySQL database tables.So it is very important to backup your MySQL database periodically.Taking regular backups is very important.You can easily restore your website if you have taken a backup.I will be covering different ways of MySQL backup and restore process.

MySQL database backup

Using PhpMyAdmin If you are using shared hosting then high chances are that you have PhpMyAdmin installed for MySQL database management.This is simple to use tool.You can use this tool for backing up your MySQL database as well as restoring your MySQL database.It does offer lots of other tasks. So I have written a separate tutorial on PhpMyAdmin.The tutorial provides step by step details of doing tasks commonly required by Bloggers.You can read the tutorial here. Using MySQLDump Recently I had to move from shared hosting to VPS hosting.I was not too eager to install PhpMyAdmin on my VPS because it is extra burden on managing it and configuring it.Low size RAM was also one reason of not using PhpMyAdmin.So I had to find other way of backing up my database. If you are on VPS or dedicated server then you can use mysqldump to backup your database.This is very easy to use command line tool.You have to connect with your VPS server or dedicated server using SSH shell.After that fire below command from command line
#mysqldump -u mysql-user-name -p database-name > output-file-name.sql
  • In above mysqldump command in place of mysql-user-name enter the user name of your MySQL installation.If you have not created any new user or are unsure about the user name then use root as user name
  • Enter the name of database you want to backup in place of database-name.
  • Enter output file name in place of output-file-name.Keep the .sql extension as it is.The file generated with be of this extension so keeping it is good.
After execution of above mysqldump command your database will be backed up in form of queries in output-file-name.sql.You can now download the output file to your computer or laptop.This file can be used with PhpMyAdmin to restore the database or can be used by mysql command line for restoring.We will see process to restore your database from MySQL command line later in the article. The file gets created in the directory you are in while executing the mysqldump command.

MySQL database restore

Using PhpMyAdmin As mentioned above PhpMyAdmin can be used for restoring your database as well.I am not covering the details in this tutorial. Using MySQL command line This backup method works
  • if you have taken backup using mysqldump command mentioned above
  • if you have taken backup using PhpMyAdmin
When I was switching from shared hosting to VPS hosting I had to restore my database from PhpMyAdmin backup file.It is good to always create .sql format backup file.I preferably use this format for backing up my database (for both mysqldump and PhpMyAdmin). Below are the steps followed by me to populate MySQL database
  • Transfer your backup .sql file to your VPS system.Ideally in home directory of your VPS
  • Now type below command to open mysql prompt.
#mysql -u root -p
  • You will be prompted for password for user root.Enter the password and you can see mysql prompt as shown below
mysql>
  • Now create a database.This database will be used to populate data from backup file
  • In below command replace database-name with name of your database.After entering below command hit enter to execute it.
mysql> create database database-name;
  • Now lets assume your backup file name is data.sql and this file is under home directory.Remember I told above to transfer the backup file to your home directory.
  • Use below command to populate your database with backup data.
mysql> use database-name;
mysql> source data.sql;
The first command instructs MySQL to use database-name (replace database-name with your newly created database).The second command populates database with data.I have used file name data.sql only in the command.The file should be located in same directory from which you issued mysql command.For example the data.sql file is in /root directory.Then if you have used mysql command from /root you can use data.sql only. You can also give fully qualified path in the second command.For example if your data.sql file is located in /home directory then you can use below command
mysql> source /home/data.sql;
You need to your backup file name in place of data.sql file. Consider sharing this post in case you found this useful.

Share this post on

Leave a Reply

Your email address will not be published. Required fields are marked *