test_db-master.zip

The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens Corporate Research. The data is in XML format.http://timecenter.cs.aau.dk/software.htm

1.导入示例数据库employees，为表添加索引：idx_hire(emp_no,hire_date)

  1. alter table employees.employees add index idx_hire(emp_no,hire_date)
  3. # 检查
  4. show index from employees.employees;

记录好创建索引时的时间

2.用mysqldump对数据库employees全库进行备份

  1. mysqldump -uroot -proot@123 --databases employees > ~/test_db-master/backup/db-employees.sql

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don’t want to restore GTIDs, pass —set-gtid-purged=OFF. To make a complete dump, pass —all-databases —triggers —routines —events.

3.插入任意一条数据进入表employees，并且查询出来

  1. # 表结构
  2. desc employees.employees;
  3. +------------+---------------+------+-----+---------+-------+
  4. | Field      | Type          | Null | Key | Default | Extra |
  5. +------------+---------------+------+-----+---------+-------+
  6. | emp_no     | int           | NO   | PRI | NULL    |       |
  7. | birth_date | date          | NO   |     | NULL    |       |
  8. | first_name | varchar(14)   | NO   |     | NULL    |       |
  9. | last_name  | varchar(16)   | NO   |     | NULL    |       |
  10. | gender     | enum('M','F') | NO   |     | NULL    |       |
  11. | hire_date  | date          | NO   |     | NULL    |       |
  12. +------------+---------------+------+-----+---------+-------+
  13. 6 rows in set (0.00 sec)
  15. # 查看emp_no最大值
  16. select max(emp_no) from employees.employees;
  17. +-------------+
  18. | max(emp_no) |
  19. +-------------+
  20. |      499999 |
  21. +-------------+
  22. 1 row in set (0.00 sec)
  24. # 插入数据
  25. insert into employees.employees values (500000,'2021-6-8',"zhang","san",'M','2020-7-30');
  26. # or
  27. insert into employees.employees (emp_no,birth_date,first_name,last_name,gender,hire_date) values (500001,'2021-6-8',"li","si",'M','2020-7-30');
  29. # flush privileges;
  31. # 查询
  32. select * from employees where emp_no=500000;
  33. # or
  34. select * from employees where first_name="li";

主键没有AUTO_INCREMENT表示不是自增的，也可以查看建表语句来判断：

show create table employees.employees;

4.解析binlog日志，查看binlog日志的记录。mysqlbinlog —no-defaults —base64-output=decode-rows -vv - -start-datetime=‘2021-04-01 18:10:03’ mysql-bin.000033>bin33.log

  1. mysqlbinlog --no-defaults --database=employees --start-datetime='2021-06-08 18:57:10' --stop-datetime='2021-06-08 20:00:00' mysql-binlog.000006 | more

5.在解析的binlog中找到3步骤的操作并观察记录方式。

6.再次使用mysqldump导出数据库employees

  1. mysqldump -uroot -proot@123 --databases employees > ~/test_db-master/backup/db-employees2.sql

7.使用步骤2的备份恢复数据库，观察恢复的数据库（此时没有新增的那条数据）

8.删除employees库，用步骤6的备份恢复employees库（此时有新增的那条数据）