假想场景：数据库Mysql5.1.X，已做“主-从”，在其中一台从库上备份，主库不执行备份，从库不停止，主从数据不一样的时间差尽量短，在备份中有slave信息（主库的二进制日志名和位置），以便于在不停主库的情况下，新建主-从，或者随时可恢复到某个时间点。MyISAM和InnoDB都存在，备份要保证数据一致性。
基于以上场景，采用了innobackupex+bash shell，实现自动完整+增量备份，实现自动邮件通知和FTP上传，以及本地和异地的备份滚动。
脚本中也有其它被注释了的备份方式，以供参考。
下载包，这里我下载了二制包
wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.0.4-484.tar.gz
把命令拷贝到/usr/bin下即可
脚本的大致思路：以天为单位，首次运行是完整备份，下次将增量备份，恢复到最近的数据需要恢复所有增量备份。如果完整备份失败，那么要删除当天的备份目录，再开始。因为脚本是判断lsn.txt文件是否存在来决定本次该完整备份还是增量备份。
在介绍脚本之前，首先要说说Innodb存储引擎的热备
Innodb 存储引擎由于是事务性存储引擎，有redo 日志和相关的undo 信息，而且对数据的一致性和完整性的要求也比MyISAM 要严格很多，所以Innodb 的在线（热）物理备份要比MyISAM 复杂很多，比如你要考虑到备份的数据一致性，对备份MYSQL的影响，缓存的污染，CPU、磁盘I/O和网络的压力，恢复的难度，时间和恢复的方式等等，一般很难简单的通过几个手工命令来完成，大都是通过专门的Innodb在线物理备份软件来完成。
现在有比较好的软件：
一个是开源的:http://www.percona.com/software/percona-xtrabackup
innobackupex和xtrabackup
innobackupex是一个perl脚本集合，可以备份所有存储引擎，并可以保证其数据一致性，它会调用xtrabackup
xtrabackup是一个C语言编写的程序，只能备份innodb和xtrdb存储引擎的表
一个是被MySQL官方合并到付费企业版的：http://www.innodb.com/products/hot-backup/
官方文档：http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/index.html
mysqlbackup和ibbackup
mysqlbackup是一个C语言编写的程序，可以备份所有存储引擎，并可以保证其数据一致性，是一个会调用ibbackup的命令
ibbackup只能备份innodb和存储引擎的表
另外innobackup可能是之前www.innodb.com做的一个perl命令集合。
至少在mysqlbackup3.5软件包中，innobackup和ibbackup命令被软链接到mysqlbackup命令，innobackup和ibbackup命令在未来很可能被替代。
innobackupex安装配置和使用
到官网下载相应版本的包，二进制包、RPM包、源码包均可。经测试，发现通过源码包安装非常麻烦，因为安装过程容易出很多问题。建议直接下载二进制包使用，设置一个PATH或者拷贝到/usr/bin下即可使用了
权限配置说明：
RELOAD and LOCK TABLES (unless the –no-lock option is specified) in order to FLUSH TABLES WITH
READ LOCK prior to start copying the files and
REPLICATION CLIENT in order to obtain the binary log position,
CREATE TABLESPACE in order to import tables (see Importing and Exporting Individual Tables) and
SUPER in order to start/stop the slave threads in a replication environment.
mysql>
CREATE USER ‘bakuser’@’localhost’ IDENTIFIED BY ‘pass’;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘bakuser’@’localhost’;
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,SUPER ON *.* TO ‘bakuser’@’pass’;
FLUSH PRIVILEGES;
或：
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,SUPER ON *.* TO ‘bakuser’@’localhost’ IDENTIFIED BY ‘pass’;
FLUSH PRIVILEGES;
注意，通过socket连接mysql实际上对root@localhost授权就够了。
示例：
下面是单独运行命令成功过的示例：
innobackupex –socket=/opt/mysql/var/mysql.sock –user=bakuser –password=pass –defaults-file=/opt/mysql/my.cnf –throttle=2 –slave-info –safe-slave-backup –use-memory=200MB /opt/data_bak/mysql_bak
在某次完整备份后第一次增量备份：
innobackupex –socket=/opt/mysql/var/mysql.sock –user=bakuser –password=pass –defaults-file=/opt/mysql/my.cnf –throttle=2 –slave-info –safe-slave-backup –use-memory=200MB –incremental –incremental-basedir=BASEDIR /opt/data_bak/mysql_bak
在最近完整备份后第二、三次增量备份：
或基于目录
innobackupex –socket=/opt/mysql/var/mysql.sock –user=bakuser –password=pass –defaults-file=/opt/mysql/my.cnf –throttle=2 –slave-info –safe-slave-backup –use-memory=200MB –incremental –incremental-basedir=BASEDIR /opt/data_bak/mysql_bak
innobackupex –socket=/opt/mysql/var/mysql.sock –user=bakuser –password=pass –defaults-file=/opt/mysql/my.cnf –throttle=2 –slave-info –safe-slave-backup –use-memory=200MB –incremental –incremental-basedir=incrementaldir1 /opt/data_bak/mysql_bak
或基于日志LSN
innobackupex –socket=/opt/mysql/var/mysql.sock –user=bakuser –password=pass –defaults-file=/opt/mysql/my.cnf –throttle=2 –slave-info –safe-slave-backup –use-memory=200MB –incremental –incremental-lsn=0:173917517 /opt/data_bak/mysql_bak
innobackupex –socket=/opt/mysql/var/mysql.sock –user=bakuser –password=pass –defaults-file=/opt/mysql/my.cnf –throttle=10 –slave-info –safe-slave-backup –use-memory=200MB –incremental –incremental-lsn=0:173918957 /opt/data_bak/mysql_bak
恢复：
先合并增量到完整备份
innobackupex –apply-log –redo-only BASE-DIR
innobackupex –apply-log –redo-only BASE-DIR –incremental-dir=INCREMENTAL-DIR-1
innobackupex –apply-log BASE-DIR –incremental-dir=INCREMENTAL-DIR-2 这是最后一个，也就是最新的增量
innobackupex –apply-log BASE-DIR
解后恢复到数据目录
更多资料：http://www.percona.com/software/percona-xtrabackup
脚本内容：
把脚本修改为适合自已的实际场景的，几乎只需要修改变量设定部分，变量设置不正确，脚本运行会报错，通过脚本的日志和innobackupex.log可以查看原因，比如MYSQL的配置文件路径（切记my.cnf中需要有datadir和innodb等相关设置），权限，本地备份目录，FTP目录等一定要再三检查。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 |
#!/bin/bash #History ###################################################### #update author #2012/10/15 zhaoyn create #2012/10/22 zhaoyn add ftp #2012/11/28 zhaoyn add mysqlhotcopy #2012/12/05 zhaoyn Improve function #2012/12/12 zhaoyn Backup of stored procedures and events #2012/12/24 zhaoyn Increase the log output #2013/01/09-11 zhaoyn Various physical storage engine hot backup, InnoDB incremental backup command #Note: use hot backup script, may also need some other pre-configured. # wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.0.4-484.tar.gz # http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/index.html # 30 */2 * * * root /root/sh/backup_mysql.sh >> backup_mysql.log 2>&1 ###################################################### ########## variable ####################################### ### base config ### backupdir=/data_bak/mysql_bak/innobackupex #mysqlhost=”127.0.0.1″ #mysqlport=”3306″ mysqlsocket=“/opt/mysql/var/mysql.sock” mysqldbname=“dbname” mysqluser=“root” mysqlpw=“pass” ### hotcopy ### hc_mysqluser=“bakuser” hc_mysqlpw=“pass” mysqlconfig=“/opt/mysql/my.cnf” iops=8000 # This option specifies a number of I/O operations (pairs of read+write) per second. incr=“yes” # yes or no, to turn on or off the daily incremental backups ### ftp ### ftpip=‘1.2.3.4’ ftpport=’21’ ftpuser=‘backup’ ftppw=‘pass’ ftpbackupdir=“innobackupex” ftpsw=“yes” # To turn on or off the FTP upload ftpdeldate=$(date –d “15 days ago” +%Y%m%d) ### mail ### servername=“dbserver” mailfromadd=‘dbserver<dbserver@domain.com>’ mailtoadd=‘user1<user1@domain.com>’ #mailccadd=’user2<user2@domain.com>’ ### lang path time ### export LANG=C export LC_ALL=C export PATH=/opt/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin datetime=$(date +%Y%m%d–%H%M) todaydate=$(date +%Y%m%d) deldate=$(date –d “7 days ago” +%Y%m%d) ####### function ####################################### function mailto() { # mail /usr/sbin/sendmail –t <<EOF From: $mailfromadd To: $mailtoadd Cc: $mailccadd Subject: $servername Backup directory $backupdir does not exist. ————————————————— $servername Backup directory $backupdir does not exist ————————————————— EOF } function mailto2() { # mail /usr/sbin/sendmail –t <<EOF From: $mailfromadd To: $mailtoadd Cc: $mailccadd Subject: $servername complete the mysql–backup ————————————————— $servername complete the mysql–backup, innobackupex $teststring ————————————————— EOF } function mailto3() { # mail /usr/sbin/sendmail –t <<EOF From: $mailfromadd To: $mailtoadd Cc: $mailccadd Subject: $servername backup problems. ————————————————— $servername backup problems. ————————————————— EOF } echo “” echo “” echo “`date` Start.” echo “##################################” ####### delete old backups ####################################### echo “`date` Delete old backup.” if [ –d “$backupdir” ] && [ “$deldate” –gt 20120901 ]; then cd $backupdir # find “$backupdir” -maxdepth 1 -name “*mysqldump*.gz” -mtime +”$deldays” -exec rm -f {} \; # find “$backupdir” -maxdepth 1 -name “*mysqlhotcopy*” -mtime +”$deldays” -exec rm -f {} \; find “$backupdir” –maxdepth 1 –type d –name “$deldate” | xargs rm –rf else echo “Backup directory does not exist, or did not get to date.” mailto exit 1 fi echo “`date` Start Backup.” ####### mysqldump #################################### #mysqldump -S”$mysqlsocket” -u$mysqluser -p$mysqlpw –opt –routines –triggers –master-data=2 \ #–default-character-set=utf8 –lock-all-tables –quote-names –flush-logs –hex-blob –force \ #”$mysqldbname” > “$mysqldbname”_mysqldump_${datetime}.sql #echo “`date` Begin to compress.” #tar czpf “$mysqldbname”_mysqldump_${datetime}.tar.gz “$mysqldbname”_mysqldump_${datetime}.sql #sync;sleep 2;sync;sleep 2 #rm -f “$mysqldbname”_mysqldump_${datetime}.sql ####### physical cold backup #################################### #/etc/rc.d/init.d/mysqld stop #sleep 2s ;sync;sync #echo “`date` Begin to compress.” #tar -czvpf mysql_${datetime}.tar.gz /etc/my.cnf /opt/mysql/var #/etc/rc.d/init.d/mysqld start ####### physical hot backup #################################### ### 1. unix/linux, only myisam, mysqlhotcopy ### #mysqlhotcopy -S “$mysqlsocket” -u “$mysqluser” -p “$mysqlpw” “$mysqldbname” –flushlog ./ #sync;sleep 2;sync;sleep 2 #mv “$mysqldbname” “$mysqldbname”_mysqlhotcopy_${datetime} #mysqldump -S”$mysqlsocket” -u$mysqluser -p$mysqlpw –opt –routines –default-character-set=utf8 \ #–quote-names –force –no-data “$mysqldbname” > “$mysqldbname”_mysqldump_structure-func_${datetime}.sql #echo “`date` Begin to compress.” #tar czpf “$mysqldbname”_mysqlhotcopy_${datetime}.tar.gz \ #”$mysqldbname”_mysqlhotcopy_${datetime} “$mysqldbname”_mysqldump_structure-func_${datetime}.sql #sync;sleep 2;sync;sleep 2 #rm -rf “$mysqldbname”_mysqlhotcopy_${datetime} “$mysqldbname”_mysqldump_structure-func_${datetime}.sql ### 2. unix/linux, any storage engine, innobackupex, all databases ### test –e “$todaydate” || mkdir “$todaydate” cd “$todaydate” i=0 if [ ! –f lsn.txt ]; then echo “`date` Start full backup.” # fullbackup innobackupex —socket=“$mysqlsocket” —user=“$mysqluser” —password=“$mysqlpw” —defaults–file=“$mysqlconfig” \ —throttle=“$iops” —slave–info —safe–slave–backup —use–memory=200MB —no–timestamp “$datetime”_fullbackup >> \ innobackupex.log 2>&1 grep “last_lsn” “$datetime”_fullbackup/xtrabackup_checkpoints | awk –F[” “] ‘{print $3}’ >> lsn.txt mysqldump –S“$mysqlsocket” –u$mysqluser –p$mysqlpw —opt —routines —default–character–set=utf8 —quote–names \ —force —no–data “$mysqldbname” > “$datetime”_fullbackup/“$mysqldbname”_mysqldump_structure–func_${datetime}.sql sync;sleep 2;sync;sleep 2 tar czpf “$datetime”_“$mysqldbname”_fullbackup.tar.gz “$datetime”_fullbackup rm –rf “$datetime”_fullbackup i=1 fi last_lsn=`tail –1 lsn.txt` teststring=`tail –1 innobackupex.log | awk –F‘: ‘ ‘{print $(NF-0)}’` if [ “$last_lsn” == “” ] && [ “$i” –eq 0 ] && [ “$incr” == “yes” ]; then echo “`date` Did not get to last_lsn, cancel incremental backup, exit.” mailto3 exit 1 elif [ “$teststring” != ‘completed OK!’ ] && [ “$i” –eq 0 ] && [ “$incr” == “yes” ]; then echo “`date` Last backup was not successful, cancel incremental backup, exit.” mailto3 exit 1 elif [ “$teststring” == ‘completed OK!’ ] && [ “$i” –eq 0 ] && [ “$incr” == “yes” ]; then # incremental backup innobackupex —socket=“$mysqlsocket” —user=“$mysqluser” —password=“$mysqlpw” —defaults–file=“$mysqlconfig” \ —throttle=“$iops” —slave–info —safe–slave–backup —use–memory=200MB —no–timestamp \ —incremental —incremental–lsn=“$last_lsn” “$datetime”_incremental >> innobackupex.log 2>&1 grep “last_lsn” “$datetime”_incremental/xtrabackup_checkpoints | awk –F[” “] ‘{print $3}’ >> lsn.txt mysqldump –S“$mysqlsocket” –u$mysqluser –p$mysqlpw —opt —routines —default–character–set=utf8 —quote–names \ —force —no–data “$mysqldbname” > “$datetime”_incremental/“$mysqldbname”_mysqldump_structure–func_${datetime}.sql sync;sleep 2;sync;sleep 2 tar czpf “$datetime”_“$mysqldbname”_incremental.tar.gz “$datetime”_incremental rm –rf “$datetime”_incremental fi teststring=`tail –1 innobackupex.log | awk –F‘: ‘ ‘{print $(NF-0)}’` if [ “$teststring” != ‘completed OK!’ ]; then echo “`date` The backup failed, exit.” mailto3 exit 1 elif [ “$teststring” == ‘completed OK!’ ] && [ “$i” –eq 1 ]; then echo “`date` The full backup was successful.” mailto2 fi ### 3. cross-platform, any storage engine, mysqlbackup, all databases ### #mysqlbackup –socket=”$mysqlsocket” –user=$mysqluser –password=”$mysqlpw” –default-character-set=utf8 \ #–compress-level=1 –sleep=30 –with-timestamp backup –backup-dir=./ ####### FTP backup #################################### if [ “$ftpsw” == ‘yes’ ] && [ “$ftpdeldate” –gt 20120901 ]; then echo “`date` Start uploading to offsite.” ftp –v –n –i <<END open $ftpip $ftpport user $ftpuser $ftppw bin cd $ftpbackupdir/$ftpdeldate mdelete *.gz cd /$ftpbackupdir rmdir $ftpdeldate mkdir $todaydate cd $todaydate mput ${datetime}*.gz bye END else echo “`date` Not open FTP, or did not get to date. done.” fi echo “`date` All completed.” |
建议：脚本中只启用了innobackupex备份，实际上，MYSQL备份最好逻辑备份和物理备份都同时进行，并且还定期做一份数据结构的备份，使各种备份的优势互补。
发表回复