使用mysqldump对数据库作备份

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点完整备份一次

      保留三天的备份文件数据

      保留三天的二进制日志文件

      使用二进制日志文件进行备份之后数据的恢复

备份步骤：

1. 修改mysql的配置文件：

   开启二进制日志文件

   vi /usr/local/mysql/my.cnf

   在[mysqld]下面添加一行：

   log-bin=/opt/mysql/binlog/mysql-binlog           （把二进制日志文件单独放到一个目录下）    

2. 创建备份目录和二进制日志目录：

   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起来

3. 使用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

原文：http://10142693.blog.51cto.com/10132693/1633753