MySQL数据库应用

一、MySQL 二进制日志

1.启动二进制日志

（1）概念及功能

二进制日志以事件的形式记录各个更新操作，每个事件都会附加一些其他信息，

如事件的开始时间、结束时间等。

二进制文件内容为二进制信息，不可直接查看。

二进制日志主要用于记录数据库的变化情况，内容主要包括数据库所有的更新操 作，如 use 语句、insert 语句、delete 语句、update 语句、create 语句、alter 语 句和 drop 语句等。以事件形式记录了所有的DDL和DML(除了数据查询语句)语句，还包 含执行的消耗的时间，在数据丢失的紧急情况下，我们可以利用binlog日志功能进行数据恢 复。

数据库管理员可以通过二进制日志查看数据库过去某一时刻发生了哪些变化，必 要时可以使用二进制日志恢复数据库。

（2）开启日志功能

查看是否开启：MySQL 命令“show variables like 'log_bin';”可以查看二进 制日志是否开启，默认为 OFF，表示没有开启二进制日志。

开启日志：在 MySQL 配置文件 my.ini （该文件默认位置位于 MySQL 根目录 下，5.7的版本在C盘下找my.ini文件，如C:\ProgramData\MySQL\MySQL Server 5.7）的 [mysqld] 选项组中，添加“log_bin= D:\db_data\mysqllog\binary_log”可以开启二进制日志，配置的日志路径中的文 件夹必须提前建好，否则重启mysql服务报错。

1 ‐‐ 参数 log_bin 的值定义了二进制日志文件名。二进制日志参数一旦开启，

2 MySQL 将自动创建二进制日志文件，如 binary_log.000001。

3 ‐‐ 每次重启 MySQL 服务后，都会生成一个新的二进制文件，扩展名数字依次递增，

4 如 binary_log.000002等。MySQL 还会创建一个二进制日志索引文件

5 ‐‐ 使用 MySQL 命令“show variables like 'log_bin_index';”，可以查看二进制

6 日志索引文件的路径。

7 ‐‐ 二进制日志索引文件的内容是所有二进制日志文件的清单，它记录了所有二进制日志文

件

8 的绝对路径。show binary logs;可以查看日志文件清单列表

2.查看二进制日志文件的内容

由于二进制日志文件的内容以事件的方式进行存储，数据库管理人员可以使用 MySQL

命令“show binlog events”方便地查看二进制日志文件的内容。

（1）语法结构：

1 ‐‐ 语法结构：

2 SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

3 ‐‐ 语法解释：

4 FROM pos：pos 指定查看二进制日志文件的位置起始点（起始位置，），可以小于第一行

的

5 起始位置，或者值已经优点位置点，如图下文日志事件截图。

6 LIMIT [offset,]：offset指定查看二进制日志文件的位置起始点（起始行）

7 【从设置的pos开始算偏移量】，不写从第0行开始，

8 不指定就是将该二进制日志文件的末尾位置点作为终止点。

9 row_count：查询总条数。

（2）使用

1 show binlog events; #只查看第一个binlog文件的内容

2 show binlog events in 'mysql‐bin.000002';#查看指定binlog文件的内容

3 show binary logs; 或show master logs; #获取binlog日志文件列表

4 show master status; #查看当前正在写入的binlog文件及偏移位置

（3）常用命令

1 ‐‐ 1、先查看日志是否开启，未开启则开启

2 show variables like 'log_bin';

3 ‐‐ 2、查看有哪些日志文件

4 show binary logs;

5 ‐‐ 3、查看当前正在写入的日志文件

6 show master status;

7 ‐‐ 4、查看某个日志文件里的信息

8 show binlog events in 'mysqllog.000001';

9 ‐‐ 5、查看指定位置的日志事件

10 show binlog events in 'mysqllog.000001' from 292 limit 2, 2;

11 ‐‐ 6、重新产生新日志文件

12 flush logs;

3.使用二进制日志文件恢复数据库

