How to restore deleted database in mysql database

To recover a deleted MySQL database, first confirm that it has been deleted. Then, there are two recovery methods: using a backup file or recovering from a binary log file. Restoring from a backup file requires first stopping the server, copying the files, and running the query. Restoring from a binary log file requires ensuring logging is enabled, extracting the operation and running the query to create and restore the database. After the recovery is completed, you can query whether the recovery was successful.

How to recover a deleted database in MySQL

To recover a deleted MySQL database, you can take The following steps:

1. Confirm that the database has been deleted

First, confirm whether the database has been completely deleted. You can use the following query:

<code>SHOW DATABASES;</code>

If the deleted database does not appear in the results, confirm that the database has been deleted.

2. Recover the database

There are two ways to recover a deleted database:

• Use backup files: If you created a backup before the database was deleted, you can restore it from the backup file.
• Recovering from binary log files: If binary logging is enabled, you can use the mysqlbinlog tool to recover the database from binary log files.

3. Restore from backup file

To restore the database from backup file, follow these steps:

• Stop MySQL server.
• Copy the backup file to the MySQL data directory.
• Start the MySQL server.
• Run the following query to recover the database:

<code>CREATE DATABASE database_name;
SOURCE backup_file_path;</code>

4. Recovering from the binary log file

To recover the database from the binary log file, Please perform the following steps:

• Before deleting the database, make sure binary logging is enabled.
• Find the binary log file that saves the database deletion operation.
• Use the mysqlbinlog tool to extract the database deletion operation.
• Create and restore a deleted database using the CREATE DATABASE and REPLAY BINARY LOG queries.

For example:

<code>mysqlbinlog -v binary_log_file | grep 'CREATE DATABASE database_name'
mysql -u root -p
CREATE DATABASE database_name;
REPLAY BINARY LOG FROM "'position_of_database_deletion'"</code>

5. Verify recovery

After restoring the database, use the following query to verify whether the recovery was successful:

<code>SHOW DATABASES;</code>

Confirm that the deleted database is displayed in the results.

The above is the detailed content of How to restore deleted database in mysql database. For more information, please follow other related articles on the PHP Chinese website!