Mysql备份

1） 使用mysqldump备份

(1). 连接参数
    -u  :  备份时使用的用户名
    -p  :  备份时使用的密码
    -S  :  备份时使用socket连接
    -P  :  备份时使用的端口
    -h  :  备份时使用的连接地址

(2). 基础备份参数
    -A  :  全备数据库
    mysqldump -uroot -p123 -h localhost -P 3306 -A > /backup/full.sql
    -B  ：备份单个库，或多个库时使用的参数 
    mysqldump -uroot -p123 -h localhost -P 3306 -B dbname [dbname] > /backup/full.sql
    db  table [talbe]  ： 备份单库下的单表或多表使用的参数

(3). 特殊备份参数
    -d  只备份建表的信息
    -t   只备份表数据
    -R  备份存储过程和函数
    -E  备份事件
    --triggers  备份触发器

    --master-data=2  # 记录备份时刻二进制日志位置和文件名。如果不加--single-transaction的时候会锁表，加了--single-transaction会对于innodb存储引擎不会锁表
    --master-data[=#]   This causes the binary log position and filename to be
                  appended to the output. If equal to 1, will print it as a
                  CHANGE MASTER command; if equal to 2, that command will
                  be prefixed with a comment symbol. This option will turn
                  --lock-all-tables on, unless --single-transaction is
                  specified too (in which case a global read lock is only
                  taken a short time at the beginning of the dump; don't
                  forget to read about --single-transaction below). In all
                  cases, any action on logs will happen at the exact moment
                  of the dump. Option automatically turns --lock-tables
                  off.


    --single-transaction  # 对于Innodb引擎，进行一次性快照备份，不锁表。
    --single-transaction 
                  Creates a consistent snapshot by dumping all tables in a
                  single transaction. Works ONLY for tables stored in
                  storage engines which support multiversioning (currently
                  only InnoDB does); the dump is NOT guaranteed to be
                  consistent for other storage engines. While a
                  --single-transaction dump is in process, to ensure a
                  valid dump file (correct table contents and binary log
                  position), no other connection should use the following
                  statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                  TRUNCATE TABLE, as consistent snapshot is not isolated
                  from them. Option automatically turns off --lock-tables.

(4). 终极备份语句
    mysqldump -uroot -p123 -h localhost -P 3306 -A -R -E --triggers --master-data=2 --single-transaction | gzip > /back/full.sql.gz  #  实现mysql比较好的全备
    mysqldump -uroot -p123 -h localhost -P 3306 -B db_name [db_name] -R -E --triggers --master-data=2 --single-transaction | gzip > /back/dbs.sql.gz  #  实现mysql比较好的库的备份

(5). 其他参数
--max-allowed-packet=128M  # 要发送或接收的最大数据包大小   
--set-gtid-purged=auto #  (主从的时候，使用auto/on , 当本机普通备份时,使用off，可以去掉警报提示， 在现实中一般不用关注这个参数保持默认的auto就好)

(6). 恢复
先通过全部恢复
再通过binlog恢复全备之后的数据
    通过查看备份的文件里面的binlog日志文件和位置得到开始的事件位置。或通过查看备份的文件里面的gtid号
    通过binlog日志截取在全备以后的数据，恢复备份后面的数据。

2) 使用Xtrabackup备份，恢复

(1). 软件下载的地址 
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

(2). 针对非innodb表，进行锁表备份，copy所有的非innodb表文件
     针对innodb表，立即刷新内存中的脏页数据到磁盘，copy所有的innodb表相关的额文件(ibdata1,ibd,frm)。并将备份过程中产生的新的数据变化的部分redo一起备份
     在恢复时，xtrabackup会调用innodb引擎的CSR过程，将数据和redo的LSN追平，然后进行一致性恢复

(3). 全量备份和恢复
# 备份
 innobackupex --user=root --password=123 --host=localhost --port=3306 --no-timestamp /backup/full   # 全备份

# 恢复
innobackupex --apply-log /backup/full  # 对全备整理数据
cp /backup/full/* /data/mysql/data/
chown -R mysql:mysql /data/mysql/data/
启动数据库，查看数据

(4). 增量备份和恢复
增量备份是基于增量或全备的基础之上完成的。
增量备份的基础是InnoDB引擎使用了LSN机制，非InnoDB引擎不存在增量备份的说法，每次都是全备。
对于增量备份的恢复期间需要对已提交的事务前滚，未提交的事务回滚。
增量备份的恢复应按照备份的顺利逐个逐个replay，需要使用--apply-log --redo-only选项。
仅仅最后一个增量备份不需要使用–redo-only选项。
如果要做完全恢复或时点恢复，需要结合binlog来实现

# 备份
innobackupex --user=root --password=123 --host=localhost --port=3306 --no-timestamp /backup/full   # 全备份
innobackupex --user=root --password=123 --host=localhost --port=3306 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1  # 基于全备的增量备份
innobackupex --user=root --password=123 --host=localhost --port=3306 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2  # 基于上次增量备份的增量备份
innobackupex --user=root --password=123 --host=localhost --port=3306 --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3  # 基于上次增量备份的增量备份

--no-timestamp ： 不创建时间戳
--incremental ：开启增量备份
--incremental-basedir ：增量备份时的基础备份路径

# 恢复
innobackupex --apply-log --redo-only /backup/full  # 对全备整理数据
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full  # 对inc1合并整理到full
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2 /backup/full  # 对inc2合并整理到full    
innobackupex --apply-log --incremental-dir=/backup/inc3 /backup/full  # 注意最后一次不要加--redo-only 参数
innobackupex --apply-log /backup/full  # 对合并后的full进行整理

--incremental-dir ：这个参数代表需要被合并进去的增量备份目录
--redo-only ：这迫使xtrabackup跳过“回滚”阶段并仅执行“重做”

 然后把整理后的full恢复
rm -fr /data/mysql/data/*
cp -r /backup/full/* /data/mysql/data/
chown -R mysql:mysql /data/mysql/data/
systemctl restart msyql.service

然后用binlog日志恢复inc3后的明写入的数据
查看inc3增量备份的xtrabackup_binlog_pos_innodb找到binlog的position或若开启了GTID，也会将GTID取出
用binlog的postion或gtid做为起始位置到数据库坏掉的那一刻的postion或gtid做为终点，截取这段时间里的数据，然后恢复到数据库中，然后查看数据是否完整。

如果时数据库只是误删除了一张表可以单张表的恢复
进入数据库
创建那张误删除的表  create table table_name{xxx};
删除那张误删除的表的表空间  alter table table_name discard tablespace;
复制那张误删除的表ibd文件  cp /backup/full/db_name/table_name.idb /data/mysql/data/db_name/
修改权限  chown -R mysql:mysql /data/mysql/data/
进入数据库导入表空间   alter table table_name import tablespace;
查看数据是否修复

(5). 备份文件介绍
在备份的文件夹中，有几个文件值得注意：
xtrabackup_binlog_pos_innodb记录了binlog的position，若开启了GTID，也会将GTID取出。在用于备份+binlog恢复或建立slave的场景里十分有用。
xtrabackup_checkpoints记录了此次备份的类型和lsn号的起始值，是否压缩等
xtrabackup_info则记录了备份工具的信息，时间，备份对象（是针对全实例还是某库表），是否是增量，binlog位置等