Restore database (or database table) from backup

by

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

To restore a database or a specific table from a backup in MySQL, you typically use the mysql command-line client or a similar tool. Here’s a general approach:

  1. Ensure you have a backup: First, make sure you have a recent backup of the database or table you want to restore.
  2. Access the MySQL command-line interface: Open your terminal or command prompt and log in to MySQL using a command like:
    css
    mysql -u username -p

    Replace username with your MySQL username. You’ll be prompted to enter your password.

  3. Select the database: If you’re restoring a specific database, select it using:
    USE database_name;

    Replace database_name with the name of your database.

  4. Restore the database or table: You can restore the entire database or a specific table from your backup. If you’re restoring the entire database, you can do so using:
    css
    mysql -u username -p database_name < backup_file.sql

    Replace username with your MySQL username, database_name with the name of your database, and backup_file.sql with the path to your backup file.

    If you’re restoring a specific table, you’ll need to use mysql along with sed or awk to filter out the table you want from the backup file. Here’s a basic example using sed:

    css
    sed -n -e '/CREATE TABLE `table_name`/,/UNLOCK TABLES/p' backup_file.sql | mysql -u username -p database_name

    Replace table_name with the name of the table you want to restore, username with your MySQL username, and database_name with the name of your database.

  5. Verify the restoration: After the restoration process completes, you can verify that the database or table has been restored correctly by querying it using MySQL commands.

Always ensure you have tested your backup and restoration process in a non-production environment to avoid any data loss or corruption.