mysql备份和恢复

转载 一寸HUI 2019/9/10 21:42:03

对于来说,数据的备份和恢复是一项很基本的操作。在意外的情况下(服务器宕机,磁盘损坏,卡损坏等),要保证数据不丢失,或者是最小程度的丢失,是每个每时每刻应该关心数据库的备份了。本来说明下备份的工具,原理以及使用。一、备份与恢复的概述按照是否能够继续提供服务,将数据库备份类型划分

       对于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工具

五、快照备份

 

上一篇:MySQL EXPLAIN结果集分析 - 附带大量案例

下一篇:Mysql高手系列 - 第7篇:玩转select条件查询,避免踩坑

赞(0)

共有 条评论 网友评论

验证码: 看不清楚?
    扫一扫关注最新编程教程