mysql备份--心得

来源：互联网发布：java基础环境搭建编辑：程序博客网时间：2024/06/14 06:18

0、

怎么知道某数据库是什么存储引擎？

1、

InnoDB存储引擎：

备份策略：

（1）mysqldump+复制BIN LOGS（官方文档：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html）

热备份（属于物理备份，必须启用归档日志模式）+完全备份+增量备份+二进制日志备份。

如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果

（2）xtrabackup（官方文档：https://www.percona.com/downloads/）

如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

备份工具：

mysqldump：逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备

xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

补充知识点：

什么是热备份：

热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。即热备份是系统处于正常运转状态下的备份。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog()方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。

Oracle的逻辑备份就是export.将数据从Oracle中取出来热备份是在数据库运行的情况下进行备份。与冷备份一起被称作 物理备份两者区别，我个人认为：1  热备份时间相比于逻辑备份 花费得更短  2  热备份可以将数据库恢复到某一个时间点上3  恢复的时候花费得时间相比较而言也很短。 4  热备份的维护比较难，而且不能够出错。否则后果严重

什么是逻辑备份？

逻辑备份是指使用软件技术从数据库中导出数据并写入一个输出文件，该文件的格式一般与原数据库的文件格式不同，只是原数据库中数据内容的一个映像。因此，逻辑备份文件只能用来对数据库进行逻辑恢复，即数据导入，而不能按数据库原来的存储特征进行物理恢复。逻辑备份一般用于增量备份，即备份那些在上次备份以后改变的数据。

逻辑备份：是利用SQL语言从数据库中抽取数据并存于二进制文件的过程。

第一类为物理备份，该方法实现数据库的完整恢复，但数据库必须运行在归档模式下（业务数据库在非归档模式下运行），且需要极大的外部存储设备，例如磁带库，具体包括冷备份和热备份。冷备份和热备份是物理备份(也称低级备份)，它涉及到组成数据库的文件，但不考虑逻辑内容。

第二类备份方式为逻辑备份，业务数据库采用此种方式，此方法不需要数据库运行在归挡模式下，不但备份简单，而且可以不需要外部存储设备，包括导出/导入（EXPORT/IMPORT）。这种方法包括读取一系列的数据库日志，并写入文件中，这些日志的读取与其所处位置无关。

简单整理MySQL的日志操作命令

转自：http://www.jb51.net/article/76648.htm

1.首先确认你日志是否启用了

?
1
MySQL>show variables like 'log_bin';

如果启用了，即ON那日志文件就在MySQL的安装目录的data目录下

2.怎样知道当前的日志

?
1
MySQL> show master status; 

3.看二进制日志文件用MySQLbinlog

?
1
shell>MySQLbinlog mail-bin.000001 

或者

?
1
shell>MySQLbinlog mail-bin.000001 | tail

4.正确删除MySQL BIN-LOG 日志实操

在mysql中会生大量的如mysq-bin.000001这类日志文件了，这些都是二进制文件了，如果我们是普通的日志没有进行主从配置就可以直接使用reset master进行删除了这个方法很简单，
如果没有主从复制，可以通过reset master的方式，重置数据库日志，清除之前的日志文件：

?
1
mysql> reset master;

还有一各就是在my.cnf里配置。

?
1
expire_logs_days = 3

二进制日志自动删除的天数。这里设置了自动清除3天前的logs。

默认值为0，表示“没有自动删除”。

例

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 按文件:删除mysql-bin.000354之前的日志,不包含mysql-bin.000354
 
MYSQL>purge binary logs to 'mysql-bin.000354';
 
Query OK, 0 rows affected (0.16 sec)
 
# 按时间:删除2011-11-10 00:00:00 之前的日志
 
MYSQL>purge binary logs before '2011-11-10 00:00:00';
 
# 按时间:请理三天之前的日志
 
MYSQL> purge master logs before date_sub(now(), interval 3 day);
 
自动清理日志 :
 
# 修改my.cnf文件配置bin-log过期时间
 
 
expire-logs-days=7
 
max-binlog-size=268435456

