多种方法备份Mysql数据库
前言：MySQL数据库的备份是重中之重，在生产过程中，数据库会因硬件故障，软件故障，******，误操作等造成数据丢失，但经过精密的备份，完全能把数据恢复过来.
我们提供的服务有：成都网站建设、做网站、微信公众号开发、网站优化、网站认证、泰兴ssl等。为近千家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的泰兴网站制作公司
一，备份工具
这里介绍几种常用的备份方式
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 /backup cp -a /var/lib/mysql/* /backup
模拟丢失数据，删除mysql数据
rm -rf /var/lib/mysql/*
重启数据库，发现数据丢失（这里是yum安装能直接重启数据库，如果是编译安装还需要初始化）
service mysqld restart mysql> select * from test.student; ERROR 1146 (42S02): Table 'test.student' doesn't exist mysql>
把备份的数据还原到数据目录
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-bin service 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.sql mysql> 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 = 4088 COMMIT/*!*/; # at 896 #161102 18:36:25 server id 1 end_log_pos 977 Query thread_id=41 exec_time=0 error_code=0 SET 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 /exbackup innobackupex --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/ 总用量 30760 drwx------. 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_logfile1 drwx------. 2 root root 4096 11月 2 20:14 mysql drwx------. 2 root root 4096 11月 2 20:14 oldboy drwx------. 2 root root 4096 11月 2 20:14 performance_schema drwx------. 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 restart mysql> 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/ 总用量 8 drwx------. 7 root root 4096 11月 2 20:49 2016-11-02_20-14-08 drwx------. 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 restart mysql> 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 /exbackup innobackupex --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/
总结：只是多了配置文件的参数，其他步骤完全一致，这里就不花篇幅进行这些操作了，大家可以进行测试，本文没有过多的原理解释，全是干货操作，想了解这些备份方法的原理，参数的网友就见谅了
当前文章：多种方法备份Mysql数据库
当前URL：http://6mz.cn/article/jidscj.html