这里写目录标题

  • 数据库备份与恢复
    • 一.日志
      • 1.事务日志
      • 2.错误日志
      • 3.通用日志
      • 4.慢查询日志
      • 5.二进制日志备份
    • 二.备份
      • 1.备份类型
      • 2.完全备份
        • 1）物理冷备份
        • 2）mysqldump 备份与恢复
      • 3.增量备份

数据库备份与恢复

一.日志

MySQL 支持丰富的日志类型，如下：

  • 事务日志：transaction log

    事务日志的写入类型为”追加”，因此其操作为”顺序IO”；通常也被称为：预写式日志 write ahead logging事务日志文件： ib_logfile0， ib_logfile1

  • 错误日志 error log

  • 通用日志 general log

  • 慢查询日志 slow query log

  • 二进制日志 binary log

  • 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

语法：

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])routime_body proc_parameter : [IN|OUT|INOUT] parameter_name type

例子：

delimiter //CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)BEGIN SELECT * FROM students WHERE stuid = id;END//delimiter ;call selectById(2);

1.事务日志

事务日志：transaction log

  • redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
  • undo log：保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

Innodb事务日志相关配置：

show variables like '%innodb_log%';innodb_log_file_size 50331648 #每个日志文件大小字节innodb_log_files_in_group 2 #日志组成员个数innodb_log_group_home_dir ./ #事务文件路径ll -h /var/lib/mysql

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2select @@innodb_flush_log_at_trx_commit;#查看默认值1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
级别012
安全性较高最高最高
性能最高最差较高

高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快

2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

双1设置：

说明：

  • 设置为1，同时sync_binlog = 1表示最高级别的容错 （二进制日志）

  • innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB 10.2.6 后废弃）

演示：

[root@localhost ~]# mysql-uroot -p'自己的密码' 随便数据库  call sp_testlog;#生成一百万条数据修改参数：setglobal innodb_flush_log_at_trx_commit=2;select @@innodb_flush_log_at_trx_commit;call sp_testlog;

2.错误日志

错误日志

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;yum安装cat /var/log/mysqld.log

记录哪些警告信息至错误日志文件

#CentOS7 mariadb 5.5 默认值为1#CentOS8 mariadb 10.3 默认值为2log_warnings=0|1|2|3... #MySQL5.7之前log_error_verbosity=0|1|2|3... #MySQL8.0

例子：

SHOW GLOBAL VARIABLES LIKE 'log_warnings';

3.通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句

通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

通用日志相关设置

general_log=ON|OFFgeneral_log_file=HOSTNAME.loglog_output=TABLE|FILE|NONE

例子：

范例: 启用通用日志并记录至文件中select @@general_log; #默认没开启set global general_log=1; #开启SHOW GLOBAL VARIABLES LIKE 'log_output';#默认通用日志存放在文件中select @@general_log_file;#通用日志存放的文件路径

4.慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件long_query_time=N #慢查询的阀值，单位秒,默认为10sslow_query_log_file=HOSTNAME-slow.log#慢查询日志文件log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk #上述查询类型且查询时长超过long_query_time，则记录日志log_queries_not_using_indexes=ON#不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有log_slow_verbosity= Query_plan,explain #记录内容log_slow_queries = OFF#同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除setglobal slow_query_log=1;#开启set long_query_time=1;select sleep(10)

5.二进制日志备份

  • 记录导致数据改变或潜在导致数据改变的SQL语句

  • 记录已提交的日志

  • 不依赖于存储引擎类型

功能：通过”重放”日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

二进制日志记录三种格式

基于”语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少

基于”行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式

混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

show variables like 'binlog_format';

二进制日志文件格式

有两类文件1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.0000022.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：

sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项log_bin=mysql-bin默认是关闭#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENTmax_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G#说明：文件达到上限时的大小未必为指定的精确值binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除

在线查看 二进制

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]例子：show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

离线查看二进制日志

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志

命令格式：

mysqlbinlog [OPTIONS] log_file… --start-position=# 指定开始位置 --stop-position=# --start-datetime=#时间格式：YYYY-MM-DD hh:mm:ss --stop-datetime=--base64-output[=name]-v -vvv# at 328#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0use `mydb`/*!*/;SET TIMESTAMP=1446712300/*!*/;CREATE TABLE tb1 (id int, name char(30))/*!*/;事件发生的日期和时间：151105 16:31:40事件发生的服务器标识：server id 1事件的结束位置：end_log_pos 431事件的类型：Query 事件发生时所在服务器执行此事件的线程的ID：thread_id=1语句的时间戳与将其写入二进制文件中的时间差：exec_time=0错误代码：error_code=0事件内容：GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

例子：

mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -vmysqlbinlog--start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

二进制日志事件的格式：

# at 328#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0use `mydb`/*!*/;SET TIMESTAMP=1446712300/*!*/;CREATE TABLE tb1 (id int, name char(30))/*!*/;事件发生的日期和时间：151105 16:31:40事件发生的服务器标识：server id 1事件的结束位置：end_log_pos 431事件的类型：Query 事件发生时所在服务器执行此事件的线程的ID：thread_id=1语句的时间戳与将其写入二进制文件中的时间差：exec_time=0错误代码：error_code=0事件内容：GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

二.备份

1.备份类型

  • 完全备份，部分备份

  • 完全备份：整个数据集

  • 部分备份：只备份数据子集，如部分库或表

  • 完全备份、增量备份、差异备份

  • 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

    增量备份

    差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份

  • 冷备：读、写操作均不可进行，数据库停止服务

  • 温备：读操作可执行；但写操作不可执行

  • 热备：读、写操作均可执行

