Restore MySQL database from binary log

To learn more about what is binary log and how to setup Click here

MySQL binary log : mysqlbinlog utility

mysqldump – MySQL Database Backup and restore program

Purge Binary log

MySQL Master-Master-Slave-Slave Replication

Database Recover from MySQL binary log:

  • Binary logs store all the queries (in STATEMENT format) or row changes (in ROW format) that happened since they were recorded.
  • Basically executes all insert, updates & deletes that occurred to the server during those binlogs, serially. That is why recovering from the binary logs is only useful for point in time recovery (recovering the latest changes since the last backup).
  • If the goal is to have a path to recovering the database, then consider using Replication and having a Slave. The Slave will usually be up to the minute with the Master. This is far better.

Using mysqlbinlog utility we can view the binlog file content and recover the database.

If it’s a single file you can recover using:

mysqlbinlog /var/lib/mysql-bin.000016 | mysql –uroot –pPassword
If it’s multiple files then just extract all content to one .sql file and directly restore it:

mysqlbinlog /var/lib/mysql-bin.000016 > /logs/allbinlog.sql

To append second binlog content to allbinlof.sql file use below command:

mysqlbinlog /var/lib/mysql-bin.000016 >> /logs/allbinlog.sql


Database Recover from MySQL binary log with start date and time:

Let’s suppose we have a file called mysql-binlog.000111. There are a number of ways this can be exported, based on your needs, and those choices are available via command line flags (man mysqlbinlog for details).

Let’s say you need data from a certain start time up to a certain end time. This can be specified on the command line. You can also specify the database that you wish to export the data for. This is because if you run multiple databases, they are all logged to the same set of binlogs.

mysqlbinlog –start-datetime=”2016-05-01 05:00:00” –stop-datetime=”2016-05-01 08:05:00” –database mydatabase mysql-binlog.000111 > output.txt

That command will pull all of the transactions from the mysql-binlog.000111 file for the ‘mydatabase’ database starting from the specified data and output them to the output.txt file.

Now you can restore this to the database:

mysql backupdatabase < output.txt



Related Posts

  • 67
    What is database? Database is an organized collection of information about an entity having controlled redundancy and serves multiple applications. DBMS (database management system) is an application software that is developed to create and manipulate the data in database. A query language can easily access a data in a database.…
    Tags: database, data, mysql, db
  • 63
    Mysql binary log is a special kind of logging facility provided by MySql by which we can record the log of the database changes statement along with the moment when statement get executed. We can log all insert, update and delete statement of the database in the mysql binary log.…
    Tags: log, binary, database, mysql, data
  • 49
    To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
    Tags: mysql, log, database, file
  • 46
    MySql export schema without data Login to DB server Run the below command to take backup of schema of database without data. # mysqldump -u root –p  -d olddb > backup.sql OR # mysqldump -u root -p --no-data dbname > schema.sql    
    Tags: data, database, mysql, db, command
  • 44
    The mysqli_connect() function opens a new connection to the MySQL server. Syntax: mysqli_connect(host,username,password,dbname,port,socket); Parameter Description host Specifies a host name or an IP address username Specifies the MySQL username password Specifies the MySQL password dbname Optional. Specifies the default database to be used port Optional. Specifies the port number to…
    Tags: mysql, database, db

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: