MySQL Encrypted Database Backup for Mysqldump and Xtrabackup

What does Database Encryption and Decryption mean?

Database encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm.

Database decryption is converting the meaningless cipher text into the original information using keys generated by the encryption algorithms.

Database encryption can be provided at the file or column level.


Here am going to explain how to encrypt your mysql databases with mysqldump and xtrabackup.first we will see for mysqldump

MySQLdump Encryption & Decryption:

Mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database.

To encrypt your database here I have used ccrypt.ccrypt is a utility for the secure encryption and decryption of files and streams.

I have used Fedora 20 server to do this process.

First install ccrypt

#yum install ccrypt

am already have an mysql server in this machine so i will just show you how to take encrypted backup with ccrypt.

Create a hidden keyfile.

#vi /etc/key/.backupkey

in this file add any content like below,

ex : Ukl8GiJ4Q#uy@iP

Now give permission for this file,

#chmod 600 /etc/key/.backupkey

Now we can encrypt our backup,

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey > backup_c.sql

This backup.sql will be now an encrypted backup when you open it, it will be a non readable format.

Decryption:

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Now you can see the real content in this file.
 

Compressed Mysqldump Encrypted backup:

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey | gzip -c > backup_c.sql.gz

Decryption:

#gunzip  backup_c.sql.gz > backup_c.sql

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Xtrabackup Encryption & Decryption:
 
Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

For xtrabackup you need to install latest Percona Xtrabackup(Version should be more than 2.1.4) and openssl for encryption.

Xtrabackup installation steps in this below link,
http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

Now we can encrypt our backup with openssl for Xtrabackup.

Before doing that we need to generate openssl key. 

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

 

with the above key we are going to encrypt with Xtrabackup

Encryption: 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup
 

 after executed the above command you can see some line like below,

[01] Encrypting ./xxxx/xxxx.ibd to /backup/2014-12-27_04-49-28/xxxx/yyyy.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./yyyy/aaaa.ibd to /backup/2014-12-27_04-49-28/dddd/rrrr.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./rrr/ssss.ibd to /backup/2014-12-27_04-49-28/yysss/ddasffd.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./gggds/ssssss.ibd to /backup/2014-12-27_04-49-28/dsfsd/fdsfds.ibd.xbcrypt
.
.
.

and at the end,

innobackupex: Backup created in directory '/backup/2014-12-27_04-49-28'
141227 04:51:51  innobackupex: Connection to database server closed
141227 04:51:51  innobackupex: completed OK!
   Now it has taken an encrypted backup.

Xtrabackup encryption with keyfile,

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

Now copy the iv result in a txt file,

#cat /etc/key/xtrabackupkey.txt

FEC2DE284641AC8A0636D07BEEC5A514

Now do the xtrabackup with key file, 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key-file=/etc/key/xtrabackupkey.txt  /backup

Decryption:

For decryption required xtrabackup 2.1.4 version.

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

It will extract the xtrabackup encrypted folder to decrypted folder in same location.

Compressed Xtrabackup Encrypted backup:

Encryption:

# innobackupex --user=root --password=pa$$123 --export --compress --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup

Decryption:

First we need to decompress the file, see how to decompress in the below link,

http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

then decrypt by the below command with same encrypt key,

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

Now all the databases are decrypted in the same directory.

MySQL HA - Percona XtraDB Cluster Installation steps

All would like to keep their databases server for High Availabilty with out any downtime and data loss.Percona XtraDB Cluster is one of the method to keep your DB server HA.Here I have given the steps to install and configure Percona XtraDB cluster.

Basic Requirements:

Three servers for three nodes.

Node 1:

1.Create a new linux server.

2.Download Percona xtradb cluster “tar.gz” file from this link.

http://www.percona.com/downloads/Percona-XtraDB-Cluster/

I have downloaded 5.5.34-25.

3.Install as like mysql(there is no changes while installing Percona Xtradb).

