夏日博客

A-A+

几种MySQL备份常用方法整理

2017年11月06日 PHP技术文章 暂无评论 阅读 0 views 次

对于MySQL备份方法有很多种,下面我来给大家整理在我们WEB运维过程中一些常用的数据库备份方法,希望对各位同学会有所帮助.

总结下几种常用的mysql备份方法.

一、直接拷贝数据库文件

首先把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入,代码如下:

mysql>FLUSH TABLES WITH READ LOCK;

使用tar或cp等命令备份数据库文件,这里使用tar:

tar zcvf /backup/mysql_$(date "+%Y%m%d").tar.gz /var/lib/mysql

c、备份完后解锁数据表

mysql> unlock tables;这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可,需要注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件,因为当 Innodb 表损坏时,就可以依靠这些日志文件来恢复.

二、使用mysqlhotcopy备份

mysqlhotcopy 是一个 PERL 程序,使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库,它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件,包括数据表定义文件、数据文件、索引文件,所在的机器上.

mysqlhotcopy 只能用于备份 MyISAM.

例,把数据库test备份到/backup目录下,代码如下:

/usr/local/mysql/bin/mysqlhotcopy -uroot -pxxx test /backup

mysqlhotcopy在执行前将会锁定数据库,执行完毕后将自动释放锁定.

还原:mysqlhotcopy 备份出来的是整个数据库目录,还原时直接拷贝覆盖就行了,注意权限问题,代码如下:

cp -rf /backup/test /var/lib/mysql/

注意,想要使用 mysqlhotcopy,必须要有 SELECT、RELOAD(要执行 FLUSH TABLES) 权限,并且还必须要能够有读取 datadir/db_name 目录的权限.

三、mysqldump

mysqldump 是SQL级别的备份机制,它将数据表导成 SQL 脚本文件,可以方便的在不同的mysql版本间进行恢复或升级,这也是最常用的备份方法.

例,使用mysqldump备份,代码如下:

/usr/local/mysql/bin/mysqldump -uroot -pxxx --opt test >/backup/test.sql

还原:/usr/local/mysql/bin/mysqldump -uroot -pxxx test

四、使用主从复制机制(replication)（实现数据库实时备份）推荐

