Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Thursday, 8 February 2018

Database Recovery Using binlog file (mysql-bin.xxxxx)

Database Recovery Using binlog file (mysql-bin.xxxxx)

mysqlbinlog Utility for Processing Binary Log Files.

In this  article, I'm discussing about the use of mysqlbinlog Utility. How to read the binary logs , How to use it in case of Disaster recovery , how to export it in other readable format etc. I will start our discussion from basic and then drill in deep step-by-step.


Before starting our discussion, I would like to put a small footprint about MySQL binary log.



The binary log files that the server generates are written in binary format.MySQL records all the transaction in binary log. But we can not able to read this log normally due to it's binary format .To examine these files in text format, use the mysqlbinlog utility. As this file contains all the transaction so we can use this log for any Disaster Recovery / Point-in-Time recovery* .



We can also use mysqlbinlog to read relay log files written by a slave server in a replication* setup. Relay logs have the same format as binary log files.



How to display the content of  binary log file named mysql-bin.xxxxx ?


>> mysqlbinlog  [options] mysql-bin.xxxxx

eg.

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe  mysql-bin.xxxxx 

We can export this file to other format like .sql/.txt etc

eg.
C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe  mysql-bin.xxxxx > D:\GetMySQL.sql


We can also read this log from remote server by using some additional parameters like --read-from-remote-server ,--host, --password, --port, --protocol, --socket, --user etc.


Some of the important parameter's which we use in DB recovery as --database=name, --debug-info , --start-datetime=datetime , --stop-datetime=datetime , --start-position=N , --stop-position=N 


Once again I bring to mind you that Binary log is by-default disabled. So you need to enable* it before starting MySQL services. Binary Log is also use in setup of MySQL Replication*

For example, we have 5 binlog mysql-bin.00001,mysql-bin.00002,mysql-bin.00003,mysql-bin.00004,mysql-bin.00005

We have some limitation in this recovery.

1: We must have to restore any old backup which contains all the database/ table .
2: Need to administrative privileges to perform this activity.
3: Server health must be good in which we will perform this activity.

Now perform below steps in your task...

1: Restore any available old backup .
2: read and export all the binlog file to readable format.
3: Analyze binlog file Start time/Stop Time/Start Position/Stop Position etc.
4: Now we use the below commands for restoring...

For restoring mysql-bin.00001

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe mysql-bin.00001 | mysql -h [host ip] -u root -p ;

If you want to recover by Specifying time stamp..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --stop-time="2018-02-07 23:59:59" d:\mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-time="2018-02-07 00:00:01" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-time="2018-02-07 00:00:01" --stop-time="2018-02-07 23:59:59" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

If you want to recover by Specifying particular position..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --stop-position="255124" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-position="107" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-position="107" --stop-position="255124" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

If you want to recover by Specifying the operation perform any particular database..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --database="getmysql" --start-time="2018-02-07 00:00:01" --stop-time="2018-02-07 23:59:59" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

Some more parameter also useful in recover which are already mention earlier .


Note: I'm suggesting to perform this activity on standby server to avoid any miss happening after that propagate the same on production server.


Friday, 2 February 2018

How to recover Your crashed/corrupted databases?.

Recover Your crashed/corrupted databases.

We have tried our best effort to scrape out some information which hopefully help to diagnose the issue, but sometime it's really painful to recover the same. 
So we are sharing some points regarding this concern...

On Linux -->>

Steps to get it back up.

1. Stop mysqld Service.
2. Backup /var/lib/mysql/ib*     [Backup MySQL datafolder]
3. Add the following line into /etc/my.cnf:
innodb_force_recovery = 3
4. Restart mysqld.
5. Dump all tables:# mysqldump -u root --password=pwd --routines --all-database > /home/getmysql.sql
6. Drop all databases which need recovery.
7. Stop mysqld.
8. Remove /var/lib/mysql/ib*
9. Comment out or remove innodb_force_recovery = 3 in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/getmysql.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < getmysql.sql


On Windows -->>

Steps to get it back up.

1. Stop mysql Service.
2. Backup C:\ProgramData\MySQL\MySQL Server 5.6\data    [Backup MySQL datafolder]
3. Add the following line into C:\Program Files\MySQL\MySQL Server 5.6\my.ini:
innodb_force_recovery = 3
4. Restart mysql Service.
5. Dump all tables:# mysqldump -u root --password=pwd --routines --all-database > d:\getmysql.sql
6. Drop all databases which need recovery.
7. Stop mysql service.
8. Remove C:\ProgramData\MySQL\MySQL Server 5.6\ibdata*
9. Comment out or remove innodb_force_recovery = 3 in C:\Program Files\MySQL\MySQL Server 5.6\my.ini
10. Restart mysql service. Look at mysql error log. By default it should be C:\ProgramData\MySQL\MySQL Server 5.6\data\getmysql.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < getmysql.sql

Hopefully it will recover you crashed database. If you have still facing any error kindly collect screenshot and some related information and Share with us by Click Here
Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...