Mysql Installation link, http://knowmysql.blogspot.in/2012/12/20-easy-steps-to-install-mysql-in-linux.html

4.Start as like Mysql

5.Give root privilges.

6.Stop the mysql server.

7.Copy my.cnf file for safety.

8.Add the below lines in my.cnf file.

basedir=

datadir=

server-id = 1

log-bin=mysql-bin

log_slave_updates

binlog_format=row

wsrep_cluster_address=gcomm://

162.213.38.46

,162.213.38.32,199.180.197.233 -- give all the 3 nodes server ip.

wsrep_provider=/usr/local/cluster1/lib/libgalera_smm.so

-- check this path correctly

wsrep_slave_threads=2

wsrep_node_address =162.213.38.46

(Current node server IP)

wsrep_cluster_name=clustertest1

-- give any name

wsrep_sst_method=rsync

wsrep_node_name=Clusternode1

-- give any name

innodb_autoinc_lock_mode=2

9.Start the server by following.

#./bin/mysqld_safe –defaults-file=/etc/my.cnf –basedir=/... --datadir=/.... --user=mysql --wsrep-cluster-address="gcomm://" &

or

service mysql start

10. Check the mysql error log. There it should be like this below,

[Note] WSREP: 0 (Clusternode1): State transfer to 2 (Clusternode1) complete.

[Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 7694)

[Note] WSREP: Member 0 (Clusternode1) synced with group.

[Note] WSREP: Shifting JOINED -> SYNCED (TO: 7694)

[Note] WSREP: Synchronized with group, ready for connections

10.Then login to mysql and check the status of these variables.

Mysql -u root -p

> show status like 'wsrep_%';

wsrep_cluster_size 1 -- no. of nodes connected to this node

wsrep_local_state_comment Synced

wsrep_connected ON --- node connection status

wsrep_incoming_addresses 162.213.38.46:3306 -- current node IP.

wsrep_ready ON

Node 2:

Do the same steps from 1 to 7 for this new node.

Then do the following,

1.Add the following in my.cnf.

basedir=

datadir=

server-id = 2

log-bin=mysql-bin

log_slave_updates

binlog_format=row

wsrep_cluster_address=gcomm://162.213.38.46 -- give node 1 server ip.

wsrep_provider=/usr/local/cluster2/lib/libgalera_smm.so

-- check this path correctly

wsrep_slave_threads=2

wsrep_cluster_name=clustertest1

-- give node 1 cluster name here

wsrep_sst_method=rsync

wsrep_node_name=Clusternode1

-- give node 1 name here.

innodb_autoinc_lock_mode=2

2.Start the server by following.

#/etc/init.d/mysql start

3.login to server.

#mysql -u root -p

4.Check the following status,

mysql> show status like 'wsrep_%';

wsrep_cluster_size 2 -- no. Of nodes connected to this node

wsrep_local_state_comment Synced

wsrep_connected ON --- node connection status

wsrep_incoming_addresses 162.213.38.46:3306,162.213.38.42:3306 -- current node IP and node 1 ip should be come here.

wsrep_ready ON

Node 3:

Do the same steps from 1 to 7 for this new node.

Then do the following,

1.Add the following in my.cnf.

basedir=

datadir=

server-id = 23

log-bin=mysql-bin

log_slave_updates

binlog_format=row

wsrep_cluster_address=gcomm://162.213.38.46 -- give node 1 server ip.

wsrep_provider=/usr/local/cluster2/lib/libgalera_smm.so

-- check this path correctly

wsrep_slave_threads=2

wsrep_cluster_name=clustertest1

-- give node 1 cluster name here

wsrep_sst_method=rsync

wsrep_node_name=Clusternode1

-- give node 1 name here.

innodb_autoinc_lock_mode=2

2.Start the server by following.

#/etc/init.d/mysql start

3.login to server.

#mysql -u root -p

4.Check the following status,

