Tip: after the article is written, the directory can be generated automatically. Please refer to the help document on the right for how to generate it
preface
I saw it on the bus in the morning**_ The article on MySQL binlog written by yidianyu * * in Jiangnan is very detailed about the use of binlog to recover the database. I can understand it only at the level of installing mysql, so I try it in the company without going on a business trip.
1, What is binlog?
binlog is actually the same as oracle's archivelog archive log.
2, MYSQL enable binlog
Check whether mysql has enabled binlog
mysql> show variables like 'log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+-----------------------------+ 5 rows in set (0.00 sec)
log_bin=no means that the binlog function is not enabled in the database.
How to determine which my. Is currently used by mysql CNF profile
Use the following command to view all mysql configuration files. mysql will automatically identify from the first one. If not, it will be postponed.
[root@hisdb etc]# mysql --help | grep 'Default options' -A 1 Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
perhaps
[root@hisdb etc]# whereis my.cnf my: /etc/my.cnf
In my Insert the following parameters into CNF
# This parameter indicates that the binlog function is enabled and specifies the storage directory of binlog log-bin=binlog # Set the maximum bytes of a binlog file # Set maximum 100MB max_binlog_size=104857600 # Set the validity period of binlog file (unit: day) expire_logs_days = 7 # The binlog log only records the updates of the specified database (used when configuring master-slave replication) #binlog-do-db=javaboy_db # The binlog log does not record the updates of the specified database (used when configuring master-slave replication) #binlog-ignore-db=javaboy_no_db # How many times to write to the cache and brush the disk once. The default value of 0 means that the operating system decides how often to write to the disk according to its own load # 1 means that each transaction commit will write to the disk immediately, and N means that n transaction commit will write to the disk sync_binlog=0 # Get a unique id for the current service (it needs to be configured after MySQL 5.7) server-id=1
Restart mysql
mysql> show variables like 'log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+-----------------------------+ 5 rows in set (0.01 sec)
Three, analog data loss (delete library run away)
Create an instance test, create a table, and insert data
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table employee6(emp_id int auto_increment primary key, emp_name varchar(50), age int, dept_id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into employee6(emp_name,age,dept_id) values
-> ('tianyun',19,200),
-> ('tom',26,201),
-> ('jack',30,201),
-> ('alice',24,202),
-> ('robin',40,200),
-> ('natasha',28,204);
Query OK, 6 rows affected (0.11 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 as select * from employee6 where 1=1;
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
View the current binlog list
mysql> show master logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 499 | No | | binlog.000002 | 1648 | No | +---------------+-----------+-----------+ 2 rows in set (0.00 sec)
Manually switch binlog. Each switch will generate a new binlog. The previous process of inserting t1 table is recorded in binlog 00000 2.
mysql> flush logs; Query OK, 0 rows affected (0.03 sec) mysql> show master logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 499 | No | | binlog.000002 | 1692 | No | | binlog.000003 | 156 | No | +---------------+-----------+-----------+ 3 rows in set (0.00 sec)
Check the binlog file, where you can find all operation records and corresponding pos points.
mysql> show binlog events in 'binlog.000002'; +---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | binlog.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.27, Binlog ver: 4 | | binlog.000002 | 125 | Previous_gtids | 1 | 156 | | | binlog.000002 | 156 | Anonymous_Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 233 | Query | 1 | 341 | create database test /* xid=7 */ | | binlog.000002 | 341 | Anonymous_Gtid | 1 | 420 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 420 | Query | 1 | 613 | use `test`; create table employee6(emp_id int auto_increment primary key, emp_name varchar(50), age int, dept_id int) /* xid=13 */ | | binlog.000002 | 613 | Anonymous_Gtid | 1 | 692 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 692 | Query | 1 | 767 | BEGIN | | binlog.000002 | 767 | Table_map | 1 | 832 | table_id: 91 (test.employee6) | | binlog.000002 | 832 | Write_rows | 1 | 982 | table_id: 91 flags: STMT_END_F | | binlog.000002 | 982 | Xid | 1 | 1013 | COMMIT /* xid=15 */ | | binlog.000002 | 1013 | Anonymous_Gtid | 1 | 1092 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1092 | Query | 1 | 1167 | BEGIN | | binlog.000002 | 1167 | Query | 1 | 1409 | use `test`; CREATE TABLE `t1` ( `emp_id` int NOT NULL DEFAULT '0', `emp_name` varchar(50) DEFAULT NULL, `age` int DEFAULT NULL, `dept_id` int DEFAULT NULL ) START TRANSACTION | | binlog.000002 | 1409 | Table_map | 1 | 1467 | table_id: 92 (test.t1) | | binlog.000002 | 1467 | Write_rows | 1 | 1617 | table_id: 92 flags: STMT_END_F | | binlog.000002 | 1617 | Xid | 1 | 1648 | COMMIT /* xid=18 */ | | binlog.000002 | 1648 | Rotate | 1 | 1692 | binlog.000003;pos=4 | +---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 18 rows in set (0.01 sec)
Backup test instance
[root@hisdb mysql]# mysqldump -uroot -p --flush-logs --lock-tables -B test>/home/test.bak.sql Enter password: [root@hisdb mysql]# cd /home/ [root@hisdb home]# ls mysql80-community-release-el7-3.noarch.rpm oracle soft test.bak.sql z
After the backup is completed, insert the t2 table in test, and this operation is recorded in binlog 00000 3
mysql> create table t2 (name varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t2 values('ning');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values('lllng');
Query OK, 1 row affected (0.09 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Delete the database and run, and delete the test instance
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database test; Query OK, 2 rows affected (1.39 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> exit
Use the previous backup for recovery. Because the amount of data here is very small, the recovery is very fast.
[root@hisdb home]# mysql -uroot -p < /home/test.bak.sql Enter password:
View data after recovery
[root@hisdb home]# mysql -uroot -p'Huawei12#$' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.01 sec) mysql> select * from t1; ERROR 1046 (3D000): No database selected mysql> use test; 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> select * from t1; +--------+----------+------+---------+ | emp_id | emp_name | age | dept_id | +--------+----------+------+---------+ | 1 | tianyun | 19 | 200 | | 2 | tom | 26 | 201 | | 3 | jack | 30 | 201 | | 4 | alice | 24 | 202 | | 5 | robin | 40 | 200 | | 6 | natasha | 28 | 204 | +--------+----------+------+---------+ 6 rows in set (0.00 sec)
Since the t2 table has not been created at the time of backup, the t2 table cannot be queried after backup recovery. The t2 table needs to be recovered with binlog.
mysql> select * from t2; ERROR 1146 (42S02): Table 'test.t2' doesn't exist
Restore the test instance with binlog to the time point before deleting the database
mysql> show master logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 499 | No | | binlog.000002 | 1692 | No | | binlog.000003 | 200 | No | +---------------+-----------+-----------+ 4 rows in set (0.00 sec)
View binlog 00000 3 and find the pos point before deleting the library
mysql> show binlog events in 'binlog.000003';
It can be seen that the database deletion and running event occurred in the Pos 989-1093, so we only need to playback the file and restore the data to the position 989.
[root@hisdb home]# mysqlbinlog /var/lib/mysql/binlog.000003 --stop-position=989 --database=test | mysql -uroot -p Enter password: WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
After seeing the security prompt, just continue to enter the password below.
When the recovery is complete, check whether the t2 table exists.
mysql> use test; 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> select * from t2; +-------+ | name | +-------+ | ning | | lllng | +-------+ 2 rows in set (0.00 sec)
The t2 table has been restored.