xxx提出要求:对线上测试环境的mysql的所有数据库作备份
[root@BJVM-CM-94 data]# cd /app/mysql/data/
[root@BJVM-CM-94 data]# du -sh
2.9G.
经查看数据只有2.9G,数据容量不是很大,所以我选择使用mysqldump进行备份
备份时间只用了1-2s的时间
备份文件大小只有280M,是原文件的1/10
一般情况:原文件大小是备份文件的几十倍
[root@www ftp.scj.com]# ps -ef | grep mysql
root 5642 1211 20 11:16 pts/0 00:00:00 grep mysql
root 29558 1 0 Apr14 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/mysql/data --pid-file=/opt/mysql/data/www.scj.com.pid
mysql 29758 29558 0 Apr14 pts/1 00:00:18 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql--datadir=/opt/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/opt/mysql/data/www.scj.com.pid --socket=/var/lib/mysql/mysql.sock
要求:每天凌晨1点完整备份一次
保留三天的备份文件数据
保留三天的二进制日志文件
使用二进制日志文件进行备份之后数据的恢复
备份步骤:
-
修改mysql的配置文件:
开启二进制日志文件
vi /usr/local/mysql/my.cnf
在[mysqld]下面添加一行:
log-bin=/opt/mysql/binlog/mysql-binlog (把二进制日志文件单独放到一个目录下)
-
创建备份目录和二进制日志目录:
mkdir -p /opt/mysql/{backup,binlog}
cd /opt/mysql/
chown -R mysql.mysql backup binlog (修改权限)
重启mysql服务: /etc/init.d/mysqld reload
[root@www ~]# ls /opt/mysql/binlog/ (查看二进制文件是否生成)
mysql-binlog.000001 mysql-binlog.index
注意:只有当二进制日志文件生成后才可执行mysqldump,否则执行会失败;若reload后没有二进制日志文件生成,那就只有尝试:先stop掉mysql,再start起来
-
使用mysqldump作完整备份:
备份除了Database|information_schema|mysql|test|performance_schema这几个库之外的所有库,使用--databases参数,切记
mysql -u root -p123456 -e "show databases" | grep -Ev "Database|information_schema|mysql|test|performance_schema" |xargs mysqldump -uroot -p123456 --lock-all-tables --routines --events --triggers --master-data=2 --flush-logs --add-drop-database --add-drop-table --databases > /opt/mysql/backup/mysql_full_`date +%F-%H:%M:%S`.sql
注意:执行此命令屏幕会输出一行:
Warning: Using a password on the command line interface can be insecure.
这句话的意思是:在命令行输入密码不安全,完全可以忽略此行,不用管(grep 本身也会忽略这句话)
-u #指定用户名
-p #指定用户密码
-h #指定主机地址
-A|--all-databases #备份所有数据库
--databases #备份指定数据库
--single-transcation #基于此项可以实现对InnoDB表做热备份,但不需要使用
--lock-all-tables #执行备份时为所有表请求加锁
-E|--events #备份事件调度器代码
--opt #同时启动各种高级选项
-R|--routines #备份存储过程和存储函数
--flush-logs #备份之前刷新日志
--triggers #备份触发器
--master-data=2 #该选项将会记录binlog的日志位置与文件名并追加到备份文件中,(如果为1将会输出CHANGE MASTER命令,主从下有用)
如下:
[root@www backup]# less mysql_full_2015-04-15-11-54-07.sql
里面有一行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000002', MASTER_LOG_POS=120;
备份后生成的二进制日志文件名及pos信号,之后所有的操作都将从120开始,记录到mysql-binlog.000002
恢复步骤:
假如,某一时刻运维人员误操作,执行了一个drop操作:
恢复开始:
首先使用全备份文件恢复到备份时的数据:
cd /opt/mysql/data
把除了information_schema|mysql|test|performance_schema这几个库之外的所有库,都临时移到一个目录里:
mv $(mysql -u root -p123456 -e "show databases" | grep -Ev "Database|information_schema|mysql|test|performance_schema" |xargs) /tmp/mysql/linshi
找到最后一个全备份的文件:
cd /opt/mysql/backup
mysql -uroot -p123456 < mysql_full_2015-04-15-13-36-50.sql (恢复到了作备份时的数据)
然后使用二进制日志文件恢复到执行drop时的数据:
cd /opt/mysql/binlog/
根据最后一个备份文件里的记录,找到二进制日志文件名和起始的pos信号:
[root@www backup]# less mysql_full_2015-04-15-11-54-07.sql
里面有一行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000002', MASTER_LOG_POS=120;
根据二进制日志文件找到执行drop时的pos信号(即:结束时的pos信号):
[root@www binlog]# mysqlbinlog mysql-binlog.000002 |less
# at 965
#150415 12:04:27 server id 1 end_log_pos 1060 CRC32 0xd681bd27 Query thread_id=2 exec_time=1 error_code=0
SET TIMESTAMP=1429113867/*!*/;
drop database allen
/*!*/;
DELIMITER ;
找到执行drop的那个位置,如上,发现执行drop前的pos信号是965
使用mysqlbinlog 开始恢复:
mysqlbinlog --start-position=120 --stop-position=965 mysql-binlog.000002 |mysql -uroot -p123456
注意:pos信号120和日志文件名000002都是在备份文件里有记录的,这个需要记下来
注意:用mysqlbinlog命令去读取二进制日志文件时可能会报下面的错误:
mysqlbinlog: unknown variable 'default-character-set=utf8'
解决办法:只需要加上--no-defaults参数即可,即:
mysqlbinlog --no-defaults mysql-binlog.000002 |less
使用脚本和计划任务实现自动备份:
vi /usr/local/mysql/run/mysql_backup_full.sh
#!/bin/bash
#这个脚本用来对数据库作全备份
user=root
password=123456
backupdir=/opt/mysql/backup/ #备份目录
#取一天前备份时生成的那个二进制日志文件
val_binlog=$(less /opt/mysql/binlog/mysql-binlog.index | awk -F"/" '{print $5}' |tail -n 2|head -n 1)
#创建备份目录
[ ! -d $backupdir ] && mkdir -p $backupdir
#备份除Database|information_schema|mysql|test|performance_schema这几个数据库之外的所有库
mysql -u $user -p$password -e "show databases" | grep -Ev "Database|information_schema|mysql|test|performance_schema" |xargs mysqldump -u $user -p$password --lock-all-tables --routines --events --triggers --master-data=2 --flush-logs --databases > ${backupdir}mysql_full_`date +%F-%H:%M:%S`.sql
#删除两天前的备份文件,即保留三个备份文件
find $backupdir -type f -mtime +2 -exec rm -f {} \;
#删除两天前的二进制日志文件,即保留三个二进制日志文件
mysql -u $user -p$password -e "PURGE MASTER LOGS TO '$val_binlog'"
[root@www run]#chmod 700 /usr/local/mysql/run/mysql_backup_full.sh (加上x执行权限)
注意:此脚本里面包含了mysql的root密码,为了安全,设置700权限,其他人都不可访问
[root@www run]#crontab -e (创建计划任务)
#分 时 日 月 周 用户名 命令
0 1 * * * cd /usr/local/mysql/run;./mysql_backup_full.sh