Msyqldump和Xtrabackup全备与增量备份mysql数据库

1、概述:

数据备份的介绍,类型,及具;

msyqldump备份的实现和数据恢复;(4GB)

Xtrabackup备份的实现和数据恢复;(G)

备份和恢复(数据):

 2、介绍

★备份:存储的数据副本;

原始数据:持续改变;(考虑问题)

★恢复:把副本应到线上系统;

仅能恢复备份操作时刻的数据状态;

★时间点恢复:

bin-logs;进制志)

★为什么备份?

灾难恢复:硬件故障(冗余)、软件故障(bug)、然灾害、客***、误操作、...

测试;

★备份时应该注意事项:

能容忍最多丢失多少数据;

恢复数据需要在多时间内完成;

需要恢复哪些数据;

☉做恢复演练:

测试备份的可性;

增强恢复操作效率;

3、备份类型

★备份的数据的集范围:

☉完全备份和部分备份

完全备份:整个数据集;

部分备份:数据集的部分,如部分表;

★完全备份、增量备份、差异备份:

完全备份

增量备份:仅备份次完全备份或增量备份以来变量的那部分数据;

差异备份:仅备份次完全备份以来变量的那部数据;

★物理备份、逻辑备份:

物理备份:复制数据件进备份;

逻辑备份:从数据库导出数据另存在个或多个件中;

★根据数据服务是否在线:

热备:读写操作均可进的状态下所做的备份;

 

温备:可读但不可写状态下进的备份;

冷备:读写操作均不可进的状态下所做的备份;

锁分为读锁和写锁:

(1)读锁,,不能写,可以重复读

(2)写锁,不能读写。

(3)备份需要考虑的因素、备份策略及备份内容

★备份策略:

