mysqlbckrvry_recover

Recovery in MySQL, as with any other database, is the manual(or automatic in some cases) but in either case, PUSHING the consistency of the data forward. This is called roll forward in database terms. Few technologies like Oracle support also roll backward, but this is a story for another time.. and another chapter :D Anyhow, so as with other technologies, roll forward is achieved via copy of logs, most precisely binlogs, binlogs are holding changes and there are 2 types of a binlogs which we can generate:

  • Statement level - Stores Queries(DMLs and DDLs). Pros: Can be used to replicate EVERYTHING, Cons: Order might be a problem
  • Row Level - Cannot be used for DDLs: Pros: a few which come to mind :) Cons: A lot, takes a lot of space, cannot replicate DDLs, cannot hold LOBs and others.

Advise: Use BOTH :). MySQL can decide when to use which PERFECTLY :) This is also default setting.

The setting of a log generation in my.cnf file is pretty simple, just add the following setting:

[root@mysqlmaster binlog]# cat /etc/my.cnf | grep bin
log-bin=/binlog/mysql-bin - This will put the binlogs into directiryL /binlog and they will have a name starting with: mysql-bin-000Number
binlog-do-db=newDB - This will specify which database to be done, if this parameter is omitted, then binlog will contain data about changes for all databases.
sync_binlog=1

The binlogs are reflected into an index file:

[root@mysqlmaster binlog]# ls -lart
total 24
dr-xr-xr-x. 20 root  root  4096 Jun 11 07:33 ..
-rw-r-----   1 mysql mysql  201 Jun 11 07:35 mysql-bin.000001
-rw-r-----   1 mysql mysql  661 Jun 11 07:37 mysql-bin.000002
-rw-r-----   1 mysql mysql  177 Jun 11 07:37 mysql-bin.000003
-rw-r-----   1 mysql mysql  100 Jun 11 07:41 mysql-bin.index    <- This thingy
drwxr-xr-x   2 mysql mysql  125 Jun 11 07:41 .
-rw-r-----   1 mysql mysql  614 Jun 11 07:42 mysql-bin.000004

This “thingy” will gold information about the location of the binlogs:

[root@mysqlmaster binlog]# cat mysql-bin.index
/binlog/mysql-bin.000001
/binlog/mysql-bin.000002
/binlog/mysql-bin.000003
/binlog/mysql-bin.000004

DO NOT UPDATE THIS FILE MANUALLY or else you can end up in a big misleading situation

An important piece of information which you need is the logposition. That is why, ALWAYS take the logposition AFTER the table are locked for MyISAM tables and before/after the backup for InnoDB tables. ALWAYS verify you have a log position, so you will know until when you have to apply. As we already noted, redos are used ONLY For crash recovery, they CANNOT be used for a PIT (Point-In-Time recovery) since they hold only CURRENT changes. That is why it is good (I would say mandatory) to have binlogs. The apply of a binlog files, happens during the mysql running time. Meaning, if I shut down mysqld, I cannot apply the logs. The application itself is easy, you can also explore the binlog's content as follows:

[root@mysqlmaster binlog]# mysqlbinlog /binlog/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180611  7:35:10 server id 1  end_log_pos 123 CRC32 0x401c9734  Start: binlog v 4, server v 5.7.21-log created 180611  7:35:10
BINLOG '
bl4eWw8BAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATSXHEA=
'/*!*/;
# at 123
#180611  7:35:10 server id 1  end_log_pos 154 CRC32 0xd8f08977  Previous-GTIDs
# [empty]
# at 154
#180611  7:36:55 server id 1  end_log_pos 219 CRC32 0x1b365e17  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180611  7:36:55 server id 1  end_log_pos 342 CRC32 0x8b605f2e  Query   thread_id=5     exec_time=0     error_code=0
use `newDB`/*!*/;
SET TIMESTAMP=1528717015/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_of

Using the binlog location, the application of a binlog is done as follows:

[root@mysqlmaster mysqldata]# mysqlbinlog /binlog/mysql-bin.000002 | mysql -uroot -p
Enter password:
[root@mysqlmaster mysqldata]# mysqlbinlog /binlog/mysql-bin.000003 | mysql -uroot -p
Enter password:
[root@mysqlmaster mysqldata]#

With that, we applied change from logs: 000002 and 000003.

We can also apply up to a specific log position as follows:

[root@mysqlmaster binlog]# mysqlbinlog --stop-position=154 mysql-bin.000007 | mysql -uroot -p
Enter password:
[root@mysqlmaster binlog]#
[root@mysqlmaster binlog]#
[root@mysqlmaster binlog]#
[root@mysqlmaster binlog]#
[root@mysqlmaster binlog]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@mysqlmaster binlog]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use newDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_newDB |
+-----------------+
| newTable        |
| newTable2       |
| newTable3       |
| testDataBH      |
+-----------------+
4 rows in set (0.00 sec)

mysql> select * from newTable3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Julien |
+------+--------+

  • mysqlbckrvry_recover.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1