工具mysqlbinlog ，在命令窗口下输入该工具命令当数据库遭到破坏时，数据库管理人员可以借助 mysqlbinlog 工具，读取二进 制日志文件中指定的日志内容，并将该日志内容复制、粘贴到 MySQL 客户机中直接 运行，将数据库恢复到正确的状态。

1 读取二进制日志文件内容：mysqlbinlog ‐‐no‐defaults D:\db_data\mysqllog.00000 1;

也可以使用管道操作符“|”，直接将指定的二进制日志内容导入到MySQL 客户 机中运行，管道操作符“|”的使用方法如下：

1 mysqlbinlog 日志文件 | mysql ‐u 用户 ‐p

（1）基于操作点方式恢复数据库

1 ‐‐ 找到操作点

2 show binlog events in 'mysqllog.000001';

3 ‐‐ 使用mysqlbinlog指定恢复的起始和结束点

4 mysqlbinlog ‐‐start‐position=2492 ‐‐stop‐position=2724

5 "D:\db_data\mysqllog.000001" ‐d cater | mysql ‐u root ‐p 具体密码;

（2）基于时间点方式恢复数据库

1 ‐‐1、 将二进制日志输入到txt文件，变成我们看得懂的形式

2 mysqlbinlog ‐‐no‐defaults ‐‐base64‐output=decode‐rows

3 D:\db_data\mysqllog.000001 > D:\db_data\000010.txt

4 ‐‐ 2、打开txt文件找到要恢复操作的时间点

5 mysqlbinlog ‐‐no‐defaults "D:\db_data\mysqllog.000001"

6 ‐‐stop‐datetime="2020‐12‐27 11:16:35" | mysql ‐uroot ‐p123456

（3）使用例子

1 ‐‐ 使用二进制日志文件还原误操作的数据

2 ‐‐ 1、先查看日志是否开启，未开启则开启

3 show variables like 'log_bin';

4 ‐‐ 2、查看有哪些日志文件

5 show binary logs;

6 ‐‐ 3、查看当前正在写入的日志文件，确定文件名

7 show master status;

8 ‐‐ 4、刷新日志，产生新的日志文件，方便恢复时候查看

9 flush logs; ‐‐ 刷新后到日志目录下确认

10 ‐‐ 5、插入数据（一个订单，及订单下的两条明细）

11 INSERT INTO

`t_order`(STATUS,MONEY,REMARKS,CASHIER_ID,ORDER_TIME,COOK_ID,

12 COOK_TIME)

13 VALUES ('1', '0', '焖肉细米线，不要味精', 10,NOW(),17,NOW());

14 INSERT INTO

`t_orderdetail`(ORDER_ID,FOOD_ID,FOOD_NUM,TASTE,COUNT,REMARK)15 VALUES ('19', '10', '小碗', '焖肉',1,'不要腌菜');

16 INSERT INTO

`t_orderdetail`(ORDER_ID,FOOD_ID,FOOD_NUM,TASTE,COUNT,REMARK)

17 VALUES ('19', '14', '小碗', '杂酱',2,'不要腌菜');

18 ‐‐ 6、手动删除插入的第一条订单明细（模拟误操作）

19 ‐‐ 7、查看当前日志文件信息

20 show binlog events in 'mysqllog.000002';

21 ‐‐ 8、挨条查看，找到三次插入和一次删除操作的日志，

22 ‐‐确认删除掉这条数据对应的插入操作日志的起始点（如下他截图）

23 起始是480，结束是753

24 ‐‐ 9、（基于操作点方式）还原刚才误删的数据（实质为执行这条数据对应插入日志的SQ

L）

25 ‐‐ 在命令提示符窗口下，进入mysql的bin目录后执行如下命令：

26 mysqlbinlog ‐‐no‐defaults ‐‐start‐position=480 ‐‐stop‐position=753

27 "D:\db_data\mysqllog.000002" ‐d cater | mysql ‐u root ‐p

28 ‐‐ 10、（基于时间点方式）还原刚才误删的数据（实质为执行这条数据对应插入日志的SQ

L）

29 ‐‐ 在命令提示符窗口下，进入mysql的bin目录后执行如下两个命令：

