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 | +------+