几种MySQL备份常用方法整理

对于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. #caishzh20120509
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")#日期
11. [-d"$BAKDIR"]||mkdir-p"$BAKDIR"#如果备份目录不存在则创建
13. mysqldump-opt-u$USER-p$PASSWD$DB|gzip>$BKDIR/$DB_$DATA.gz#备份并使用gzip打包
15. if[$?-eq0];then
16. #echo"$DBmysqlbackup"|mutt$MAIL-a$BKDIR/$DB_$DATA.sql-s"$DBmysqlbackup"
17. ls-lh$BAKDIR/*.gz>$BAKDIR/messages.txt
18. mutt$MAIL-a$BKDIR/$DB_$DATA.gz-s"$DATE：$DBmysqlbackup"<$BAKDIR/messages.txt
19. else
20. echo"$DATEmysqlbackupfailed"|mail-s"$DATEmsyqlbacupfailed"$MAIL
21. fi

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

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

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

1. netstopmysql
2. xcopyc:/mysql/data/bbs/*.*d:/db_bak/bbs/%date:~0,3%//y
3. netstartmysql
4. 或者
5. netstopmysql
6. xcopyD:/Mysql/dataD:/Mysql/BAK/BAK-%date:~0,10%/S/E/I
7. netstartmysql

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

1. xcopyc:/.d:/aaa/s/e
3. XCOPYsource[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. #amysqlincrementalbackupscript.
4. #byflute
5. #++++++++++++++++++++++++++++++++++
6. BAK_DIR="."
7. whilegetopts:u:p:d:cOPTION
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"-uuserforlogin"
28. echo"-pPasswordtousewhenconnectingtoserver"
29. echo"-dwhichdatabasetobackup"
30. echo"-bbackupdatabasefilestodirectory,defaultiscurrentdirectory"
31. echo"-cmustbeusageforfirstbackup,iscompletebackup"
32. exit1
33. ;;
34. esac
35. done
36. MYSQL_BIN=/usr/local/mysql/bin
37. DATADIR=`${MYSQL_BIN}/mysql-u${DB_USER}-p${PASSWD}-e"showvariableslike'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"upfilesuccess."
50. else
51. logger-f~/mysqlbak.log-tMYSQLBAK'uploadfile${_TNAME}failed'
52. ssh192.168.1.10"logger-f~/mysqlbak.log-tMYSQLBAK'uploadfile${_TNAME}failed'"
53. fi
54. else
55. echo"tarfilefailed."
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. return0
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. return0
77. fi
78. ID=`expr$ID+1`
79. done
81. fi
82. }

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

1. mkdir%BAKPATH%＼data
3. %MYSQLPATH%＼bin＼mysqldump-u%USERNAME%-p%PASSWORD%--single-transaction--default-
4. character-set=utf8--flush-logs--master-data=2--delete-master-logsspirit_beast>%
5. BAKPATH%＼data＼spirit_beast%date:~0,10%.sql
8. %MYSQLPATH%＼bin＼mysqldump-u%USERNAME%-p%PASSWORD%--single-transaction--default-
9. character-set=utf8--flush-logs--master-data=2--delete-master-logsquartz_event>%
10. BAKPATH%＼data＼quartz_event%date:~0,10%.sql
13. rara-ag%BAKPATH%＼full＼%BAKPATH%＼data＼*.sql
15. rmdir/s/q%BAKPATH%＼data＼
16. //phpfensi.com
17. @echo%date%%time%fullbakupfinish>>C:/mysqlbakup.log