数据库 (五) – 事务、日志与备份

一、事务

参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

1.1 锁机制

    • 锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。
    • 锁是Mysql在服务器层和存储引擎层的的并发控制。
    • 加锁是消耗资源的,锁的各种操作,包括获得锁、检测锁是否是否已解除、释放锁等
  • 锁粒度:
    • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
    • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
  • 锁的读写类型:
    • 读锁:也称共享锁,只读不可写(包括当前事务),多个读互不阻塞
    • 写锁:也称独占锁、排它锁,写锁会阻塞其它事务(不包括当前事务)的读和它锁
  • 兼容
    如果事务T1获得了行r的共享锁,那么事务T2也可以获得行r的共享锁,这种情况称之为锁兼容,事务T3需要等行r释放共享锁,才能获得排他锁,这叫锁不兼容。
  • 实现
    • 存储引擎:自行实现其锁策略和锁粒度
    • 服务器级:实现了锁,表级锁,用户可显式请求
  • 分类:
    • 隐式锁:由存储引擎自动施加锁
    • 显式锁:用户手动请求
  • 锁策略:在锁粒度及数据安全性寻求的平衡机制
  • 死锁:
    • 两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态
    • 解决1:将任何的等待转化为回滚,并且事务重新开始。但这会浪费性能
    • 解决2:超时。当一个等待时间超过设置的值时,事务进行回滚。参数:innodb_lock_wait_timeout
  • 参考:https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
# 使用
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

# 关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

# 查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

1.2 事务介绍

  • 事务 Transaction
    事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务,可以对数据库中的数据更新处理的提交和取消进行管理
  • ACID特性
    • A:atomicity 原子性:整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
    • C:consistency 一致性:指的是事务中包含的处理要满足数据库提前设置的约束,如主键约束或者 NOT NULL 约束等
    • I:Isolation 隔离性:一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发
    • D:durability 持久性:一旦事务提交,其所做的修改会永久保存于数据库中
  • 事务日志:
    • 事务日志的写入类型为“追加”,因此其操作为“顺序IO”;通常也被称为:预写式日志 write ahead logging
    • 事务日志文件: ib_logfile0, ib_logfile1
# 启动事务
BEGIN
或
BEGIN WORK
或
START TRANSACTION

# 结束事务
COMMIT; # 提交
ROLLBACK; # 回滚
# 注意:只有事务型存储引擎中的 DML 语句方能支持此类操作.ROLLBACK回滚后,就得需要重新启动事务了.

# 自动提交:
set autocommit={1|0} # 默认为1,为0时设为非自动提交 建议:显式请求和提交事务,而不要使用“自动提交”功能

# 事务支持保存点:savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
  • 事务生命周期

1.3 事务隔离级别

  • 事务隔离级别:从上至下更加严格
    • READ UNCOMMITTED 可读取到未提交数据,产生脏读
    • READ COMMITTED 可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致
    • REPEATABLE READ 可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置
    • SERIALIZABILE 可串行化,未提交的读事务阻塞修改事务,或者未提交的修改事务阻塞读事务。导致并发性能差
  • MVCC: 多版本并发控制,和事务级别相关
事务隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)
# 查看
MariaDB [(none)]> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

# 指定事务隔离级别,服务器变量 tx_isolation 指定,默认为 REPEATABLE-READ,可在 GLOBAL 和 SESSION 级进行设置

# 临时配置
SET tx_isolation='type'
# type:{READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE }

# 配置文件
vim /etc/my.cnf
    

[mysqld]

transaction-isolation=REPEATABLE-READ

二、日志

  • 日志类型
    • 事务日志 transaction log
    • 错误日志 error log
    • 通用日志 general log
    • 慢查询日志 slow query log
    • 二进制日志 binary log
    • 中继日志 reley log
  • 日志永久修改需要在my.cnf的【mysqld】中添加

2.1 事务日志

  • 事务日志:事务型存储引擎自行管理和使用,建议和数据文件分开存放
    • redo log:每当有操作执行前,将数据真正更改时,先前相关操作写入重做日志。这样当断电,或者一些意外,导致后续任务无法完成时,系统恢复后,可以继续完成这些更改
    • undo log:当一些更改在执行一半时,发生意外,而无法完成,则可以根据撤消日志恢复到更改之前的壮态