MyISAM：温备，不支持热备 不支持 事务

InnoDB：都支持

  • cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份

  • LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份

  • mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部

    分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份

  • xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份

  • MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现

  • mysqlbackup：热备份， MySQL Enterprise Edition 组件

  • mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、

    FLUSH TABLES和cp或scp来快速备份数据库

2.完全备份

1）物理冷备份

通过打包备份 数据库文件夹的方式备份（一般用于数据库迁移）

备份方式： 冷备份，一定要先停数据库

备份工具： cp tar 等

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

例子：

cd/var/lib/#在需要备份的主机上进入到数据库文件夹tar zcvf /opt/mysql_back.tar.gzmysql#打包数据库文件夹cd/optscp mysql_back.tar.gz 192.168.91.100:/opt/#远程拷贝第二台主机tar xf mysql_back.tar.gz#解压cd /var/lib/mv mysql mysql.bak-r#先备份原来的 数据库cp -a /opt/mysql /var/lib/
2）mysqldump 备份与恢复
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份，但数据库本身定义不备份mysqldump [OPTIONS] -B DB1 [DB2 DB3...]#支持指定数据库备份，包含数据库本身定义也会备份mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库，包含数据库本身定义也会备份

mysqldump 常见通用选项：

-A, --all-databases #备份所有数据库，含create database-B, --databases db_name…#指定备份的数据库，包括create database语句-E, --events：#备份相关的所有event scheduler-R, --routines：#备份所有存储过程和自定义函数--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器-d, --no-data#只备份表结构,不备份数据,即只备份create table -t, --no-create-info #只备份数据,不备份表结构,即不备份create table -n,--no-create-db #不备份create database，可被-A或-B覆盖--flush-privileges #备份mysql或相关时需要使用-f, --force #忽略SQL错误，继续执行--hex-blob#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码-q, --quick #不缓存查询，直接输出，加快备份速度
mysqldump逻辑热备mysqldump -uXXX -p[XXX] --databases 库1 [库2 ...] > XXX.sql 备份一个或多个库及库中的表数据mysqldump -uXXX -p[XXX] --all-databases > XXX.sql 备份所有的库mysqldump -uXXX -p[XXX] 库名 > XXX.sql备份指定库中的所有表数据（不包含创建库的操作）mysqldump -uXXX -p[XXX] 库名 表1 表2 ... > XXX.sql备份指定库中的一个或多个表数据（不包含创建库的操作）
完全恢复先登录到mysql，再执行 source XXX.sql（注：如果sql文件只包含表的备份，需要先创建库并use切换库）mysql -uXXX -pXXX [库名] < XXX.sql#如果sql文件只包含表的备份，要指定库名cat XXX.sql | mysql -uXXX -pXXX [库名]

例子：

mysqldump -uroot -p'Admin@123' hellodb> /data/hellodb.sql#单库 --databases 不加无法保存库名，加了会保存库名mysqldump -uroot -p'Admin@123' --databases hellodb db1> /data/hellodb.sql#多库mysqldump -uroot -p'Admin@123' --all-databases > /data/all_data.sql#全库mysqldump -uroot -p'Admin@123' hellodb students > /data/students.sql#单表mysqldump -uroot -p'Admin@123' hellodb students teachers > /data/students.sql#多表mysqldump -uroot -p'Admin@123' -dhellodbstudents > /data/students.sql#只保留表结构-dmysql -uroot -pAdmin@123 < /data/hellodb.sql

恢复例子：

mysql -uroot -pAdmin@123 -e 'show databases;'#-e在linux中执行数据库mysql -uroot -pAdmin@123 -e 'drop database hellodb;'# 删库mysql -uroot -pAdmin@123 < /data/hellodb.sqlmysql -uroot -pAdmin@123 -e 'create database hellodb;'mysql -uroot -pAdmin@123 hellodb < /data/hellodb.sql·mysql -uroot -pAdmin@123 -e 'show tables from hellodb;'

3.增量备份

备份

1.开启二进制日志功能vim /etc/my.cnf[mysqld]log-bin = mysql-binbinlog_format = MIXED#可选，指定二进制日志(binlog)的记录格式为 MIXEDserver-id = 1#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENTsystemctl restart mysqldls -l /var/lib/mysql-bin.*2.可每周对数据库或表进行完全备份mysqldump -u root -pAdmin@123 hellodb students > /opt/my_h_s_$(date +%F).sqlmysqldump -u root -p --databases hellodb > /opt/my_$(date +%F).sql3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)mysqladmin -u root -p flush-logs4．插入新数据，以模拟数据的增加或变更use xxx;insert into students values(3,'user3','male','game');insert into students values(4,'user4','female','reading');5．再次生成新的二进制日志文件(例如 mysql-bin.000003)mysqladmin -u root -p flush-logs#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中6．查看二进制日志文件的内容cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002#--base64-output=decode-rows：使用64位编码机制去解码并按行读取#-v：显示详细内容

恢复

1．一般恢复（1）模拟丢失更改的数据的恢复步骤use xxx;delete from info1 where id=3;delete from info1 where id=4;mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -pAdmin@1232．断点恢复mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002例：# at 302#230922 16:41:16插入了“user3”的用户数据# at 623#230922 16:41:24插入了“user4”的用户数据（1）基于位置恢复#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p#仅恢复“user4”的数据，跳过“user3”的数据恢复mysqlbinlog --no-defaults --start-position='682' /opt/mysql-bin.000002 | mysql -uroot -p（2）基于时间点恢复#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p#仅恢复“user4”的数据，跳过“user3”的数据恢复mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start