mysql数据的备份及恢复脚本

数据库 waitig 472℃ 百度已收录 0评论

mysqldump

for MyISAM
mysqldump –user=root –all-databases –flush-privileges  –lock-all-tables \
–master-data=1 –flush-logs –triggers –routines –events \
–hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

for InnoDB
mysqldump –user=root –all-databases –flush-privileges  –single-transaction \
–master-data=1 –flush-logs –triggers –routines –events \
–hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

binlog备份
mysqlbinlog  –read-from-remote-server  –host=192.168.83.37  –port=3306 \
–user=repl_user  –password testpassword –raw  –stop-never  –result-file=/backup/00001-bin.000001

1.利用专门的slave来备份(rsync)
2.在业务低峰利用xtrabackup在slave上做备份
利用HDFS或自己做一个存储管理系统来备份这些存储。

1.全量备份
#!/bin/bash

innobackupex  –user=root –password=testpassword  –defaults-file=/var/lib/mysql/my.cnf  /mysql_backup/xtrabackup/base

2.(写入数据)基于1做增量备份
#!/bin/bash
filename=$1
if [ -z $filename ];then
exit 0
fi

innobackupex –user=root  –password=testpassword –defaults-file=/var/lib/mysql/my.cnf  –incremental –incremental-basedir=/mysql_backup/xtrabackup/base/$filename  /mysql_backup/xtrabackup/delta

3.恢复全量
#!/bin/bash
filename=$1
if [ -z $filename ];then
exit 0
fi

innobackupex –user=root  –password=testpassword –defaults-file=/var/lib/mysql/my.cnf  –apply-log  –redo-only  /mysql_backup/xtrabackup/$filename

4.恢复增量
#!/bin/bash

innobackupex –user=root  –password=testpassword –defaults-file=/var/lib/mysql/my.cnf  –apply-log  –redo-only  /mysql_backup/xtrabackup/base/xxxxx –incremental-dir=/mysql_backup/xtrabackup/delta/xxxxx

5.复制恢复
#!/bin/bash
innobackupex –user=root  –password=testpassword –defaults-file=/var/lib/mysql/my.cnf  –copy-back /mysql_backup/xtrabackup/base/xxxxx


本文由【waitig】发表在等英博客
本文固定链接:mysql数据的备份及恢复脚本
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)