30 ‐‐ 1》先把日志文件转为看得懂的文件

31 mysqlbinlog ‐‐no‐defaults ‐‐base64‐output=decode‐rows

32 D:\db_data\mysqllog.000003 > D:\db_data\00003.txt

33 ‐‐ 2》从转出的00003.txt文件找到要还原的操作的起始和结束时间点后，执行如下命

令：

34 mysqlbinlog ‐‐no‐defaults

35 ‐‐start‐datetime="2020‐12‐27 15:25:31"

36 ‐‐stop‐datetime="2020‐12‐27 15:25:34" "D:\db_data\mysqllog.000003"

37 | mysql ‐uroot ‐p

注意：如果要还原的是误修改的数据，还不能只是简单的执行它的插入日志，可能要先

删了这条数据，再从插入开始依次还原相关操作。

二、MySQL 备份和还原

1.数据备份的意义

（1）误区以为复制就是备份（backup）

以硬件备份代替备份

只把数据文件作为备份的目标

2.逻辑备份

（1）工具：MySQL 转储数据库时常用的自带工具 mysqldump备份数据库。

1 mysqldump ‐uroot ‐p ‐l ‐F [ ‐‐database ] choose > d:\db_backup\choose.sql

2 数据库备份期间，使用“‐l”参数为 choose 数据库施加读锁，避免备份期间数据发生变

化。

3 数据库备份期间，如果开启的是二进制日志，则使用“‐F”参数关闭所有的二进制日志文件，

4 [ 选项 database 可以省略。]。

（2）几种备份方式：

1 1》备份所有数据库：打开 cmd 窗口，执行以下 mysqldump 命令

2 （先建好目录和文件，没有分号结束）：

3 ‐‐ 备份所有数据库

4 mysqldump ‐uroot ‐p ‐‐all‐databases > d:\db_backup\alldb.sql

5 2》备份部分数据库

6 ‐‐ 备份cater数据库

7 mysqldump ‐uroot ‐p ‐‐databases cater > d:\db_data\cater.sql

8 ‐‐ 备份某几个（cater,card）数据库

9 mysqldump ‐uroot ‐p ‐‐databases cater card> d:\db_data\cater2.sql

10 3》备份某几个数据表

11 ‐‐ 备份cater数据库中的t_order表和t_food表

12 mysqldump ‐uroot ‐p cater t_order t_food> d:\db_data\cater.sql

（3）备份数据库时的一致性问题

同一时刻取出所有数据：

对于事务支持的存储引擎，如 Innodb 或 BDB 等，可以通过控制将整个备份过程 保持在同一个事务中，使用“--single-transaction”选项。

1 mysqldump ‐uroot ‐p ‐‐single‐transaction cater > d:\db_data\cater.sql

数据库中的数据处于静止状态：

通过以下锁表参数，可以保证数据库中的数据处于静止状态。

--lock-tables 每次锁定一个数据库的表，此参 数默认为 true。

--lock-all-tables 一次锁定所有的表，适用于 dump 的表分别处于各个不同的数据库中的情况。

3.使用 source 命令还原数据库（1）概述：

对于已经备份的包含 create 语句、insert 语句的文本文件，可以使用 MySQL 的 source 命令将数据库的备份文件导入到数据库中。

（2）语法：

1 source filename.sql

2 filename.sql 为 mysqldump 工具创建的包含 DDL 语句和 DML 语句的数据库文件(含路

径))。

3 注意，执行时不需要指定数据库名。

（3）注意事项：

执行 source 命令前必须使用 use 语句选择好数据库，不然会出现“error 1046(3D000):no database selected”的错误。

只能在 cmd 界面执行，不能在 mysql 工具里面执行 source 命令，否则会报 错。因为 cmd 是直接调用 mysql.exe 来执行命令的，而这些诸如 Navicat for

MySQL 的编辑工具，只是利用 MySQL Connector 连接 MySQL 来管理 MySQL，并不是直接调用 mysql.exe。

（4）使用步骤及例子：

