MySQL数据库备份

MySQL数据库备份

备份类型：逻辑备份、物理备份、在线热备

逻辑备份：mysqldump备份

1、表级备份： mysqldump -p123 db01 emp dept > /tmp/mysqlbak/emp.sql 多个表

恢复：mysql -p db01 </tmp/mysqlbak/emp.sql

2、库级备份： mysqldump --databases db01 db02 -p > /tmp/mysqlbak/db01.sql

恢复： mysql -p </tmp/mysqlbak/db01.sql

3、全备： mysqldump -p --lock-tables --all-databases > /backup/full.sql

如果数据文件全部丢失，先停止数据库，进行初始化，重设置密码，然后恢复。

增备：使用二进制日志文件恢复到想要的状态

mysqlbinlog --start-position=120 --stop-position=341 mybinlog.000004|mysql -p

逻辑导入导出：无论是什么存储引擎，本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份

select xxx into outfile ‘/path/file’ from table_name;

物理备份：

1、xtrabackup备份

1）：yum -y install libev-4.15-3.el7.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

使用工具innobackupex可以对所有存储引擎的数据备份

创建一个用户，给与相应的权限

grant reload,process,lock tables,replication client on . to ‘admin’@‘localhost’ identified by ‘123’;

相关权限：RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE

完全备份：innobackupex --user=admin --password=123 /full_xtrabckup

备份后的数据不能使用，必须应用日志文件

–apply-log选项来应用日志

innobackupex --user=admin --password=123 --apply-log /full_xtrabckup/2018-10-12_23-23-57/

增备：原理是配合二进制日志文件恢复想要的数据

xtrabackup_binlog_info文件中存放着增量备份需要的文件和起始地址

备份命令：mysqlbinlog --start-position=12229 mybinlog.000005 > /backup/date +%F.sql

起始位置根据自己主机情况更改

还原全备：innobackupex --defaults-file=/etc/my.cnf --copy-back /full_xtrabckup/2018-10-12_23-23-57/

还原增备：mysql> source /backup/date +%F.sql

2）：通过innobackupex实现增量备份

全备： innobackupex --user=admin /incremental_bak/

增备1： innobackupex --incremental /incremental_new_bak --incremental-basedir=/incremental_bak/2018-08-04_14-41-46/ --user=admin

增备2：innobackupex --incremental /incremental_new_bak/ --incremental-basedir=/incremental_new_bak/2018-08-04_14-49-40 --user=admin

增备时：需要依赖上一个备份文件，incremental后指定的目录必须是上一个备份文件的地址，否则失败

备份文件需要应用日志，恢复时直接全量恢复

应用日志到备份集：

innobackupex --apply-log --redo-only /incremental_bak/2018-08-04_14-41-46/

应用增量备份1：

innobackupex --apply-log --redo-only /incremental_bak/2018-08-04_14-41-46/ --incremental-dir=/incremental_new_bak/2018-08-04_14-49-40/

应用增量备份2：

innobackupex --apply-log /incremental_bak/2018-08-04_14-41-46/ --incremental-dir=/incremental_new_bak/2018-08-04_14-55-02/

最后一个增备是，需要回滚，不加选项–redo-only

或者回滚未提交事务：innobackupex --apply-log /xtrabak/2017-07-15_07-46-49/（完全备份的备份集）

恢复全备：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /incremental_bak/2018-08-04_14-41-46/