# Innodb 事务日志相关配置:
MariaDB [(none)]> show variables like '%innodb%log%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_encrypt_log               | OFF       |
| innodb_flush_log_at_timeout      | 1         |
| innodb_flush_log_at_trx_commit   | 1         |
| innodb_locks_unsafe_for_binlog   | OFF       |
| innodb_log_buffer_size           | 16777216  |
| innodb_log_checksums             | ON        |
| innodb_log_compressed_pages      | ON        |
| innodb_log_file_size             | 50331648  |
| innodb_log_files_in_group        | 2         |
| innodb_log_group_home_dir        | ./        |
| innodb_log_optimize_ddl          | OFF       |
| innodb_log_write_ahead_size      | 8192      |
| innodb_max_undo_log_size         | 10485760  |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_scrub_log                 | OFF       |
| innodb_scrub_log_speed           | 256       |
| innodb_undo_log_truncate         | OFF       |
| innodb_undo_logs                 | 128       |
+----------------------------------+-----------+

# 参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

innodb_log_buffer_size 16777216 #16M,用来缓冲日志数据的缓冲区的大小,当此值快满时, InnoDB将必须刷新数据到磁盘上
innodb_log_checksums # 是否为redo log启用checksum校验
innodb_log_file_size 50331648 # 48M,每个日志文件大小
innodb_log_files_in_group 2 # 日志组成员个数
innodb_log_group_home_dir ./ # 事务文件路径,建议放在单独一块磁盘上



#系统变量 :innodb_flush_log_at_trx_commit # 默认为1
MariaDB [(none)]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

# 说明:设置为1,同时sync_binlog = 1表示最高级别的容错innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
1 默认情况下,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
0 提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能,但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入日志缓冲区,但每秒都会进行一次刷新。 性能比0略好一些,但操作系统或停电可能导致最后一秒的交易丢失
3 模拟MariaDB 5.5组提交(每组提交3个同步),此项MariaDB 10.0支持
  • 事务日志优化

2.2 错误日志

  • 错误日志
    • mysqld启动和关闭过程中输出的事件信息
    • mysqld运行中产生的错误信息
    • event scheduler运行一个event时产生的日志信息
    • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
# 错误日志相关配置
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%error%';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| log_error     | /app/mysql/mysql.log |
+---------------+----------------------+

# 错误文件路径
log_error=/PATH/TO/LOG_ERROR_FILE

# 是否记录警告信息至错误日志文件:show variables like "%log_warnings%";
log_warnings=2|1|0

log_warnings的值为0,表示不记录警告信息。
log_warnings的值为1,表示警告信息一并记录到错误日志中。
log_warnings的值大于1,表示"失败的连接"的信息和创建新连接时"拒绝访问"类的错误信息也会被记录到错误日志中。
mysql5.5中log_warnings参数的默认值为1
mysql5.7中log_warnings参数的默认值为2

2.3 通用日志

通用日志:记录对数据库的通用操作,包括错误的SQL语句
#通用日志相关设置
MariaDB [(none)]> show variables like "%general_log%";
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | OFF        |
| general_log_file | ubuntu.log |
+------------------+------------+

general_log=ON|OFF  -- 日志是否开启
general_log_file=HOSTNAME.log -- 设置日志文件保存位置

log_output=TABLE|FILE|NONE
FILE -- 表示将日志存入文件,默认值是FILE
TABLE -- 表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中

2.4 慢查询日志

慢查询日志:用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中
# 开启或关闭慢查询
slow_query_log=ON|OFF

# 慢查询的阀值,单位秒,默认10秒
long_query_time=N

# 慢查询日志文件
slow_query_log_file=HOSTNAME-slow.log

# 查询类型且查询时长超过long_query_time,则记录日志
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# 不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语句是否记录日志,默认OFF,即不记录
log_queries_not_using_indexes=ON

# 多少次查询才记录,mariadb特有
log_slow_rate_limit = 1 

# 记录内容
log_slow_verbosity= Query_plan,explain 

# 新版已废弃
log_slow_queries = OFF 同slow_query_log 

2.5 二进制日志

  • MySQL的二进制日志(binary log)是一个二进制文件,主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志(binary log)中记录了对MySQL数据库执行更改的所有操作,并且记录了语句发生时间、执行时长、操作数据等其它额外信息,但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志(binary log)主要用于数据库恢复和主从复制,以及审计(audit)操作.
  • 功能:通过“重放”日志文件中的事件来生成数据副本 注意:建议二进制日志和数据文件分开存放
  • 中继日志:relay log
    主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件
  • 参考:
    https://blog.csdn.net/demonson/article/details/80664141
    https://dev.mysql.com/doc/refman/5.7/en/binary-log.html
  • 二进制日志记录三种格式
    • 基于“语句”记录:statement,记录语句,默认模式
    • 基于“行”记录:row,记录数据,日志量较大
    • 混合模式:mixed, 让系统自行判定该基于哪种方式进行
      格式配置:show variables like 'binlog_format';
  • 二进制日志文件由两类文件构成
    • 日志文件:mysql|mariadb-bin.文件名后缀,二进制格式
      如: mariadb-bin.000001
    • 索引文件:mysql|mariadb-bin.index,文本格式
