In database specific web applications, databases are very precious for proper functioning. So it is an important task to backup data to overcome any type of accidental data lost.To achieve the best backup strategy we can use binary logging feature of MySQL.

backup mysql using binary logging

1. What is Binary Logging?

A. Binary Logging is MySQL logging feature which is used to log each and every Create, Update & Delete queries happened in MySQL database. It also have the time at which particular change has done. So you can track all the changes and their time stamp if needed.

Binary logging has many advantages:

a. To extract logs for particular time period.

b. To implement replication.

c. Implementing incremental backups.

2. How to setup MySQL to do binary logging?

A. In Linux:

a. Open /etc/mysql/my.cnf using following command:

      sudo nano /etc/mysql/my.cnf

b. Uncomment

     log-bin="/var/log/mysql/" (Path of log files)
     binlog-do-db="test" (Which database is to be included for Binary Logging)
     expire_logs_days=10 (Expire old logs after how many days)
     max_binlog_size=100M (Size of each log file)
     binlog-ignore-db="sampledata" (Which database is to be excluded from Binary Logging)

c. Press Ctrl+X and then press Y and then press enter to save the file. (Specific commands for Nano Editor. If you are using another editor change them accordingly.

d. Now restart MySQL by using following command:

sudo service apache2 restart

Now check for the log files presence in /var/log/mysql folder. There will be two files present.

In Windows:

If you are using XAMPP then search for C:\xampp\mysql\bin\my.ini. here you can find the my.cnf path in following code:

# You can copy this file to
# C:/xampp/mysql/bin/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:/xampp/mysql/data) or
# ~/.my.cnf to set user-specific options.

a. Add log-bin="C:/xampp/mysql/data/binlog/bin-log" in the file.

b. Restart MySQL server

Now check for the log files presence in "C:/xampp/mysql/data/binlog/bin-log" or in /var/log/mysql(for linux) folder. There will be two files present:
1. mysql-bin.index : It is index files which tells MySQL that where are the Log files present and what is the name of log file.

2. mysql-bin.000001 : It is log file with its sequence number as extension. 000001 means it is first log file, next log file will be 000002.

 Binary Logging is successfully activated.

Some of main functions related to MySQL Binary Logging:

1. How to see Binary Logs in MySQL?

A. In MySQL write command MYSQL BINARY LOGS;

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |       150 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       150 |
| mysql-bin.000005 |       126 |
| mysql-bin.000006 |       150 |
| mysql-bin.000007 |       126 |
| mysql-bin.000008 |       150 |
| mysql-bin.000009 |       107 |
+------------------+-----------+
9 rows in set (0.01 sec)

2. How to create new log file manually?

A. Write command FLUSH LOGS;, new log file with incremented filename will be created.

3. How to delete or PURGE previous logs?

A. Just write PURGE BINARY LOGS TO 'mysql-bin.000003';. This will purge the binary logs which are older than given log filename. i.e. our query will PURGE mysql-bin.000001 & mysql-bin.000002.

This is all with functions in binary logging.

Now come to last and precious step i.e. Recovering data using Binary Logging in MySQL

Recovering data in MySQL using Binary Logging

To recover data in MySQL we can use mysqlbinlog utility. It is good tool to recover data from binary logs.

1. If you want to recover data directly from log file to database then write following command:

In Linux: 

mysqlbinlog /var/log/mysql/mysql.bin.000001 | mysql -uroot -padmin

In Windows:

C:\xampp\mysql\bin>mysqlbinlog c:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

It will execute binary log and done the changes which are present in binary log file given by you.

2. If you want to extract sql file from binary log use the following command.

In Linux:

mysqlbinlog /var/log/mysql/mysql-bin.000001 > /var/www/filename.sql

 In Windows:

C:\xampp\mysql\bin>mysqlbinlog c:\xampp\mysql\data\binlog\bin-log.000001 > filename.sql

 To recover using extracted .sql file use following:

In Linux:

mysql -u root -padmin < /var/www/filename.sql

 In Windows:

C:\xampp\mysql\bin>mysql -u root -p admin < c:\pathtofile\filename.sql

3. If you want to extract .sql for particular time then use following:

In Linux:

mysqlbinlog --start-datetime="2013-02-07 18:33:44" /var/log/mysql/mysql-bin.000001 > /var/wwwfilename.sql

In Windows:

 C:\xampp\mysql\bin>mysqlbinlog --start-datetime="2013-04-10 07:14:22" c:\xampp\mysql\data\binlog\bin-log.000001 > filename.sql

–start-datetime & –stop-datetime are used in given command. Where start and stop have general meanings that start will tell from where to start decoding. And stop tells where to stop decoding.

Hope this article will help you in easily Backup database using Binary Logging in MySQL.

For any queries comment or post your query in Our Programming Q&A Forum

How to backup MySQL using binary logging UG ArticlesMySQLPHPProgramming
In database specific web applications, databases are very precious for proper functioning. So it is an important task to backup data to overcome any type of accidental data lost.To achieve the best backup strategy we can use binary logging feature of MySQL. 1. What is Binary Logging? A. Binary Logging is...
In database specific web applications, databases are very precious for proper functioning. So it is an important task to backup data to overcome any type of accidental data lost.To achieve the best backup strategy we can use <a title="Binary Logging in MySQL" href="https://dev.mysql.com/doc/refman/5.0/en/binary-log.html" target="_blank">binary logging</a> feature of MySQL. <a href="http://www.grasphub.com/wp-content/uploads/2014/02/mysql-logo-1.png"><img class="aligncenter size-full wp-image-2725" alt="backup mysql using binary logging" src="http://www.grasphub.com/wp-content/uploads/2014/02/mysql-logo-1.png" width="489" height="253" /></a> <strong>1. What is Binary Logging?</strong> A. Binary Logging is MySQL logging feature which is used to log each and every Create, Update & Delete queries happened in MySQL database. It also have the time at which particular change has done. So you can track all the changes and their time stamp if needed. Binary logging has many advantages: a. To extract logs for particular time period. b. To implement replication. c. Implementing incremental backups. <strong>2. How to setup MySQL to do binary logging?</strong> A. <span style="text-decoration: underline;"><strong>In Linux:</strong></span> a. Open <strong>/etc/mysql/my.cnf</strong> using following command: <pre class="lang:default decode:true"> sudo nano /etc/mysql/my.cnf</pre> b. Uncomment <pre class="lang:default decode:true"> log-bin="/var/log/mysql/" (Path of log files) binlog-do-db="test" (Which database is to be included for Binary Logging) expire_logs_days=10 (Expire old logs after how many days) max_binlog_size=100M (Size of each log file) binlog-ignore-db="sampledata" (Which database is to be excluded from Binary Logging)</pre> c. Press <strong>Ctrl+X</strong> and then press <strong>Y </strong>and then press enter to save the file. (Specific commands for Nano Editor. If you are using another editor change them accordingly. d. Now restart MySQL by using following command: <pre class="lang:default decode:true">sudo service apache2 restart</pre> Now check for the log files presence in <strong>/var/log/mysql </strong>folder. There will be two files present. <span style="text-decoration: underline;"><strong>In Windows:</strong></span> If you are using XAMPP then search for <code>C:\xampp\mysql\bin\my.ini</code>. here you can find the my.cnf path in following code: <pre class="lang:default decode:true"># You can copy this file to # C:/xampp/mysql/bin/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is C:/xampp/mysql/data) or # ~/.my.cnf to set user-specific options.</pre> a. Add <strong><code>log-bin="C:/xampp/mysql/data/binlog/bin-log"</code></strong><code> </code>in the file. b. Restart MySQL server Now check for the log files presence in <strong><code>"C:/xampp/mysql/data/binlog/bin-log" or in /var/log/mysql(for linux)</code> </strong>folder. There will be two files present: 1. <strong>mysql-bin.index</strong> : It is index files which tells MySQL that where are the Log files present and what is the name of log file. 2. <strong>mysql-bin.000001</strong> : It is log file with its sequence number as extension. 000001 means it is first log file, next log file will be 000002. <h3> Binary Logging is successfully activated.</h3> <h3><strong>Some of main functions related to MySQL Binary Logging:</strong></h3> <strong>1. How to see Binary Logs in MySQL?</strong> A. In MySQL write command MYSQL BINARY LOGS; <pre class="lang:default decode:true">mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 126 | | mysql-bin.000002 | 150 | | mysql-bin.000003 | 126 | | mysql-bin.000004 | 150 | | mysql-bin.000005 | 126 | | mysql-bin.000006 | 150 | | mysql-bin.000007 | 126 | | mysql-bin.000008 | 150 | | mysql-bin.000009 | 107 | +------------------+-----------+ 9 rows in set (0.01 sec)</pre> <strong>2. How to create new log file manually?</strong> A. Write command <strong>FLUSH LOGS;, </strong>new log file with incremented filename will be created. <strong>3. How to delete or PURGE previous logs?</strong> A. Just write <strong><code> PURGE BINARY LOGS TO 'mysql-bin.000003';.</code></strong><code> </code><code>This will purge the binary logs which are older than given log filename. i.e. our query will PURGE mysql-bin.000001 & mysql-bin.000002.</code><strong><code> </code></strong> This is all with functions in binary logging. <em>Now come to last and precious step i.e. <strong>Recovering data using Binary Logging in MySQL</strong></em> <h3><strong>Recovering data in MySQL using Binary Logging</strong></h3> To recover data in MySQL we can use <strong>mysqlbinlog</strong> utility. It is good tool to recover data from binary logs. <strong>1. If you want to recover data directly from log file to database then write following command:</strong> <strong>In Linux:  </strong> <pre class="lang:default decode:true">mysqlbinlog /var/log/mysql/mysql.bin.000001 | mysql -uroot -padmin</pre> <strong>In Windows:</strong> <pre class="lang:default decode:true">C:\xampp\mysql\bin>mysqlbinlog c:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p</pre> It will execute binary log and done the changes which are present in binary log file given by you. <strong>2. If you want to extract sql file from binary log use the following command.</strong> <strong>In Linux:</strong> <pre class="lang:default decode:true">mysqlbinlog /var/log/mysql/mysql-bin.000001 > /var/www/filename.sql</pre> <strong> In Windows:</strong> <pre class="lang:default decode:true">C:\xampp\mysql\bin>mysqlbinlog c:\xampp\mysql\data\binlog\bin-log.000001 > filename.sql</pre> <strong> To recover using extracted .sql file use following:</strong> <strong>In Linux:</strong> <pre class="lang:default decode:true">mysql -u root -padmin < /var/www/filename.sql</pre> <strong> In Windows:</strong> <pre>C:\xampp\mysql\bin>mysql -u root -p admin < c:\pathtofile\filename.sql</pre> <strong>3. If you want to extract .sql for particular time then use following:</strong> <strong>In Linux:</strong> <pre class="lang:default decode:true">mysqlbinlog --start-datetime="2013-02-07 18:33:44" /var/log/mysql/mysql-bin.000001 > /var/wwwfilename.sql</pre> <strong>In Windows:</strong> <pre class="lang:default decode:true"> C:\xampp\mysql\bin>mysqlbinlog --start-datetime="2013-04-10 07:14:22" c:\xampp\mysql\data\binlog\bin-log.000001 > filename.sql</pre> --start-datetime & --stop-datetime are used in given command. Where start and stop have general meanings that start will tell from where to start decoding. And stop tells where to stop decoding. Hope this article will help you in easily Backup database using Binary Logging in MySQL. For any queries comment or post your query in <a title="Programming Q&A Forum" href="http://code.grasphub.com" target="_blank">Our Programming Q&A Forum</a>