Mysql全备、增量备份及恢复

Mysql字符集

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

• Mysql常见字符集

注: 建议使用utf-8

• 怎么选择合适的字符集
  1.如果处理各种各样的文字，发布到不同语言国家地区，应选Unicode字符集，对MySQL来说就是utf8(每个汉字三个字节)
  2.如果只是需要支持中文，并且数据量很大，性能要求也高，可选GBK(定长，每个汉字占双字节，英文也占双字节)，如果是大量运算，比较排序等，定长字符集更快，性能也高
  3.处理移动互联网业务，可能需要使用utf8mb4字符集，apple
• 查看当前mysql支持的字符集

show character set

• 查看当前字符集的设置情况

show variables like 'character_set%'

Mysql备份恢复

• 备份类型
  1.按备份对数据库的影响范围可分为
  Hot backup : 热备，指在数据库运行中直接备份，对正在运行的数据库没有任何影响
  Cold backup : 冷备，指在数据库停止的情况下进行备份(OfflineBackup)
  Warm backup : 温备，备份同样在数据库运行时进行，但是会对当前数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性
  2.按备份后文件内容可以分为
  逻辑备份
  指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句，或者是表内的实际数据，如mysqldump和SELECT * INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长
  裸文件备份
  拷贝数据库的物理文件，数据库既可以处于运行状态（mysqlhotcopy 、ibbackup、xtrabackup这类工具），也可以处于停止状态，恢复时间较短
  3.按照备份数据库的内容来分
  完全备份：对数据库完整的备份
  增量备份：在上一次完全备份基础上，对更新的数据进行备份（xtrabackup）
  日志备份：二进制日志备份，主从复制
• 逻辑备份工具mysqldump
  数据大的时候不推荐使用

导出数据

# 导出所有数据库, -A等同于--all-databases
mysqldump -uroot -p123456 -A > all.sql
mysqldump -uroot -p123456 --all-databases > all.sql

# 导出某个数据库
mysqldump -uroot -p123456 数据库名 > test.sql

# 导出单张表
mysqldump  -uroot -p123456 数据库名 表名 > test.sql

# 导出库的表结构
mysqldump  -uroot -p123456 -d 数据库名 > test.sql

# 只导出数据
mysqldump  -uroot -p123456 -t 数据库名 > test.sql

# 导出数据库，并自动生成库的创建语句
mysqldump  -uroot -p123456 -B 数据库名 > test.sql

导入数据

# 导入所有数据库
mysql  -uroot -p123456 < all.sql
# 导入指定数据库
mysql  -uroot -p123456 数据库名 < test.sql
# 使用source方式导入指定数据库
use 数据库名
source /root/test.sql

# 导入表
source /root/test.sql
或
mysql  -uroot -p123456 表名 < test.sql

• mysqlhotcopy 裸备份文件
  不常用，5.7中已经去掉此命令，知道即可
  mysqlhotcopy vs mysqldump
  1.mysqldump采用sql级别的备份机制，数据量大的时候，占用系统资源较多，支持myisam,innodb
  2.mysqlhotcopy只是简单的缓存写入和文件复制，占用资源少，适合大数据库，只支持myisam
  3.mysqlhotcopy只能运行在数据库目录所在的机器，mysqldump可以在远程客户端
  4.mysqlhotcopy恢复只需要拷贝备份文件到源目录即可，mysqldump需要导入sql文件到源库中

• 备份脚本

mysql-autoback.sh

#!/bin/bash   
export LANG=en_US.UTF-8  
savedir=/database_back/   
cd "$savedir"  
time="$(date +"%Y-%m-%d")"  
mysqldump -u root -p123456 数据库名 > test-"$time".sql 

然后添加到定时任务即可。

xtrabackup备份工具

Xtrabackup包括两个主要工具：Xtrabackup和innobackupex：
Xtrabackup只能备份InnoDB和XtraDB两种引擎表，而不能备份MyISAM数据表。
innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等，智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备的功能

• 下载安装
  官网下载地址

yum -y install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

• 全备和还原

# 备份
innobackupex --user=root --password=123456 /tmp

# 会根据时间自动生成备份文件夹
/tmp/
├── 2019-10-25_15-26-13
│   ├── backup-my.cnf
│   ├── ibdata1
│   ├── mysql
│   ├── performance_schema
│   ├── test
│   ├── xtrabackup_checkpoints
│   ├── xtrabackup_info
│   └── xtrabackup_logfile

# 选项
# --no-timestamp，指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹
# full文件夹不能提前创建
innobackupex --user=root --password=123456 --no-timestamp /tmp/db_backup/full

注:
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步到数据文件中的事务。因此，此时数据文件仍处于不一致状态。准备的主要作用正是通过回滚未提交的事务及同步已经提交的事务到数据文件也使得数据文件处于一致状态
执行
innobackupex --apply-log <完全备份的目录名称>

# 还原备份
# 1.停止mysql
systemctl stop mariadb
# 2.删除数据库数据(测试，生产环境请勿这么玩)
rm -rf /var/lib/mysql
# 3.还原
innobackupex --copy-back /tmp/2019-10-25_15-26-13/
# 4.授权
chown -R mysql:mysql /var/lib/mysql/
# 5.启动
systemctl start mariadb

• 增量备份和还原
  作用: 减少备份数据重复，节省磁盘空间，缩短备份时间
  实现: 依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页
  注: 在创建增量备份之前需要先全备，否则增量备份是没有意义的

# 1.全备
innobackupex --user=root --password=123456 --no-timestamp /tmp/db_backup/full
# 2.查看xtrabackup_checkpoints信息
[root@centos7-app full]# cat xtrabackup_checkpoints 
backup_type = full-backuped  # 代表全备
from_lsn = 0
to_lsn = 1597964
last_lsn = 1597964  # 创建第一个增量备份
compact = 0
recover_binlog_info = 0
# 3.创建增量备份
# --incremental, 增量备份数据存储路径
# --incremental-basedir, 基于哪个全备进行增量备份
innobackupex --user=root --password=123456 --incremental /tmp/db_backup/ --incremental-basedir=/tmp/db_backup/full/
# 4.查看xtrabackup_checkpoints 
[root@centos7-app 2019-10-25_16-51-43]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1597964
to_lsn = 1597964
last_lsn = 1597964
compact = 0
recover_binlog_info = 0

注:
xtrabackup只会影响xtradb或者innodb的表，其他引擎的表在增量备份的时候只会复制整个文件，不会差异

# 可以指定—incremental-lsn来代替—incremental-basedir的方法创建增量备份
innobackupex --user=root --password=123456 --incremental /tmp/db_backup/ --incremental-lsn=1597964

# 增量备份还原
# 完全备份
innobackupex --apply-log --redo-only BASEDIR
# 第一个增量备份
innobackupex --apply-log --redo-only BASEDIR --incremental-dir=INCREMENTAL-DIR-1
# 第二个增量备份
innobackupex --apply-log --redo-only BASEDIR --incremental-dir=INCREMENTAL-DIR-2
恢复时，直接使用第1次的完全备份即可