五、mysql自动备份脚本,使用mysqldump工具,代码如下:

  1. #!/bin/bash   
  2. #caishzh 20120509   
  3.    
  4. BAKDIR="/backup/mysql"     #mysql备份目录   
  5. MAIL="xxx@xxx.com"          #邮箱地址   
  6. USER="root"                         #mysql用户名   
  7. PASSWD="jjj"                         #mysql密码   
  8. DB="test"                              #数据库名   
  9. DATE=$(date "+%Y%m%d")     #日期   
  10.    
  11. [ -d "$BAKDIR" ] || mkdir -p "$BAKDIR"     #如果备份目录不存在则创建   
  12.    
  13. mysqldump -opt -u$USER -p$PASSWD $DB |gzip > $BKDIR/$DB_$DATA.gz     #备份并使用gzip打包   
  14.    
  15. if [ $? -eq 0 ];then   
  16.      #echo "$DB mysql backup" |mutt $MAIL -a $BKDIR/$DB_$DATA.sql -s "$DB mysql backup"   
  17.      ls -lh $BAKDIR/*.gz>$BAKDIR/messages.txt   
  18.      mutt  $MAIL -a $BKDIR/$DB_$DATA.gz -s "$DATE：$DB mysql backup" < $BAKDIR/messages.txt   
  19. else   
  20.      echo "$DATE mysql backup failed"|mail  -s "$DATE msyql bacup failed"  $MAIL   
  21. fi   

六,此方法只可用于windows主机

假想环境,MySQL 安装位置：C:/MySQL,数据库名称为:bbs,数据库备份目的地:d:/db_bak/.

新建db_bak.bat,写入以下代码:

  1. net stop mysql   
  2. xcopy c:/mysql/data/bbs/*.* d:/db_bak/bbs/%date:~0,3%/ /y   
  3. net start mysql   
  4. --或者   
  5. net stop mysql   
  6. xcopy D:/Mysql/data D:/Mysql/BAK/BAK-%date:~0,10%  /S/E/I   
  7. net start mysql  

然后使用Windows的“计划任务”定时执行该批处理脚本即可,例如,每天凌晨3点执行back_db.bat,保存一周的数据,用%date:~0,3%,保存每天的数据,用%date:~4,10%,代码如下:

  1. xcopy c:/. d:/aaa /s/e   
  2.                      
  3. XCOPY source [destination] [/A | /M] [/D[:date]] [/P]   
  4.                            [/C] [/I] [/Q] [/F] [/L] [/   
  5.                            [/K] [/N] [/O] [/X] [/Y] [/   
  6.                            [/EXCLUDE:file1[+file2][+fi   

七,linux增量备份,代码如下:

  1. #!/bin/sh   
  2. #+++++++++++++mysqlback++++++++++++   
  3. #a mysql incremental backup script.   
  4. #by flute   
  5. #++++++++++++++++++++++++++++++++++   
  6. BAK_DIR="."   
  7. while getopts :u:p:d:c OPTION   
  8. do   
  9.   case ${OPTION} in   
  10.       u)   
  11.           DB_USER=${OPTARG}   
  12.           ;;   
  13.       p)   
  14.           PASSWD=${OPTARG}   
  15.           ;;   
  16.       d)   
  17.           DB_NAME=${OPTARG}   
  18.           ;;   
  19.       b)   
  20.           BAK_DIR=${OPTARG}   
  21.           ;;   
  22.       c)   
  23.           INIT="true"   
  24.           ;;   
  25.       /?)   
  26.       echo "Usage: mysqlback.sh [OPTIONS]"   
  27.       echo "-u        user for login"   
  28.       echo "-p        Password to use when connecting to server"   
  29.       echo "-d        which database to backup"   
  30.       echo "-b        backup database files to directory,default is current directory"   
  31.       echo "-c        must be usage for first backup,is complete backup"   
  32.       exit 1   
  33.       ;;   
  34.   esac   
  35. done   
  36. MYSQL_BIN=/usr/local/mysql/bin   
  37. DATADIR=`${MYSQL_BIN}/mysql -u ${DB_USER} -p${PASSWD} -e "show variables like 'datadir'" | sed -n '2p' | awk '{print $NF}'`   
  38. upfile ()    #上传文件   
  39. {   
  40.     _FNAME=$1   
  41.     _TNAME=`echo ${_FNAME}|sed 's//.sql//g'`   
  42.     _TNAME="${_TNAME}.tgz"   
  43.     tar -czf ${_TNAME} ${_FNAME}   
  44.     if [ $? = 0 ]   
  45.         then   
  46.         scp ${_TNAME} 192.168.1.10:/usr/local/mysql/bak   
  47.         if [ $? = 0 ]   
  48.             then   
  49.             echo "upfile success."   
  50.         else   
  51.             logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'   
  52.             ssh 192.168.1.10 "logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'"   
  53.         fi   
  54.     else   
  55.         echo "tar file failed."   
  56.     fi   
  57. }   
  58. bak_file ()    #获取备份文件名   
  59. {   
  60.     _DB_NAME=$1   
  61.     _BAK_DIR=$2   
  62.     _BDATE=`date "+%Y-%m-%d"`   
  63.     _BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}.sql"   
  64.     if [ ! -f ${_BAKFILE} ]   
  65.     then   
  66.         echo ${_BAKFILE}   
  67.         return 0   
  68.     else   
  69.     ID=1   
  70.     while [ 1 ]   
  71.           do   
  72.           _BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}_${ID}.sql"   
  73.           if [ ! -f ${_BAKFILE} ]   
  74.               then   
  75.               echo ${_BAKFILE}   
  76.               return 0   
  77.           fi   
  78.           ID=`expr $ID + 1`   
  79.     done   
  80.            
  81.     fi   
  82. }   

八,windows增量备份,代码如下:

  1. mkdir %BAKPATH%＼data   
  2.    
  3. %MYSQLPATH%＼bin＼mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default-   
  4. character-set=utf8 --flush-logs --master-data=2 --delete-master-logs spirit_beast > %   
  5. BAKPATH%＼data＼spirit_beast%date:~0,10%.sql   
  6.    
  7.    
  8. %MYSQLPATH%＼bin＼mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default-   
  9. character-set=utf8 --flush-logs --master-data=2 --delete-master-logs quartz_event > %   
  10. BAKPATH%＼data＼quartz_event%date:~0,10%.sql   
  11.    
  12.    
  13. rar a -ag %BAKPATH%＼full＼ %BAKPATH%＼data＼*.sql   
  14.    
  15. rmdir /s /q %BAKPATH%＼data＼   
  16. //www.xiariboke.net   
  17. @echo  %date% %time% full bakup finish >> C:/mysqlbakup.log  
标签：

给我留言