MySQL Binlogs:: How to recover

So I realized I had not made a post about this after this situation that recently came up.

Here is the scenario: A backup was taken at midnight, they used MySQL dumps per database. Then at ten am the next day the database crashed. A series of events happened before I was called in, but they got it up to a version of the database with MyISAM tables and the IBD files missing from the tablespace.

So option 1, restoring from backup would get us to midnight and we would lose hours of data. Option 2, we reimport the 1000's of ibd files and keep everything. Then we had option 3, restore from backup, then apply the binlogs for recent changes.

To make it more interesting, they didn't have all of the ibd files I was told, and I did see some missing. So not sure how that was possible but option 2 became an invalid option. They, of course, wanted the least data loss possible, so we went with option 3.

To do this safely I started up another instance of MySQL under port 3307. This allowed me a safe place to work while traffic had read access to the MyISAM data on the port 3306 instance.

Once all the backup dump files uncompressed and imported into the 3307 instance I was able to focus on the binlog files.

At first this concept sounds much harder risky than it really is. It is actually pretty straight forward and simple.

So first you have to find the data your after. A review of the binlog files gives you a head start as to what files are relevant. In my case, somehow they managed to reset the binlog so the 117 file had 2 date ranges within it.

First for binlog review, the following command outputs the data in human-readable format.

mysqlbinlog --defaults-file=/root/.my.cnf  --base64-output=DECODE-ROWS  --verbose mysql-bin.000117 >   review_mysql-bin.000117.sql


*Note... Be careful running the above command. Notice I have it dumping the file directly in same location as binlog. So VALIDATE that your file name is valid.  This mysql-bin.000117.sql is different than this mysql-bin.000117 .sql  . You will loose your binlog with the 2nd option and a space before .sql.

Now to save the data so it can be applied. Since I had several binlogs I created a file and I wanted to double-check the time ranges anyway.


mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql

mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"



Now I applied all the data from those binlogs for the given time ranges. The client double-checked all data and was very happy to have it all back.

Several different options existed for this situation, this happened to workout best with the client.

Once the validated all was ok on the restored version it was a simple stop both databases, moved the data directories (wanted to keep the datadir defaults intact) , chown the directories just to be safe and start up MySQL. Now the restored instance was up on port 3306.

