MySQL日常维护工具------备份

MySQL日常维护工具-备份

一、MySQL字符集

（一）字符集介绍

字符(Character)是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。计算机要准确的处理各种字符集文字，就需要进行字符编码，以便计算机能够识别和存储各种文字。

字符集补充说明：

ASCII字符集：American Standard Code for Information Interchange，美国信息互换标准编码。7位（bits）表示一个字符，共128字符，字符值从0到127。包括控制字符：回车键、退格、换行键等。可显示字符：英文大小写字符、阿拉伯数字和西文符号。

ASCII扩展字符集：它是从ASCII字符集扩充出来的，扩充后的符号增加了表格符号、计算符号、希腊字母和特殊的拉丁符号。ASCII扩展字符集使用8位（bits）表示一个字符，共256字符。

Unicode字符集编码是Universal Multiple-Octet Coded Character Set 通用多八位编码字符集的简称，是由一个名为 Unicode 学术学会(Unicode Consortium)的机构制订的字符编码系统，支持现今世界各种不同语言的书面文本的交换、处理及显示。

UTF-8是Unicode的其中一个使用方式。 UTF是 Unicode Tranformation Format，即把Unicode转作某种格式的意思。UTF-8使用可变长度字节来储存 Unicode字符，又称万国码。例如ASCII字母继续使用1字节储存，重音文字、希腊字母或西里尔字母等使用2字节来储存，而常用的汉字就要使用3字节。辅助平面字符则使用4字节。

MySQL数据库字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念，其中字符集用来定义MySQL数据字符串的存储方式，而校对规则定义字符串比较的方式。

（二）查看当前MySQL支持的字符集

MySQL可以支持多种字符集，同一台服务器，库或表的不同字段都可以指定不同的字符集

查看所有的字符集

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "show character set \G;"

#查看所有的字符集

查看常用的字符集：

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "show character set\G;" |egrep "gbk|utf8|latin1"|awk '{print $0}'

查看字符集的校对规则：

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "show collation;"

（三）查看MySQL当前的字符集设置情况

[root@localhost ~]# MySQL -uroot -pAbcd1234

MySQL> show variables like 'character_set%';

名词解释：

character_set_client：客户端请求数据的字符集

character_set_connection：客户机/服务器连接的字符集

character_set_database：默认数据库的字符集。

character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的

character_set_results：结果集，返回给客户端的字符集

character_set_server：数据库服务器的默认字符集

character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

查看当前数据库的校对规则:

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e " show variables like 'collation%';"

默认情况下字符集选择规则：

(1)编译MySQL 时，指定了一个默认的字符集（-DDEFAULT_CHARSET=utf8），如果未指定默认是latin1;

(2)安装MySQL 后，可以在配置文件 (my.cnf) 中指定服务器的默认字符集（character-set-server=utf8），如果没指定，这个值继承自编译时指定的;

配置文件中指定服务器的默认字符集会影响参数：character_set_server 和 character_set_database

(3)启动MySQLd 时，可以在命令行参数中指定一个默认的字符集，如果没指定，这个值继承自配置文件中的配置,此时character-set-server被设定为这个默认的字符集;

例如：./MySQLd --character-set-server=utf8 &

影响参数：character_set_server 和 character_set_database

