This howto guide will step you through how to restore a MySQL database from a backup file, using the MySQL client command line tools on a Linux machine.
Backing up databases is important, but it is useless if you don’t know how to restore the backup. Regular restores should be done to ensure that your backup is operating correctly.
Restoring a MySQL database:
To restore a MySQL database from a plain text MySQL backup file, run the following command:
$ mysql -u username -h localhost -p database_name < database.sql
Change “database_name” to be the name of the database that you want to restore to (The database must exist in MySQL first if its being restored to a new server).
Change database.sql to the name of the backup file that will be restored.
Change “username” to be the username that you want to connect to the MySQL database server using.
Using the “-p” parameter means that it will prompt you for the password before it does the restore. You can append a password directly after “-p” if you don’t want it to ask you for the password.
For example, to use a password of “pass123” you would use the -p parameter as “-ppass123”
If you want to connect to a MySQL server on another physical machine, you will need to modify the “-h” parameter. If you want to connect to localhost, this parameter cam be omitted, however I left it in there for the example above.
For example, to connect to a MySQL server called “SQLSVR1” you would use the parameter “-h SQLSVR1”.
Restore a MySQL database from a compressed (gzip) backup:
To restore a MySQL database from a compressed (gzip) backup file, run the following command:
$ gzip -d --stdout database.sql.gz | mysql -u username -h localhost -p database_name
Change “database.sql.gz” to the name of the compressed MySQL backup file that will be restored.
