23 Feb

Restore / Recover dropped MySQL database from binary logs

In this post I will share a recovery scenario of a MySQL database restore from the binary logs.

Recently someone accidentally dropped an important MySQL database and the backup was not present!

As we know the the binary log contains DMLs to table data and that’s where our hope lies. Luckily the binary log retention period (expire_logs_days) was set to 30 days and the CREATE DATABASE was still present in the binary logs.

Thus this restore of the dropped MySQL database can be done from extracting respective DMLs point-in-time. Here we will play the binary logs against a temporary MySQL instance and extract the required database.

Let’s begin the recovery of database.
Continue Reading…

03 Feb

MySQL grant syntax & dynamic database using wildcards

I was recently looking at a MySQL grant script to create default system users after server setup.
One of the syntax failed with error and that drew my attention to the details.

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''dbname%'.* to 'dbuser'@'%'' at line 1

Continue Reading…

-- Kedar Vaijanapurkar --