如果你是主从mysql日志文件请参考下面方法

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
//删除日志之前，先检查主从服务器当前使用的日志文件,
 
 
//首先登录 要删除日志的服务器的 mysql 终端
#mysql -u root -pxxxxx
 
 
//检查复制主服务器状态
Mysql>show master status
 
 
+------------------+-----------+--------------+----------------------------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB            |
+------------------+-----------+--------------+----------------------------------------+
| mysql-bin.000097 | 541677824 | www   | test,mysql,information_schema |
+------------------+-----------+--------------+----------------------------------------+
 
 
//复制主服务器当前正在使用的日志文件是:mysql-bin.000097
 
 
//检查复制从服务器状态
Mysql>show slave statusG
 
  
 
//复制从服务器当前正在使用的复制主服务器日志文件是:mysql-bin.000103
 
  
 
 
//当前正在使用的日志文件是000097,我需要做的是删除00095号之前的所有日志(预留出最近几天的日志)
Mysql>purge master logs to ‘mysql-bin.000095;
 
  
 
 
#ll /usr/local/mysql/var/
 
 
//从结果中发现，编号000097之前的所有日志都已经删除

2、

转自：http://blog.csdn.net/jesseyoung/article/details/42236615

innodb_file_per_table 可通过SET GLOBAL动态的修改为ON或OFF，也可以在my.cnf中做永久性修改，在my.cnf中修改后生效的话需要重启mysqld服务。

mysql> set global innodb_file_per_table =ON;
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
注：动态修改后仅对后续操作生效，如原来为共享表空间，动态修改为独立表空间后仅新建的表为独立表空间。

3、

使用Xtrabackup备份（不支持ubuntu16.04,14.04）

为了更好地演示, 我们这次使用mariadb-5.5的版本, 使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启

下载安装xtrabackup（https://www.cnblogs.com/zhoujinyi/p/4088866.html）