(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character-set-server;

例如：MySQL> create database databasename default character set=utf8;

(5)当选定了一个数据库时，character_set_database被设定为这个数据库默认的字符集;

例如：MySQL> set character_set_database=utf8;

(6)在这个数据库里创建一张表时，表默认的字符集被设定为character_set_database，也就是这个数据库默认的字符集;

(7)当在表内设置列时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;

（四）实战，迁移数据

背景：公司业务数据book,由于之前建表没注意字符集的问题，导致之前写入的数据出现乱码。现在要将之前的数据和现在数据的字符集一致，不出现乱码情况，将字符集为latin1已有记录的数据转成utf8，并且已经存在的记录不乱码。

步骤

1：建库及建表的语句导出，修改为utf8

2：导出之前所有的数据

3：修改MySQL服务端和客户端编码为utf8

4：删除原有的库表及数据

5：导入新的建库及建表语句

6：导入之前的数据

（1）准备实验环境

1、确保你的数据库服务器的默认字符集是utf8

修改/etc/my.cnf配置文件中character-set-server=utf8，重启MySQL

[root@localhost ~]# vim /etc/my.cnf

character-set-server=utf8

[root@localhost ~]# systemctl restart MySQLd

2、准备默认字符集是latin1的表文件

由于我们books表的字符集是utf8，删除book数据库，重新创建book数据库，导入字符集为latin1的sql文件book.sql

上传book<book_latin1.sql文件

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "drop database book;"

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "create database book;"

[root@localhost ~]# MySQL -uroot -pAbcd1234 book<book_latin1.sql

查看books表的字符集

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "show create table book.books;"

查看表内容

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e "select * from book.books;"

除了英文和时间，中文都是乱码

（2）导出数据表结构

[root@localhost ~]# MySQLdump -uroot -pAbcd1234 --default-character-set=latin1 -d book> booktable.sql

（3）编辑booktable.sql 将latin1修改成utf8

[root@localhost ~]# vim booktable.sql

（4）确保数据库不再更新，导出所有数据

[root@localhost ~]# MySQLdump -uroot -pAbcd1234 --quick --no-create-info --extended-insert --default-character-set=latin1 book>bookdata.sql

参数说明：

--quick: 不把select出来的结果放在buffer中，而是直接dump到标准输出。该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中，减少内存消耗。

--no-create-info:不要创建create table语句

--extended-insert:使用包括几个values列表的多行insert语法，这样文件更小，IO也小，导入数据时会非常快

--default-character-set=latin1:按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

（5）打开bookdata.sql 将SET NAME latin1 修改成SET NAME utf8

[root@localhost ~]# vim bookdata.sql

（6）新建book2库

[root@localhost ~]# MySQL -e "create database book2 default charset utf8;" -uroot -pAbcd1234

（7）建立表，导入我们之前导出的表结构

[root@localhost ~]# MySQL -uroot -pAbcd1234 book2 <booktable.sql

（8）导入数据

[root@localhost ~]# MySQL -uroot -pAbcd1234 book2 <bookdata.sql

（9）查看结果

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e"show create table book2.books;"

（10）查看表内容

[root@localhost ~]# MySQL -uroot -pAbcd1234 -e"select * from book2.books;"

二、MySQL备份恢复

（一）备份的目的：

做灾难恢复：对损坏的数据进行恢复和还原

需求改变：因需求改变而需要把数据还原到改变以前

测试：测试新功能是否可用

（二）备份需要考虑的问题：

可以容忍丢失多长时间的数据；

恢复数据要在多长时间内完；

恢复的时候是否需要持续提供服务；

恢复的对象，是整个库，多个表，还是单个库，单个表。

1、MySQL备份的类型

（1）按照备份时对数据库的影响范围分为：

Hot backup(热备)

Cold Backup（冷备）

Warm Backup（温备）

Hot backup：指在数据库运行中直接备份，对正在运行的数据库没有任何影响。（Online Backup），官方手册称为在线备份。（备份的同时，业务不受影响）

Cold Backup：指在数据库停止的情况下进行备份(Offline Backup) ，官方手册称为离线备份。（需要关MySQL服务，读写请求均不允许状态下进行）

Warm Backup：备份同样在数据库运行时进行，但仅支持读请求，不允许写请求；例如，加一个读锁以保证备份数据的一致性。（服务在线，但仅支持读请求，不允许写请求）

2、按照备份后文件内容：

逻辑备份

指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句，或者是表内的实际数据，如MySQLdump和SELECT * INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长

物理文件备份

对数据库物理文件（如数据文件、日志文件等）的备份，数据库既可以处于运行状态（MySQLhotcopy 、ibbackup、xtrabackup这类工具），也可以处于停止状态（如cp、tar等），恢复时间较短。

3、按照备份数据库的内容来分，又可以分为：

完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和存储空间，所以，做一次完全备份的周期要长些。完全是其他类型的基础。

差异备份：在上一次完全备份基础上，对更新的数据进行备份。因为只备份数据库部分的内容。它比完全备份小，因为只包含自上次完全备份以来所改变的数据。它的优点是存储和恢复速度快。

增量备份：在上次备份的基础上，对更新的数据进行备份

日志备份：二进制日志备份。

4、建议的备份策略：

完全+增量+二进制日志

完全+差异+二进制日志

（三）逻辑备份工具MySQLdump

MySQLdump是MySQL数据库自带的一款命令行工具，MySQLdump属于单线程，功能是非常强大的，不仅常被用于执行数据备份任务，甚至还可以用于数据迁移。

MySQLdump是MySQL自带的逻辑备份工具，它的备份原理是，通过协议连接到MySQL数据库，将数据转换成标准SQL语句（一堆 CREATE , DROP ，INSERT等语句）；但我们需要还原时，只要执行这些语句即可将对应的数据还原。

优点：

备份粒度相当灵活，既可以针对整个MySQL服务，也可以只备份某个或者某几个DB，或者还可以指定只备份某个或者某几个表对象，甚至可以实现只备份表中某些符合条件的记录（-w, --where: 只导出符合条件的记录）。

缺点：

1、当数据是浮点数时，会出现精度丢失。

2、MySQLdump的备份过程属于逻辑备份，备份速度、恢复速度与物理备份工具相比较慢，而且MySQLdump备份的过程是串行化的，不会并行的进行备份，当数据量较大时，一般不会使用MySQLdump进行备份，因为效率较低。

MySQLdump对innodb存储引擎支持热备，innodb支持事务，我们可以基于事务通过MySQLdump对数据库进行热备（--single-transaction选项）。

MySQLdump对myisam存储引擎只支持温备，通过MySQLdump对使用myisam存储引擎的表进行备份时，最多只能实现温备，因为在备份时会对备份的表请求一个读锁，当备份完成后，锁会被释放。

1、导出数据：

语法： MySQLdump [options] [db_name [tbl_name ...]]>导出的文件名.sql

我们说过MySQLdump是一个客户端命令，所以，就像使用MySQL命令连接数据库一样，我们需要指定连接的用户名，需要连接的数据库服务ip，以及使用-p选提示我们输入密码，这些用法都与我们的MySQL命令一致。

常用参数：

-?, --help: 显示帮助信息，英文的；

-u, --user: 指定连接的用户名；

-p, --password: 指定用户的密码，可以交互输入密码；

-S , --socket: 指定socket文件连接，本地登录才会使用。

-h, --host: 指定连接的服务器名称或者IP。

-P, --port=: 连接数据库监听的端口。

--default-character-set: 设置字符集，默认是UTF8。

-A,--all-databases: 导出所有数据库。不过默认情况下是不会导出information_schema库。

-B, --databases: 导出指定的某个/或者某几个数据库，参数后面所有名字都被看作数据库名，用空格隔开，包含CREATE DATABASE创建库的语句。

--tables: 导出指定表对象，参数格式为“库名表名”，默认该参数将覆盖-B参数。

-w, --where: 只导出符合条件的记录。

-l, --lock-tables: 默认参数，锁定读取的表对象，想导出一致性备份的话最好使用该参数，但会导致无法对表执行写入操作。

--single-transaction:

该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于innoDB存储引擎。

在InnoDB导出时会建立一致性快照，在保证导出数据的一致性前提下，又不会堵塞其他会话的读写操作。指定这个参数后，其他连接不能执行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE这类语句，事务的隔离级别无法控制DDL语句。本选项和--lock-tables 选项是互斥的，使用参数--single-transaction会自动关闭该选项。

-d, --no-data: 只导出表结构，不导出表数据。

-t, --no-create-info: 只导出数据，而不添加CREATE TABLE 语句。

-f, --force: 即使遇到SQL错误，也继续执行。

-F, --flush-logs: 在执行导出前先刷新二进制日志文件，一般来说，如果是全库导出，建议先刷新日志文件，否则就不用了。

-x, --lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局锁定，并且自动关闭--single-transaction 和--lock-tables 选项。这个参数副作用比较大，这是全库锁定，备份执行过程中，该库无法进行读写操作，不是所有业务场景都能接受的。请慎用。

-n, --no-create-db: 不生成建库的语句CREATE DATABASE … IF EXISTS，即使指定---all-databases或--databases这类参数。

--triggers: 导出表的触发器脚本，默认就是启用状态。使用–skip-triggers禁用它。

-R, --outines: 导出存储过程以及自定义函数。

A:导出所有数据库

#MySQLdump -uroot -pAbcd1234 -A >all.sql

或

#MySQLdump -uroot -pAbcd1234 --all-databases >all2.sql

参数-A代表所有，等同于—all-databases

B:导出某个数据库

MySQLdump -u 用户名 -p 数据库名 > 导出的文件名.sql

#MySQLdump -uroot -pAbcd1234 book >book.sql

#vim book.sql

C:导出单张表

#MySQLdump -uroot -pAbcd1234 book books >books.sql #导出book库books表

D:导出库的表结构

#MySQLdump -uroot -pAbcd1234 -d book>booktable.sql #只导出book库的表结构

E：只导出数据

#MySQLdump -uroot -pAbcd1234 -t book>bookdata.sql #只导出book库中的数据

F:导出数据库，并自动生成库的创建语句

#MySQLdump -uroot -pAbcd1234 -B book2 >book2.sql

#MySQL -uroot -pAbcd1234 < book2.sql 导入不用指定数据名

2、导入数据：

A：导入所有数据库

#MySQL -uroot -pAbcd1234 <all.sql

B：导入数据库

#MySQL -uroot -pAbcd1234 book <book.sql #如果导入时，没有对应的数据库，需要你手动创建一下：MySQL> create database book;

使用source导入

MySQL> create database book;

MySQL> use book;

MySQL> source /root/book.sql

c:导入表

MySQL> drop table books;

MySQL> source /root/books.sql; ##导入表时，不需要重新，创建表。要先进到相应的数据库中

MySQL> select * from books;

D：导入表结构和数据

MySQL> create database book;

#MySQL -uroot -pAbcd1234 book<booktable.sql

#MySQL -uroot -pAbcd1234 book<bookdata.sql

3、二进制日志：Binary Log & Binary Log Index

（四）MySQL二进制日志概述：

MySQL的二进制日志记录着数据库的所有增、删、改等操作日志(前提是要在自己的服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用MySQLbinlog命令来查看。

Binlog的用途：

1：主从同步

2：恢复数据库

执行MySQL> show variables like 'log_bin%';查看binlog是否开启

Off表示MySQL当前binlog功能没有开启。

通过编辑my.cnf中的log-bin选项可以开启二进制日志，形式如下：

log-bin [=DIR/[filename]]

例如：log-bin=/data/MySQL/log/MySQL-bin

其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为filename.number，number的形式为000001、000002、……等。每次重启MySQL服务或运行MySQL> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

开启binary log功能：

修改/etc/my.cnf配置文件，添加如下内容：

log-bin=/data/MySQL/log/MySQL_bin

server-id=1

重启MySQL服务，使配置文件修改生效

[root@localhost log]# systemctl restart MySQLd

查看binlog文件

MySQL-bin.index 文件（binary log index）的功能是记录所有Binary Log 的绝对路径，保证MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log 文件。

用MySQL> show variables like 'log_bin%';查看bin-log是否开启，如图：

binlog 还有其他一些附加选项参数：

1、“max-binlog-size”设置binlog 的最大存储上限，一般设置为512M或1G,一般不能超过1G。当日志达到该上限时，MySQL 会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog 产生，一般都是因为在即将达到上限时，产生了一个较大的事务，为了保证事务安全，MySQL 不会将同一个事务分开记录到两个binlog 中。

2、“binlog-do-db=db_name”参数明确告诉MySQL，需要对某个（db_name）数据库记录binlog，如果有了“binlog-do-db=db_name”参数的显式指定，MySQL 会忽略针对其他数据库执行的sql query，而仅仅记录针对指定数据库执行的sql query。

3、“binlog-ignore-db=db_name”与“binlog-do-db=db_name”完全相反，它显式指定忽略某个（db_name）数据库的binlog 记录，当指定了这个参数之后，MySQL 会记录指定数据库以外所有的数据库的binlog。

4、binlog-cache-size ：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size，默认值32768。

例如：binlog-cache-size=4M

同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

5、binlog-format= {ROW|STATEMENT|MIXED}参数指定二进制日志的类型

概念解释：MySQL复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条修改数据的sql语句会记录到binlog中。优点是并不需要记录每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如LOAD_FILE()、UUID()、USER()、FOUND_ROWS()等函数的语句无法被复制)

② ROW模式（RBR）

不记录每条sql语句的信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由MySQL服务器自行判断。

其中基于行的定义格式数据量会大一些，但是可以保证数据的精确性。

查看二进制日志：

1、查看binlog日志文件列表：

MySQL> show binary logs;

2、查看当前使用的二进制文件及日志文件中事件当前位置：

3、删除所有的二进制日志：

MySQL>reset master

restet master：清空index文件中列出的所有二进制日志，并创建一个新的二进制日志文件，

4、重新开始一个新的日志文件：

使用flush logs生成新的二进制日志文件，用以保存之后的数据库操作语句的记录。

MySQL> flush logs;

5、查看binlog日志文件的内容：

二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是不能直接使用文件内容查看命令打开并查看二进制日志。

想查看到二进制日志文件中具体的内容并应于恢复场景还得借助MySQLbinlog这个工具。

语法格式： MySQLbinlog [options] log_file ...

输出内容会因日志文件的格式以及MySQLbinlog工具使用的选项不同而略不同。

MySQLbinlog的可用选项可参考man手册。

二进制日志文件的格式包含语句模式、行模式和混合模式（即由服务器决定在什么情况下记录什么类型的日志），基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。

为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用MySQLbinlog工具的-v（--verbose）选项，该选项会将事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，

例如：在数据库test_db的stu表中执行一下增、删、改操作

以上对数据的增、删、改会记录到当前的binlog日志文件中，可以在shell命令行中执行MySQLbinlog命令查看：

从上图可以看出二进制日志文件对增、删、改等数据操作的语句执行时间和位置进行了记录。

4、实战1：MySQLdump全库备份、binlog的增量备份与还原

（五）MySQLdump的全库备份、binlog的增量备份

1、MySQLdump的全库备份

完全备份是对整个数据库的备份，保存的是备份完成时刻的数据库。也是其他类型备份的基础。

完全备份的优点是备份与恢复操作简单，缺点是数据存在大量的重复，占用大量的存储空间，备份的时间长。

以test_db数据库为例，对test_db数据进行完全备份操作。

[root@localhost ~]# mkdir /opt/MySQL_backup

[root@localhost ~]# MySQLdump -uroot -pAbcd1234 --single-transaction --flush-logs -B test_db > /opt/MySQL_backup/test_db_$(date +%Y%m%d%H%M%S).sql

注：使用--flush-logs生成新的二进制日志文件，用以保存之后的数据库操作记录。

生成的编号是000014的二进制文件用于保存完全备份之后的数据库操作的记录。

2、增量备份

增量备份的优点是没有重复的数据，备份量不大，时间短。缺点也很明显，需要上次完全备份及完全备份之后所有的增量备份才能恢复，操作比较繁琐。

MySQL本身没有提供直接的增量备份方法，但可以通过MySQL的二进制日志间接实现增量备份。二进制日志对备份的意义如下：

l二进制日志保存了所有更新数据库的操作记录。

l二进制日志在启动MySQL服务后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。

l只需要定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

增量备份实战演练：

以test_db数据库为例，在前面对test_db数据进行完全备份基础上进行增量备份

在前面执行完MySQLdump全库备份后，新生成的编号是000014的二进制文件用于保存完全备份之后的数据库操作的记录。

向数据库插入新的数据，模拟数据的增加或者变更，并执行一次flush-logs操作分割日志文件，生成新的二进制日志文件。

MySQL> insert into stu values(102,'li1');

MySQL> insert into stu values(103,'li2');

MySQL> quit

[root@localhost ~]# MySQLadmin -uroot -pAbcd1234 flush-logs

查看二进制文件序列：

编号是000014的二进制文件中保存了刚才执行插入语句。再次向数据库插入1条数据，语句会保存在编号为000015的二进制文件中，然后执行分割二进制文件命令

MySQL> insert into stu values(104,'li3');

MySQL> exit

[root@localhost ~]# MySQLadmin -uroot -pAbcd1234 flush-logs

到现在为止，使用MySQLdump命令创建了test_db数据库的完全备份和编号是000014、000015的二进制文件的增量文件。把这两个二进制文件保存到安全的地方就可以了。

模拟对test_db数据库的数据破坏操作，例如，删除stu表

MySQL> drop table stu;

3、下面使用MySQL的完全备份和二进制日志的增量备份进行恢复

为了防止还原时产生大量的二进制日志，在还原时可临时关闭二进制日志后再还原：

MySQL> set sql_log_bin=0;

先使用MySQL命令进行完全备份的回复操作

# MySQL -uroot -pAbcd1234 test_db < /opt/MySQL_backup/test_db_20241015090530.sql

登录数据库查看完全备份的数据恢复结果

MySQL> use test_db;

MySQL> select * from stu;

+-----+-------+

| sid | NAME |

+-----+-------+

| 101 | aiden |

+-----+-------+

1 row in set (0.00 sec)

可以看到数据库还原到了完全备份时刻的状态，说明完全恢复是成功的，接着使用二进制文件进行恢复完全备份后的数据。

使用二进制文件进行增量恢复操作，需要注意的是恢复的顺序，要先恢复最先生成的二进制文件，然后依次执行（本例中000014、000015两个二进制文件按照先后顺序恢复）。

# MySQLbinlog /data/MySQL/log/MySQL-bin.000014 | MySQL -uroot -pAbcd1234

# MySQLbinlog /data/MySQL/log/MySQL-bin.000015 | MySQL -uroot -pAbcd1234

查看数据库内容，增量备份的新插入的两条数据找回来了，说明数据恢复成功。

MySQL> use test_db

MySQL> select * from stu;

+-----+-------+

| sid | NAME |

+-----+-------+

| 101 | aiden |

| 102 | li1 |

| 103 | li2 |

| 104 | li3 |

+-----+-------+

4 rows in set (0.00 sec)

数据库恢复完成后再打开二进制日志功能：

MySQL> set sql_log_bin=1;

5、实战2：利用二进制日志实现基于时间点或位置的恢复

利用二进制日志可实现基于时间点和位置的恢复。例如，由于误操作删除了一行数据，这时完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句。

先对test_db数据库执行MySQLdump做完全备份：

# MySQLdump -uroot -pAbcd1234 --single-transaction --flush-logs -B test_db > /opt/MySQL_backup/test_db_$(date +%Y%m%d%H%M%S).sql

生成的编号是000019的二进制文件用于保存完全备份之后的数据库操作的记录。

假定需要往数据库中插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据是不应该删除的。

MySQL> insert into stu values(120,'li20');

MySQL> delete from stu where sid=104;

MySQL> insert into stu values(121,'li21');

MySQL> select * from stu;

+-----+-------+

| sid | NAME |

+-----+-------+

| 101 | aiden |

| 102 | li1 |

| 103 | li2 |

| 120 | li120 |

| 121 | li121 |

+-----+-------+

5 rows in set (0.00 sec)

上面的模拟的误操作是删除了sid=104的记录

编号为000019的二进制文件中保存了正确的插入语句，同时也保存了不应该执行的删除语句。

使用MySQLbinlog命令可以查看binlog文件中误删除语句delete的开始时间/位置和结束时间/位置

[root@localhost ~]# MySQLbinlog -v /data/MySQL/log/MySQL-bin.000019

………省略内容

# at 490

#190710 14:16:21 server id 1 end_log_pos 567 CRC32 0xae536a14 Querythread_id=29exec_time=0error_code=0

SET TIMESTAMP=1562739381/*!*/;

BEGIN

/*!*/;

# at 567

#190710 14:16:21 server id 1 end_log_pos 619 CRC32 0x29035efe Table_map: `test_db`.`stu` mapped to number 988

# at 619

#190710 14:16:21 server id 1 end_log_pos 663 CRC32 0xe0915a8f Delete_rows: table id 988 flags: STMT_END_F

BINLOG '

tYIlXRMBAAAANAAAAGsCAAAAANwDAAAAAAMAB3Rlc3RfZGIAA3N0dQACAw8ClgAA/l4DKQ==

tYIlXSABAAAALAAAAJcCAAAAANwDAAAAAAEAAgAC//xoAAAAA2xpM49akeA=

'/*!*/;

### DELETE FROM `test_db`.`stu`

### WHERE

### @1=104

### @2='li3'

# at 663

#190710 14:16:21 server id 1 end_log_pos 694 CRC32 0xea437bf6 Xid = 9145

COMMIT/*!*/;

通过MySQLbinlog命令所显示的结果可以看到误操作delete的开始位置是at 490，开始时间是#190710 14:16:21，结束位置是at 663，结束时间是#190710 14:16:21。

下面演示基于位置的恢复方法。

[root@localhost ~]# MySQLadmin -uroot -pAbcd1234 flush-logs //分割日志

MySQL> set sql_log_bin=0; //临时关闭binlog功能

先使用MySQL命令进行完全备份的回复操作：

# MySQL -uroot -pAbcd1234 test_db < /opt/MySQL_backup/test_db_20241015090530.sql

登录数据库查看完全备份的数据恢复结果

MySQL> use test_db;

MySQL> select * from stu;

+-----+-------+

| sid | NAME |

+-----+-------+

| 101 | aiden |

| 102 | li1 |

| 103 | li2 |

| 104 | li3 |

+-----+-------+

4 rows in set (0.00 sec)

接下来使用二进制日志继续恢复数据

1、基于时间点的恢复，就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复，使用MySQLbinlog加上--stop-datetime选项，表示从二进制日志中读取指定时间之前的日志事件，后面误操作的语句不执行，--start-datetime选项表示从二进制日志中读取指定时间之后的日志事件。

需要注意的是，二进制文件中保存的日期格式需要调整为用”-”和”:”分隔。

使用基于时间点的恢复，可能会出现在一个时间点里同时存在正确的操作和存在错误操作。所以基于位置是一种更为精确的恢复方式。

2、基于位置的恢复

--start-position 从二进制日志中读取指定position 事件位置作为开始。

--stop-position 从二进制日志中读取指定position 事件位置作为事件截至。

上面的误操作delete开始位置是at 490，结束位置是at 663。

# MySQLbinlog --stop-position=490 /data/MySQL/log/MySQL-bin.000019 | MySQL -uroot -pAbcd1234

# MySQLbinlog --start-position=663 /data/MySQL/log/MySQL-bin.000019 | MySQL -uroot -pAbcd1234

查看恢复结果：

MySQL> select * from stu;

+-----+-------+

| sid | NAME |

+-----+-------+

| 101 | aiden |

| 102 | li1 |

| 103 | li2 |

| 104 | li3 |

| 120 | li120 |

| 121 | li121 |

+-----+-------+

6 rows in set (0.00 sec)

从上面显示可以看出数据恢复到正常状态。

MySQL> set sql_log_bin=1;

6、实战3：写个自动备份MySQL数据库shell脚本

生产环境中MySQL数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本

制定企业备份策略的思路：

制定企业备份策略要根据企业数据库的实际读写的频繁性与数据的重要性进行

l数据更新频繁，则应该进行较为频繁的备份

l数据较为重要，则在有适当更新时进行备份

l在数据库压力小的时间段进行备份。

MySQLdump备份方案：

周日凌晨1点全库备份

设置crontab任务，每天执行备份脚本

# crontab –e

#每个星期日凌晨1:00执行完全备份脚本

0 1 * * 0 /root/MySQLfullbackup.sh >/dev/null 2>&1

完全备份MySQLfullbackup.sh脚本内容：

[root@localhost ~]# cat MySQLfullbackup.sh

#!/bin/sh

# Name:MySQLFullBackup.sh

# 定义数据库目录

MySQLDir=/usr/local/MySQL

# 定义用于备份数据库的用户名和密码

user=root

userpwd=123456

dbname=test_db

# 定义备份目录

databackupdir=/opt/MySQLbackup

[ ! -d $databackupdir ] && mkdir $databackupdir

# 定义备份日志文件

logfile=$databackupdir/MySQLbackup.log

DATE=$(date +%Y%m%d)

cd $databackupdir

# 定义备份文件名

dumpfile=MySQL_${DATE}.sql

gzdumpfile=MySQL_${DATE}.sql.tar.gz

# 使用MySQLdump备份数据库，请根据具体情况设置参数

$MySQLDir/bin/MySQLdump -u$user -p$userpwd --single-transaction --flush-logs -B $dbname > $dumpfile

# 压缩备份文件

if [ $? -eq 0 ]; then

echo "--------------------------------------------------------" >> $logfile

tar czf $gzdumpfile $dumpfile >> $logfile 2>&1

echo "BackupFileName:$gzdumpfile" >> $logfile

echo "DataBase Backup Success!" >> $logfile

rm -f $dumpfile

else

echo "--------------------------------------------------------" >> $logfile

echo "$(date +%Y-%m-%d) DataBase Backup Fail!" >> $logfile

xtrabackup备份工具使用

xtrabackup简介

前面介绍MySQLdump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用MySQLdump备份就不太适合了。

这时就需要一种好用又高效的工具，目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。

Xtrabackup提供了命令行工具：

xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；

Xtrabackup是由percona提供的MySQL数据库备份工具，特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快。

官方链接地址：http://www.percona.com/software/percona-xtrabackup；可以下载源码编译安装，也可以下载适合的RPM包或使用yum进行安装或者下载二进制源码包。

xtrbackup 安装

注：MySQL8.4.0需安装XtraBackup8.4或更新版本

1、上传软件包

方法一：

网上下载地址

https://downloads.percona.com/downloads/Percona-XtraBackup-8.4/Percona-XtraBackup-8.4.0-1/binary/redhat/9/x86_64/percona-xtrabackup-84-8.4.0-1.1.el9.x86_64.rpm

[root@localhost ~]# ls

anaconda-ks.cfg percona-xtrabackup-84-8.4.0-1.1.el9.x86_64.rpm

[root@localhost ~]# yum -y localinstall percona-xtrabackup-84-8.4.0-1.1.el9.x86_64.rpm

方法二：将提前下载好percona-xtrabackup-84.zip上传到服务器上

[root@localhost ~]# ls

anaconda-ks.cfg percona-xtrabackup-84.zip

2、安装解决并解决依赖

配置好本地yum源（略）

[root@localhost ~]# unzip percona-xtrabackup-84.zip

[root@localhost ~]# cd percona-xtrabackup-84

[root@localhost percona-xtrabackup-84]# dnf -y install percona-xtrabackup-84-8.4.0-1.1.el9.x86_64.rpm

[root@localhost percona-xtrabackup-84]# cd