在不同的环境中完成mysql备份

一、实验环境准备

1、所用的mysql为源码安装的mysql-5.5.33，这里就不演示了，参照​​http://qidian510.blog.51cto.com/7319882/1282064​​

2、创建一个新目录存放二进制日志

#mkdir /binlogdircd 
#chown mysql:mysql /binlogdir
#vim /etc/my.cnf
log-bin=/binlogdir/mysql-bin     #二进制日志文件存放位置
innodb_file_per_table = 1        #启用InnoDB表每表一文件，默认所有库使用一个表空间

#service mysqld start

3、配置所用的测试库和测试表

   mysql> create database testdb;

   mysql> use testdb;

   mysql> CREATE TABLE tb1 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, Name CHAR(30) NOT NULL UNIQUE KEY, Age TINYINT UNSIGNED);

   mysql> insert into tb1 values (1,'Huchong',24);

   mysql> insert into tb1 values (2,'Huafeng',24);

   mysql> insert into tb1 values (3,'Jim',21);

   mysql> insert into tb1 values (4,'Tom',25);

创建用于存放备份的目录

   #mkdir /backup

   #chown -R mysql:mysql /backup

二、用mysqldump实现热备（逻辑备份）

1、进行完全备份

   #mysqldump -uroot -pmypass --single-transaction --events--master-data=2 --all-databases > /backup/all_db_`date +%F`.sql

各选项的意义：

    --single-transaction: 基于此选项能实现热备InnoDB表；mysql> show table status\G；可以查看表的类型，如果表的类型不是全部为InnoDB，则需要使用--lock-all-tables；

    --events备份数据的同时，备份事件调度器代码；

    --master-data=2 在备份文件中记录当前二进制日志的位置

        --all-databases 备份所有库

  mysql> delete from tb1 where id = 4;     #对数据库进行修改，模拟增量备份

   #less /backup/all_db_2013-09-01.sql        #查看其开始位置

  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=2352;     #开始位置在2352

接着查看结束位置，也是当前所在的位置

​​​​

2、增量备份：

   #cd /binlogdir

   # mysqlbinlog --start-position=2352 --stop-position=2542 mysql-bin.000007 > /backup/cactidb_`date +%F_%H`.sql

在服务器上进行其他操作，然后将二进制日志导出

   mysql> create table tb2 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, Name CHAR(30) NOT NULL UNIQUE KEY);

   mysql> drop databases testdb；      #模拟数据库发生意外

# mysqlbinlog --start-position=2542 mysql-bin.000007    #查看二进制日志最终的位置执行drop database testdb的前面的位置#at 2716

​​​​

   # mysqlbinlog --start-position=2542 --stop-position=2716 mysql-bin.000007 > /tmp/cadb.sql   #导出二进制日志也可以不用导出二进制文件，在滚动后直接复制即可。

3.恢复   

     mysql> setsql_log_bin=0;           #恢复过程的二进制日志记录对我们没有意义，所以关闭此功能

      mysql> flush logs；      #滚动一下日志

   # mysql -uroot -pmypass < /backup/all_db_2013-09-01.sql

   # mysql -uroot -pmypass < /backup/cactidb_2013-09-01_20.sql

   # mysql -uroot -pmypass < /tmp/cadb.sql

这时候修复完成，我们可以进去查看一下以确认

三、基于lvm的备份，属于几乎热备（属于物理备份）

前提：事务日志必须跟数据文件在同一个逻辑卷上；

1、我们安装mysql时就是将其安装在逻辑卷上，这里我们就不演示了，可以参照http://qidian510.blog.51cto.com/7319882/1282064

​如果我们不在逻辑卷上：需要进行操作将其移到逻辑卷上

  #mysqldump -uroot -pmypass --lock-all-tables --all-databases --events > /backup/all_db_`date +%F`.sql      

  #service mysqld stop  

  #rm -rf /mydata/*

  #mkdir /mydata/data      #在挂载的目录下创建新目录，属主属组为mysql

  #chown -R /mydata/data

  #cd /usr/local/mysql     

  #scripts/mysql_install_db --user=mysql --datadir=/mydata/data/   #进行初始化

  #service mysqld start

​

   mysql>set session sql_log_bin=0;              #关闭二进制日志

   mysql>source /backup/all_db_`date +%F`.sql       #导入数据all_db_`date +%F`.sql

    mysql> flush privileges;                  #让其生效

   mysql> show binary logs；               #查看二进制日志文件​

   mysql> flush tables with read lock;      #施加全局锁

   mysql> flush logs;

​​​​

注意：施加锁以后不能退出，我们重新打开一个会话框进行操作

