Recover MySQL database from binary logs
Databases sometimes disappear. The human factor and all that ... If you did not make backups (and you should) or they are already outdated, do not despair, there is still an opportunity to recover the lost information. 
Starting from version 4.1.3, MySQL records all queries that lead to data changes in the binary update log (binary log). But this option may be disabled by default. On my home machine, for example, binary logs were not kept after installation. Enabled by uncommenting the line
The update log is usually stored in files of the mysql-bin type. tsiferki . If the settings do not specify the full path, these files will be placed in the directory where MySQL stores databases.
')
You may not need all the log files to restore the database, individual tables, or even specific records. Look at the date of their change.
To work with binary logs, you will need the mysqlbinlog utility. It comes with a MySQL server. The utility processes the log files and outputs usable SQL code directly to the console. The output can be redirected to a file (
In this case, the mysql-bin.000012 file (from the current directory) will be processed, the output will recover in out.sql, only commands related to changing the database with the name db_name will be output. With the -s option, we disabled the output of additional overhead information.
Another example:
Here we, among other things, limit ourselves to displaying queries that were executed by the user user_name starting from the specified date. The -t option tells the utility to process the logs that follow the mysql-bin.000001 file. Please note that if you redirect the output directly to MySQL, fresh entries will be added to the update log and looping will occur. To prevent this, add the -D parameter, which disables logging. The ban will be available only if you execute the command from under the root.
The rest of the parameters are scanned in the same way as for any console program:
Generally, redirecting output directly to muscle is not recommended. In addition, if you use an intermediate SQL file, from there you can remove the ill-fated
To restore the database from the SQL file, use the command:
And do not forget to make backups:
Or even like this:
Useful links:
Starting from version 4.1.3, MySQL records all queries that lead to data changes in the binary update log (binary log). But this option may be disabled by default. On my home machine, for example, binary logs were not kept after installation. Enabled by uncommenting the line
log_bin = /var/log/mysql/mysql-bin.log in the configuration file (my.cnf). On the server, it was originally conducted. However, the parameter in the settings looks different there: log-bin = mysql-bin . Logs are also kept if MySQL is started with the key --log-bin[=file_name] .The update log is usually stored in files of the mysql-bin type. tsiferki . If the settings do not specify the full path, these files will be placed in the directory where MySQL stores databases.
')
You may not need all the log files to restore the database, individual tables, or even specific records. Look at the date of their change.
To work with binary logs, you will need the mysqlbinlog utility. It comes with a MySQL server. The utility processes the log files and outputs usable SQL code directly to the console. The output can be redirected to a file (
mysqlbinlog [] [_] > .sql ), directly to MySQL ( mysqlbinlog [] | mysql [] ), or you can specify a file for output in the utility parameters. For example:mysqlbinlog -s -d db_name -r out.sql mysql-bin.000012In this case, the mysql-bin.000012 file (from the current directory) will be processed, the output will recover in out.sql, only commands related to changing the database with the name db_name will be output. With the -s option, we disabled the output of additional overhead information.
Another example:
mysqlbinlog -s -d db_name -u user_name --start-datetime="2009-01-23 21:10:00" -t mysql-bin.000001 > out.sqlHere we, among other things, limit ourselves to displaying queries that were executed by the user user_name starting from the specified date. The -t option tells the utility to process the logs that follow the mysql-bin.000001 file. Please note that if you redirect the output directly to MySQL, fresh entries will be added to the update log and looping will occur. To prevent this, add the -D parameter, which disables logging. The ban will be available only if you execute the command from under the root.
The rest of the parameters are scanned in the same way as for any console program:
mysqlbinlog --helpGenerally, redirecting output directly to muscle is not recommended. In addition, if you use an intermediate SQL file, from there you can remove the ill-fated
DROP DATABASE , which led to data loss. The file you may need in the future.To restore the database from the SQL file, use the command:
mysql -u user_name -p < out.sqlAnd do not forget to make backups:
mysqldump db_name > backup.sqlOr even like this:
mysqldump -u user_name --password -A > backup.sqlUseful links:
Source: https://habr.com/ru/post/50064/
All Articles