1 ‐‐ 上文备份某几个表，然后创建一个新的数据库来还原刚才备份的表

2 ‐‐ 1、备份cater数据库中的t_order表和t_food表，在命令窗口下进入mysql的bin目录执

行：

3 mysqldump ‐uroot ‐p cater t_order t_food> d:\db_data\cater.sql

4 ‐‐ 2、navicate下创建一个数据库cater2

5 ‐‐ （准备还原）

6 ‐‐ 3、用mysql命令先登录数据库

7 mysql ‐u root ‐p

8 ‐‐ 2、如果备份文件里没有备份数据库的，需要指定数据库

9 use 数据库名;

10 ‐‐ 3、执行还原文件

11 source d:\db_data\cater.sql

4.逻辑备份还原综合示例

（1）需求场景

1. 某大型数据库运行期间，开启了二进制日志。

2. 凌晨 1:00 时，数据库处于正常状态。

3. 从凌晨 1:00 开始，数据库管理员开始备份数据库，直到凌晨 2:00 为止，数据库

备份成功。

4. 数据库一直正常运行到凌晨 3:00，然后数据库崩溃，数据库无法正常访问。数据

库管理员恢复数据库，直到凌晨 4:00，数据库恢复成功

（2）处理思路：

1 （1）凌晨 1:00 数据库管理员使用 mysqldump 命令备份数据库（以 choose 数据库为

例）

2 mysqldump ‐uroot ‐p ‐l ‐F [ ‐‐database ] choose > d:\db_backup\choose.sql

3 （2）凌晨 2:00 数据库备份成功。在凌晨 2:00 到凌晨 3:00 数据库正常运行期间，

4 数据库用户对 choose 数据库中的表进行的 DML 操作将记录在最新的二进制日志文件中

5 （3）凌晨 3:00 数据库突然崩溃，凌晨 4:00 才发现数据库无法正常访问，此时需要

6 恢复数据库。使用 source 命令将choose 数据库还原至凌晨 1:00 时的 SQL 语句为

7 source d:\db_backup\choose.sql。该步骤实现了 choose 数据库的完全备份。

8 （4）使用 mysqlbinlog 运行最新的二进制日志文件 binary_log.000011 中所有的

9 SQL 语句，则将凌晨 2:00 至凌晨 3:00 之间对数据库 choose 所进行的正常 DML 语句

10 执行恢复操作，恢复语句如下：

11 mysqlbinlog ‐‐no‐defaults D:\db_data\mysqllog\binary_log.000011

12 | mysql ‐uroot ‐p

5.MySQL 存储引擎和物理备份

数据库中的数据以文件的方式组织在一起，物理备份最简单的实现方法就是对这 些文件进行复制、粘贴。

如果在物理备份期间，MySQL 服务实例没有关闭，备份过的数据可能已经发生 了变化，此时需要借助热备份工具实现物理备份。

InnoDB 和 MyISAM 是许多人在使用 MySQL 时最常用的两个表类型

MyISAM 类型不支持事务处理等高级处理，而 InnoDB 类型支持。MyISAM 类 型的表强调的是性能，其执行速度比 InnoDB 类型更快

物理备份的 3 种方式：冷备份、温备份、热备份

三、MySQL 复制

1.复制的概念

MySQL 复制（replication）是将一台 MySQL 主服务器（master）的数据更改异步地

复制到另外一台或多台从服务器（slave）的过程。复制的内容可以是记录的更新，也可以

是更新语句（如 insert 语句、update 语句、delete 语句）。

所谓异步，是指从服务器的数据更新与主服务器的数据更新存在一定时差（虽然延时非 常小）

复制操作是异步进行的，即在进行复制时，所有对复制中的表的更新必须在主服务器上 进行，从服务器不需要持续地保持连接来接受主服务器上的数据，以避免用户对主服务器上 的表进行的更新与对从服务器上的表所进行的更新之间相冲突。

2.复制的实现

