MYSQL DATABASE DRILL OR BINARY LOGS RESTORATION

Please follow the below steps:

Required Files:
1. Source  and Destination(DRILL) server IP details
3. Full Backup of Source Server
4. Binary Logs of Source Server


Step 1: Take the Source server Full backup files and import into Drill server using below commands.

mysql -h <Drill_server_ip> -u <user_name> -p -f <db_name>    < "<Backup file.sql>"

Ex: mysql -h 10.0.0.1 -u root -p -f auditdb < "E:/auditdb.sql"

Step 2: Once Successfully restored full backup file then  take the binary logs from Full backup happened time to present time 

Step 3: Execute and save the result set of below select query in source server. To verify Objects list after backup drill.

SELECT ROUTINE_SCHEMA AS db, ROUTINE_TYPE AS object_type, COUNT(*) AS count   FROM INFORMATION_SCHEMA.ROUTINES  GROUP BY ROUTINE_SCHEMA, ROUTINE_TYPE UNION SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*)   FROM INFORMATION_SCHEMA.TABLES  GROUP BY TABLE_SCHEMA, TABLE_TYPE UNION SELECT TABLE_SCHEMA, CONCAT('INDEX (', INDEX_TYPE, ')'), COUNT(*)   FROM INFORMATION_SCHEMA.STATISTICS  GROUP BY TABLE_SCHEMA, INDEX_TYPE UNION SELECT TRIGGER_SCHEMA, 'TRIGGER', COUNT(*)   FROM INFORMATION_SCHEMA.TRIGGERS  GROUP BY TRIGGER_SCHEMA UNION SELECT EVENT_SCHEMA, 'EVENT', COUNT(*)   FROM INFORMATION_SCHEMA.EVENTS  GROUP BY EVENT_SCHEMA  ORDER BY DB, OBJECT_TYPE;

Step 4: Convert all binary logs to sql file using below command

mysqlbinlog "<Binalog path>"  > "DestinationPath.sql"

ex:  
mysqlbinlog "E:/binlog/bin-log.000006" > "E:/bin-log_06.sql"


Step 5: Restore the all converted binlogs in Drill server.

mysql -h <Drill_server_ip> -u <user_name> -p   < "<binlog file.sql>"


Ex: mysql -h 10.0.0.1 -u root -p  < "E:/bin-log_06.sql"


Step 6: Execute and save the result set of below select query in Drill server.

SELECT ROUTINE_SCHEMA AS db, ROUTINE_TYPE AS object_type, COUNT(*) AS count   FROM INFORMATION_SCHEMA.ROUTINES  GROUP BY ROUTINE_SCHEMA, ROUTINE_TYPE UNION SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*)   FROM INFORMATION_SCHEMA.TABLES  GROUP BY TABLE_SCHEMA, TABLE_TYPE UNION SELECT TABLE_SCHEMA, CONCAT('INDEX (', INDEX_TYPE, ')'), COUNT(*)   FROM INFORMATION_SCHEMA.STATISTICS  GROUP BY TABLE_SCHEMA, INDEX_TYPE UNION SELECT TRIGGER_SCHEMA, 'TRIGGER', COUNT(*)   FROM INFORMATION_SCHEMA.TRIGGERS  GROUP BY TRIGGER_SCHEMA UNION SELECT EVENT_SCHEMA, 'EVENT', COUNT(*)   FROM INFORMATION_SCHEMA.EVENTS  GROUP BY EVENT_SCHEMA  ORDER BY DB, OBJECT_TYPE;

Step 7: Now Validate the Database by comparing results set which is got in Step 3 and Step 6.