preface

With the rapid development of the Internet today, MySQL as a relational database is undoubtedly one of the most widely used database operating systems, and as a developer, more or less need to understand some database knowledge.

This will be demonstrated with MySQL5.7, and MySQL has been updated to 8.0 as of now.

This article will be updated as a series that includes, but is not limited to:

  • MySQL basic architecture and log files
  • MySQL database Log file Bin Log recovery allows you to delete database files without running
  • MySQL language: DDL/DML/DQL/DCL
  • MySQL transaction
  • MySQL storage engine: MyISAM/InnoDB
  • MySQL cluster scheme and construction
  • MySQL database MySQL database

Binary Log

Binary Log is a very important Log in MySQL. DDL and DML statements of SQL statements are used to record changes in the database, excluding data record query operations.

If you accidentally delete some data or worse, the entire library is deleted by you, then don’t worry, with bin log you don’t need to delete the library to run away. The database must have bin log enabled

By default, the bin log feature is turned off. You can run the following command to check whether binary log is enabled:

show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | Check log_bin_use_v1_row_events | OFF | | sql_log_bin | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 6 rows in the set (0.00) sec)Copy the code

How do I enable Bin Log

Mysqld = my.cnf (mysqld);

STATEMENT/ROW/MIXED binlog_format=ROW # log-bin=mysqlbinlogCopy the code

Run the following command to check whether the bin log function is enabled:

show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | log_bin | ON | / / open | log_bin_basename | /var/lib/mysql/mysqlbinlog | | log_bin_index | /var/lib/mysql/mysqlbinlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 6 rows in the set (0.00 SEC)Copy the code

Once enabled, we can create a database and add some records to see the changes in the bin log file

Create database young; Use young; Create table 'user' (' id 'int primary key,' name 'varchar(255)) InnoDB charset= UTf8MB4; Insert into user values(1,'zhangsan'); insert into user values(2,'lisi'); Update user set name='gebilaowang' where id = 2; Select * from user; +----+-------------+ | id | name | +----+-------------+ | 1 | zhangsan | | 2 | gebilaowang | +----+-------------+ 2 rows In set (0.00 SEC) drop database young;Copy the code

Now that the data has been created, manipulated, and deleted, what happens to the bin log file

// You can also run show binray logs; Check show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | mysqlbinlog.000001 | 154 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) / / view Bin log Log file show binlog events in 'mysqlbinlog.000001'; +--------------------+------+----------------+-----------+-------------+------------------------------------------------ -+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +--------------------+------+----------------+-----------+-------------+------------------------------------------------ - + | mysqlbinlog. 000001 | | 4 Format_desc | 121 | 123 | Server ver: 5.7.30 - log, Binlog ver: 4 | | mysqlbinlog.000001 | 123 | Previous_gtids | 121 | 154 | | | mysqlbinlog.000001 | 314 | Anonymous_Gtid | 121 | 379 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbinlog.000001 | 379 | Query | 121 | 476 | create database young | | mysqlbinlog.000001 | 476 | Anonymous_Gtid | 121 | 541 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbinlog.000001 | 541 | Query | 121 | 716 | use `young`; create table `user` ( `id` int primary key, `name` varchar(255) ) engine=InnoDB charset=utf8mb4 | | mysqlbinlog.000001 | 716 | Anonymous_Gtid | 121 | 781 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbinlog.000001 | 781 | Query | 121 | 854 | BEGIN | | mysqlbinlog.000001 | 854 |  Table_map | 121 | 905 | table_id: 113 (young.user) | | mysqlbinlog.000001 | 905 | Write_rows | 121 | 955 | table_id: 113 flags: STMT_END_F | | mysqlbinlog.000001 | 955 | Xid | 121 | 986 | COMMIT /* xid=340 */ | | mysqlbinlog.000001 | 986 | Anonymous_Gtid | 121 | 1051 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbinlog.000001 | 1051 | Query | 121 | 1124 | BEGIN | | mysqlbinlog.000001 | 1124 | Table_map | 121 | 1175 | table_id: 113 (young.user) | | mysqlbinlog.000001 | 1175 | Write_rows | 121 | 1221 | table_id: 113 flags: STMT_END_F | | mysqlbinlog.000001 | 1221 | Xid | 121 | 1252 | COMMIT /* xid=341 */ | | mysqlbinlog.000001 | 1252 | Anonymous_Gtid | 121 | 1317 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbinlog.000001 | 1317 | Query | 121 | 1390 | BEGIN | | mysqlbinlog.000001 | 1390 | Table_map | 121 | 1441 | table_id: 113 (young.user) | | mysqlbinlog.000001 | 1441 | Update_rows | 121 | 1506 | table_id: 113 flags: STMT_END_F | | mysqlbinlog.000001 | 1506 | Xid | 121 | 1537 | COMMIT /* xid=342 */ | | mysqlbinlog.000001 | 1537 | Anonymous_Gtid | 121 | 1602 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbinlog.000001 | 1602 | Query | 121 | 1697 | drop database young | +--------------------+------+----------------+-----------+-------------+------------------------------------------------ -+ 25 rows in set (0.00 SEC)Copy the code