MySQL 支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可 以作为其他从服务器的主服务器。如果 MySQL 主服务器的访问量比较大，通过复制技术，可使从服务器来响应用户的查询操作，从而降低了主服务器的访问压力，同时从服务器也可以作为主服务器 的备份服务器。

一般而言，数据库复制技术可以从以下几个方面改善分布式数据库集群系统的功 能和性能：可用性、性能、可扩展性、备份

3.MySQL 主从复制步骤

（1）master 主服务器配置

1 ‐‐1、 配置主服务器数据库配置文件：修改my.ini 配置文件，一般情况下该文件位于 MyS

QL

2 主安装目录下，具体的配置内容如下：

3 server_id=1。server_id 属性值为数据库 ID，此 ID 唯一不能重复，否则主从复制会发

生错误

4 log_bin= binary_log。log_bin 属性值为 MySQL 二进制日志文件名。此项为必填项，

否则

5 不能实现主从复制；如该项值为空，则以计算机名字加编号来命名二进制日志文件。

6 binlog_do_db=lingju。binlog_do_db 属性值为需要同步的数据库名（本例将主从同步

7 lingju 数据库）。如果需要同步另外的数据库，则重复设置 binlog_do_db。如果未设

置

8 binlog_do_db，则默认同步所有的数据库。

9 binlog_ignore_db=mysql。binlog_ignore_db 属性值为不需要同步的数据库名。

10

11 ‐‐ 2、重启主服务器数据库

12 停止 MySQL 服务的命令是“net stop mysql”

13 启动 MySQL 服务的命令是“net start mysql”

14 ‐‐ 3、在主服务器数据库添加用于同步的账号

15 为了实现主从复制，要求同步账户拥有 replication slave 权限

16 具体命令：

17 grant replication slave on *.* to shang@'192.168.6.87' identified by 'ab

c123';

18 （扩展‐‐‐授予账户所有的权限）

19 grant all privileges on *.* to shang@'192.168.6.87 ' identified by 'abc1

23'

20 with grant option;

21 ‐‐ 4、显示主服务器最新状态信息

22 执行“show master status;”命令，可以查看 master 数据库当前正在使用的二进制日

志

23 文件及当前执行二进制日志文件的位置。

24 其中 file 值为当前使用的二进制日志文件名，position 值为当前执行的二进制日志文

件  的位置。

（2）slave从服务器配置

1 ‐‐ 1、配置从服务器数据库配置文件

2 只需要配置从服务器数据库配置文件的 server_id 即可，需要注意的是该 server_id

的值

3 不能与主服务器数据库配置文件的 server_id 的值相同。

4 ‐‐ 2、停止从服务器的 slave 线程

5 使用命令“stop slave;”可以停止从服务器的 slave 线程。

6 ‐‐ 3、设置从服务器实现主从复制

7 change master to master_host='192.168.6.82',master_user='shang',

8 master_password='abc123', master_log_file='binary_log.000007',

9 master_log_pos=120;

10 这里的 master_log_file、master_log_pos 的值要与 master 主服务器的值一致，

11 否则将无法实现主从复制（同步）。

12 ‐‐ 4、开启从服务器的 slave 线程

13 使用命令“start slave;”可以启动从服务器的 slave 线程。

14 ‐‐ 5、显示从服务器最新状态信息

15 执行“show slave status \G;”命令，用于显示从服务器的最新状态信息。

16 ‐‐ 6、测试主从复制的正确性

17 在主服务器上执行一个更新操作（insert、update 和 delete），观察是否在从服务器

上

18 同步执行更新。

四、MySQL 查询优化

1.数据库查询优化介绍

（1）存在问题：

查询时间长、响应速度慢，甚至查询结果不够准确等现象。

（2）原因：

一是硬件设备（如CPU、磁盘）的存取速度跟不上，内存容量不够大；

另一个很重要的原因则是数据查询方法不适当，亦或没有进行数据查询优化。

（3）查询优化方法

查询优化有许多方法，按照优化的层次一般可以分为代数优化和物理优化。

代数优化是指关系代数表达式的优化，即按照一定的规则，改变代数表达式中操 作的次序和组合，使查询执行更高效；