mysql> show status like 'wsrep_%';

wsrep_cluster_size 3 -- no. Of nodes connected to this node

wsrep_local_state_comment Synced

wsrep_connected ON --- node connection status

wsrep_incoming_addresses 162.213.38.46:3306,162.213.38.42:3306,199.180.197.233:3306 -- current node IP, node 1 and node 2 ips should be come here.

wsrep_ready ON

Then finally test the node by creating a database from any one of the node and check it is in all nodes or not.

Percona 5.6.x Installation Error: scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory

If you get an below error while installing Percona 5.6.x  tarball file.

./scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/var/lib/mysqldata/ --user=mysql
-bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory

Solution:

Install perl perl-devel

#yum install perl perl-devel

Again if you try it, you may get again an error like below,

./scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/var/lib/mysqldata/ --user=mysql
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./scripts/mysql_install_db line 42.
BEGIN failed--compilation aborted at ./scripts/mysql_install_db line 42.

Solution:

#yum install 'perl(Data::Dumper)'

Then install it.This problem because of Perl.Perl is required for installation.  -:)

openssl 1.0.1g heartbleed updates for Centos,Redhat and Fedora

The openssl heartbleed bug has made the rounds today here i have given how to update it to latest one from source file.

Check you openssl version

#openssl version
OpenSSL 1.0.1 14 Mar 2012  --  it is very old version

Download openssl-1.0.1g.tar.gz  from http://www.openssl.org/source/

Check gcc and glibc installed or not by,

#rpm -qa|grep gcc

#rpm -qa|grep glibc

if the both files are not  installed then install it.

now extract openssl-1.0.1g.tar.gz

cd  openssl-1.0.1g

./config

make

make install

cp /usr/bin/openssl /usr/bin/openssl.org

cp -r /usr/local/ssl/bin/openssl /usr/bin/openssl

cp -r /etc/pki/tls/openssl.cnf /etc/pki/tls/openssl.cnf.org

cp -r /usr/local/ssl/openssl.cnf /etc/pki/tls/openssl.cnf

#openssl version
OpenSSL 1.0.1g 7 Apr 2014


Now you have updated the openssl to latest one.

Percona Plugins for Cacti MySQL Graph View Monitoring

Every DBA would like to monitor mysql server wit out any point of failure.

Here am going to explain how to monitor MySQL Server with each and every internal process by Cacti and Percona MySQL Plugins.

First install cacti in your machine.Here I have given the steps for ubuntu 12.04.

Cacti Installation :

1.Update you machine.

#apt-get update

2. Install the Apache,PHP,MySQL and its dependencies.

#apt-get install apache2  apache2-mpm-prefork apache2-utils

#apt-get install libapache2-mod-php5 php5-cli php5-common php5-cgi php php5-mysql

#apt-get install mysql-server mysql-client

3. Install snmpd

# apt-get install snmpd

edit the snmpd.conf file.

#vi /etc/snmp/snmpd.conf

#agentAddress udp:161,udp6:[::1]:161   --- remove # here

#rocommunity secret  CactiServerIpAddress   ---  remove # here and add Your cacti server public ip

restart snmpd
#service snmpd restart

4.Install cacti
# apt-get install cacti cacti-spine

here it will ask the mysql user name and password give the correct user name and password. once installation finish check in mysql there one new database name "cacti" is created or not.

5.Go to web browser

http://cacti ip address /cacti/install

Default user name password for cacti login is admin.

Once you done the installation in browser you will go to the dashboard page.

Percona Plugins installation in Cacti:

1.Download cacti percona tar.gz plugin from the below link in cacti server and in your local machine desktop.

http://www.percona.com/downloads/percona-monitoring-plugins/LATEST/

2.Extract the file percona-monitoring-plugins-1.1.3.tar.gz in both cacti server and local machine.

#tar -xvzf percona-monitoring-plugins-1.1.3.tar.gz