# 二进制日志相关的服务器变量:
sql_log_bin=ON|OFF # 是否记录二进制日志,默认ON
log_bin=/PATH/BIN_LOG_FILE # 指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED # 二进制日志记录的格式,默认 STATEMENT
max_binlog_size=1073741824 # 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G 说明:文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0 # 设定是否启动二进制日志即时同步磁盘功能,默认0,由
操作系统负责同步日志到磁盘
expire_logs_days=N # 二进制日志可以自动删除的天数。 默认为0,即不自动删除

# 查看系统变量log_bin,如果其值为OFF,表示没有开启二进制日志(binary log),如果需要开启二进制日志,则必须在my.cnf中[mysqld]下面添加log-bin [=DIR\[filename]] ,DIR参数指定二进制文件的存储路径;filename参数指定二级制文件的文件名。 其中filename可以任意指定,但最好有一定规范。系统变量log_bin是静态参数,不能动态修改的(因为它不是Dynamic Variable)。如下所示:
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> set log_bin=ON;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

vim /etc/my.cnf
    

[mysqld]

log_bin=/data/binlog/logbin.log #这样就会在指定的路径下生成 mariadb-bin 的二进制文件。默认 OFF,表示不启用二进制日志功能 #要生成二进制日志,还需要开启sql_log_bin=ON,不过这样默认是开启的 # 查看mariadb自行管理使用中的二进制日志文件列表,及大小 SHOW {BINARY | MASTER} LOGS # 查看使用中的二进制日志文件 SHOW MASTER STATUS; # 查看二进制文件中的指定内容 SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count] 比如:show binlog events in ‘mysql-bin.000001′ from 6516 limit 2,3

2.5.1 mysqlbinlog

mysqlbinlog : 二进制日志的客户端命令工具,可查看二进制日志文件
# 命令格式:
mysqlbinlog [OPTIONS] log_file…

--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=

# 时间格式:YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv

# 示例:
mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
mysqlbinlog mysql-bin.0000003 -v > testlog.sql # 利用二进制重放数据库

2.5.2 二进制日志事件的格式

# 查看日志
mysqlbinlog --no-defaults logbin.000001

# at 600
#210402 15:13:17 server id 1  end_log_pos 757 CRC32 0x326af4f3     Query    thread_id=12    exec_time=0    error_code=0
SET TIMESTAMP=1617347597/*!*/;
INSERT INTO shop.test VALUES ('0002', ' 打孔器 ', ' 办公用品 ', 500, 320, '2009-09-11')
/*!*/;

#事件发生的日期和时间:210402 15:13:17
#事件发生的服务器标识:server id 1
#事件的结束位置:end_log_pos 757
#事件的类型:Query
#事件发生时所在服务器执行此事件的线程的ID:thread_id=12
#语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
#错误代码:error_code=0
#事件内容:

2.5.3

# 清除指定二进制日志:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

# 示例:
PURGE BINARY LOGS TO ‘mariadb-bin.000003’; # 删除3之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

# 删除所有二进制日志,index 文件重新记数
RESET MASTER [TO #]; # 删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从1开始,一般是master主机第一次启动时执行,MariaDB10.1.6开始支持TO #

# 切换日志文件:
FLUSH LOGS;

三、备份和恢复

  • 为什么要备份
    灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
  • 备份注意要点
    • 能容忍最多丢失多少数据
    • 恢复数据需要在多长时间内完成
    • 需要恢复哪些数据
  • 还原要点
    • 做还原测试,用于测试备份的可用性
    • 还原演练
  • 备份时需要考虑的因素
    • 温备的持锁多久
    • 备份产生的负载
    • 备份过程的时长
    • 恢复过程的时长
  • 备份什么
    • 数据
    • 二进制日志、InnoDB的事务日志
    • 程序代码(存储过程、函数、触发器、事件调度器)
    • 服务器的配置文件

3.1 备份类型

  • 冷、温、热备份
    • 冷备:读写操作均不可进行
    • 温备:读操作可执行;但写操作不可执行
    • 热备:读写操作均可执行
      MyISAM:温备,不支持热备
      InnoDB:都支持
  • 物理和逻辑备份
    • 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
    • 逻辑备份:从数据库中“导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

3.2 备份工具

  • cp, tar 等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
  • **LVM **的快照:先加锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
  • mysqldump:逻辑备份工具,适用所有存储引擎,温备;支持完全或部分备份;对 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来快速备份数据库

3.3 基于 LVM 的备份

  1. 请求锁定所有表
    mysql> FLUSH TABLES WITH READ LOCK;
  2. 记录二进制日志文件及事件位置
    mysql> FLUSH LOGS;
    mysql> SHOW MASTER STATUS;
    mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE
  3. 创建快照
    lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
  4. 释放锁
    mysql> UNLOCK TABLES;
  5. 挂载快照卷,执行数据备份
  6. 备份完成后,删除快照卷
  7. 制定好策略,通过原卷备份二进制日志

3.4 逻辑备份工具

逻辑备份工具:mysqldump, mydumper, phpMyAdmin

3.4.1 mysqldump 工具

  • 参考:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
  • 优点:备份简单,恢复容易。
  • 缺点:schema 和数据存储在一起,巨大的SQL语句、单个巨大的备份文件(备份的库和>表都在一个文件中)
# 语法
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]

