MySQL DB rollback

Go to mysql master and check  mysql-bin logs and current position: 

mysql -u root -p mysql> show master status\g;   +------------------+----------+--------------+------------------+ | File             | Position | +------------------+----------+--------------+------------------+ | mysql-bin.000452 | 10771124 | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

So, the latest bin log is 452.  

The binary log is a set of files that contain information about data modifications made by the MySQL server.

• Now we have volume, based on the snapshot,  made at 11.59 (server time) or 1.59 AWS time. 
• Now lets copy 2 or 3 binary logs ( mysql-bin.000451 and mysql-bin.000452) to /home directory, as we'll detach and attach new volume

sudo cp  /vol/log/mysql/mysql-bin.000451 /home/ubuntu/   sudo cp  /vol/log/mysql/mysql-bin.000452 /home/ubuntu/

• Convert binary logs to READABLE format and catch exact time or end_log_pos (log position) when unneeded SQL was executed:
  

mysqlbinlog -v mysql-bin.000452 > restore452.sql && grep -B5 "foreign_key_checks=0" restore452.sql   ALTER TABLE  ********** /*!*/; # at 30442757 #161222 15:56:51 server id 1 end_log_pos 30442826 Query thread_id=4632930 exec_time=0 error_code=0 SET TIMESTAMP=1482415011/*!*/; SET @@session.foreign_key_checks=0/*!*/;

• Convert  1482415011 to a human time - GMT: Thu, 22 Dec 2016 13:56:51 GMT (also remember about time drift! -2 hours of #161222 15:56:51 )

So, we need to recover DB up to 13:56:50 (1 sec before destructive statement)

• Detach old volume, containing /vol with mysql data-files and attach the one, made from snapshot:
  

sudo service mysql stop sudo lsof +D /vol sudo fuser -km /vol sudo umount /vol

Detach volume, using AWS console

Check, that the volume was detached, using lsblk and after attach as /dev/sdh

lsblk sudo mount /dev/xvdh1 /vol sudo service mysql start

Usually there is a problem with logs directory or permissions, so check in /etc/mysql/my.cnf logs dir. 

To create log path:  

mkdir /vol/log mkdir /vol/log/mysql sudo chown -R mysql:mysql /vol/mysql/ sudo service mysql start

Now, when Mysql started (make sure that apache or nginx are down! DB should have NO connections during restoring period) get the name of a current binary log: 

mysql -u root -p

show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 16154404 |              | mysql            | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

• Finally, here are our commands to restore:

mysqlbinlog --start-datetime="2016-12-22 12:02:23" --stop-datetime="2016-12-22 12:21:24" /home/ubuntu/mysql-bin.000451 | mysql -u root -pPASSWORD DBNAME mysqlbinlog --start-datetime="2016-12-22 12:21:24" --stop-datetime="2016-12-22 13:56:20" /home/ubuntu/mysql-bin.000452 | mysql -u root -pPASSWORD DBNAME

Don't forget to start apache or nginx after final checks.

UPDATE: better to use log positions, not time to avoid data loss in those milliseconds