How do I restore a database from backup?

I use mysqldump to dump the entire structure and contents of a database into a SQL format file. Therefore all I need to do to restore this file is to execute the SQL contained therein. You might be able to import it directly into phpMyAdmin via the import process to restore a database, however if phpMyAdmin is not an option then you can restore from the command prompt utility.

mysql -u my_user_name -p < myDataBase_backup.sql

The options that I have specified for the above command are u - user and p - password. Because I have not specified the password, when I hit enter MySQL will prompt me for a password before continuing.

The < myDatabase_backup.sql means that when the MySQL connection to the server is made, execute that file as an SQL command.


configuration file

You can set MySQL option flags in a configuration file, however I have opted to skip over adding the username and password to this file. This means that whenever I use the command line MySQL utility I will always need to specify the username and password.

My configuration file for mysqldump has the following parameters specified.

databases
add-drop-database

The databases parameter adds the following code to the sql file:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `myDataBase` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `myDataBase`;

Failure to have this code in your SQL file when you attempt to run it from the command line will result in an error because no database would have been selected for your SQL statements to run against.


The add-drop-database parameter simply adds a drop database statement into the file as shown below.
/*!40000 DROP DATABASE IF EXISTS `MyDataBase`*/; 
Tags: 

Comments

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.