The redo log is usually located in the files like mysql-bin.digits. If its full pathname is not set in the settings, then these files will be located in the directory, in which MySQL keeps databases.
Perhaps, you won’t need all log files to recover the database, separate tables or even specific records. Check their modification date.
In order to work with binary logs, you will need a mysqlbinlog tool. It comes along with MySQL server set. This tool processes log files and displays a perfectly usabe SQL code directly on the console. The result can be routed to the file (mysqlbinlog [parameters] [log_files] > file.sql), or directly to MySQL (mysqlbinlog [parameters] | mysql [parameters]) or point to the file to be retrieved in tool parameters. For example:
mysqlbinlog -s -d db_name -r out.sql mysql-bin.000012
In this case a mysql-bin.000012 file will be processed (from the current directory), the output will move to out.sql, and commands will be displayed which relate to the madification of db_name database. By the –s parameter we have resrticted the display of any other service information.
Another example:
mysqlbinlog -s -d db_name -u user_name --start-datetime="2009-01-23 21:10:00" -t mysql-bin.000001 > out.sql
Here, among other things, we confine ourselves to displaying the queries executed by the user_name starting from the data specified. Parameter -t tells the tool that logs after the mysql-bin.000001 file also have to be processed. Please note, that if you will reroute the output directly into MySQL, than the redo log is going to be filled with fresh entries which may lead to circularity. You can avoid that by adding a –D parameter, restricting logging. The restriction will be accessible only if it would be ran from the root.
The remaining parameters can be viewed in the same way as in any other console program:
mysqlbinlog --help
As a matter of fact, the output redirection into the muscle is not recommended. In addition to that, if you use an intermediate SQL file, you should be able to delete a notorious DROP DATABASE, that has led to data loss. That file could be useful in the future as well.
Run the followng command to recover the database from the SQL file:
mysql -u user_name -p < out.sql
Don’t forget to backup:
mysqldump db_name > backup.sql
Or even like this:
mysqldump -u user_name --password -A > backup.sql