January 23, 2009 at 22:48Restoring a MySQL database from binary logs
Databases sometimes disappear. The human factor and all that ... If you didn’t do backups (but you should) or if they are already outdated, do not despair - there is still the opportunity to recover lost information. 
Starting with version 4.1.3, MySQL writes all queries that result in data changes to the binary log. But this option may turn out to 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 form mysql-bin. digits. If the full path is not specified in the settings, these files will be placed in the directory in which MySQL stores the databases.
Perhaps, to restore the database, individual tables, or even specific records, you will not need all the log files. Look at the date of their change.
To work with binary logs, you will need the mysqlbinlog utility. It comes bundled with a MySQL server. The utility processes the log files and displays the usable SQL code directly to the console. The output can be redirected to a file ( ), directly to MySQL ( ), or you can specify a file for output in the utility parameters. For instance:
In this case, the mysql-bin.000012 file (from the current directory) will be processed, the output will be fixed in out.sql, only the commands related to changing the database with the name db_name will be displayed. With the -s option, we disabled the display of additional overhead information.
Another example:
Here, among other things, we restrict ourselves to displaying queries that were executed by user_name starting from the specified date. The -t parameter tells the utility that the logs that come after the mysql-bin.000001 file should also be processed. Please note that if you redirect the output immediately to MySQL, then fresh entries will be added to the update log and a loop will occur. To prevent this, add the -D option, which prohibits logging. The ban will be available only if you execute the command from under the root.
We look at the remaining parameters in the same way as for any console program:
In general, redirecting the output directly to the muscle is not recommended. In addition, if you use an intermediate SQL file, you can delete the unfortunate one
To restore the database from an SQL file, we use the command:
And do not forget to backup:
Or even like that:
Useful links:
Starting with version 4.1.3, MySQL writes all queries that result in data changes to the binary log. But this option may turn out to 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.login the configuration file (my.cnf). On the server, they were conducted initially. However, there is the option in the settings is different: log-bin = mysql-bin. Logs are also maintained if MySQL is started with the key --log-bin[=file_name]. The update log is usually stored in files of the form mysql-bin. digits. If the full path is not specified in the settings, these files will be placed in the directory in which MySQL stores the databases.
Perhaps, to restore the database, individual tables, or even specific records, you will not need all the log files. Look at the date of their change.
To work with binary logs, you will need the mysqlbinlog utility. It comes bundled with a MySQL server. The utility processes the log files and displays the usable SQL code directly to the console. The output can be redirected to a file ( ), directly to MySQL ( ), or you can specify a file for output in the utility parameters. For instance:
mysqlbinlog [параметры] [лог_файлы] > файл.sqlmysqlbinlog [параметры] | mysql [параметры]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 be fixed in out.sql, only the commands related to changing the database with the name db_name will be displayed. With the -s option, we disabled the display 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, among other things, we restrict ourselves to displaying queries that were executed by user_name starting from the specified date. The -t parameter tells the utility that the logs that come after the mysql-bin.000001 file should also be processed. Please note that if you redirect the output immediately to MySQL, then fresh entries will be added to the update log and a loop will occur. To prevent this, add the -D option, which prohibits logging. The ban will be available only if you execute the command from under the root.
We look at the remaining parameters in the same way as for any console program:
mysqlbinlog --helpIn general, redirecting the output directly to the muscle is not recommended. In addition, if you use an intermediate SQL file, you can delete the unfortunate one
DROP DATABASEthat led to data loss from there. You may need the file in the future. To restore the database from an SQL file, we use the command:
mysql -u user_name -p < out.sqlAnd do not forget to backup:
mysqldump db_name > backup.sqlOr even like that:
mysqldump -u user_name --password -A > backup.sqlUseful links: