Mysql之备份与恢复

admin 2019-4-12 485

Mysql备份常用方法(逻辑备份和物理备份)

逻辑备份mysqldump
  恢复时通过mysqldump命令备份的sql语句还原到mysql数据库中
  补充,增量备份备份binlog日志文件即可,恢复增量即通过mysqlbinlog工具截取binlog日志转换成sql语句,通过mysql或source进行语句还原
物理备份备份方法
  使用cp,rsync,tar,scp等工具,由于在备份期间数据依然在写数据,所以直接复制会引起数据丢失,在恢复数据库时,对新数据库的路径,配置也有要求,一般要和远程保持一致。为了确保数据一致性,可以选择人工停库或者锁库后进行。但是一般生产部允许,除非可以申请停机或锁表
      物理备份两部:1、停库或锁表,打包拷贝  2、第三方xtrabackup

企业场景全量和增量的频率
1、中小公司,全量一般每天一次,业务流量低谷进行,备份时锁表
         增量:定时,例如每分钟rsync推一次binlog
2、大公司,一般周备,节省备份时间,减小备份压力,缺点是binlog文件副本太多,还原比较麻烦
3、一主错从环境,主从复制本身就是实时远程备份,可以解决物理故障
4、一主多从环境,可以采取一个从库上专门进行备份,通过延时同步解决人为误操作

mysql全量备份与增量备份

按天全备

周一0点全量备份          周二0点全量备份                                          
01.sql.gz02.sql.gz
周一增量备份周二增量备份
mysql-bin.00025
mysql-bin.00026
..........
mysql-bin.index
mysql-bin.00035
mysql-bin.00036
..............
 mysql-bin.index

优点:恢复时间短,维护成本低

缺点:暂用时间多,暂用系统资源多,经常锁表影响客户体验

 

 

 

 

 

 

 

 


按周全备

        周六0点全量备份              
周一增量备份周二增量数据周三增量数据周四增量数据
mysql-bin.00025
mysql-bin.00026
mysql-bin.00027
...........
 mysql-bin.index
mysql-bin.00035
mysql-bin.00036
mysql-bin.00037
.............
mysql-bin.index
mysql-bin.00040
mysql-bin.00041
mysql-bin.00042
..............
mysql-bin.index
mysql-bin.00050
mysql-bin.00051
mysql-bin.00052
..............
mysql-bin.index

 优点:暂用空间小,暂用系统资源少,无需锁表或次数少,用户体验好些
缺点:维护成本高,恢复麻烦,时间长

 

 

 

 

 

 

 

 


MySql的备份命令

  myisam引擎
    #mysqldump -uroot -pxxx -A -B -F --master-data=2  -x --events >/opt/name.sql.gz
   innodb引擎
    #mysqldump -uroot -pxxx  -A -B -F --master-data=2 --events --single-transaction | gzip>/opt/name.sql.gz

