精华内容
下载资源
问答
  • (4.3)mysql备份还原——mysql备份策略 1、指定备份策略时需要考虑的点 【1.1】备份周期:2次备份间隔时长 【1.2】备份方式:在备份周期中,使用什么备份方式、备份模式 【1.3】实现方式:自动/手动备份 ...

    (4.3)mysql备份还原——mysql备份策略

     

    1、指定备份策略时需要考虑的点

    【1.1】备份周期:2次备份间隔时长

    【1.2】备份方式:在备份周期中,使用什么备份方式、备份模式

    【1.3】实现方式:自动/手动备份

    【1.4】备份介质:思考好存放介质,介质目录,raid等

    【1.5】保证良好的一致性

    【1.6】备份成本:时间、空间、性能 的消耗

    【1.7】恢复成本:数据恢复消耗时间,数据恢复容忍丢失量,业务宕机时间

     

    2、一般备份策略

    【2.1】小型(10G以内):

      直接拷贝数据库文件(文件系统备份工具cp),适合小型数据库,是最可靠的。

    【2.2】中型(10-100G):

      mysqldump备份数据库(完备+增量备,速度较慢),在myIsam是温备份,InnoDB是热备份

    【2.3】大型(100G以上):

      Xtrabackup && lvs 快照从屋里角度实现几乎热备的完全备份,配合二进制日志备份实现增量备份,速度快适合比较繁忙的数据库。

     

    3、mysql灾难恢复的可能情况

    【3.1】系统崩溃,只剩下数据文件

    【3.2】误删delete、drop、truncate表,以及mdf/frm,ibd ibdata1文件变成0字节

    【3.3】各种mysql错误的修复:如Mysql无法启动,事务死锁,ibdata1文件无法启动等等

    【3.4】mysql数据库中数据文件ibdata1和ibd出现坏块

    【3.5】mysql数据文件被误删、被覆盖、被老备份数据覆盖

    【3.6】磁盘阵列上崩溃,或者严重损坏

    【3.7】ibdata1文件丢失或者被覆盖,IBD表文件丢失以及被覆盖。

    转载于:https://www.cnblogs.com/gered/p/10668339.html

    展开全文
  • 前几天网站数据库因为一个误操作导致数据全部丢失,更加悲剧的是由于疏忽只有几个月前的备份,而...备份策略是:每周进行一次完全备份,每天一次增量备份。完全备份采用的是mysqldump,增量备份是基于二进制日志的。...
    前几天网站数据库因为一个误操作导致数据全部丢失,更加悲剧的是由于疏忽只有几个月前的备份,而最近几个月网站内容增加了很多(出来混迟早是要还的啊!!!),万幸的是在配置服务器的时候打开了二进制日志。于是用了一天一夜从几十G的日志文件中恢复了数据。亡羊补牢,赶紧配置上了完全备份和增量备份。备份策略是:每周进行一次完全备份,每天一次增量备份。完全备份采用的是mysqldump,增量备份是基于二进制日志的。配置自动备份的过程中查了不少资料,感觉网上的都是重复的资料,解释也不是很清楚,下面把查到的资料和自己的一些理解记录下来,方便自己做个记录也给需要的提供点方便。
    数据库:mysql 5.1.54
    数据库引擎:MyISAM([color=red]下面的备份仅适用于MyISAM[/color],而且已经在线上服务器运行有效,InnoDB有一点修改,请自行查找并测试)
    操作系统:ubuntu10.04

    完全备份:
    完全备份采用的命令:
    mysqladmin -u XXX -pXXX flush-tables
    mysqldump -u XXX -pXXX --master-data=2 --flush-logs --delete-master-logs --databases db1 db2 > backup.sql

    其中需要注意的是-p后面紧接着的是密码(中间没有空格),这样就不用手动输入密码了。第一行是为备份做准备,目的是mysql执行flush-tables命令,将缓存中的数据写入到文件中。第二行是主要的备份命令。 --master-data=2的意思是在生成的备份文件开头加入此次备份截至到二进制日志的编号,为以后的增量备份做准备。此外需要注意的是使用--master-data[=#]参数时默认使用了参数--lock-all-tables on。而--lock-all-tables on参数的意思是通过试用一个全局读锁锁定整个mysql数据库,同时这个参数mysqldump的第二个参数--flush-logs也有影响。--flush-logs是新建二进制日志。在同时备份多个数据库时如果有多个数据库(如例子中的db1 db2)则新建多个二进制日志(新建日志数目与数据库个数相同),但是如果有参数--lock-all-tables on,则无论多少个数据库只新建一个二进制日志。--delete-master-logs删除除了新生成的二进制日志外所有的日志文件。因为随着网站运行日志文件会越来越大,需要删除以释放空间。至于-u,-p,-databases命令就不用解释了。
    我写的数据库完整备份shell脚本

    #!/bin/sh
    #使用mysqldump备份数据库
    #备份时间暂定为每周一次,写在crontab中
    today=`date +"%Y_%m_%d_%H_%M_%S"`
    #backupTimes是备份保留份数。
    backupTimes=4
    delete()
    {
    i=0
    for fullBackFill in `ls -t full*`
    do
    i=$((i+1))
    if [ $backupTimes -lt $i ]
    then
    rm $fullBackFill
    fi
    done
    }

    backupbasedir=/home/ftp/backup/full/
    cd $backupbasedir
    today_bkdir=$backupbasedir"full"$today".sql"

    mysqladmin -u XXX -pXXX flush-tables
    #mysqldump参数:--master-data=2是为了在mysqldump导出的文件开头写上pos,供恢复用。另外该参数默认执行--lock-all-tables
    #这次备份会删除以前的二进制日志
    mysqldump -u XXX -pXXX --master-data=2 --flush-logs --delete-master-logs --databases db1 db2 > $today_bkdir
    #删掉早于4次(这个数由backupTimes决定)的备份
    delete


    增量备份:
    增量备份使用的命令

    mysqladmin -u XXX -pXXX flush-tables
    mysqladmin -u XXX -pXXX flush-logs

    命令作用已经在上面讲过,不再赘述。
    增量备份shell脚本

    #!/bin/sh

    mysqladmin -u XXX -pXXX flush-tables
    mysqladmin -u XXX -pXXX flush-logs
    backupDir=/home/ftp/backup/incremental/
    time=`date +%Y_%m_%d_%H_%M_%S`
    timeFolder=$backupDir$time"/"
    echo $timeFolder
    if [ -e $timeFolder ]
    then
    echo
    else
    mkdir -p $timeFolder
    fi
    i=0
    for binfile in `ls -t /usr/local/mysql/var/mysql-bin.[0-9]*`
    do
    i=$((i + 1))
    if [ 1 -eq $i ]
    then
    echo "The new generated bin-log: "$binfile
    else
    filename=$(basename $binfile)
    # echo $binfile
    mv $binfile $timeFolder$filename
    fi
    done


    [b]需要注意的是备份脚本由于环境变量,权限配置等等原因只适用于本服务器,贴出来是让给大家一个参考,也希望反馈些建议。请参考脚本结合各自环境使用。
    这里只是简要介绍,如果想理解的更详细更深刻请[color=red]查询mysql手册备份与恢复部分和mysqldump命令手册[/color],上面有详细介绍,比网上千篇一律的介绍更详细更权威,博客里介绍的只是单机数据库备份,分布式备份在手册上也有详细介绍[/b]
    展开全文
  • mysql备份还原

    2019-05-31 23:21:43
    14、 备份还原 14.1、 备份的目的 做灾难恢复:对损坏的数据进行恢复和还原 需求改变:因需求改变而需要把数据还原到改变以前 测试:测试新功能是否可用 14.2、 备份需要考虑的问题 可以容忍丢失多长时间的数据; ...

    14、 备份还原
    14.1、 备份的目的

    做灾难恢复:对损坏的数据进行恢复和还原
    需求改变:因需求改变而需要把数据还原到改变以前
    测试:测试新功能是否可用

    14.2、 备份需要考虑的问题

    可以容忍丢失多长时间的数据;
    恢复数据要在多长时间内完;
    恢复的时候是否需要持续提供服务;
    恢复的对象,是整个库,多个表,还是单个库,单个表。

    14.3、 备份的类型

    14.3.1、 1、根据是否需要数据库离线

    冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;
    温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;
    热备(hot backup):备份的同时,业务不受影响。

    注:

    1、这种类型的备份,取决于业务的需求,而不是备份工具
    2、MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具

    14.3.2、 2、根据要备份的数据集合的范围
    完全备份:full backup,备份全部字符集。
    增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。
    差异备份:differential backup 上次完全备份以来改变了的数据。
    建议的恢复策略:
    完全+增量+二进制日志
    完全+差异+二进制日志

    14.3.3、 3、根据备份数据或文件

    物理备份:直接备份数据文件

    优点:

    备份和恢复操作都比较简单,能够跨mysql的版本,
    恢复速度快,属于文件系统级别的

    建议:

    不要假设备份一定可用,要测试
    mysql>check tables;检测表是否可用
    逻辑备份: 备份表中的数据和代码

    优点:

    恢复简单、
    备份的结果为ASCII文件,可以编辑
    与存储引擎无关
    可以通过网络备份和恢复

    缺点:

    备份或恢复都需要mysql服务器进程参与
    备份结果占据更多的空间,
    浮点数可能会丢失精度
    还原之后,缩影需要重建

    14.4、 备份的对象

    1、 数据;
    2、配置文件;
    3、代码:存储过程、存储函数、触发器
    4、os相关的配置文件
    5、复制相关的配置
    6、二进制日志

    14.5、 注意事项:
    使用的时候MySQL当要导入或者导出数据量大的库的时候,用PHPMYADMIN甚至MySQL Administrator这些工具都会力不从心,这时只能使用MySQL所提供的命令行工具mysqldump进行备份恢复。数据量太大的时候不推荐使用,可支持MyISAM,InnoDB
    14.6、 备份和恢复的实现
    逻辑备份工具mysqldump(温备份)
    特点:适用于小型企业,
    适用于数据库中数据少的情况,
    导出数据时锁表(不允许写入数据)
    支持MyISAM,InnoDB

    14.6.4、 MySQL数据的导出和导入工具:mysqldump
    MySQLdump常用
    备份:语法: mysqldump [TMPIONS] database [tables] >导出的文件名.sql
    mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

    还原:系统命令行: MySQL -uroot -p123456 <f:\all.sql

    常见选项:
    –all-databases, -A: 备份所有数据库
    –databases, -B: 用于备份多个数据库,如果没有该选项,mysqldump把第一个名字参数作为数据库名,后面的作为表名。使用该选项,mysqldum把每个名字都当作为数据库名。

    –force, -f:即使发现sql错误,仍然继续备份
    –host=host_name, -h host_name:备份主机名,默认为localhost
    –no-data, -d:只导出表结构
    –password[=password], -p[password]:密码
    –port=port_num, -P port_num:制定TCP/IP连接时的端口号
    –quick, -q:快速导出
    –tables:覆盖 --databases or -B选项,后面所跟参数被视作表名
    –user=user_name, -u user_name:用户名
    –xml, -X:导出为xml文件
    备份:

    1.备份全部数据库的数据和结构

    mysqldump -uroot -p123456 -A >F:\all.sql

    2.备份全部数据库的结构(加 -d 参数)

    mysqldump -uroot -p123456 -A-d>F:\all_struct.sql

    3.备份全部数据库的数据(加 -t 参数)

    mysqldump -uroot -p123456 -A-t>F:\all_data.sql

    4.备份单个数据库的数据和结构(,数据库名mydb)

    mysqldump -uroot-p123456 mydb>F:\mydb.sql

    5.备份单个数据库的结构

    mysqldump -uroot -p123456 mydb-d>F:\mydb.sql

    6.备份单个数据库的数据

    mysqldump -uroot -p123456 mydb-t>F:\mydb.sql

    7.备份多个表的数据和结构(数据,结构的单独备份方法与上同)

    mysqldump -uroot -p123456 mydb t1 t2>f:\multables.sql

    8.一次备份多个数据库

    mysqldump -uroot -p123456 --databases db1 db2>f:\muldbs.sql
    还原

    还原部分分(1)mysql命令行source方法 和 (2)系统命令行方法

    1.还原全部数据库:

    (1) mysql命令行:mysql>source f:\all.sql

    (2) 系统命令行: mysql -uroot -p123456 <f:\all.sql

    2.还原单个数据库(需指定数据库)

    (1) mysql>use mydb

    mysql>source f:\mydb.sql

    (2) mysql -uroot -p123456 mydb <f:\mydb.sql

    3.还原单个数据库的多个表(需指定数据库)

    (1) mysql>use mydb

    mysql>source f:\multables.sql

    (2) mysql -uroot -p123456 mydb<f:\multables.sql

    4.还原多个数据库,(一个备份文件里有多个数据库的备份,此时不需要指定数据库)

    (1) mysql命令行:mysql>source f:\muldbs.sql

    (2) 系统命令行: mysql -uroot -p123456<f:\muldbs.sql
    写个shell备份脚本
    [root@cong11 ~]# vim mysql-autoback.sh
    #!/bin/bash
    export LANG=en_US.UTF-8
    savedir=/database_back/
    cd “savedir&quot;time=&quot;savedir&quot; time=&quot;(date +”%Y-%m-%d")"
    mysqldump -u root -p123456 book > book-"$time".sql
    再添加计划任务就ok
    14.6.5、 xtrabackup备份工具使用
    xtrabackup(开源)简介(大型数据库)
    我们知道,针对InnoDB存储引擎,MySQL本身没有提供合适的热备工具,ibbackup虽是一款高效的首选热备方式,但它是是收费的。好在Percona公司给大家提供了一个开源、免费的Xtrabackup热备工具,它可实现ibbackup(收费)的所有功能,并且还扩展支持真正的增量备份功能,是商业备份工具InnoDB Hotbackup的一个很好的替代品。
    Xtrabackup包括两个主要工具:Xtrabackup和innobackupex:
    Xtrabackup只能备份InnoDB和XtraDB两种引擎表,而不能备份MyISAM数据表。
    innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等,智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份,需要和xtrabackup结合起来实现全备

    xtrbackup 安装
    (mysql5.7.20需安装最新版XtraBackup2.4.9)
    上传软件包
    [root@cong11 ~]# ls
    anaconda-ks.cfg boost_1_59_0.tar.bz2 mysql-community-5.7.26-1.el7.src.rpm
    book_utf8.sql mysql-5.7.26 Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
    网上下载地址
    https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
    解压
    [root@cong11 ~]# tar -xf Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
    安装解决并解决依赖
    配置好本地yum源
    [root@cong11 ~]# yum -y install percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
    在这里插入图片描述
    报错:需要libev.so.4()依赖
    解决:
    上传libev-4.03-3.el6.x86_64.rpm包
    或者
    网络下载地址:http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm
    [root@cong11 ~]# rpm -ivh libev-4.03-3.el6.x86_64.rpm
    [root@cong11 ~]# yum install -y percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
    修改数据目录
    注意:安装完成以后记得更改你的/etc/my.cnf配置文件制定数据目录,因为Xtrabackup是根据你的/etc/my.cnf配置文件来获取你备份的文件,比如在/etc/my.cnf的[mysqld] 下添加datadir=/usr/local/mysql/data,然后重启mysql
    [root@cong11 ~]# vim /etc/my.cnf
    [mysqld]
    datadir=/data/mysql/data
    [root@cong11 ~]# systemctl restart mysqld #重启msyql
    xtrbackup使用
    我们一般使用innobackupex脚本
    innobackupex是perl脚本对xtrabackup的封装,和功能扩展。
    备份准备工作:
    权限和链接
    xtrabackup需要连接到数据库(需要数据库用户)和datadir操作权限(需要系统用户)。
    xtrabackup或者innobackupex在使用过程中涉及到2类用户权限:
    1、系统用户,用来调用innobackupex或者xtrabackup
    2.数据库用户,数据库内使用的用户
    连接到服务:innobackupex或者xtrabackup通过-user和-password连接到数据库服务
    $ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/
    $ innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 - 压缩
    解释:stream:指定归档压缩
    $ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/
    其他连接选项:
    在这里插入图片描述
    端口,socket,主机ip
    [root@mysql-srv1 ~]# innobackup --help | grep “socket” //查看有关socket的帮助信息
    可以单独创建用来备份数据库的用户,安全,并赋予对应的权限
    全备和全备还原
    使用innobackupex创建全备
    创建全备:
    #mkdir /db_backup

    innobackupex --user=root --password=123456 -S /usr/local/mysql/mysql.socket /db_backup/

    解释:-S:指定套接字文件

    在这里插入图片描述
    或:
    [root@cong11 ~]# innobackupex --user=root --password=123456 /db_backup/ 2>>/db_backup/db_backup.log
    #把输出信息从定向到/tmp/db_backup/db_backup.log文件

    [root@cong11 ~]# ls /tmp/db_backup/
    2019-05-21_12-19-25 2019-05-21_12-21-44 db_backup.log
    内部机制:在备份的时候innobackupex会调用xtrabackup来备份innodb表,并复制所有的表定义,其他引擎的表(MyISAM,MERGE,CSV,ARCHIVE)。
    其他选项:
    –no-timestamp,指定了这个选项备份会直接备份在BACKUP-DIR,不再创建时间戳文件夹。
    –default-file,指定配置文件,用来配置innobackupex的选择
    innobackupex --user=root --password=123456 --no-timestamp /tmp/db_backup/full
    (使用–no-timestamp时,后面的这个full目录必须跟上且不能提前自己建立,它由innobackupex自动建立,否则会报错innobackupex: Error: Failed to create backup directory)
    使用innobackupex还原备份
    1、停止数据库
    [root@cong11 ~]# /etc/init.d/mysqld stop
    2、移走数据库数据(危险不要删除数据库)
    [root@cong11 ~]# mv /data/mysql/data /mnt
    3、使用工具还原数据
    [root@cong11 ~]# innobackupex --copy-back /db_backup/2019-05-21_12-21-44/
    4、查看权限
    [root@cong11 ~]# ll -d /data/mysql/data
    drwxr-x— 7 root root 192 5月 21 14:27 /data/mysql/data/
    5、修改权限
    [root@cong11 ~]# chowm -R mysql:mysql /data/mysql/data
    6、启动mysql
    [root@cong11 ~]# systemctl restart mysqld
    注:datadir必须是为空的,innobackupex --copy-back不会覆盖已存在的文件,还要注意,还原时需要先关闭服务,如果服务是启动的,那么就不能还原到datadir。
    7、查看数据是否完整
    [root@cong11 ~]# mysql -uroot -p123456 -e “select * from book.books;”
    在这里插入图片描述
    创建增量备份和还原
    增量备份作用:减少备份数据重复,节省磁盘空间,缩短备份时间
    增量备份的实现,依赖于innodb页上面的LSN(log sequence number),每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。
    创建增量备份
    1:首先创建全备
    在创建增量备份之前需要一个全备,不然增量备份是没有意义的。
    [root@cong11 ~]# innobackupex --user=root --password=123456 -S /usr/local/mysql/mysql.sock /db_backup/
    #ll /db_backup/ //查看结果
    检查备份文件夹下的xtrabackup-checkpoints,查看信息
    [root@cong11 ~]# cat /tmp/db_backup/2019-05-21_12-21-44/xtrabackup_checkpoints
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 2622668 #从2622668开始备份
    last_lsn = 2622677 #到2622677结束,开始在这里创建第一个增量备份
    compact = 0
    recover_binlog_info = 0
    flushed_lsn = 2622677
    2、插入一些数据到表里面
    mysql> use HA;
    mysql> select * from students;
    mysql> desc students;
    在这里插入图片描述
    mysql> insert into students values(1,‘zheng’,20,‘M’);
    3、使用-incremental创建增量备份
    语法:
    innobackupex --user=root --password=123456 --incremental /增量1路径 --incremental-basedir=全备路径 后面指定为哪个全备上进行增量备份
    解释:增量备份时需要查看xtrabackup_checkpoints 里的last_lsn号,根据这个号进行增量备份
    [root@cong11 ~]# innobackupex --user=root --password=123456 -S /usr/local/mysql/mysql.sock --incremental /db_backup/ --incremental-basedir=/db_backup/2019-05-21_12-21-44/
    //如果有问题,需要指定套接字文件
    4、再查看LSN<日志序列号>
    [root@cong11 ~]# cat /tmp/db_backup/2019-05-21_15-26-55/xtrabackup_checkpoints
    backup_type = incremental
    from_lsn = 2622668 #全备的最后编号,从这个编号开始备份
    to_lsn = 2623258
    last_lsn = 2623267 #编号更新
    compact = 0
    recover_binlog_info = 0
    flushed_lsn = 2623267
    5、增量备份创建的替代方法
    可以使用指定-incremental-lsn来代替-incremental-basedir的方法创建增量备份。
    [root@cong11 ~]# innobackupex --user=root --password=123456 --incremental /tmp/db_backup/ --incremental-lsn=2622668
    注意:xtrabackup只会影响xtradb或者innodb的表,其他引擎的表在增量备份的时候只会复制整个文件,不会差异。
    增量备份恢复
    1、停止mysql,删除数据
    [root@cong11 ~]# systemctl stop mysqld
    [root@cong11 ~]# rm -rf /data/mysql/data
    2、在所有备份目录下重做已提交的日志
    执行命令,把所有备份日志 集中在全备目录中,然后使用修改后的全备去恢复数据库。
    语法:
    第一步:
    步骤(1)innobackupex --apply-log --redo-only BASE-DIR
    步骤(2)innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
    步骤(3)innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
    其中BASE-DIR是指全备目录,INCREMENTAL-DIR-1是指第一次的增量备份,INCREMENTAL-DIR-2是指第二次的增量备份,以此类推。
    这里要注意的是:最后一步的增量备份并没有–redo-only选项!还有,可以使用–use_memory提高性能。
    如果只有一个增量备份,只需要执行步骤(1)和步骤(3)即可
    以上语句执行成功之后,最终数据在BASE-DIR(即全备目录)下。
    [root@cong11 ~]# innobackupex --apply-log --redo-only /db_backup/2019-05-21_12-21-44
    [root@cong11 ~]# innobackupex --apply-log /db_backup/2019-05-21_12-21-44 --incremental-dir=/db_backup/2019-05-21_15-26-55/
    第一步完成之后,我们开始第二步:回滚未完成的日志:
    解释:在执行备份的时候,如果有写入数据会写入到重做日志里,回滚未完成的日志时,会将写入到重做日志里的数据写入数据库里。
    语法:innobackupex --apply-log BASE-DIR
    innobackupex --apply-log /db_backup/2019-05-21_12-21-44
    上面执行完之后,BASE-DIR里的备份文件已完全准备就绪,最后一步是拷贝:
    语法:innobackupex --copy-back BASE-DIR
    innobackupex --copy-back /db_backup/2019-05-21_12-21-44
    4、恢复mysql权限
    [root@cong11 ~]# chown -R mysql:mysql /data/mysql/data
    5、启动mysql
    [root@cong11 ~]# systemctl restart mysqld
    6、测试数据
    [root@cong11 ~]# mysql -uroot -p123456 -e"select * from HA.students;"
    在这里插入图片描述

    展开全文
  • MySQL备份还原操作

    万次阅读 2019-03-18 11:50:28
    MySQL备份还原操作 目标  备份的概念  Mysqldump备份操作  MySQL还原操作  MySQL双机热备份 概述  在数据库表丢失或损坏的情况下,备份数据库是很重要的。如果发生系统崩溃,能够将表尽可能丢失最少的...

    MySQL备份和还原操作

    目标

    备份的概念
    Mysqldump备份操作
    MySQL还原操作
    MySQL双机热备份

    概述

    在数据库表丢失或损坏的情况下,备份数据库是很重要的。如果发生系统崩溃,能够将表尽可能丢失最少的数据恢复到崩溃发生时的状态。

    备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件

    Mysqldump备份

    导出库表(mysqldump)
    mysqldump -u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables > 路径
    mysqldump -hhostname -uusername -p dbname tbname>xxxx.sql
    ** 按指定条件导出数据库表内容。(-w选项 –where)
    mysqldump -hhostname -uusername-p dbname tbname -w’id >= 1 and id<= 10000’–skip-lock-tables > xxxx.sql

    mysqldump -hhostname -uusername -p dbname tbname --where=‘unit_id >= 1 and unit_id <= 10000’> ~/xxxx.sql

    Mysqldump备份库表详细举例

    1. 导出整个数据库
      mysqldump -u 用户名 -p数据库名 > 导出的文件名
      mysqldump -u breezelark-p mydb > mydb.sql
    1. 导出一个表(包括数据结构及数据)
      mysqldump -u 用户名 -p数据库名 表名> 导出的文件名
      mysqldump -u lingxi -p mydb mytb> mytb.sql
    1. 导出一个数据库结构(无数据只有结构)
      mysqldump -u lingxi -p -d --add-drop-table mydb >mydb.sql
      -d 没有数据–add-drop-table 在每个create语句之前增加一个drop table

    数据库备份

    完全备份数据库
    增量备份

    完全备份数据库

    mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >fullbackup_sunday_11_PM.sql
    在sql文件中我们会看到两行:
    – Position to start replication or point-in-time recovery from
    – CHANGE MASTER TO MASTER_LOG_FILE=’bin-log.000002′, MASTER_LOG_POS=107;
    第二行包含了我们需要的信息,是指备份后所有的更改将会保存到bin-log.000002二进制文件中。

    增量 备份

    然后在星期一下午11点我们来做一次增量备份:
    mysqladmin flush-logs
    这时将会产生一个新的二进制日志文件bin-log.000003,bin-log.000002则保存了自星期天下午11点到现在的所有更
    改,我们只需要把这个文件备份到安全的地方就行了。然后星期二我们又做增量备份,还是执行同样的命令,这时我们
    保存bin-log.000003文件。
    如果mysqld在运行则停止,然后用–log-bin[=file_name]选项来启动。二进制日志文件中提供了执行mysqldump之后对数据库的更改进行复制所需要的信息。
    对于InnoDB表,可以进行在线备份,不需要对表进行锁定
    MySQL支持增量备份:需要用–log-bin选项来启动服务器以便启用二进制日志“二进制日志”。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改),应使用FLUSH LOGS回滚二进制日志。然后,你需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份;恢复时,按照下面的解释应用。下次进行完全备份时,还应使用FLUSH LOGS–flushlogs回滚二进制日志。
    “mysqldump:数据库备份程序”,

    数据库还原

    备份的程序,在出现崩溃后,可以恢复数据:
    · 操作系统崩溃
    · 电源故障
    · 文件系统崩溃
    · 硬件问题(硬盘、母板等等)
    该命令不包括mysqldump和mysql程序的–user和—password等选项。应包括必要的选项让MySQL服务器允许你连接它。
    我们假定数据保存在MySQL的InnoDB存储引擎中,支持事务和自动崩溃恢复。我们假定崩溃时MySQL服务器带负载。如果不带负载,则不需要恢复。
    出现操作系统崩溃或电源故障时,我们可以假定重启后硬盘上的MySQLś数据仍可用。由于崩溃,InnoDB数据文件中的数据可能不再保持一致性,但InnoDB读取它的日志并会查到挂起的提交的和未提交的事务清单,它们没有清空到数据文件中。InnoDB自动卷回未提交的事务,并清空到它的数据文件中。通过MySQL错误日志将该恢复过程相关信息传达给用户

    数据恢复

    Mysql -u用户名 –p密码 数据库名 < 导出的文件名

    恢复备份

    比如星期三中午12点出现了故障,这时需要恢复,我们首先导入星期天的完整备份:
    mysql < fullbackup_sunday_3_AM.sql
    接着我们导入星期一和星期二的增量备份:
    mysqlbinlog bin-log.000002 bin-log.000003 | mysql
    注意: “mysqlbinlog:用于处理二进制日志文件的实用工具”

    备份策略

    对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份
    出现操作系统崩溃或电源故障时,InnoDB自己可以完成所有数据恢复工作。应遵从下面的指导:
    · 一定用–log-bin或甚至–log-bin=log_name选项运行MySQL服务器,其中日志文件名位于某个安全媒介上,不同于数据目录所在驱动器。如果你有这样的安全媒介,最好进行硬盘负载均衡(这样能够提高性能)。
    · 定期进行完全备份,使用mysqldump命令进行在线非块备份。
    · 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

    备份策略

    对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份
    出现操作系统崩溃或电源故障时,InnoDB自己可以完成所有数据恢复工作。应遵从下面的指导:
    · 一定用–log-bin或甚至–log-bin=log_name选项运行MySQL服务器,其中日志文件名位于某个安全媒介上,不同于数据目录所在驱动器。如果你有这样的安全媒介,最好进行硬盘负载均衡(这样能够提高性能)。
    · 定期进行完全备份,使用mysqldump命令进行在线非块备份。
    · 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

    MySQL双机热备份

    双机热备份通过日志文件来传输入服务器上数据的变化。主服务器进程在数据被更新时触发。并将相应的日志文件发送到从服务器。从服务器进程接收到主服务器发送的日志文件,做出相应的更改操作

    双机热备份的工作原理

    在这里插入图片描述

    主服务器的配置

    找到配置文件my.ini
    编辑my.ini文件,定义到mysqld,加入内容
    Server-id=1
    Log-bin=c:/log-bin.log
    Binlog-do-db=dbname
    创建dbname数据库
    进入mysql操作,为从服务器授权访问数据库的用户名和密码
    grant replication slave on . to ‘root’@从服务器IP identified by ‘密码’
    重启MySQL服务器,使更改生效
    重新进入到MySQL的命令,执行命令检测配置是否生效:
    Show master status\G

    从服务器的配置

    在从服务器中创建与主服务器中相同的数据库
    找到从服务器中的my.ini文件,定位到[mysqld],加入以下内容:
    Server-id=2
    Master-host=主服务器IP
    Master-port=3306(主服务器端口号)
    Master-user=root(主服务器用户名)
    Master-password=密码(主服务器密码)
    Master-connect-retry=60(等待尝试重新连接的秒数)
    重新启动MySQL服务器,使配置生效
    进入到从服务器MySQL操作,执行启动进程
    Slave start

    展开全文
  • MySQL备份还原策略

    2008-08-31 13:49:00
    1直接拷贝数据库文件直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在备份文件前,执行以下 SQL 语句:FLUSH TABLES WITH READ LOCK;也就是把内存中的数据都...
  • Percona-xtrabackup——mysql备份还原 背景: 前文percona-xtrabackup——mysql备份中使用策略是每天0点进行全备,以后每5分钟在前一次备份的基础上进行一个增量备份,这样还原时最多可能需要还原1个全备、287增量...
  • MySQL备份还原之一

    2014-06-13 17:10:58
    一、备份准备工作 1.查看服务器状态: mysql> \s 2.查看数据目录存放位置 mysql> show variables like '%datadir%'; 3.修改二进制日志的存放位置 (1).建立一目录用于存放二进制日志 mkdir /mybinlog chown ...
  • mysql备份还原

    2019-04-24 16:24:41
    mysql备份还原 备份:副本,备份的目的是为了防止数据丢失或者数据库出现问题时,进行恢复(备份存放在另一个盘) 备份类型: 热备份、温备份和冷备份 热备份:读写不受影响 温备份:仅可以执行读操作 冷备份...
  • MySQL 备份还原详解

    2017-05-19 13:37:28
    大纲 一、MySQL备份类型 二、MySQL备份都备份什么? 三、MySQL备份工具 四、MySQL备份策略 五、备份准备工作 六、备份策略具体演示 ...
  • 本文讨论 MySQL备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb,文中设计的 MySQL 版本为 5.0.22。 目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL ...
  • MySQL备份还原(转)

    2009-07-02 17:58:00
    本文讨论 MySQL备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb,文中设计的 MySQL 版本为 5.0.22。目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL ...
  • mysql备份还原

    2018-06-02 13:45:30
    mysql备份还原备份容易,还原难。一般情况,都是没事备份,出事还原。备份:给数据创建一个数据副本,在出现任何数据故障时,保证数据不丢失。RAID1,RAID10,是在硬件上保证数据备份,但是在逻辑上,并不能进行...
  • 摘要本期月报是SQL Server备份还原专题分享系列的第六期,打算分享给大家如何监控SQL Server备份还原进度。场景引入由于SQL Server备份还原操作是重I/O读写操作,尤其是当数据库或数据库备份文件比较大的到时候。...
  • mysql 备份还原

    2019-10-07 14:16:37
    本文讨论 MySQL备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb,文中设计的 MySQL 版本为 5.0.22。 目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL ...
  • MySQL数据库备份还原 为什么要备份   灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景 备份注意要点   能容忍最多丢失多少数据   恢复数据需要在多长时间内完成   需要...
  • mysql多种备份还原

    千次阅读 2018-08-12 03:38:44
    所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略。 ...
  • MySQL备份还原

    2017-06-04 15:20:00
    一 理解MySQL备份与恢复 备份:存储的数据副本;原始数据,持续改变。 恢复:把副本应用到线上系统;仅能恢复至备份操作时刻的数据状态。 时间点恢复:binary logs。 为什么备份?1.灾难恢复:硬件故障(冗余)、软件...
  • 数据库备份,数据库为school,素材如下: 1.创建student和score表 CREATE TABLE student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , sex VARCHAR(4) , birth YEAR, department ...
  • MySQL备份与恢复策略

    万次阅读 2018-07-27 14:12:28
    MySQL备份与恢复策略 逻辑备份与恢复 什么是数据库逻辑备份? 常用的逻辑备份 逻辑备份恢复方法 物理备份与恢复 什么是数据库物理备份 MySQL物理备份所需文件 各存储引擎常用物理备份方法 ...
  • mysql数据库备份还原

    2017-09-21 16:59:16
    增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着,第一次增量备份的对象是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一...
  • MySQL:(十二)备份还原 (一)关系型数据库基础 (二)MySQL安装 (三)管理数据库和表 (四)用户和权限管理 (五)函数,存储过程和触发器 (六)MySQL架构 (七)存储引擎 (八)MySQL服务器选项,系统和状态...
  • MySQL 备份还原

    2015-12-25 18:14:37
    大纲一、备份类型分类二、各备份工具对比图三、mysqldump实现备份四、lvm快照实现备份五、xtrabackup实现备份一、备份类型分类(1)、根据备份时服务器是否能在线分类热备份:hot backup,读、写不受影响温备份:warm ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,547
精华内容 3,018
关键字:

mysql备份还原策略

mysql 订阅