Point in time/position recovery with MySQLDUMP backup in MySQL

Point in time/position recovery with MySQLDUMP backup in MySQL

Point in time required the MySQL Server is in binary logs.

Following are the steps involved in point in time recovery with example:

1. Create the Database.

mysql> create database test10;
Query OK, 1 row affected (0.26 sec)

2. Create the table.

mysql> create table t (id int);
ERROR 1046 (3D000): No database selected

mysql> use test10
Database changed

mysql> create table t (id int);
Query OK, 0 rows affected (1.29 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.08 sec)

mysql> insert into t values(2);
Query OK, 1 row affected (0.13 sec)

mysql> select * from t;

+------+
| id   |
+------+
|    1 |
|    2 |
+------+

3. Show the binary logs.

mysql> show binary logs;

+-----------------------+-----------+-----------+
| Log_name              | File_size | Encrypted |
+-----------------------+-----------+-----------+
| IXC1-MYSQL-bin.000008 |       574 | No        |
| IXC1-MYSQL-bin.000009 |   3652775 | No        |
| IXC1-MYSQL-bin.000010 |     11278 | No        |
| IXC1-MYSQL-bin.000011 |       178 | No        |
| IXC1-MYSQL-bin.000012 |      1090 | No        |
+-----------------------+-----------+-----------+
12 rows in set (0.16 sec)

4. Go to binary files & take backup of TEST10 database.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -u root -p test10 > C:\backuptest10.sql
Enter password: *****

mysql>  show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| IXC1-MYSQL-bin.000012 |     1090 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+

5. Delete the table.

mysql> delete from t;
Query OK, 2 rows affected (0.39 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

6. Show the binary logs and check at which position it run delete query.

show binlog events in 'IXC1-LP48ZJ622-bin.000012';

mysql> show binlog events in 'IXC1-LP48ZJ622-bin.000012';
+-----------------------+------+----------------+-----------+-------------+----------------------------------------------------+
| Log_name              | Pos  | Event_type     | Server_id | End_log_pos |Info                                               |
+-----------------------+------+----------------+-----------+-------------+----------------------------------------------------+
| IXC1-MYSQL-bin.000012 | 1059 | Xid            |         1 |        1090 |COMMIT /* xid=12 */                                |
| IXC1-MYSQL-bin.000012 | 1090 | Anonymous_Gtid |         1 |        1169 |SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| IXC1-MYSQL-bin.000012 | 1169 | Query          |         1 |        1246 |BEGIN                                              |
| IXC1-MYSQL-bin.000012 | 1246 | Table_map      |         1 |        1295 |table_id: 85 (test10.t)                            |
| IXC1-MYSQL-bin.000012 | 1295 | Delete_rows    |         1 |        1340 |table_id: 85 flags: STMT_END_F                     |
| IXC1-MYSQL-bin.000012 | 1340 | Xid            |         1 |        1371 |COMMIT /* xid=45 */                                |
+-----------------------+------+----------------+-----------+-------------+----------------------------------------------------+

7. Take the backup up to position.

mysqlbinlog --stop-position=1090 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\IXC1-LP48ZJ622-bin.000012" > C:\incompletereocvery.sql

Note: other option is you have timing:
mysqlbinlog --start-datetime="2019-06-19 16:20:00" --stop-datetime="2019-06-19 16:25:00" "C:\ProgramData\MySQL\MySQL Server 8.0\Data\IXC1-LP48ZJ622-bin.000010" > C:\pointintime.sql

9. Create a separate database for restore the table.

mysql> create database test11;
Query OK, 1 row affected (0.11 sec)

10. Restore the last full database backup.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p test11 < C:\backuptest10.sql
Enter password: *****

Error: occurred if you have any create database in between your binary log. To avoid always create mysqldump backup with –flush-logs option.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p test11 < C:\incompletereocvery.sql
Enter password: *****
ERROR 1007 (HY000) at line 37: Can't create database 'test10'; database exists

In my case error occurred so i generate log with start or stop position.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlbinlog --start-position=1059 --stop-position=1090
C:\ProgramData\MySQL\MySQL Server 8.0\Data\IXC1-LP48ZJ622-bin.000012" > C:\incompletereocvery.sql

11. Start the incomplete recovery up-to particular position.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p test11 < C:\incompletereocvery.sql
Enter password: *****

12. Check the new database and table with data.

mysql> use test11
Database changed
mysql> select * from t;

+------+
| id   |
+------+
|    1 |
|    2 |
+------+

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.