How to restore MySQL or MariaDB database from backup

Restoring a database is crucial when it comes to disaster recovery, data migration, or setting up a development or test environment. While a backup provides a safety net for data, knowing how to restore is just as essential.

Both MySQL and MariaDB are relational database management systems that share a common lineage. They have similar command sets, tools, and functionalities. This includes their method for database backup and restoration using the mysqldump tool and mysql command-line client, respectively.

Restoring a database involves sourcing the backup file into the database server. The backup file typically contains SQL statements, which, when executed, will reconstruct the database to its state at the time of backup. However, before initiating a restore, ensure that the database server is operational and that you have the necessary permissions.

1. Log in to the server or machine where the MySQL or MariaDB server is installed.
2. Ensure that the backup file, typically with a .sql extension, is accessible.
3. Create a new database or choose an existing one to restore the backup into.

   $ mysql -u username -p -e "CREATE DATABASE my_new_database;"

   If restoring to an existing database, consider taking a backup first as the restore operation will overwrite the existing data.

4. Use the mysql command to restore the backup into the chosen database.

   $ mysql -u username -p my_new_database < /path/to/backupfile.sql

5. Wait for the restore operation to complete. For larger databases, this can take a while.
6. Once completed, check the data in the database to verify the restoration.

   $ mysql -u username -p -e "USE my_new_database; SHOW TABLES;"

7. Test database connections and applications that rely on this database to ensure data integrity and functionality.

   Always conduct thorough testing after a restoration to ensure all data and functions are intact.

Remember, regularly testing your backups by restoring them to a test environment can help ensure data integrity and a smoother recovery process when it truly matters.