# 选项
-A            # 备份所有数据库,含create database
-B db_name…   # 指定备份的数据库,包括create database语句
-E            # 备份相关的所有event scheduler
-R            # 备份所有存储过程和自定义函数
--triggers    # 备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=utf8    #指定字符集
--master-data[=#]  #此选项须启用二进制日志
    "1":所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
    "2":记录为注释的CHANGE MASTER TO语句
    此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--single-transaction)

-F   # 备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--single-transaction或-x,--master-data 一起使用实现,此时只刷新一次日志
--compact   #去掉注释,适合调试,生产不使用
-d                  # 只备份表结构
-t                  # 只备份数据,不备份create table
-n                  # 不备份create database,可被-A或-B覆盖
--flush-privileges  # 备份mysql或相关时需要使用
-f                  # 忽略SQL错误,继续执行
--hex-blob          # 使用十六进制符号转储二进制列,当有包括BINARY, VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
-q                  # 不缓存查询,直接输出,加快备份速度
  • MyISAM备份选项:支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
# 锁定方法如下:
-x,--lock-all-tables    #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能
#注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables    #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
  • InnoDB备份选项: 支持热备,可用温备但不建议用
--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE
#此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互排斥
#备份大型表时,建议将--single-transaction选项和--quick结合一起使用
  • 生产环境实战备份策略
# InnoDB 建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > BACKUP/fullbak_BACKUP_TIME.sql

# MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >BACKUP/fullbak_BACKUP_TIME.sql
  • mysqldump 的示例
# 备份所有数据库
mysqldump -A > dump.sql

# 指定数据库备份
mysqldump -B hellodb > /data/hellodb.sql

# 对架构进行备份
#其实备份的内容就是表结构和数据,使用的SQL语句表示
mysqldump --single-transaction test  > test_backup.sql

# 还原数据库

mysql db_name < dump.sql

3.5 备份工具 xtrabackup

  • Xtrabackup 是由 percona 开源的免费数据库热备份软件,它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁);mysqldump备份方式是采用的逻辑备份,其最大的缺陷是备份和恢复速度较慢,如果数据库大于50G,mysqldump备份就不太适合。
  • 手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
  • 特点:
    • 1)备份速度快,物理备份可靠
    • 2)备份过程不会打断正在执行的事务(无需锁表)
    • 3)能够基于压缩等功能节约磁盘空间和流量
    • 4)自动备份校验
    • 5)还原速度快
    • 6)可以流传将备份传输到另外一台机器上
    • 7)在不增加服务器负载的情况备份数据

3.5.1 xtrabackup 安装

参考:https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
# 从Percona网站下载存储库软件包的deb软件包
wget https://repo.percona.com/apt/percona-release_latest.(lsb_release -sc)_all.deb
# 使用dpkg安装下载的软件包。为此,请以超级用户身份或使用sudo运行以下命令
sudo dpkg -i percona-release_latest.(lsb_release -sc)_all.deb
# 启用存储库: percona-release enable-only tools release
percona-release enable-only tools release

sudo apt-get install percona-xtrabackup-24 # 安装 2.4 版本
apt-get install percona-xtrabackup-80 # 安装 8.0 版本
# 为了进行压缩备份,请安装qpress软件包
sudo apt-get install qpress
# 查看内容
dpkg -L percona-xtrabackup-24

3.5.2 xtrabackup 版本介绍

Xtrabackup 2.2版