物理优化则是指存取路径和底层操作算法的选择。选择的依据可以是基于规则 的，也可以是基于代价的，还可以是基于语义的。

2.基于索引的优化优化规则：

（1）前导模糊查询不能使用索引；

（2）union、in、or 都能够命中索引，建议使用 in；

（3）负向条件查询不能使用索引，可以优化为 in 查询；

（4）满足联合索引最左前缀原则；

（5）使用范围列查询可以使用索引；

（6）把计算放到业务层而不是数据库层；

（7）尽量避免强制类型转换，因为这将导致全表扫描；

（8）更新十分频繁、数据区分度不高的字段上不宜建立索引；

（9）被查询列要被所建的索引覆盖；

（10）如果有 order by、group by 的场景，请注意利用索引的有序性；

（11）业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一

索引；

（12）单索引字段数不允许超过 5 个。

注意：在使用索引字段作为条件时，如果该索引是组合索引，那么必须使用该 索引中的第一个字段作为条件，才能保证系统使用该索引，否则该索引将不会被 使用，并且应尽可能地让字段顺序与索引顺序一致。

3.where 子句优化

（1）避免在 where 子句中对字段进行 null 值判断

在 where 子句中对字段进行 null 值判断，这将导致数据库引擎放弃 使用索引而进行全表扫描，而全表扫描会耗费数据库资源，延长检索时间 可以设置默认值 0，这样可以确保表中没有 null 值

（2）避免在 where 子句中使用 != 或 <> 操作符

数据库引擎将放弃使用索引而进行全表扫描

（3）慎用 in 和 not in

会使系统无法使用索引，而只能直接搜索表中的数据。如果 in 范围内的值是连 续的，可以用“between... and”替换 in 如果必须使用 in，则应将 in 后面值的列表中可能出现最频繁的值放在最前面， 出现得最少的放在最后面，这样可以减少判断的次数

（4）避免在 where 子句中对字段进行表达式操作数据库引擎将放弃使用索引而进行全表扫描

（5）避免在 where 子句的“=”左边进行函数运算、算术运算或其他表达式运算

（6）尽量使用 exists

4.子查询性能优化

（1）尽量使用连接查询代替子查询 连接查询中 join 子句之所以更有效率一些，是因为被嵌套的内层子查询需在内存中创建临 时表，较为费时，而使用 join 子句则不需要创建临时表

（2）not in、not exists 子查询可以改用 left join 代替

（3）in、exists 子查询可以用 inner join 代替

如果子查询结果集没有重复记录，in、exists 子查询可以用 inner join 代替

（4）in 子查询用 exists 代替

（5）使用 left join 或 exists 判断是否存在记录

不要用含有 count(*) 的子查询判断是否存在记录，最好使用 left join 或 exists

（6）尽量避免嵌套（nest）子查询

查询嵌套层次越多，效率越低，因此应当尽量避免嵌套子查询

（7）过滤（filter）记录行

子查询中尽量过滤出尽可能多的记录行

5.其他 SQL 语句优化

（1）查询时按需取材

不要返回不需要的行、列，即避免“select * from 表”。

（2）避免或简化排序

应当避免或简化对大型表进行重复的排序。

（3）尽量在 group by 子句、having 子句之前剔除多余的行

（4）消除对大型表数据的顺序存取

在嵌套查询中，对表的顺序存取可能对查询效率产生致命的影响。

（5）尽量用 distinct（唯一），不要用 group by

如果 group by 的目的不包括计算，只是分组，那么使用 distinct 速度更快。

（6）避免困难的正规表达式

like 关键字支持通配符匹配，技术上称为正规表达式，但这种匹配特别耗费时间

（7）尽量使用存储过程

存储过程将数据的处理工作放在服务器上，减少了网络的开销

（8）尽量少用视图，特别是嵌套视图

由于对视图操作比直接对表操作慢，所以在执行逻辑复杂的查询时，可以考虑使用存储过程

来代替视图

（9）SQL 语句中的字段值要与数据类型精确匹配