我们这里通过wget percona官方的rpm包进行安装[root@node1 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   [root@node1 ~]# yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   #需要EPEL源

xtrabackup介绍

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
备份过程快速、可靠；
备份过程不会打断正在执行的事务；
能够基于压缩等功能节约磁盘空间和流量；
自动实现备份检验；
还原速度快；
摘自马哥的文档

xtrabackup实现完全备份

我们这里使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份
使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.

备份过程

[root@node1 ~]# mkdir /extrabackup  #创建备份目录[root@node1 ~]# innobackupex --user=root /extrabackup/ #备份数据###################提示complete表示成功*********************[root@node1 ~]# ls /extrabackup/  #看到备份目录2016-04-27_07-30-48

一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份

[root@node1 ~]# innobackupex --apply-log /extrabackup/2016-04-27_07-30-48/  #指定备份文件的目录#一般情况下下面三行结尾代表成功*****************InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 369661462160427 07:40:11 completed OK![root@node1 ~]# cd /extrabackup/2016-04-27_07-30-48/[root@node1 2016-04-27_07-30-48]# ls -hl  #查看备份文件total 31M-rw-r----- 1 root root  386 Apr 27 07:30 backup-my.cnfdrwx------ 2 root root 4.0K Apr 27 07:30 employees-rw-r----- 1 root root  18M Apr 27 07:40 ibdata1-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1drwx------ 2 root root 4.0K Apr 27 07:30 mysqldrwx------ 2 root root 4.0K Apr 27 07:30 performance_schemadrwx------ 2 root root 4.0K Apr 27 07:30 test-rw-r----- 1 root root   27 Apr 27 07:30 xtrabackup_binlog_info-rw-r--r-- 1 root root   29 Apr 27 07:40 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root  117 Apr 27 07:40 xtrabackup_checkpoints-rw-r----- 1 root root  470 Apr 27 07:30 xtrabackup_info-rw-r----- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

恢复数据

[root@node1 ~]# rm -rf /data/*   #删除数据文件***不用启动数据库也可以还原*************[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/   #恢复数据, 记清使用方法#########我们这里是编译安装的mariadb所以需要做一些操作##########[root@node1 data]# killall mysqld[root@node1 ~]# chown -R mysql:mysql ./* [root@node1 ~]# ll /data/      #数据恢复total 28704-rw-rw---- 1 mysql mysql    16384 Apr 27 07:43 aria_log.00000001-rw-rw---- 1 mysql mysql       52 Apr 27 07:43 aria_log_control-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile0-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile1-rw-rw---- 1 mysql mysql      264 Apr 27 07:43 mysql-bin.000001-rw-rw---- 1 mysql mysql       19 Apr 27 07:43 mysql-bin.index-rw-r----- 1 mysql mysql     2166 Apr 27 07:43 node1.anyisalin.com.err[root@node1 data]# service mysqld restartMySQL server PID file could not be found!                  [FAILED]Starting MySQL..                                           [  OK  ]MariaDB [(none)]> SHOW DATABASES;  #查看数据库, 已经恢复+--------------------+| Database           |+--------------------+| information_schema || employees          || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec

增量备份

#########创建连两个数据库以供测试#####################MariaDB [(none)]> CREATE DATABASE TEST1;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> CREATE DATABASE TEST2;Query OK, 1 row affected (0.00 sec)[root@node1 ~]# innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-04-27_07-30-48/ [root@node1 ~]# ls /extrabackup/2016-04-27_07-57-22/ #查看备份文件total 96-rw-r----- 1 root root   386 Apr 27 07:57 backup-my.cnfdrwx------ 2 root root  4096 Apr 27 07:57 employees-rw-r----- 1 root root 49152 Apr 27 07:57 ibdata1.delta-rw-r----- 1 root root    44 Apr 27 07:57 ibdata1.metadrwx------ 2 root root  4096 Apr 27 07:57 mysqldrwx------ 2 root root  4096 Apr 27 07:57 performance_schemadrwx------ 2 root root  4096 Apr 27 07:57 testdrwx------ 2 root root  4096 Apr 27 07:57 TEST1drwx------ 2 root root  4096 Apr 27 07:57 TEST2-rw-r----- 1 root root    21 Apr 27 07:57 xtrabackup_binlog_info-rw-r----- 1 root root   123 Apr 27 07:57 xtrabackup_checkpoints-rw-r----- 1 root root   530 Apr 27 07:57 xtrabackup_info-rw-r----- 1 root root  2560 Apr 27 07:57 xtrabackup_logfile

BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/extrabackup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

整理增量备份

[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/ --incremental-dir=/extrabackup/2016-04-27_07-57-22/

恢复数据

[root@node1 ~]# rm -rf /data/*   #删除数据[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/     #整理增量备份之后可以直接通过全量备份还原[root@node1 ~]# chown -R mysql.mysql /data/[root@node1 ~]# ls /data/ -ltotal 28732-rw-rw---- 1 mysql mysql     8192 Apr 27 08:05 aria_log.00000001-rw-rw---- 1 mysql mysql       52 Apr 27 08:05 aria_log_controldrwx------ 2 mysql mysql     4096 Apr 27 08:05 employees-rw-r----- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1-rw-r----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile0-rw-r----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile1drwx------ 2 mysql mysql     4096 Apr 27 08:05 mysql-rw-rw---- 1 mysql mysql      245 Apr 27 08:05 mysql-bin.000001-rw-rw---- 1 mysql mysql       19 Apr 27 08:05 mysql-bin.index-rw-r----- 1 mysql mysql     1812 Apr 27 08:05 node1.anyisalin.com.err-rw-rw---- 1 mysql mysql        5 Apr 27 08:05 node1.anyisalin.com.piddrwx------ 2 mysql mysql     4096 Apr 27 08:05 performance_schemadrwx------ 2 mysql mysql     4096 Apr 27 08:05 testdrwx------ 2 mysql mysql     4096 Apr 27 08:05 TEST1drwx------ 2 mysql mysql     4096 Apr 27 08:05 TEST2-rw-r----- 1 mysql mysql       29 Apr 27 08:05 xtrabackup_binlog_pos_innodb-rw-r----- 1 mysql mysql      530 Apr 27 08:05 xtrabackup_infoMariaDB [(none)]> SHOW DATABASES;  #数据还原+--------------------+| Database           |+--------------------+| information_schema || TEST1              || TEST2              || employees          || mysql              || performance_schema || test               |+--------------------+7 rows in set (0.00 sec)#关于xtrabackup还有很多强大的功能没有叙述、有兴趣可以去看官方文档

4、(先5、后4、)

数据库还原：转自：http://blog.csdn.net/early__xz/article/details/51036817

mysql通过bin-log日志恢复

我们同事在操作数据库的时候不小心删除了一个表里面的内容，这些个内容全是储存的一些用户信息，而且我所在公司也一直没有对数据库进行备份，所以但是就闷逼了，还好开启了bin-log。

恢复是我也遇到了不少问题，首先就盲目的在网上找，网上写的东西都不全，都是给的你一条命令，且并没有解释命令的含义是什么，所以造成花了不少时间，

mysql的bin-log是一个实时同步日志操作的文件，就是说当你把某个数据库下的表删除了也会被记录下来，所以恢复恢复的命令中要加参数--stop-datatime这个参数是在你删除这个数据库之前，例如你在早上9点1分的时候吧数据删了，那你就要指定在这个时间之前的时间，例如9:00 再一个就是恢复数据库的时候一定要知道bin-log恢复只能恢复整个库，就是说例如你有一个库叫test，在这个库下有tesr_table这个表，你不小心吧这个表里面的内容删了，然后直接回复是不成功的，需要的是吧这个数据库整个删除再恢复下面是我恢复时用的命令：

mysqlbinlog --database=MYSQLDATA_test(你要恢复的库名称) --stop-datatime="2016-04-01 09:30:23"（这个时间是在你删除库之前的时间） /var/lib/mysql/mysql-bin.000054（bin-log最近的一个日志） | mysql -u root -p****（指定用什么用户什么密码来操作）

：在很长时间才恢复的话建议先把这个库拷贝一份到新的库中，然后把在线上正在用的服务指定到新的库上，然后再把这个库删除恢复。恢复后把新增的数据拷到恢复的数据库上，然后重新指定数据库。

下面是我自己的内容：

还原的前提：

将workbench中的runhang数据库删除。

开始还原：

先知道全备份数据库在mysql-bin.000037的位置，

结果：

再mysql的友好客户端workbench中往表tb1中插入数据：

insert into tb1 values (4);

后并看master status ，显示的位置

此时，刷新数据库，发现workbench中已经存在数据库runhang，但tb1表中只有id=1,2,3

最后发现tb1中已经有id=4;还原数据库完成。

5、

mysql全量备份、增量备份脚本。

DBFullyBak.sh:

#!/bin/bash# Program#    use mysqldump to Fully backup mysql data per week!# History#    2013-04-27 guo     first# Path#    ....BakDir=/home/zh/backup/mysql/backupLogFile=/home/zh/backup/mysql/backup/bak.logDate=`date +%Y%m%d`Begin=`date +"%Y年%m月%d日 %H:%M:%S"`cd $BakDirDumpFile=$Date.sqlGZDumpFile=$Date.sql.tgzmysqldump -uroot -proot --quick  --flush-logs --delete-master-logs --single-transaction --databases runhang > $DumpFile/bin/tar czvf $GZDumpFile $DumpFile/bin/rm $DumpFileLast=`date +"%Y年%m月%d日 %H:%M:%S"`echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFilecd $BakDir/dailyrm -f *

增量备份：

DBDailyBak.sh

#!/bin/bash# Program#    use cp to backup mysql data everyday!# History#    2013-05-02 guo     first# Path#    ....BakDir=/home/zh/backup/mysql/backup/dailyBinDir=/var/log/mysqlLogFile=/home/zh/backup/mysql/backup/bak.logBinFile=/var/log/mysql/mysql-bin.indexmysqladmin -uroot -proot  flush-logs#这个是用于产生新的mysql-bin.00000*文件Counter=`wc -l $BinFile |awk '{print $1}'`NextNum=0#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。for file in  `cat $BinFile`do        base=`basename $file`        #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./        NextNum=`expr $NextNum + 1`        if [ $NextNum -eq $Counter ]        then                echo $base skip!  >> $LogFile        else                dest=$BakDir/$base                if(test -e $dest)                #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去。                then                        echo  $base exist! >> $LogFile                else                        cp $BinDir/$base $BakDir                        echo $base copying >> $LogFile                fi        fidoneecho `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile

dbbackup.py:

import osimport timeimport datetimedef method_cron():print("aaa")os.system('bash /home/zh/mytest/saler/DBFullyBak.sh');def method_cron1():print("aaabbb")os.system('bash /home/zh/mytest/saler/DBDailyBak.sh')

settings.py:

CRONJOBS=[('00 02 * * *', 'saler.bpscript.method_cron', '>>/home/zh/mytest/time_job.log'),('33 14 * * 2' ,'saler.dbbackup.method_cron','>/dev/null 2>&1'),('48 14 * * 1-6','saler.dbbackup.method_cron1','>/dev/null 2>&1'),]

阅读全文

0 0