Overview
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.
Configuration
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
Manual Recover
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.
Apply to specific log position
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 | +------+--------+