完全+差异+时间点还原(进制binlog

完全+增量+时间点还原 (进制binlog

注意:

事务志和进制志应该放在有冗余能的磁盘上,RAID10最好

备份具:

 

 

4、备份具---mysqldump:

mysql服务带的备份具;逻辑备份具;

完全、部分备份;

InnoDB:热备;

MyISAM:温备;

★mysqldumper:比mysqldump快上好几十倍

★作:

逻辑备份、完全备份、部分备份;

##假如我们的备份策略为完全+增量+binlog备份,要使binlog进制志重读,就要确定从备份那刻开始,binlog的起始件位置,这时就要使--master-data=[#]选

项,(确保进制志是开启的)

(1)开启log-bin日志做备份恢复:

##在mysql配置文件里面加上一下两句开启log-bin日志功能

[root@cml5~]# cat /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/mydata/mysql-bin


server-id=1    ##5.7版本之后要加上server-id最好是唯一值

进入数据库查看:

mysql>show variables like 'log_bin%';

bb2b60cf1b97cedfe3f656e12b219b30.png

查看mydata目录已经有一个mysql-bin.000004二进制日志文件:

[root@cml5mydata]# ls

a.sh      bin_log cml5.err        ibdata1      ibtmp1 mysqlbak.log     performance_schema  sys

auto.cnf  chen    cml5.pid        ib_logfile0  my.cnf mysql-bin.000004  qqq                 yy

backup    cml     ib_buffer_pool  ib_logfile1  mysql  mysql-bin.index   rrr

(2)进入数据库创建一个数据库cml:

386ed653182aca02c72d16d61964f83d.png

模拟删除数据库:

1e1fa218eab229652f04fba5ed057c86.png

##恢复到创建了cml这个数据库之前的结点:

##查看mysql-bin.000004二进制日志文件:

[root@cml5mydata]# mysqlbinlog mysql-bin.000004
# at154
#17111120:16:01 server id 1  end_log_pos 219CRC32 0xa11ff2b3         Anonymous_GTID      last_committed=0      sequence_number=1        rbr_only=no
SET@@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#17111120:16:01 server id 1  end_log_pos 310CRC32 0x0285112a  Query        thread_id=4      exec_time=0      error_code=0
SET TIMESTAMP=1510402561/*!*/;
SET@@session.pseudo_thread_id=4/*!*/;
SET@@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET@@session.sql_mode=1436549152/*!*/;
SET@@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\Cutf8 *//*!*/;
SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET@@session.lc_time_names=0/*!*/;
SET@@session.collation_database=DEFAULT/*!*/;
create database cml
/*!*/;
# at 310
#17111120:22:02 server id 1  end_log_pos 375CRC32 0x2ec9b66d         Anonymous_GTID      last_committed=1      sequence_number=2        rbr_only=no
SET@@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 375
#17111120:22:02 server id 1  end_log_pos 458CRC32 0x51fb74e9  Query        thread_id=5      exec_time=0      error_code=0
SETTIMESTAMP=1510402922/*!*/;
drop database cml
/*!*/;
SET@@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER;
# Endof log file
/*!50003SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


##恢复到从219到310的节点:

[root@cml5 mydata]# mysqlbinlog --start-position="219"--stop-position="310" mysql-bin.000004 | mysql -uroot -predhat
mysql:[Warning] Using a password on the command line interface can be insecure.


##进入数据库查看cml数据库上是否已经恢复:

6f7dcd22db01913c3a451cf9ae660756.png

##起始点还原:

##删除cml数据库使用起始点还原:

0a43f6cfad763606a829f8be8632d052.png

[root@cml5 mydata]# mysqlbinlog --start-datetime="2017-11-11 20:40:37"--stop-datetime="2017-11-11 20:40:55" mysql-bin.000004 | mysql -uroot-predhat
mysql:[Warning] Using a password on the command line interface can be insecure.


##查看数据库是否已经还原数据库cml:

8b1950955e20cdd889f1e28d91f43fe8.png

##关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。

mysql>flush logs;
QueryOK, 0 rows affected (0.00 sec)


 

5、Mysqldump备份:

(1)备份单个库:

[root@cml5~]#  mysqldump -uroot -predhat --single-transaction -R --triggers -E --master-data--flush-logs --databases data_test >`date +%F"-%H:%M"`.sql


单备份某个库:

[root@cml5~]#  mysqldump -uroot -predhat --single-transaction -R --triggers -E --master-data--flush-logs -databases data_test >`date +%F"-%H:%M"`.sql


 

(2)备份所有的库:

[root@cml5~]#  mysqldump -uroot -predhat --single-transaction-R --triggers -E --masterdata --flush-logs --all-databases >`date+%F"-%H:%M"`.sql


(3)恢复所有的数据:

[[root@cml5~]# mysql -uroot -predhat < 2017-11-07-17\:01.sql


 

(4)恢复单个库:

[[root@cml5~]# mysql -uroot -predhat data_test< 2017-11-07-17\:01.sql


(5)备份数据做全备份,然后倒入从:

[root@cml5 mydata]# mysqldump -uroot --single-transaction -R --triggers -E --master-data --flush-logs --all-databases > cml.sql
[root@cml5 mydata]# mysql -uroot -predhat < cml.sql 
mysql:[Warning] Using a password on the command line interface can be insecure.



(6)那么基于线上没可能收到备份所以写一个shell脚本来自动备份:

[root@cml5~]# cat mysqlback.sh 
#!/bin/bash
##date=2017-11-7
##name=cml
##email=********@qq.com
##title=mysqldumpbackup
 
EMAIL="*******@qq.com"
mysqlbak_dir=/usr/local/mysql/mydata
mysqlbak_log=/usr/local/mysql/mydata/mysqlbak.log
DATE=`date+%Y%m%d`
TIME=`date+"%y-%m %H:%M:%S"`
DUMPBAK=$DATE.sql
GZDUMPBAK=$DATE.sql.gz
WEEK=`date+%w`
cd$mysqlbak_dir
USER=root
PASSWD=redhat
bin_log=`ls-tr mysql-bin.0*| head -1`
bin_log_dir=/usr/local/mysql/mydata/bin_log
 
if [ !-f $bin_log_dir ];then
        mkdir $bin_log_dir
fi
 
echo"--------------------------$TIME-----------------------------">> $mysqlbak_log
 
 
if [$WEEK == 0 ];then     ##日期是星期天的时候执行
##week=0~6
        cd $mysqlbak_dir
        /usr/local/mysql/bin/mysqldump -u$USER-p$PASSWD --single-transaction -R --triggers -E --master-data --flush-logs--all-databases > $DUMPBAK 2>&1
        echo "mysqldump backupsuccess!!" >> $mysqlbak_log
        tar -czvf $GZDUMPBAK $DUMPBAK >>$mysqlbak_log 2>&1
        echo "$GZDUMPBAK backupsuccess!!" >> $mysqlbak_log
#      if [ -f $GZDUMPBAK ];then
#              echo "$GZDUMPBAK BACKUPSUCCESS!!" |mail -s "mysqldump backup" $EMAIL
#      fi
        rm -rf $DUMPBAK
        rm -rf $bin_log
##上面步骤是全备过程
else
        cd $mysqlbak_dir
        /usr/local/mysql/bin/mysqladmin -u$USER-p$PASSWD flush-logs
        cp -p $bin_log $bin_log_dir
        echo "copy $bin_log to$bin_log_dir" >> $mysqlbak_log
        rm -rf $bin_log
        echo "backup bin_logsuccess!!" >> $mysqlbak_log
#      echo "backup bin_log $bin_logSUCCESS!" | mail -s "backup bin_log" $EMAIL
##下面是增备过程
fi


 

[root@cml5~]# cd /usr/local/src/script/
[root@cml5script]# ls
mysqlback.sh
[root@cml5script]# chmod a+x mysqlback.sh
 
crontab-e
1 0 * ** /usr/local/src/script/mysqlback.sh


 


6
、备份具---Xtrabackup:

(1)介绍及安装

★简介:

·Xtrabackup是由percona提供的mysql数据库备份具,据官介

绍,这也是世界上惟款开源的能够对innodb和xtradb数据库进

热备的具。

★特点:

·物理备份,备份过程快速、可靠;

·备份过程不会打断正在执的事务;

·能够基于压缩等功能节约磁盘空间和流量;

·动实现备份检验;

·还原速度快;

★持的存储引擎功能

·MyISAM:温备,不持增量备份;

·InnoDB:热备,增量;

★安装:

·其最新版的软件可从http://www.percona.com/software/

percona-xtrabackup/获得。

·本基于CentOS 7.2的系统,因此,直接下载相应版本的rpm包安装即可。

(2)在所以数据库主机上安装xtrabackup工具

[root@cml2src]# wget  https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm
[root@cml2src]# yum localinstall percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm


(3)使用xtrabackup工具做全备:

##先查看数据库的databases:

4d864c05f1400f763569d2cbd6164a2d.png

 

##创建一个/data备份目录:

[root@cml5 ~]# mkdir /data/


##把数据库全备到/data目录下:

[root@cml5 ~]# innobackupex --defaults-file=/etc/my.cnf -uroot -predhat --host=127.0.0.1 /data/


79022a45d8a4c776a77a4a1fea0f2aa8.png

##看到completed ok表示已经全备完

##查看备份生成的文件:

[root@cml5 2017-11-11_21-42-13]# pwd
/data/2017-11-11_21-42-13
[root@cml5 2017-11-11_21-42-13]# cat xtrabackup_binlog_info 
mysql-bin.000008     524
[root@cml5 2017-11-11_21-42-13]# ls
backup-my.cnf  cml   ibdata1  performance_schema  rrr xtrabackup_binlog_info xtrabackup_info     yy
chen  ib_buffer_pool  mysql  qqq  sys  xtrabackup_checkpoints  xtrabackup_logfile


(4)在另一台主机cml6上做一次全备的还原(模拟数据库的主机down掉加入新一台主机)

##先在cml5主机把主备文件copy到对端目录下:

[root@cml5 2017-11-11_21-42-13]# scp -r * cml6:/data/


##在对端cml6主机上创建data目录给权限:

[root@cml6 ~]# mkdir /data
[root@cml6 ~]# chown -R mysql:mysql /data/


 

##准备一个数据,保证数据没有提交的事务,提交或者回滚:

[root@cml6 data]# innobackupex --defaults-file=/etc/my.cnf -uroot -predhat--host=127.0.0.1 --apply-log /data/


6ab00cd9137d86aa1b555e7eba883434.png

##出现comleted ok表示已经成功

 

##直接恢复,先暂停mysql服务器:

[root@cml6 data]# /etc/init.d/mysqld stop
Shuttingdown MySQL. SUCCESS!
[root@cml6data]# innobackupex --defaults-file=/etc/my.cnf -uroot -predhat--host=127.0.0.1 --copy-back /data/
17111005:39:37 innobackupex: Starting the copy-back operation
 
IMPORTANT:Please check that the copy-back run completes successfully.
           At the end of a successful copy-backrun innobackupex
           prints "completed OK!".
 
innobackupexversion 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
Originaldata directory /usr/local/mysql/mydata is not empty!


 

##修改my.cnf的matadir和给权限:

[root@cml6 data]# cat /etc/my.cnf
[mysqld]
datadir=/data
socket=/usr/local/mysql/mysql.sock
log_bin=/usr/local/mysql/mydata/mysql-bin
server-id=2
#skip-grant-tables
[root@cml6 data]# chown -R mysql:mysql /data/


##直接启动mysql即可:

[root@cml6 data]# /etc/init.d/mysqld start
StartingMySQL.Logging to '/data/cml6.err'.
 SUCCESS!


##查看数据库是否与cml5一致:

332d2c6f27e331475e9792ac8bbf9dd1.png

 

执行的时候加上压缩参数。

[root@cml5 data]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=redhat --stream=tar /data/backup | gzip > /data/backup1/`date +%F_%H-%M-%S`.tar.gz


 

(5)使用xtrabackup工具做增量备份:

##先在mysql数据库上增加一个数据库cml2:

73c9c51d468d5fdeacb94aa5e7d5e284.png

##然后创建目录备份的件录:

[root@cml5 ~]# mkdir /data/zhengliang1
[root@cml5 ~]# mkdir /data/zhengliang2


 

##使用xtrabackup做增量备份:

[root@cml5 ~]# innobackupex --default-file=/etc/my.cnf -uroot -predhat --host=127.0.0.1--incremental /data/zhengliang1/--incremental-basedir=/data/2017-11-11_21-42-13/


1f486f443326cfc7d867a78c7870f67e.png

##出现completed ok!表示已经成功:

 

##查看备份的数据:

[root@cml5 ~]# cd /data/zhengliang1/2017-11-12_00-06-04/
[root@cml5 2017-11-12_00-06-04]# ls
backup-my.cnf  cml2            ibdata1.meta        qqq xtrabackup_binlog_info xtrabackup_logfile
chen           ib_buffer_pool  mysql               rrr  xtrabackup_checkpoints  yy
cml            ibdata1.delta   performance_schema  sys xtrabackup_info
[root@cml5 2017-11-12_00-06-04]# cat xtrabackup_binlog_info 
mysql-bin.000008     683


 

##scpzhengliang1的目录到对端cml6的任意一个目录下:

[root@cml5 2017-11-12_00-06-04]# scp -r /data/zhengliang1/ cml6:/usr/local/src/


 

“准备”(prepare)增量备份与整理完全备份有着一些不同,尤其要注意的是:

需要在每个备份(包括完全和各个增量备份)上,将已经提交的事务进行“重放”。“重放”之后,所有的备份数据将合并到完全备份上。(重放这步骤一定不能忘记)

基于所有的备份将未提交的事务进行“回滚”。

 

##这儿步骤意识是把本地的数据库“重放”

[root@cml6 src]# innobackupex --default-file=/etc/my.cnf -uroot -predhat --apply-log --redo-only/data/


 

##这步骤是合并全备和增备的日志:

[root@cml6 src]# innobackupex --default-file=/etc/my.cnf -uroot -predhat --apply-log--redo-only /data/--incremental-dir=/usr/local/src/zhengliang1/2017-11-12_00-06-04/


##然后在全目录下恢复“回滚”:

[root@cml6 src]# innobackupex --default-file=/etc/my.cnf -uroot -predhat --copy-back/data/
17111006:10:44 innobackupex: Starting the copy-back operation
 
IMPORTANT:Please check that the copy-back run completes successfully.
           At the end of a successful copy-backrun innobackupex
           prints "completed OK!".
 
innobackupexversion 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id:97330f7)
Originaldata directory /data is not empty!


##查看本地数据库是否与cml5的对应:(注意整个备份恢复都不用关闭mysql或重启mysql)

1aa8d8754b4f151f86435d8858fbd087.png

##已经多了cml2的数据库了

 

##在cml5在做一次增量备份的时候,假如加入一台新的主机的话备份就先要做(全备恢复à第一次增量备份恢复à第二次增量备份恢复à……)

##在操作一次以上步骤:

##加入新的数据

6dac584d8926c66b4890218ce2b7504d.png

##备份到zhengliang2目录:

[root@cml5 data]# innobackupex --default-file=/etc/my.cnf -uroot -predhat --host=127.0.0.1--incremental /data/zhengliang2/--incremental-basedir=/data/zhengliang1/2017-11-12_00-06-04/


 

##查看备份数据:

[root@cml5 data]# cd zhengliang2/2017-11-12_00-28-50/
[root@cml5 2017-11-12_00-28-50]# ls
backup-my.cnf  cml3            mysql               sys                     xtrabackup_logfile
chen           ib_buffer_pool  performance_schema  xtrabackup_binlog_info  yy
cml            ibdata1.delta   qqq                 xtrabackup_checkpoints
cml2           ibdata1.meta    rrr                 xtrabackup_info
[root@cml5 2017-11-12_00-28-50]# cat xtrabackup_binlog_info 
mysql-bin.000008     842


 

##scp到对端主机:

[root@cml5 2017-11-12_00-28-50]# scp -r /data/zhengliang2/ cml6:/usr/local/src/


 

##在对端主机上恢复备份:

##回放

[root@cml6 src]# innobackupex --defaults-file=/etc/my.cnf -uroot -predhat --apply-log--redo-only /data/


##合并日志:

[root@cml6 src]# innobackupex --defaults-file=/etc/my.cnf -uroot -predhat --apply-log--redo-only /data/ --incremental-dir=/usr/local/src/zhengliang2/2017-11-12_00-28-50/


##回滚:

[root@cml6 src]# innobackupex --defaults-file=/etc/my.cnf -uroot -predhat --copy-back/data/
17111006:29:50 innobackupex: Starting the copy-back operation
 
IMPORTANT:Please check that the copy-back run completes successfully.
           At the end of a successful copy-back runinnobackupex
           prints "completed OK!".
 
innobackupexversion 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id:97330f7)
Originaldata directory /data is not empty!


##查看数据库:

7020dccf1f79ef1b66737fcdc2a4f5c9.png