--master---master-data=   (--master-data=--B, --  使用-B参数,后面可以接多个库,否则只能有一个库,之后的都被认为是表
--single---single-transaction 来保证备份的一致性,实际上它的工作原理是设定本次会话的隔离级别为:REPEATABLE READ,以确保本次会话(dump)时,不会看到其他会话已经提交了的数据。
-x,--lock-all-tables    Locks all tables across all databases. This is achieved by taking a global read lock for the duration of thewhole dump. Automatically turns --single-transaction and -l, --lock-tables   Lock all tables for read.

--default-character-set=latin1 指定字符集(一般不用)
-d  只备份表结构
-t  只备份数据
-T,--tab  分离表和数据,数据是文本
-R  备份存储过程
-q  

锁表备份

1、mysql> flush table with read lock;  锁表 (窗口不能退出,不然失效) 2、新开窗口进行导出备份(如果数据量大,且允许停机,就使用停机打包,而不用dump)   #mysqldump -uroot -pxxx --events  -A -B  --master-data=2| gzip >/opt/bak_$(date  +%F).sql.gz 3、mysql> unlock tables;  解锁

数据库表的备份

# mysqldump -uroot -pxxx databasename table1 table2 table3 >/opt/table.sql

案例:多个库和多个表备份到一起了,如何恢复单个库或表

  1、将备份导入测试库,然后把需要的备份出来,恢复到正式库
  2、 单表:grep tablename bak.sql> name.sql
      单库:循环过滤库里所有表
  3、 事先分库分表备份 

分库备份命令

#mysql -uroot -prootabcd -e "show databases;" | grep -Evi "Database|information_schema|performance_schema" | sed -r 's#(.*)#mysqldump -uroot -prootabcd --events -B \1 |gzip >/tmp/\1.sql.gz#g'| bash

分库备份脚本


#!/bin/sh MYUSER=root MYPASS=rootabcd [ ! -d /server/backup/ ] && mkdir -p /server/backup  -p MYCMD="mysql -u$USER -p$MYPASS" MYDUMP="mysqldump -u$MYUSER -p$MYPASS -B " for database in `$MYCMD -e "show databases;" | grep -Evi "Database|information_schema|performance_schema|mysql"` do         $MYDUMP $database | gzip> /server/backup/${database}_$(date +%F).sql.gz done


==============================================================================
数据库优化1、insert批量插入
2、不要用*,列出查询列,指定范围
select id,name from testselect id,name from test limit 2;
select id,name from test where id=1;select id,name from test where name="oldgirl"; 字符串查询带引号
select id,name from test where name="oldgirl" or id=5;select id,name from test where id>2 and id<4;
select SQL_NO_CACHE  id,name from test where id>2 and id<4; 不查缓存

排序(order by的列也可以考虑建索引)
select id,name from test order by id asc;
select id,name from test order by id desc;    逆序

刷新binlog参数(logbin文件生效参数log-bin)
binlog日志生成,每次重启和-F参数重新生成,以及超过1.1G后重新生成

mysqldump用于对某一时刻的数据全备,例如在0点进行备份bak.sql.gz
增量备份:当有数据写入到数据库时,还会同时把更新的sql语句写入到对应文件里,即binlog文件
10点丢失数据需要恢复,处理方法如下:
a、将0点时刻的备份bak.sql.gz数据还原,即数据库数据截至时间为00点
b、0点到10点的数据,从binlog里恢复


知识技巧
binlog日志切割(即刷新binlog)确定全备和增量备份的临界点。备份时候加-F(--flush-logs)参数,刷新binlog,生成新文件,将来增量恢复从这个文件开始
[root@test85 3306]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -F -d oldboy student >t2.sql
然后记录下ls 出binlog位置(发到邮箱或写到log),恢复时从新刷新的binlog中提取数据



利用source命令恢复数据库(用的不多)1、登陆数据库2、mysql>use oldboy3、使用source命令,后面接脚本文件mysql>source oldboy_db.sql 给文件系统路径,默认是登陆mysql前的文件系统路径。(注意编码 utf8无签名)

利用mysql命令恢复数据库[配置文件中有mysqldump参数模块可以设置]
[mysqldump]
quick
max_allowed_packet = 8M1、指定库恢复
#mysql -uroot -poldboy123 -S /data/3306/mysql.sock  oldboy<1.sql (备份oldboy库的时候如果加了-B,这边都不用加库名了)

针对压缩的备份数据恢复
方法一(建议此种)gzip -d /opt/mysql_bak.sql.gz
mysql -uroot -prootabcd < /opt/mysql_bak.sql
方法二gunzip< mysql_bak.sql.gz >/opt/ mysql_bak.sql
mysql -uroot -pxxx < /opt/mysql_bak.sql
或者gunzip <mysql_bak.sql.gz | mysql -uroot -pxxx


mysqlbinlog工具解析binlog日志实践1、解析指定库的binlog日志,利用-d参数指定库
平时插入数据记录时,先use 库 在insert。如果是insert into database.table values就不行
[root@test85 3306]# mysqlbinlog -d oldboy mysql-bin.000001 |egrep -v "^#|--|^$|\*"mysqlbinlog截取位置
mysqlbinlog mysqlbin.000020 --start-position=365 --stop-position=456 -r pos.sql     -r 输出到文件
用时间不精确
mysqlbinlog mysqlbin.000020 --start-datetime='2014-10-16 15:44' --stop-datetime=='2014-10-17 15:44' -r time.sql
(测试中发现时间及位置均不包括结束点)
mysqlbinlog命令--解析binlog日志为sql语句    -d参数根据指定库拆分binlog(单表根据关键字过滤)    --start-datetime=name --stop-datetime=name    --start-position=# --stop-position=#    -r, --result-file=name  相当于重定向
    解析row语句:mysqlbinlog --base64-output=“decode-rows” --verbose mysql-bin.000001
    
binlog的删除
mysql>reset master   binlog全部干掉
mysql>purge master logs to 'mysql-bin.00004'删除之前的日志 (不包括0004)
# grep expire /data/3306/my.cnf     配置文件自动删
expire_logs_days = 7     自动删除7天前的备份 

以上很类似mysql innodb引擎的--single-transaction备份策略

mysql备份方案
如果是单机备份环境,如果大于1台服务器一定要做主从复制1、主从本身就是备份,而且是实时备份。主从同步的功能本身就是解决物理故障宕机的实时备份方案。缺点:不能防止逻辑故障数据丢失

选择从库备份
1)选择一个不对外提供业务的从库做备份
2)开启binlog
3)备份时可以锁表也可以停止sql_thread, 不停io_thread,即暂停应用sql。备份期间,如果主库挂了,那么,从库备份加上备份后的binlog就是完整的主库内容。
4)数据量小于50G,mysqldump足够,如果数据量大于50G,xtrabackup物理工具。或者从库停止sql_thread,cp或打包的方案(还原主库----biglog-----停thread的那个点到打开)
5)1主5从,3从对外LVS集群提供服务,1个从定时任务,开发,后台用,最后一个啥也不干,最大限度保持和主一致(还原时,删除中继日志,其他change master 切换为主库)

mysql增量恢复条件
1、开启了log-bin日志功能(主从库都需开启了)(位置点)
至少存在全备加上全备之后的时刻到出问题时刻的所有增量binlog文件
2、存在mysql数据库全备


最新回复 (0)
全部楼主
返回
发新帖
老师机论坛