Its a simple article on How you can restore your data using mysql binary logs.
mysql binary logs contain a record of each SQL query executed against the database that changes data since the logs where last flushed (new log file started).MySQL Binary Logs must be enabled by passing the –log-bin option to mysqld.
The log contains queries like UPDATE and DELETE. The log does not record SELECT or SHOW queries for example because they do not change the database.
MySQL Binary Logs are some times confused with the InnoDB binary transaction log. InnoDB uses a binary log to journal changes to the InnoDB table space file(s) as a protection from crashes to protect table space data integrity.
When --log-bin is enabled even InnoDB transactions are written to the binary transaction log used for replication or restores.
At this time take a scenario you has binary logs as follows:
see the location where binary logs are stored.
localhost-bin.000001
localhost-bin.000002
localhost-bin.000003
localhost-bin.index
firstly lock the tables as
LOCK TABLES
then flush the logs as
FLUSH LOGS
At this time take a scenario you has binary logs as follows:
see the location where binary logs are stored.
localhost-bin.000001
localhost-bin.000002
localhost-bin.000003
localhost-bin.000004
localhost-bin.index
after flushing the logs a new bianry log generated as localhost-bin.000004.
Now upto binary log localhost-bin.000003 you can take the complete backup.
take the backup using any method that you want.
This is you complete backup upto the log localhost-bin.000003
Then unlock the tables as
UNLOCK TABLES
Restoring with Binary Logs
To restore from a point in time using binary logs you would:
Restore the database from the last SQL Dump completed before the desired recovery point.
Use mysqlbinlog to restore to the desired point in timewhere N is the log entry number you want to restore up to.
mysqlbinlog –start-position=N localhost-bin.000004 | mysql N is the position of binary log.
>mysqlbinlog –start-position=N localhost-bin.000004 | mysql
ReplyDeleteIn your example, you have more than one binary log to execute on the MySQL server, the problem with your approach is - Processing binary logs this way using multiple connections to the server causes problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports “unknown table.”
The safe method is to process them all using a single connection to the server.
mysqlbinlog binlog.[0-9]* | mysql -u root -p
Thanks for your information...
ReplyDelete