MySQL备份与恢复

作者: 5473 分类: MySQL 发布时间: 2015-03-27 15:49 阅读数:530
冷备份(停机)
冷备份通常用在非核心业务,这类业务一般都允许中断,冷备的优点是速度快,恢复也简单。可通过直接复制物理文件实现冷备!
备份:
  1. 关闭mysql服务进程
    sudo service mysql stop
  2. 把data数据目录和日志目录复制到其他磁盘
    sudo mkdir /bak
    sudo cp -r /var/lib/mysql /bak
恢复:
  1. 用上面磁盘中复制的数据替换现有数据目录
    sudo rm -rf /var/lib/mysql
    sudo cp -r /bak/mysql /var/lib/
    chown -R mysql:mysql /var/lib/mysql
  2. 启动mysql服务进程
    sudo service mysql start 
    可能报错(CentOS默认打开SELinux):
    [nbsl@mysql_test1 mysql]# sudo service mysql start
    Starting MySQL.The server quit without updating PID file (/[FAILED]mysql/mysql_test1.nbsl.com.pid).

    解决方案1:关闭SELinux:
    sudo vi /etc/sysconfig/selinux
    修改
    SELINUX=enforcing  为 SELINUX=disable

    解决方案2:修改/var/lib/mysql目录selinux的content type
    sudo chcon -R -h -t mysqld_db_t /var/lib/mysql

逻辑备份(锁表,只读)
逻辑备份通常用于数据迁移或者数据量很小时,它采用的是数据导出的备份方式。
备份:
  • 导出所有的数据库
    mysqldump -utest -ptest -q --single-transaction -A >all.sql
  • 导出其中的某几个数据库
    mysqldump -utest -ptest -q --single-transaction -B test employees >test_employees.sql

    Mysqldump参数说明
    -q , –quick 不缓冲查询,直接导出到标准输出。默认为打开状态,使用–skip-quick取消该选项。
    -A , –all-databases 
    导出全部数据库。
    -B , –databases 导出几个数据库。参数后面所有名字参量都被看作数据库名。
恢复:
  • Shell命令行方式
    mysql -utest -ptest <all.sql
    mysql -utest -ptest yourdb<yourdb.sql
  • mysql命令行方式
    source all.sql;
一般情况下,可设置星期日凌晨1:00进行全备份,星期一至星期六凌晨1:00进行增量备份。

定时设置:

sudo crontab -u root -e
0 1 * * 0 /home/nbsl/mysql_full_bak.sh
0 1 * * 1-6 /home/nbsl/mysql_increase_bak.sh
:wq

sudo crontab -u root -l 


全备与增备脚本:
逻辑全备份脚本:mysql_full_bak.sh

逻辑增量备份脚本:mysql_increase_bak.sh


恢复操作:

比如星期二中午12点出现了故障,这时需要恢复,我们首先导入星期日的完整备份:

cd /backup/mysql/fullBak/

gunzip -d employees_test_150327095954.sql.gz

mysql -utest -ptest < employees_test_150327095954.sql

接着我们导入星期一和星期二的增量备份:

cd /backup/mysql/increaseBak/15-03-27 

mysqlbinlog mysql-bin.000002 mysql-bin.000003 | mysql -utest -ptest

这时我们已经恢复了所有备份数据,我们还可以找到mysql-bin.000004(/var/lib/mysql/),进一步恢复最新的数据。

指定条件进行恢复:

  • 按指定位置恢复:
    mysqlbinlog –start-position=185 –stop-position=338 mysql-bin.000004| mysql -utest -ptest
  • 按指定时间恢复:
    mysqlbinlog –start-datetime=”2015-03-27 01:00:00″ –stop-datetime=”2015-03-27 11:59:59″ mysql-bin.000004 | mysql -utest -ptest
 

热备份(可读写)

热备份通常用在7*24不间断的重要核心业务,适合数据量比较大的环境,热备份的方式也是直接复制物理文件,与冷备份不同的是,热备份可以不停机,不锁表

Xtrabackup(http://www.percona.com)是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。

Xtrabackup有两个主要的工具:xtrabackup、innobackupex

  • xtrabackup只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表
  • innobackupex是将xtrabackup进行封装的perl脚本,它提供了备份MyISAM表的能力。由于innobackupex的功能更全面完善,所以一般选innobackupex进行备份

Xtrabackup的安装(http://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html)

innobackupex参数说明

  • –defaults-file:指定my.cnf参数文件的位置
  • –apply-log:同xtrabackup–prepare参数,一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。–apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
  • –copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
  • –remote-host=HOSTNAME:通过ssh将备份数据存储到进程服务器上
  • –stream=[tar]:备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会占满你的/tmp目录,可以通过参数–tmpdir指定目录来解决这个问题.
  • –tmpdir=DIRECTORY:当有指定–remote-host or –stream, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
  • –redo-only –apply-log:强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要
  • –use-memory=*:该参数在prepare的时候使用,控制prepareinnodb实例使用的内存
  • –databases=LIST:列出需要备份的databases,如果没有指定该参数,所有包含MyISAMInnoDB表的database都会被备份
  • –slave-info:备份从库, 加上–slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE=”, MASTER_LOG_POS=0
  • –socket=SOCKET:指定mysql.sock所在位置,以便备份进程登录mysql

全量备份(不加 –databases,默认全部数据库)

全量恢复

1) 停止数据库:sudo service mysql stop

2) 删除老数据库中的数据文件和事务日志文件(即清空/var/lib/mysql/下的文件) sudo rm -rf /var/lib/mysql/*

注意:如果未清空老数据,恢复时将报下面的错误

3) 使用–apply-log合并日志,将提交的事务进行重做,将没有提交的事务进行回滚,确保数据文件的一致性

  innobackupex –user=test –password=test –defaults-file=/usr/my.cnf –apply-log –use-memory=1G /bak/fullBak/2015-03-27_13-30-33

4)恢复数据文件

  innobackupex –user=test –password=test –defaults-file=/usr/my.cnf –copy-back/bak/fullBak/2015-03-27_13-30-33 

5) 赋权 chown -R mysql.mysql /var/lib/mysql

6) 启动数据库 sudo service mysql start


增量备份

注意:进行增量备份的前提必须做过全量备份

注意:在执行过增量备份之后再一次进行增量备份时,其–incremental-basedir应该指向上一次的增量备份所在的目录innobackupex –user=test –password=test –defaults-file=/usr/my.cnf –incremental /bak/increBak –incremental-basedir=/bak/increBak/2015-03-27_14-29-33

 
增量恢复
恢复前的准备–合并增量备份
合并完全备份:务必使用–redo-only选项,强制xtrabackup跳过”rollback”
合并第一个增量备份到完全备份:
 合并最后一个增量备份到完全备份:最后一个增量备份不需要加–redo-only,如果最后一个也使用redo-only,将使数据不一致,因而没有执行回滚。
 一旦合并了完全备份和所有增量备份,就可以回滚没有提交的事务。该步骤是可选的,如果没有做,在数据库启动时会自动进行回滚没有提交的事务,但是如果做了此操作,就可以减少服务启动的时间。innobackupex工具不会创建ib_log*文件,如要创建事务日志文件,需使用xtrabackup –prepare,否则在服务启动时进行创建。

开始备份恢复


使用Xtrabackup全备脚本:Xtrabackup_full_bak.sh

使用Xtrabackup增备脚本:Xtrabackup_increase_bak.sh

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注