对于 DBA 来说, 数据的备份和恢复是一项很基本的操作. 在意外的情况下 (服务器宕机, 磁盘损坏, RAID 卡损坏等), 要保证数据不丢失, 或者是最小程度的丢失, 是每个 DBA 每时每刻应该关心数据库的备份了. 本来说明下备份的工具, 原理以及使用.
一, 备份与恢复的概述
按照是否能够继续提供服务, 将数据库备份类型划分为:
热备份:(在线备份) 在数据库运行的过程中进行备份, 并且不影响数据库的任何操作
温备份: 能读不能写, 在数据运行的过程中进行备份, 但是对数据有影响, 如需要加全局锁保证数据的一致性.
冷备份:(离线备份) 在停止数据库的情况下, 复制备份数据库的物理文件.
按照备份后文件的内容分类:
逻辑备份: 备份文件时可读的文本文件, 比如 sql 语句, 适合数据库的迁移和升级, 但是恢复时间比较长.
裸文件备份: 复制数据库的物理文件
按照备份数据库的内容分类:
完全备份: 对数据库进行一个完整的备份
增量备份: 在完全备份的基础上, 对数据库的增量进行备份
日志备份: 只要是对 binlog 的备份
二, 冷备
只需要备份 MySQL 数据库的 frm 文件, 共享表空间文件, 独立表空间文件 (*.ibd), 重做日志文件, 以及 msyql 的配置文件 my.cnf.
优点:
备份简单, 只需要复制文件就可以
恢复简单, 只需要把文件恢复到指定位置
恢复速度快,
缺点:
备份文件较大, 因为表空间存在大量的其他数据, 比如 undo 段, 插入缓冲等
不能总是轻易跨平台
三, 逻辑备份
3.1,mysqldump
语法:
- mysqldump [OPTIONS] database [tables]
- mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
- mysqldump [OPTIONS] --all-databases [OPTIONS]
选项:
- -u, --user=name #指定用户名
- -S, --socket=name #指定套接字路径
- -p, --password[=name] #指定密码
- -P, --port=3306 #指定端口
- -h, --host=name #指定主机名
- -r, --result-file=name #将导出结果保存到指定的文件中, 在 Linux 中等同于覆盖重定向.
- --all-databases, -A #指定 dump 所有数据库. 等价于使用 --databases 选定所有库
- --databases, -B #指定需要 dump 的库. 该选项后的所有内容都被当成数据库名; 在输出文件中的每个数据库前会加上建库语句和 use 语句
- --ignore-table=db_name.tbl_name #导出时忽略指定数据库中的指定表, 同样可用于忽略视图, 要忽略多个则多次写该选项
- -d, --no-data #不导出表数据, 可以用在仅导出表结构的情况.
- --events, -E #导出事件调度器
- --routines, -R #导出存储过程和函数. 但不会导出它们的属性值, 若要导出它们的属性, 可以导出 MySQL.proc 表然后 reload
- --triggers #导出触发器, 默认已开启
- --tables #覆盖 --databases 选项, 导出指定的表. 但这样只能导出一个库中的表. 格式为 --tables database_name tab_list
- --where='where_condition', -w 'where_condition' #指定筛选条件并导出表中符合筛选的数据, 如 --where="user='jim'"
- --add-drop-database #在输出中的 create database 语句前加上 drop database 语句先删除数据库
- --add-drop-table #在输出的 create table 语句前加上 drop table 语句先删除表, 默认是已开启的
- --add-drop-trigger #在输出中的 create trigger 语句前加上 drop trigger 语句先删除触发器
- -n, --no-create-db #指定了 --databases 或者 --all-databases 选项时默认会加上数据库创建语句, 该选项抑制建库语句的输出
- -t, --no-create-info #不在输出中包含建表语句
- --replace #使用 replace 代替 insert 语句
- --default-character-set=charset_name #在导出数据的过程中, 指定导出的字符集. 很重要, 客户端服务端字符集不同导出时可能乱码, 默认使用 utf8
- --set-charset #在导出结果中加上 set names charset_name 语句. 默认启用.
- --compact #简化输出导出的内容, 几乎所有注释都不会输出
- --complete-insert, -c #在 insert 语句中加上插入的列信息
- --create-options #在导出的建表语句中, 加上所有的建表选项
- --tab=dir_name, -T dir_name #将每个表的结构定义和数据分别导出到指定目录下文件名同表名的. sql 和 txt 文件中, 其中. txt
- #文件中的字段分隔符是制表符. 要求 mysqldump 必须和 MySQL Server 在同一主机, 且 MySQL 用
- #户对指定的目录有写权限, 并且连接数据库的用户必须有 file 权限. 且指定要 dump 的表, 不能和
- #--databases 或 --all-databases 一起使用. 它的实质是执行 select into outfile.
- --fields-terminated-by=name #指定输出文件中的字段分隔符
- --fields-enclosed-by=name #指定输出文件中的字段值的包围符, 如使用引号将字符串包围起来引用
- --fields-optionally-enclosed-by=name #指定输出文件中可选字段引用符
- --fields-escaped-by=name #指定输出文件中的转义符
- --lines-terminated-by=name #指定输出文件中的换行符
- -Q, --quote-names #引用表名和列名时使用的标识符, 默认使用反引号 "`"
- --delayed-insert #对于非事务表, 在 insert 时支持 delayed 功能, 但在 MySQL5.6.6 开始该选项已经废弃
- --disable-keys, -K #在 insert 语句前后加上禁用和启用索引语句, 大量数据插入时该选项很适合. 默认开启
- --insert-ignore #使用 insert ignore 语句替代 insert 语句
- --quick, -q #快速导出数据, 该选项对于导出大表非常好用. 默认导出数据时会一次性检索表中所有数据并加入
- #到内存中, 而该选项是每次检索一行并导出一行
- --add-locks #在 insert 语句前后加上 lock tables 和 unlock tables 语句, 默认已开启.
- --flush-logs, -F #在开始 dump 前先 flush logs, 如果同时使用了 --all-databases 则依次在每个数据库 dump 前 flush,
- #如果同时使用了 --lock-all-tables,--master-data 或者 --single-transaction, 则仅 flush
- #一次, 等价于使用 flush tables with read lock 锁定所有表, 这样可以让 dump 和 flush 在完全精
- #确的同一时刻执行.
- --flush-privileges #在 dump 完所有数据库后在数据文件的结尾加上 flush privileges 语句, 在导出的数据涉及 MySQL 库或
- #者依赖于 MySQL 库时都应该使用该选项
- --lock-all-tables, -x #为所有表加上一个持续到 dump 结束的全局读锁. 该选项在 dump 阶段仅加一次锁, 一锁锁永久且锁所有.
- #该选项自动禁用 --lock-tables 和 --single-transaction 选项
- --lock-tables, -l #在 dump 每个数据库前依次对该数据库中所有表加 read local 锁 (多次加锁, lock tables...read local),
- #这样就允许对 myisam 表进行并发插入. 对于 innodb 存储引擎, 使用 --single-transaction 比
- --lock-tables #更好, 因为它不完全锁定表. 因为该选项是分别对数据库加锁的, 所以只能保证每个数
- #据库的一致性而不能保证所有数据库之间的一致性. 该选项主要用于 myisam 表, 如果既有 myisam 又有
- #innodb, 则只能使用 --lock-tables, 或者分开 dump 更好
- --single-transaction #该选项在 dump 前将设置事务隔离级别为 repeatable read 并发送一个 start transaction 语句给
- #服务端. 该选项对于导出事务表如 innodb 表很有用, 因为它在发出 start transaction 后能保证导
- #出的数据库的一致性时而不阻塞任何的程序. 该选项只能保证 innodb 表的一致性, 无法保证 myisam 表
- #的一致性. 在使用该选项的时候, 一定要保证没有任何其他连接在使用 ALTER TABLE,CREATE TABLE,
- #DROP TABLE,RENAME TABLE,TRUNCATE TABLE 语句, 因为一致性读无法隔离这些语句.
- #--single-transaction 选项和 --lock-tables 选项互斥, 因为 lock tables 会隐式提交事务.
- #要导出大的 innodb 表, 该选项结合 --quick 选项更好
- --no-autocommit #在 insert 语句前后加上 SET autocommit = 0, 并在需要提交的地方加上 COMMIT 语句
- --order-by-primary #如果表中存在主键或者唯一索引, 则排序后按序导出. 对于 myisam 表迁移到 innobd 表时比较有用, 但是
- #这样会让事务变得很长很慢
简单使用 (由于比较简单, 不具体阐述):
- mysqldump -uroot -p123456 -A -r all.sql #备份所有数据库
- mysqldump -uroot -p123456 -A> all.sql #备份所有数据库
- mysqldump -uroot -p123456 -B test test1> db_test.sql #备份 test 和 test1 数据库
- mysqldump -uroot -p123456 --single-transaction -A> all.sql #innodb 开始事务备份所有数据
- mysqldump -uroot -p123456 --default-character-set=latin1 -A> all.sql #指定字符集备份所有数据
- mysqldump -uroot -p123456 --tables test gxt1 -r gxt.sql #备份 test 库的 gxt1 表
mysqldump 工具使用建议:
1. 从性能考虑: 在需要导出大量数据的时候, 使用 --quick 选项可以加速导出, 但导入速度不变. 如果是 innodb 表, 则可以同时加上 --no-autocommit 选项, 这样大量数据量导入时将极大提升性能.
2. 一致性考虑: 对于 innodb 表, 几乎没有理由不用 --single-transaction 选项. 对于 myisam 表, 使用 --lock-all-tables 选项要好于 --lock-tables. 既有 innodb 又有 myisam 表时, 可以分开导出, 又能保证一致性, 还能保证效率.
3. 方便管理和维护性考虑: 在导出时 flush log 很有必要. 加上 --flush-logs 选项即可. 而且一般要配合 --lock-all-tables 选项或者 --single-transaction 选项一起使用, 因为同时使用时, 只需刷新一次日志即可, 并且也能保证一致性. 同时, 还可以配合 --master-data=2, 这样就可以方便地知道二进制日志中备份结束点的位置.
4. 字符集考虑: 如果有表涉及到了中文数据, 在 dump 时, 一定要将 dump 的字符集设置的和该表的字符集一样.
5. 杂项考虑: 备份过程中会产生二进制日志, 但是这是没有必要的. 所以在备份前可以关掉, 备份完后开启. set sql_log_bin=0 关闭, set sql_log_bin=1 开启.
msyqldump 结合 binlog 日志实现增量备份
1, 首先全备: mysqldump -uroot -p123456 -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables test gxt1> gxt.sql
2, 修改表中的数据: insert into test.gxt1 values(1,'王麻子');
3, 备份二进制日志: mysqlbinlog MySQL-bin.000002>new_gxt.sql #这里需要指定时间或者指定 position 对增量进行备份
4, 模拟删掉: drop table test.gxt1;
5, 恢复:
- MySQL>use test;
- MySQL>source gxt.sql;
- MySQL>source new_gxt.sql;
总结
msyqldump 是属于逻辑备份, 备份 sql 语句, 简单, 但是由于恢复时都是通过 insert 进行插入, 所有恢复速度慢, mysqldump 备份 myisam 表时因为要加 --lock-all-tables, 这时要备份的数据库全部被上锁, 可读不可写, 所以实现的是温备. mysqldump 备份 innodb 表时因为要加 --single-transaction, 会自动将隔离级别设置为 repeatable read 并开启一个事务, 这时 mysqldump 将获取 dump 执行前一刻的行版本, 并处于一个长事务中直到 dump 结束. 所以不影响目标数据库的使用, 可读也可写, 即实现的是热备
3.2,select ... into outfile
load data infile 和 select into outfile 语句是配套的. 可以通过参数 secure_file_priv 对其进行控制是否可以使用:
常用自定义格式说明:
fields terminated by 'string'指定字段分隔符;
enclosed by 'char'指定所有字段都使用 char 符号包围, 如果指定了 optionally 则只用在字符串和日期数据类型等字段上, 默认未指定;
escaped by 'char'指定转义符.
lines starting by 'string'指定行开始符, 如每行开始记录前空一个制表符;
lines terminated by 'string'为行分隔符.
默认:
fileds terminated by '\t' enclosed by ''escaped by'\\'lines terminated by'\n'starting by''
简单使用例子:
- select * from test into outfile '/data/t_data.sql';
- select * into outfile '/data/t_data.sql' from test;
- select id,name from test into outfile '/data/t_data.sql';
- select * from t into outfile '/data/t_data1.sql' fields terminated by ',' enclosed by '\'' lines starting by '\t' terminated by '\n';
3.3, 逻辑备份的恢复
语法很简单:
- MySQL -uroot -p123456 <all_bak.sql
- MySQL>source /root/all_bak.sql #登录 MySQL
- 3.4,load data infile
选项同 select into outfile 是一样的, 增加了 gnore N lines|rows 表示忽略前 N 行数据不导入, set col_name=expr 表示对列进行一些表达式运算
基本使用:
- load data infile '/home/data1.sql' into table test.gxt fields terminated by ',' (id,name)set is_enable=1; #指定字段
- load data infile '/home/data1.sql' into table test.gxt fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n';
- load data infile '/home/data1.sql' into table test.gxt fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' ignore 2 rows; #忽略前两行
- load data infile '/home/data1.sql' into table test.gxt fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set id=id+5; #设置列, 下同
- load data infile '/home/data1.sql' into table test.gxt fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com');
- load data infile '/home/data1.sql' into table test.gxt fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com'), id=id+5;
3.5,mysqldump 导出
本质和 select into outfile 一样
- MySQL -uroot -p123456 -e "select * from test.gxt"> a.txt #虽然这样也可以导出数据, 但是是没有格式的
- mysqldump -uroot -p123456 --tab /data/test test gxt1 #这里指定的目录 MySQL 用户需要有写权限, 还需要设定参数 secure-file-priv=/data/test
如上的导出方式, 既有表结构的定义, 又有表数据的导出.
mysqldump 的 "--tab" 选项同样可以指定各种分隔符. 如 "--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=...". 以下是指定字段分隔符为 ","
3.6,mysqlimport 导入
mysqlimport 本质上就是 load data infile 的命令接口, 而且大多数的语法与之相似, 不同的是 mysqlimport 可以同时导入多张表, 通过参数 --user-thread 并发导入不同的文件
简单使用例子:
- mysqlimport -uroot -p123456 --fields-terminated-by=',' test '/home/t.txt'
- mysqlimport -uroot -p123456 --fields-terminated-by=',' --user-thread test '/home/t.txt' 'home/gxt1.txt' #并发导入两个表
四, 热备
4.1,xtrabackup 安装
官网地址: https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
1, 配置 yum 源: yum installhttps://repo.percona.com/yum/percona-release-latest.noarch.rpm (推荐)
2, 安装: yum install percona-xtrabackup-24
本来装了个最新版 yum install percona-xtrabackup-80, 但是... 有点尴尬 (版本 8.0 不支持 mysql5.x), 新版本已经没有 innobackupex 这个工具了
安装完成之后会生成如下工具:
- [root@lgh3 ~]# rpm -ql percona-xtrabackup-24 | grep bin |xargs ls -l
- lrwxrwxrwx 1 root root 10 Sep 10 05:33 /usr/bin/innobackupex -> xtrabackup
- -rwxr-xr-x 1 root root 3846952 Jul 5 03:59 /usr/bin/xbcloud
- -rwxr-xr-x 1 root root 3020 Jul 5 03:53 /usr/bin/xbcloud_osenv
- -rwxr-xr-x 1 root root 3603744 Jul 5 03:59 /usr/bin/xbcrypt
- -rwxr-xr-x 1 root root 3612192 Jul 5 03:59 /usr/bin/xbstream
- -rwxr-xr-x 1 root root 21730616 Jul 5 03:59 /usr/bin/xtrabackup
xbcloud 和 xbcloud_osenv 是 xtrabackup 新的高级特性: 云备份;
xbcrypt 也是新的特性, 加密备份集;
xbstream 是 xtrabackup 的流数据功能, 通过流数据功能, 可将备份内容打包并传给管道后的压缩工具进行压缩;
xtrabackup 是主程序
innobackupex 在以前是一个 perl 脚本, 会调用 xtrabackup 这个二进制工具, 从 xtrabackup 2.3 开始, 该工具使用 C 语言进行了重写, 当前它是 xtabackup 二进制工具的一个软连接, 但是实际的使用方法却不同, 并且在以后的版本中会删除该工具
未完...
4.2,xtrabackup 备份原理
4.3,xtrabackup 工具
五, 快照备份
来源: https://www.cnblogs.com/zsql/p/11498864.html