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:
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:
<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:
mysqlbinlog
tool to extract the database deletion operation. 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!