Recover Lost MySQL data with mysqlbinlog point-in-time-recovery example

Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless.  Having your MySQL binlogs enabled can certainly help you in times of an emergency as well.  The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (row-based is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide.


[anothermysqldba]> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name                   | Value                                      |
+---------------------------------+--------------------------------------------+
| log_bin                         | ON                                         |
| log_bin_basename                | /var/lib/mysql/binlogs/mysql-binlogs       |
| log_bin_index                   | /var/lib/mysql/binlogs/mysql-binlogs.index |

show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+


So this is just a simple example using mysqlbinlog to recover data from a binlog and apply it back to the database.

First we need something to loose. If something was to happen to our database we need to be able to recover the data or maybe it is just a way to recover from someones mistake.


CREATE TABLE `table_w_rdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somedata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `moredata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 

We can pretend here and assume that we have developers/DBAs that are not communicating very well and/or saving copies of their code.


delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;
call populate_dummydata(50);

> SELECT NOW() \G
*************************** 1. row ***************************
NOW(): 2014-11-27 17:32:25
1 row in set (0.00 sec)

> SELECT  * from table_w_rdata  WHERE id > 45;
+----+----------------------------+------------------+
| id | somedata                   | moredata         |
+----+----------------------------+------------------+
| 46 | b204e9800998ecf8427e       | 0998ecf8427e     |
| 47 | d98f00b204e9800998ecf8427e | 8ecf8427e        |
| 48 | b204e9800998ecf8427e       | 800998ecf8427e   |
| 49 | 98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 50 | 98f00b204e9800998ecf8427e  | 998ecf8427e      |
+----+----------------------------+------------------+


While one procedure is created it is later written over by someone else incorrectly. 


DROP PROCEDURE IF EXISTS populate_dummydata ;
delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @C + (RAND() * @A ))) as somedata, SUBSTR(md5(''),FLOOR( @B + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;

call populate_dummydata(50);
> SELECT NOW(); SELECT  * from table_w_rdata  WHERE id > 95;
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-27 17:36:28 |
+---------------------+
1 row in set (0.00 sec)

+-----+-------------------+---------------------+
| id  | somedata          | moredata            |
+-----+-------------------+---------------------+
|  96 | 4e9800998ecf8427e | 00998ecf8427e       |
|  97 | 9800998ecf8427e   | 800998ecf8427e      |
|  98 | e9800998ecf8427e  | 204e9800998ecf8427e |
|  99 | e9800998ecf8427e  | 4e9800998ecf8427e   |
| 100 | 9800998ecf8427e   | 04e9800998ecf8427e  |
+-----+-------------------+---------------------+


The replaced version of the procedure is not generating  random values like the team wanted. The original creator of the procedure just quit from frustration. So what to do? A little time has past since it was created as well. We do know the database name, routine name and the general time frame when the incorrect procedure was created and lucky for us the bin logs are still around, so we can go get it.

We have to take a general look around since we just want a point-in-time-recovery of this procedure.We happen to find the procedure and the position in the binlog before and after it.


NOW(): 2014-11-27 19:46:17
# mysqlbinlog  --start-datetime=20141127173200 --stop-datetime=20141127173628 --database=anothermysqldba mysql-binlogs.000001  | more

 at 253053
 at 253564

# mysql anothermysqldba --login-path=local  -e "DROP PROCEDURE populate_dummydata";
# mysqlbinlog  --start-position=253053 --stop-position=253564 --database=anothermysqldba mysql-binlogs.000001 | mysql --login-path=local  anothermysqldba


> SHOW CREATE PROCEDURE populate_dummydata\G
*************************** 1. row ***************************
           Procedure: populate_dummydata
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_dummydata`( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

NOW(): 2014-11-27 19:51:03
> call populate_dummydata(50);
> SELECT  * from table_w_rdata  WHERE id > 145;
+-----+-----------------------------+------------------+
| id  | somedata                    | moredata         |
+-----+-----------------------------+------------------+
| 146 | 98f00b204e9800998ecf8427e   | 800998ecf8427e   |
| 147 | cd98f00b204e9800998ecf8427e | 800998ecf8427e   |
| 148 | 204e9800998ecf8427e         | 98ecf8427e       |
| 149 | d98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 150 | 204e9800998ecf8427e         | 9800998ecf8427e  |
+-----+-----------------------------+------------------+


We recovered our procedure from the binary log via point-in-time-recovery.
This is a simple example but it is an example of the tools you can use moving forward.

This is why the binlogs are so valuable.

Helpful URL:

• http://dev.mysql.com/doc/refman/5.6/en/binary-log.html
• http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html

"Tools" of the trade

I figured it might be worth creating a list of the top tools of the trade that we all use.
First and foremost this is to say thanks to everyone who helps create these tools.
Second it is to allow others who do not use these to see and learn how these can be used and why.

• MySQL command line client. 
• This is a given I know but it is the best access to MySQL. 
• mysql -p --prompt="\u@\h [\d]>\_ Master >"
• Xtrabackup
• ./xtrabackup --defaults-file=/etc/my.cnf --backup --stats --target-dir=~/backups/  --prepare  --export --user=root --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=/var/lib/mysql/ 
• mysqlbinlog 
• mysqlbinlog --start-datetime="2013-04-18 00:00:00" --stop-datetime="2013-04-18 01:59:59"   /var/lib/mysql/binlog.000038
• http://www.pythian.com/blog/mysqlbinlog-tips-and-tricks/
• http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html
• https://kb.askmonty.org/en/using-mysqlbinlog/
• mysqltuner 
• A good overall view into what you could have going on with your database. 
• Percona Toolkit 
• exampes:
• pt-query-digest 
• example: pt-query-digest --ask-pass /var/lib/mysql/mysql-slow.log
• pt-table-checksum
• example: pt-table-checksum --ask-pass
• pt-table-sync
• example: pt-table-sync --no-check-triggers --ask-pass --no-check-triggers --execute --print
• pt-show-grants 
• pt-show-grants  --ask-pass
• MySQL Utilities 
• exampes:
• python mysqldiskusage --server=root:password@localhost
• python mysqlindexcheck --server= root:password@localhost ps_helper.schema_index_statistics
• python mysqlprocgrep --server= root:password@localhost --match-user=root
• planet.mysql.com 
• This might make some people question but... Knowledge is power. This is the best location to learn about MySQL. 
• If you have to interview a MySQL DBA candidate, pick some common active authors and ask if they know who that person is. It will allow you to understand if they research the latest trends and information around MySQL or are just content with their experience.  
• MySQL Sandbox 
• There is a reason Giuseppe just won the MySQL Application of the year 2013 award.
• mytop 
• inspired by top but with a focus on MySQL
• innotop
• innotop is a 'top' clone for MySQL 
• https://tools.percona.com/
• This might also make some question. I add this because it is a quick and good way for people to get started and create a my.cnf that is better than the default installed version. If nothing else you can use it just to compare with what you think it should be. 

You might notice I am not a fan of the GUI. No offense to those GUI tools available but why GUI if you do not need it. But that is just my opinion and here is a list of GUI tools just to be fair. After all schema map is helpful and easily done with MySQL Workbench. 

Other tools for review:

• mycheckpoint
• common_schema
• openark-kit
• ps_helper
• db_stress
• Database benchmarking
• dim_STAT
• Nice easy tool to help analyze performance