# Xtrabackup2.2 版之前包括4个可执行文件:
innobackupex # Perl 脚本
xtrabackup # C/C++ 编译的二进制
xbcrypt # 加解密
xbstream # 支持并发写的流文件格式

xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB 表,和 MySQLServer 没有交互
innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还会和 MySQL Server 发送命令进行交互,如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在xtrabackup 之上做了一层封装实现的

xtrabackup 2.4 版

# xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的变化:
innobackupex 功能全部集成到 xtrabackup 里面,只有一个 binary程序,另外为了兼容考虑,innobackupex作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表备份,并且 Innobackupex 在下一版本中移除,建议通过xtrabackup替换innobackupex

3.5.3 xtrabackup 备份过程

3.5.4 xtrabackup 用法

  • 选项说明:https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
# 备份
innobackupex [option] BACKUP-ROOT-DIR
# 选项
--user:该选项表示备份账号
--password:该选项表示备份的密码
--host:该选项表示备份数据库的地址
--databases:该选项接受的参数为数据库名,如果要指定多个数据库,彼此间需要以空格隔开;如:"xtra_test dba_test",同时,在指定某数据库时,也可以只指定其中的某张表。如:"mydatabase.mytable"。该选项对innodb引擎表无效,还是会备份所有innodb表
--defaults-file:该选项指定从哪个文件读取MySQL配置,必须放在命令行第一个选项位置
--incremental:该选项表示创建一个增量备份,需要指定--incremental-basedir
--incremental-basedir:该选项指定为前一次全备份或增量备份的目录,与--incremental同时使用
--incremental-dir:该选项表示还原时增量备份的目录
--include=name:指定表名,格式:databasename.tablename
~~~~
# 使用innobackupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时,innobackupex还会在备份目录中创建如下文件:
(1)xtrabackup_info:innobackupex工具执行时的相关信息,包括版本,备份选项,备份时长,备份LSN(log sequence number日志序列号),BINLOG的位置
(2)xtrabackup_checkpoints:备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的
(3)xtrabackup_binlog_info:MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置,可利用实现基于binlog的恢复
(4)backup-my.cnf:备份命令用到的配置选项信息
(5)xtrabackup_logfile:备份生成的日志文件


# Prepare
innobackupex --apply-log [option] BACKUP-DIR
# 选项:
--apply-log:一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory:和--apply-log选项一起使用,当prepare 备份时,做crash recovery分配的内存大小,单位字节,也可1MB,1M,1G,1GB等,推荐1G
--export:表示开启可导出单独的表之后再导入其他Mysql中
--redo-only:此选项在prepare base full backup,往其中合并增量备份时候使
用,但不包括对最后一个增量备份的合并


# 还原
innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
# 选项:
--copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back:这个选项与--copy-back相似,唯一的区别是它不拷贝文件,而是移动文件到目的地。这个选项移除backup文件,用时候必须小心。使用场景:没有足够的磁盘空间同事保留数据文件和Backup副本

# 还原注意事项:
1.datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定,否则--copy-back选项不会覆盖
2.在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,这些文件将属于创建备份的用户 
    chown -R mysql:mysql /data/mysql
    以上需要在用户调用innobackupex之前完成
    --force-non-empty-directories:指定该参数时候,使得innobackupex --copy-back或--move-back选项转移文件到非空目录,已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件,会报错失败

3.5.5 xtrabackup 示例

xtrabackup完全,增量备份及还原
# 1 在原主机做完全备份到 /backups
xtrabackup --backup --target-dir=/backup/
# 2 在目标主机上
1)预准备:确保数据一致,提交完成的事务,回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/
2)复制到数据库目录
注意:数据库目录必须为空,MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/
3)还原属性
chown -R mysql:mysql /var/lib/mysql
4)启动服务
systemctl start mariadbir=/backup/
scp -r /backup/* 目标主机:/backup

# 1 备份过程
1)完全备份:xtrabackup --backup --target-dir=/backup/base
2)第一次修改数据
3)第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4)第二次修改数据
5)第二次增量
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
6)scp -r /backup/*  目标主机:/backup/
# 备份过程生成三个备份目录
/backup/{base,inc1,inc2}

# 2 还原过程
1)预准备完成备份,此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2)合并第1次增量备份到完全备份,
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3)合并第2次增量备份到完全备份:最后一次还原不需要加选项--apply-log-only xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4)复制到数据库目录,注意数据库目录必须为空,MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
5)还原属性:chown -R mysql:mysql /var/lib/mysql
6)启动服务:systemctl start mariadb
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