所以我们另起终端进行操作，先将上面的表保存到新创建的目录里

   # mkdir /backup/snapback-`date +%F`

   # mysql -uroot -pmypass -e 'show master status;' > /backup/snapback-2013-09-02/bin.txt

2、创建快照卷

   #lvcreate -L 100M -n mydata-snap -p r -s /dev/myvg/mydata

回到施加锁的终端上释放锁

   mysql> unlock tables;

将创建的快照卷挂载

#mount /dev/myvg/mydata-snap /mnt

3、备份

   # mkdir /backup/snapback-2013-09-02_incremental

 # cd /binlogdir

    # cp -rp /mnt/* /backup/

   mysql> insert into tb2 values (1,'tom');    #模拟数据变化

   mysql> insert into tb2 values (2,'jerry');

备份完成后，删除快照卷

   # mysqlbinlog --start-position=107 mysql-bin.000007 > /mybackup/snapback-2013-09-02/incremental.sql

   #umount /mnt

   #lvremove /dev/myvg/mydata-snap

   # service mysqld stop       #模拟数据库意外损坏

   # rm -rf/mydata/data/*

4、恢复数据库

   # cp -a /backup/snapback-2013-09-02/* /mydata/data/

   # chown -R mysql:mysql /mydata/data/*

   #service mysqld start

连接到mysql上，通过二进制日志进行恢复

   mysql>set session sql_log_bin=0;  #关闭二进制日志

   mysql>source /mybackup/snapback-2013-09-02_incremental/incremental.sql

   mysql> use testdb

   mysql> select * from tb2;

   mysql>set session sql_log_bin=0;     #确认恢复成功，打开二进制日志功能

四、基于extrabackup完成备份（物理备份）

1、下载地址:​​http://www.percona.com/software/percona-xtrabackup​​

innodb_file_per_table = 1  # 写入配置文件/etc/my.cnf中实现每表一个表空间

下载相关软件并安装，根据自己的情况选择下载

percona-toolkit-2.2.4-1.noarch.rpm

percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

   为备份建立一个只有备份权限的用户

     mysql> create user 'percona'@'localhost' identified by 'mypass';

     mysql> revoke all privileges,grant option from 'percona'@'localhost';

     mysql> grant reload,lock tables,replication client on *.* to 'percona'@'localhost';

     mysql> flush privileges;

2、完全备份一次MySQL

         ​# innobackupex --help       #可以查看帮助

   #mkdir /mybackup

   #innobackupex --host=localhost --user=percona --password=mypass /mybackup/

innobackupex: Backup created in directory '/mybackup/2013-09-02_10-13-10'

innobackupex: MySQL binlog position: filename 'mysql-bin.000005', position 583

130902 10:13:15  innobackupex: Connection to database server closed

130902 10:13:15  innobackupex: completed OK!

   一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobakupex命令的--apply-log选项可用于实现上述功能

   #service mysqld stop              #模拟异常损坏并恢复

   #rm -rf /mydata/data/*

   #innobackupex --apply-log /mybackup/2013-09-02_10-13-10/  #--apply-log把已经提交的事务同步至数据文件；没commit的事务撤销

   #innobackupex --copy-back /mybackup/2013-09-02_10-13-10/      # --copy-back       #数据库恢复，后面跟上备份目录的位置

   #chown -R mysql:mysql /mydata/data/*

#service mysqld start

3、使用innobackupex进行增量备份

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

注意：每一次恢复操作后都要重新进行备份的

   #innobackupex --host=localhost --user=percona --password=mypass /mybackup/

   mysql> drop table tb2;         #模拟数据有变化

   #innobackupex --user=percona --password=mypass --incremental/mybackup/ --incremental-basedir=/mybackup/2013-09-02_11-17-35/

           --incremental-basedir=     #表示基于什么进行增量备份

4、对mysql操作来进行第二次增量备份

   mysql> create database backupdb;

   # innobackupex --user=percona --password=mypass --incremental /mybackup/--incremental-basedir=/mybackup/2013-09-02_11-30-51/

   mysql> insert into tb1 (id,name,age)  value (4,'julia',22);   #模拟数据有变化

   mysql> insert into tb1 (id,name,age)  value (5,'kate',26);

5、模拟mysql意外损坏

   #service mysqld stop

#rm -rf /mydata/data/*

6、进行恢复

   # innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/

   # innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/--incremental-dir=/mybackup/2013-09-02_11-23-00/

   # innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/--incremental-dir=/mybackup/2013-09-02_11-32-18/

--redo-only 表示将已经提交的事务应用到数据库，还没有提交的则不撤销，防止所恢复的数据不完整

   #innobackupex --copy-back /mybackup/2013-09-02_11-30-51/

   #cd /mydata/data

   #chown -R mysql:mysql *

   #service mysqld start