多种方法备份Mysql数据库

前言：Mysql数据库的备份是重中之重，在生产过程中，数据库会因硬件故障，软件故障，******，误操作等造成数据丢失，但经过精密的备份，完全能把数据恢复过来.

一，备份工具

这里介绍几种常用的备份方式

cp:物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份

mysqldump:逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备

xtrabackup:一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份,

二，cp备份与恢复

先查看数据，我们用test库里的student表实验

mysql> select * from test.student;+------+------+| id   | name |+------+------+|    1 | a    |+------+------+1 row in set (0.00 sec)

拷贝mysql数据到指定目录

mkdir  /backupcp -a /var/lib/mysql/*  /backup

模拟丢失数据，删除mysql数据

rm -rf /var/lib/mysql/*

重启数据库，发现数据丢失（这里是yum安装能直接重启数据库，如果是编译安装还需要初始化）

service  mysqld restartmysql> select * from test.student;ERROR 1146 (42S02): Table 'test.student' doesn't existmysql>

把备份的数据还原到数据目录

cp -a /backup/*  /var/lib/mysql/

登录数据库，发现数据已经还原回来了

mysql> select * from test.student;+------+------+| id   | name |+------+------+|    1 | a    |+------+------+1 row in set (0.00 sec)mysql>

三，mysqldump备份与恢复

先查看数据，我们用test库里的student表实验

mysql> select * from test.student;+------+------+| id   | name |+------+------+|    1 | a    ||    2 | b    |+------+------+2 rows in set (0.00 sec)

修改my.cnf，添加log_bin,重启数据库

vim /etc/my.cnf[mysqld]log_bin=mysql-binservice  mysqld  restart

利用Mysqldump备份数据，及参数说明

mysqldump  -uroot   -A  --events   --master-data=2  --single-transaction   >/opt/all.sql-A  全备，恢复时不需要创建库--events  备份时间调度器--master-data  记录时刻点--single-transaction 锁表备份

插入新的数据

mysql> use  test;mysql> insert into student values(3,'c');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+------+------+| id   | name |+------+------+|    1 | a    ||    2 | b    ||    3 | c    |+------+------+3 rows in set (0.00 sec)

模拟数据丢失，误操作

mysql> drop database test;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              |+--------------------+2 rows in set (0.00 sec)

开始恢复，利用mysqldump的全备加上增量备份

1，关闭日志记录

set  global  sql_log_bin=0;

2，恢复全备,检查数据情况

mysql -uroot < /opt/all.sqlmysql> select * from test.student;+------+------+| id   | name |+------+------+|    1 | a    ||    2 | b    |+------+------+2 rows in set (0.00 sec)

3，利用时刻点恢复增量恢复，查看/opt/all.sql的开始时刻点，在文件里有类似如下字符串

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=705

在看误操作的时刻点

mysqlbinlog  /var/lib/mysql/mysql-bin.000018 # at 869#161102 18:36:09 server id 1  end_log_pos 896   Xid = 4088COMMIT/*!*/;# at 896#161102 18:36:25 server id 1  end_log_pos 977   Query   thread_id=41    exec_time=0     error_code=0SET TIMESTAMP=1478082985/*!*/;drop database test

根据开始时刻点和结束时刻点恢复

mysqlbinlog  /var/lib/mysql/mysql-bin.000018 --start-position=705 --stop-position=896 | mysql -uroot

4,恢复完成，查看数据

mysql> select * from test.student;+------+------+| id   | name |+------+------+|    1 | a    ||    2 | b    ||    3 | c    |+------+------+3 rows in set (0.00 sec)

四，xtrabackup备份与恢复之全量

1，下载工具，幷安装

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

yum install percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

2，创建备份目录，幷备份

mkdir /exbackupinnobackupex --user=root /exbackup/innobackupex --apply-log /exbackup/2016-11-02_20-14-08/

3，查看备份文件

[root@data-1-1 3306]# ll /exbackup/2016-11-02_20-14-08/总用量 30760drwx------. 2 root root     4096 11月  2 20:49 2016-11-02_20-49-56-rw-r-----. 1 root root      386 11月  2 20:14 backup-my.cnf-rw-r-----. 1 root root 18874368 11月  2 20:21 ibdata1-rw-r--r--. 1 root root  5242880 11月  2 20:21 ib_logfile0-rw-r--r--. 1 root root  5242880 11月  2 20:21 ib_logfile1drwx------. 2 root root     4096 11月  2 20:14 mysqldrwx------. 2 root root     4096 11月  2 20:14 oldboydrwx------. 2 root root     4096 11月  2 20:14 performance_schemadrwx------. 2 root root     4096 11月  2 20:14 test-rw-r-----. 1 root root       21 11月  2 20:14 xtrabackup_binlog_info-rw-r--r--. 1 root root       23 11月  2 20:21 xtrabackup_binlog_pos_innodb-rw-r-----. 1 root root      113 11月  2 20:21 xtrabackup_checkpoints-rw-r-----. 1 root root      511 11月  2 20:14 xtrabackup_info-rw-r-----. 1 root root  2097152 11月  2 20:21 xtrabackup_logfile

4,模拟数据丢失，进行恢复

rm -rf /data/*mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)

5,利用备份文件，恢复数据，重启数据库，查看数据

innobackupex --copy-back /exbackup/2016-11-02_20-14-08/ chown -R  mysql:mysql /data/*service mysqld restartmysql> show databases;+------------------------------+| Database                     |+------------------------------+| information_schema           || #mysql50#2016-11-02_20-49-56 || mysql                        || oldboy                       || performance_schema           || test                         |+------------------------------+6 rows in set (0.00 sec)

五，xtrabackup备份与恢复之增量

1，上面我们已经做了全备，我们新添数据做增量备份实验

mysql> show databases;+------------------------------+| Database                     |+------------------------------+| information_schema           || #mysql50#2016-11-02_20-49-56 || mysql                        || oldboy                       || performance_schema           || t1                           || t2                           || test                         |+------------------------------+

2，进行增量备份，注意：下一次增量 --incremental-basedir=最近的增量备份路径

 innobackupex --incremental /exbackup/ --incremental-basedir=/exbackup/2016-11-02_20-14-08/ innobackupex --apply-log --redo-only /exbackup/2016-11-02_20-14-08/  innobackupex --apply-log --redo-only /exbackup/2016-11-02_20-14-08/ --incremental-basedir=/exbackup/2016-11-02_21-31-50/

3，查看增量备份

[root@data-1-1 exbackup]# ll /exbackup/总用量 8drwx------. 7 root root 4096 11月  2 20:49 2016-11-02_20-14-08drwx------. 9 root root 4096 11月  2 21:32 2016-11-02_21-31-50

4,模拟数据丢失，进行恢复

rm -rf /data/*mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)

5,利用备份文件，恢复数据，重启数据库，查看数据

innobackupex --copy-back /exbackup/2016-11-02_20-14-08/  #直接通过全量进行增量的恢复chown -R  mysql:mysql /data/*service mysqld restartmysql> show databases;+------------------------------+| Database                     |+------------------------------+| information_schema           || #mysql50#2016-11-02_20-49-56 || mysql                        || oldboy                       || performance_schema           || t1                           || t2                           || test                         |+------------------------------+8 rows in set (0.01 sec)

六，xtrabackup备份与恢复 多实例

1，步骤基本一样，只是多加一些参数

innobackupex   --defaults-file=/data/3306/my.cnf --user=root  /exbackupinnobackupex   --apply-log /exbackup/2016-11-02_20-14-08/ innobackupex   --defaults-file=/data/3306/my.cnf --copy-back /exbackup/2016-11-02_20-14-08/

2，增量

innobackupex   --defaults-file=/data/3306/my.cnf --user=root  --incremental /exbackup/ --incremental-basedir=/exbackup/2016-11-02_20-14-08/innobackupex   --apply-log --redo-only /exbackup/2016-11-02_20-14-08/  innobackupex   --apply-log --redo-only /exbackup/2016-11-02_20-14-08/ --incremental-dir=/exbackup/2016-11-02_21-31-50/innobackupex   --defaults-file=/data/3306/my.cnf --copy-back /exbackup/2016-11-02_20-14-08/

总结：只是多了配置文件的参数，其他步骤完全一致，这里就不花篇幅进行这些操作了，大家可以进行测试，本文没有过多的原理解释，全是干货操作，想了解这些备份方法的原理，参数的网友就见谅了