Restore a Database using the MySQL SOURCE command

MySQL provides the SOURCE command to help you restore a database from a dump file.

To restore a database from a sql file created by the mysqldump tool, you can use MySQL SOURCE command or the mysql tool.

The procedure for backing up and restoring the Sakila sample database is demonstrated below.

Back up sakila database

To back up the sakila database, please use an administrator user or a user with privileges. Execute the following statement to make a backup for sakila database:

mysqldump --user=root --password=<password> --databases sakila --result-file=/bak/sakila.sql

After running successfully, the file /bak/sakila.sql will be generated.

Log in to the MySQL server as root user and drop the sakila database using the statement below.

DROP DATABASE sakila;

Restore the sakila database using SOURCE command

Here are the steps to restore the sakila database using the SOURCE command:

  1. Connect to the MySQL server using the mysql client tool:

    mysql -u root -p

    Enter the password for the root account and press Enter:

    Enter password: ********

  2. Run the following SOURCE command to restore the sakila database:

    SOURCE /bak/sakila.sql

    This step may take several seconds.

  3. Use the SHOW DATABASES statement to show the database list to check if the database has been restored:

    SHOW DATABASES LIKE 'sakila';

    +-------------------+
| Database (sakila) |
+-------------------+
| sakila            |
+-------------------+

    Now, the sakila database has been restored.

  4. Use the SHOW TABLES statement to show all tables in the sakila database to check if the tables have been restored:

    SHOW TABLES FROM 'sakila';

    +----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_copy                 |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
| student                    |
| student_score              |
| subscribers                |
| test                       |
| user                       |
+----------------------------+

    Now, the tables in the sakila database are also restored.

Restore the Sakila database using mysql command

The SOURCE command needs to be logged in to run, but you can also restore he Sakila database using the mysql tool. Please use the following mysql command to restore the sakila database:

mysql --user=root --password=<password> < /bak/sakila.sql

After excecution, you can use SHOW DATABASES and SHOW TABLES to check that the database has been recovered.

Conclusion

In MySQL, the SOURCE command can help you restore databases from backup files. In addition to that, you can restore a database using the mysql command.