mysql 备份方式和原理

1、mysqldump：

           优点：逻辑备份，生成数据为sql文件格式，单库、单表数据迁移，备份恢复方便，sql备份文件通用，不同数据库之前移植，对INNODB表在线备份

           缺点：单线程，数据量大时备份时间长，sql形式备份恢复时间长，可能在备份过程中非事务表长期锁表影响业务。备份查询所有数据，会刷掉内存中的热点数据。

mysqldump备份过程：

       例：mysqldump -uUser -pPassword [database name],... [table].  > [dump file]

       --无参数：create database --drop table--create table--lock table--disable index--insert data --enable index --unlock table

       --opt：dump文件中包含drop table if exists tablename，insert之前包含锁表语句lock tables tablename write,insert之后包含unlock tables，默认开启，使用--skip-opt禁用

       --compress, -C：主机间数据传输使用数据压缩 (导入的库必须存在)     

           例如： mysqldump --host=host1 --opt sourceDB | mysql --host=host2 -C targetDB

       --no-data：只备份表结构     

       --databases：备份多个数据库，空格分隔       

       --all-databases，-A：备份所有数据库

       --all-tablespaces, -Y：备份所有表空间     

       --tables：备份多个表，空格分隔 

       --where='id=?'[" id='a' "]：条件导出     

       -F：生成新的binlog文件   

       --compatible：导出的数据和其他数据库兼容(ansi,mysql1323,mysql140...)

       --dunp-slave=【1, 2】：获取主库的binlog位置和文件名追加到导出数据文件中，获取的是备机执行到主binlog的位置(relay_master_log_file, exec_master_log_pos) 即stop slave; show slave status; start slave；选项打开--lock-all-tables，除非--single-transaction 被指定；1和2的区别是在导出文件中change master 是否增加注释

       --master-data=【1, 2】：记录当前服务器的binlog，相当于show master status；和--dump-slave方法相似

       --single-transaction：导出一致性状态，导出之前提交begin sql语句，不阻塞业务且保证导出数据库的一致性状态(mvcc适用，不显式加锁通过判断版本对比数据，InnoDB适用，和--lock-tables互斥 (会使挂起的事务隐含提交，导出结合--quick使用)）

       --quick, -q：不缓冲查询，直接导出到标准输出，默认打开，--skip-quick取消

       --lock-tables，-l：导出前，锁定所有表。read local锁定表允许MyISAM表并行插入，事务的表(InnoDB)，使用 --single-transaction更好，不需要锁定表

       --routines，-R：导出存储过程和自定义函数     

       --events，-E ：导出事件

       --flush-logs：导出前刷新日志   

       --flush-privileges：导出mysql数据库之后，发flush privileges语句

       --hex-blob：使用十六进制导出二进制字符串字段，必须使用(binary,varbinary,blob)

       --ignore-table：不导出指定表，多个表需重复多次，一次一个表 --ignore-table=database.table ....

       mysqldump备份过程，为减少数据库中加锁时间，使用--single-transaction开启一个一致性快照事务，为了备份期间能够获得一个与数据一致的binlog pos点，使用--master-data选项

       flush tables/flush tables with read lock：

        1、flush tables 强制关闭所有正在使用的表，并刷新查询缓存，删除查询缓存中所有缓存结果(类似set query cache）

             注意：使用lock tables、执行ddl语句、正在执行DML大事务(无lock in share mode和for update显示加锁),都会阻塞

        2、flush tables with read lock：关闭所有打开的表，并使用全局读锁锁定整个实例下的所有表，(支持快照备份，备份完成后unlock tables释放锁)，获取的是一个全局读锁，不是表锁。

             注意：flush table with read lock 不会阻塞日志表的写入(查询日志，慢查询),与XA协议不兼容。

       a. 如果一个会话中使用lock tables语句，执行DDL语句，执行DML大事务(无lock in share mode 和for update显式加锁) 

       b. 另外会话执行flush tables with read lock会阻塞，数据库lock_wait_timeout太短，会导致mysqldump执行flush tables with  read lock语句获取全局读锁超时而导致备份失败

       c. 有lock tables ...read加锁,不允许使用flush tables(会阻塞),如果已使用，需要对其他表执行  刷新，在会话中使用flush tables  tablename with read lock ;   

       刷新表并获取指定表的读锁，首先获取表的独占MDL锁，所以需要等待该表的所有事务提交提交完成，然后刷新表的缓存，重新打开表，获取表读锁。并将MDL锁从独占级别降为共享。该语句获取表读锁，降级MDL锁后，其他会话可以读取表，但不能修改表数据和表结构，执行语句需要reload和lock tables权限，适用于基表(持久表)，不适用临时表，对视图使用报错。

        与lock tables 类似，使用该语句对表加锁后，在同一个会话中开启一个事务时，会被自动解锁。

set session transaction isolation level repeatable read：

         后续使用start transaction with consistent snapshot(InnoDB)开启一致性事务快照只支持RR隔离级别。其他隔离级别执行会告警，with consistent snapshot（=start transaction+select，获取一个事务号，在read view中占着，不会请求任何锁）

savepoint设置回滚点：

         savepoint：为了回滚在设置这个点发生变更的数据，savepoint 'identifier' 为事务设置一个命名的事务保存点，字符串标识符

         rollback to savepoint：将事务回滚到指定保存点位置，而不终止事务。且比这个保存点时间上设置更晚的保存点将被删除，同时释放保存点之后事务持有的MDL锁。

       当前事务在回滚点之后修改的行数据被撤销(InnoDB不会释放这些发生修改且被撤销行的行锁，这些修改的数据行行锁存储在内存中；当前事务在回滚点之后插入的行数据也会撤销(InnoDB会释放这些插入的行的行锁，锁信息存储在行数据中的事务ID上，不会单独存储在内存中，新插入的行数据被回滚之后，对应的行锁将被释放）

过程图: