MySQL备份还原总结


在对数据库备份的时候如果出现Operating system error number 24 in a file operation.这样的错误说明打开的文件太多。

解决方案ulimit -n 1048576

然后在进行对数据库的备份即可

1、介绍

所用的工具为xtrbackup该工具是由perconan公司提供的开源的而且功能非常强大。这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)自动实现备份检验；

(4)还原速度快；

对InnoDB存储引擎实现热备而对于MyISAM存储引擎只能实现温备份

2、安装

其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得。

yum intall --percona-xtrabackup-2.2.5-5027.el7.x86_64.rpm

检查生成的文件

rpm -ql percona-xtrabackup

/usr/bin/innobackupex//实现备份还原的命令

/usr/bin/xbcrypt

/usr/bin/xbstream

/usr/bin/xtrabackup

/usr/share/doc/percona-xtrabackup-2.2.5

/usr/share/doc/percona-xtrabackup-2.2.5/COPYING


3、完全备份


innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/（备份到哪里）


如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;

mysql> FLUSH PRIVILEGES;


（1）xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

（2）xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；



4、准备(prepare)一个完全备份


一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。


nnobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：


# innobackupex --apply-log  /path/to/BACKUP-DIR


在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。


5、从一个完全备份中恢复数据


innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。


# innobackupex --copy-back  /path/to/BACKUP-DIR


请确保如上信息的最行一行出现“innobackupex: completed OK!”。


当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：


# chown -R  mysql:mysql  /data/mysql/



6、使用innobackupex进行增量备份


每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。


要实现第一次增量备份，可以使用下面的命令进行：


# innobackupex --incremental /export/increment --incremental-basedir=BASEDIR


其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/export/increment目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。


需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。


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

(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。

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


于是，操作就变成了：

# innobackupex --apply-log --redo-only BASE-DIR


接着执行：

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1


而后是第二个增量：

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2


其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；


7、恢复数据和完全备份的一样只要准备做好了就可以了


innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。


# innobackupex --copy-back  /path/to/BACKUP-DIR


请确保如上信息的最行一行出现“innobackupex: completed OK!”。


当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：


# chown -R  mysql:mysql  /data/mysql/



下面是两个备份的脚本


完全备份脚本

fullbackup.sh


#!/bin/bash

#Implement a full backup of the MySQL database

#user:congfei@encompass8.com

#

#

ulimit -n 1048576

name=`/usr/bin/ls -ln /export/oldbackup/ | /usr/bin/tail -n 1 | /usr/bin/awk {print'$9'}`

nameu=`/usr/bin/basename /export/oldbackup/$name`

user=wcfoffice

password=wcfwcfwcf

backupdir=/export/backup

num=`/usr/bin/ls -l /export/backup | /usr/bin/wc -l`

/bin/mysql -u$user -p$password -e "flush logs;"

/bin/echo "0" > /tmp/test.yon

/usr/bin/innobackupex --user=$user --password=$password $backupdir  && /bin/echo "1" > /tmp/test.yon && /bin/echo "To complete a full backup at `/bin/date`" >> /tmp/my.txt

if [ `cat /tmp/test.yon` -eq 1 ] && [ $num -eq 3 ]; then

        headname=`/usr/bin/ls -ln /export/backup/ | /usr/bin/head -n 2 | /bin/grep - | /usr/bin/awk {print'$9'}`

        /usr/bin/rm -rf /export/backup/$headname

fi


增量备份脚本

incrementalbackup.sh


#!/bin/bash

#Realize incremental backup for the MySQL database

#user=congfei@encompass8.com

ulimit -n 1048576

dirnum=`/usr/bin/ls -l /export/backup | /usr/bin/wc -l`

if [ `cat /tmp/test.yon` -eq 0 ] || [ $dirnum -eq 1 ]; then

        exit;

fi

incrementaldir=/export/incremental/

username=wcfoffice

passwd=wcfwcfwcf

name=`/usr/bin/ls -ln /export/backup/ | /usr/bin/tail -n 1 | /usr/bin/awk {print'$9'}`

nameincremental=`/usr/bin/ls -ln /export/incremental/$name | /usr/bin/tail -n 1 | /usr/bin/awk {print'$9'}`

num=`/usr/bin/ls -l /export/incremental/$name | /usr/bin/wc -l`

count=`/usr/bin/ls -l /export/incremental/ | /usr/bin/wc -l`

/bin/mysql -u$username -p$passwd -e "flush logs;"

/bin/echo "0" > /tmp/test.yon

if [ $num -lt 2 ]; then

        /bin/echo "111111111111111111111111111"

/usr/bin/mkdir $incrementaldir$name

        /usr/bin/innobackupex --user=$username --password=$passwd --incremental  $incrementaldir$name   --incremental-basedir=/export/backup/$name && /bin/echo "1" > /tmp/test.yon && /bin/echo "To complete a incremental backup at `date`" >> /tmp/my.txt

if [ $? -ne 0 ]; then

                names=`/usr/bin/ls -ln /export/incremental/$name | /usr/bin/tail -n 1 | /usr/bin/awk {print'$9'}`

                /usr/bin/rm -rf /export/incremental/$name/$names &&  /bin/echo "1" > /tmp/test.yon

         fi

else


        /bin/echo "222222222222222222222222222"

        /usr/bin/innobackupex --user=$username --password=$passwd --incremental   $incrementaldir$name  --incremental-basedir=$incrementaldir$name/$nameincremental && /bin/echo "1" > /tmp/test.yon && /bin/echo "To complete a incremental backup at `date`" >> /tmp/my.txt

if [ $? -ne 0 ]; then

                names=`/usr/bin/ls -ln /export/incremental/$name | /usr/bin/tail -n 1 | /usr/bin/awk {print'$9'}`

                /usr/bin/rm -rf /export/incremental/$name/$names &&  /bin/echo "1" > /tmp/test.yon

         fi

fi

if [ `cat /tmp/test.yon` -eq 1 ] &&  [ $count -eq 3 ]; then

        headname=`/usr/bin/ls -ln $incrementaldir | /usr/bin/head -n 2 | /bin/grep - | /usr/bin/awk {print'$9'}`

        /usr/bin/rm -rf $incrementaldir$headname

fi