3.In cacti server do the following.

#cd  percona-monitoring-plugins-1.0.0/cacti/
#cp scripts/ss_get_mysql_stats.php /usr/share/cacti/site/scripts

Once you copied the php file to cacti path go to cacti dashboard in browser.

In the left side you can see import templates option click it.The you will see a new page like below.

Here browse "cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.1.xml" file from your local machine where you have extracted the percona plugin.

>percona-monitoring-plugins-1.1.1> cacti>templates>cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.1.xml

Then do import in the dashboard.It will take few minutes to import from your local machine. After imported it will show the page like below.

Percona Plugin was installed now we need to add MySQL server to monitor from cacti.

From the home page click devices.then add a new device.

Console > devices > add new device

Here give your mysql server IP and select the host templete as "Percona MySQL Server HT" option and create it,

In the next page it will show list of options to be monitor to select it.like below

once you selected the options then create it. after that it will show what are the graphs are created to monitor.

Then go to graph page and see your MySQL server current process through graph like below.

 Now you can monitor your MySQL Server for each and every process.


Note :
For host MySQL server machine. you need to add this cacti server ip in snmpd.conf

as like this,

vi /etc/snmp/snmpd.conf

rocommunity secret  CactiServerIpAddress

 Enjoy and monitor your MySQL server now!!!


References:

http://www.percona.com/doc/percona-monitoring-plugins/1.0/cacti/installing-templates.html

http://www.percona.com/doc/percona-monitoring-plugins/1.0/cacti/mysql-templates.html

https://www.digitalocean.com/community/articles/installing-the-cacti-server-monitor-on-ubuntu-12-04-cloud-server 

http://myconfigure.blogspot.in/2013/01/install-snmp-cacti-on-ubuntu-1210.html 

libaio.so.1 cannot open shared object file: No such file or directory - Ubuntu Percona MySQL Installation

While installing binary Percona MySQL in Ubuntu at first time we may get the below error.

/usr/local/mysql//bin/mysqld: error while loading shared libraries: libaio.so.1 cannot open shared object file: No such file or directory

/usr/local/mysql//bin/mysqld: error while loading shared libraries: libssl.so.6: cannot open shared object file: No such file or directory

Follow the steps to solve this,

1.Do "ldd" with mysql installation base directory.

 

#ldd /usr/local/mysql/bin/mysql

Output:

inux-vdso.so.1 => (0x00007fff83dff000)

libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f20fb4e9000)

libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f20fb1ef000)

librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f20fafe6000)

libssl.so.6 => not found

libcrypto.so.6 => not found

libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f20fade1000)

libncurses.so.5 => /lib/x86_64-linux-gnu/libncurses.so.5 (0x00007f20fabc0000)

libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f20fa803000)

/lib64/ld-linux-x86-64.so.2 (0x00007f20fb70c000)

libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007f20fa5db000) 

2.Install the below packages,

# apt-get install libaio1 libaio-dev

# apt-get install libssl-dev

3.Link the files like below,

#ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /lib/x86_64-linux-gnu/libcrypto.so.6

#ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /lib/x86_64-linux-gnu/libssl.so.6

4.Now check ldd,

#ldd /usr/local/mysql/bin/mysql

Output:

 linux-vdso.so.1 =>  (0x00007fffbe9d6000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f37776ea000)
    libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f37773ee000)
    librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f37771e5000)
    libssl.so.6 => /lib/x86_64-linux-gnu/libssl.so.6 (0x00007f3776f87000)
    libcrypto.so.6 => /lib/x86_64-linux-gnu/libcrypto.so.6 (0x00007f3776bac000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f37769a7000)
    libncurses.so.5 => /lib/x86_64-linux-gnu/libncurses.so.5 (0x00007f3776786000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f37763c6000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f3777925000)
    libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f37761ae000)
    libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007f3775f87000)

Now all files are there.

