MySQL数据库应用-CSDN博客

本文链接：https://blog.csdn.net/ArrayAda/article/details/140988581

一、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 同步执行更新。