CREATE DATABASE Young CHARACTER SET ‘UTf8MB4’ COLLATE ‘UTf8MB4_bin’ records the creation of the DATABASE

The pos value is 1602 except that drop database young is recorded for deleting the database, and this is the source of your evil. You need to restore the data to the operation before deleting the database. Before restoring the data, let’s try to see if we can query the data.

select * from user; ERROR 1046 (3D000): No database selected ERROR 1046 (3D000): No database selected show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.00 SEC)Copy the code

Bin log -> mysqlBinlog.000001 log file is stored in binlog -> mysqlbinlog.000001.

/ / can fast positioning by Linux commands to the file location to find / -name mysqlbinlog. 000001 / var/lib/mysql/mysqlbinlog. 000001 find: '/ proc / 1 / map_files' : Permission denied // Switch the directory CD /var/lib/mysql/ / Run the following command to enter the password to complete the restoration operation: mysqlbinlog --start-position=379 --stop-position=1537 mysqlbinlog.000001 | mysql -uroot -p; Mysql -uroot -p show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | user | | young | / young/see the database has been restored back + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 9 rows in the set (0.00 SEC) / / view the data recovery use young; select * from user; +----+-------------+ | id | name | +----+-------------+ | 1 | zhangsan | | 2 | gebilaowang | +----+-------------+ 2 rows In set (0.00 SEC) // Data recovery is completeCopy the code

Command interpretation:

Mysqlbinlog - start - position = data recovery - stop - position = data recovery, the starting point of the end point of the mysqlbinlog. 000001 | mysql binlog log filename (database) - uroot - p; (Database connection)Copy the code

Parameter interpretation:

  • –start-position=379
    • Where do we start the recovery? You see 379 is where we create the database, that’s where everything starts, so we’re going to start the recovery from 379
  • –stop-position=1537
    • The pos value is 1602 except that the operation of dropping the table drop database young was recorded. We need to restore the operation before deleting the database, so the pos position in the log is 1537, so the restoration ends at 1537

In addition, the recovery can be performed by time point. As you can see from the view of the binlog conversion file below, all operations are performed today and the database deletion time is around 5:00 PM. Therefore, the recovery can be determined according to the specific time point in the log file

  • –start-datetime=”2020-08-24 17:35:29″ Start time
  • –stop-datetime=”2020-08-24 17:37:00″ End time
mysqlbinlog --start-datetime="2020-08-24 17:35:29" --stop-datetime="2020-08-24 17:37:00" mysqlbinlog.000001 | mysql -uroot -p;

Copy the code

To restore the binlog file by point in time, run the following command:

#200824 17:35:29 Server ID 121 end_log_pos 716 CRC32 0xe8933d54 Query thread_id=20 exec_time=0 error_code=0 use `young`/*! * /. SET TIMESTAMP=1598261729/*! * /. create table `user` ( `id` int primary key, `name` varchar(255) ) engine=InnoDB charset=utf8mb4 /*! * /. # at 716 #200824 17:35:34 server id 121 end_log_pos 781 CRC32 0xa5ee04cf Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes /*! 50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*! * /. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*! * /. # at 781 #200824 17:35:34 server id 121 end_log_pos 854 CRC32 0x62e8cfd7 Query thread_id=20 exec_time=0 error_code=0 SET  TIMESTAMP=1598261734/*! * /. BEGIN /*! * /. # at 854 #200824 17:35:34 server id 121 end_log_pos 905 CRC32 0x5eb41275 Table_map: `young`.`user` mapped to number 113 # at 905 #200824 17:35:34 server id 121 end_log_pos 955 CRC32 0x504eb48c Write_rows:  table id 113 flags: STMT_END_F ### INSERT INTO `young`.`user` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='zhangsan' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ # at 1175 #200824 17:35:35 server id 121 end_log_pos 1221 CRC32 0xf544a4f9 Write_rows: table id 113 flags: STMT_END_F ### INSERT INTO `young`.`user` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='lisi' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ # at 1221 #200824 17:35:35 server id 121 end_log_pos 1252 CRC32 0x6da9edcf Xid = 341 COMMIT/*! * /. # at 1537 #200824 17:37:17 server id 121 end_log_pos 1602 CRC32 0x66df6fbd Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*! * /. # at 1602 #200824 17:37:17 server id 121 end_log_pos 1697 CRC32 0x4c45a414 Query thread_id=20 exec_time=0 error_code=0 SET TIMESTAMP=1598261837/*! * /. drop database youngCopy the code

By observing the converted log file, it can be seen that the database is created at 200824 17:35:29, and the database is deleted at 200824 17:37:17.