Menu

Restore a Database using the MySQL SOURCE command

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

Updated on

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 --databases sakila --result-file=/bak/sakila.sql

The client prompts for the password. Avoid putting the password value in the command line; MySQL documents that method as insecure in its password security guidance.

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 SHOW TABLES to check that the standard Sakila actor table was restored; the full table list can differ if the dump contains additional objects:

    SHOW TABLES FROM sakila LIKE 'actor';

    +--------------------------+
| Tables_in_sakila (actor) |
+--------------------------+
| actor                    |
+--------------------------+

    The output confirms that the actor table is present. If you need to verify every object, compare the full SHOW TABLES FROM sakila; output with the contents of the dump.

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 < /bak/sakila.sql

The client prompts for the password before reading the dump file.

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.