5.Now install MySQL.It will install fine with out throwing any errors.

 

Restoring records from MySQL binlog files.

MySQL is having a good features as binlog files.When ever the transaction happens in the server this binlog files have the transaction details.


Here am going to explain how to restore records from mysqlbinlog file.

For example:

First check  bin log is enabled or not.

login to mysql,check it by,

mysql>show binary logs ;
+------------------+-----------+
| Log_name   | File_size |
+------------------+-----------+
| mysql-bin.000001 |  25071488 |
| mysql-bin.000002 |       150 |
| mysql-bin.000003 |    131262 |
| mysql-bin.000004 |    463122 |
| mysql-bin.000005 |   1040619 |
+------------------+-----------+
5 rows in set (0.00 sec)

So binlog is enabled in this server.If not enabled do the below,


Remove "#" for log-bin=mysql-bin from my.cnf file

Restart mysql.


Now,I have a database name "fruits" inside I have table name is "apple" it contains 15 records as shown below.

mysql>use fruits;
Database changed

 mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| apple            |
| banana        |
| mango         |
| orange        |
+------------------+

 mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)

I am taking a backup of this apple table now,

root@knowmysql]# mysqldump -u root -p fruits apple > fruits_apple.sql

After taking backup I am inserting some rows in the apple table like below,

mysql>insert into apple (id,username,timestamp) values('16','ILK','1389335345');

Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('17','bcd','1389335355');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('18','brd','1389335375');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('19','elf','1389335395');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('20','OPQ','1389335413');
Query OK, 1 row affected (0.01 sec)


Now totally we have 20 rows in the table.

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
|  16 | ILK      | 1389335345 |
|  17 | bcd      | 1389335355 |
|  18 | brd      | 1389335375 |
|  19 | elf      | 1389335395 |
|  20 | OPQ      | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)

Now am deleting last five records from apple table.

mysql>delete from apple where id in('16','17','18','19','20');
Query OK, 3 rows affected (0.00 sec)

Now this table is having 15 records,

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
| +-----+----------+------------+

15 rows in set (0.00 sec)

Now I am going to restore the deleted records from binlog,

Take a backp of apple table now and drop it from the server.

root@test]# mysqldump -u root -p fruits apple > fruits_apple11.sql

mysql>drop table apple;

mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| banana        |
| mango         |
| orange        |
+------------------+

apple table dropped.

Import our old backup inside the fruit databases.

root@test]#mysql -u root -p fruits <  fruits_apple.sql

Now the table is having only 15 records.

 mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)
 

Now check the last binlog file from the data directory.(Binlog default location is mysql data directory)

Here am using row format for binlog in my.cnf.For row format below format is  used to view the binlog file.

 root@knowmysql]#mysqlbinlog -v --base64-output=decode-rows /var/lib/mysqldata/mysql-bin.000005|less

From binlog file search at which position the deleted records were inserted.

I have found the binlog position that insertion happens in the server for id 16,17,18,19,20 in the apple table.

starting position is,  941107 and ending position is 942025.

Now am going to restore from binlog,

root@test]#mysqlbinlog --start-position=941107 --stop-position=942025 /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p fruits

Records were restored,Now check the records in apple table.

 mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
|  16 | ILK      | 1389335345 |
|  17 | bcd      | 1389335355 |
|  18 | brd      | 1389335375 |
|  19 | elf      | 1389335395 |
|  20 | OPQ      | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)


Now Apple table is having 20 records and  restored the deleted 17,18,19 records from binlog file.


We can restore data from mysqlbinlog by date and time like below.

mysqlbinlog --start-datetime="2014-01-15  10:00:00" \ --stop-datetime="2014-01-15 10:01:04 \ /var/lib/mysqldata/mysql-bin.000005 > /data/apple.sql

mysqlbinlog --start-position=941327  /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p tablename

This way is little bit tough but we can restore all the records without missing any record.

I hope it will help you.


Thank you.