mysql数据备份与导入（二）

 6.逻辑导出导入

1.数据逻辑导出：

注意点：需要指定导出的文件，在my.cnf中新增secure_file_priv=导出到的文件，且此文件所在目录需要mysql用户的rwx权限

select 需要导出的字段 into outfile '导出到的文件' from 表名;

2.数据逻辑导入：

方法1：需要登录到mysql中

load data local infile '需导入文件全路径' into table 需导入的表名;

方法2：要求导入的文件名必须和数据表名称完全一致

mysqlimport 表所在库名  需导入文件全路径  

7.物理备份

1.xtrabackup备份介绍

优点：1.备份速度快、可靠2.支持增量备份3.备份过程不会打断正在执行的事务4基于压缩等功能节约磁盘空间和流量5.自动实现备份检验6.还原速度快

缺点：1.只能innodb增量备份myisam只支持全量备份2.innobackupex备份myisam表之前要对全库加READ LOCK，阻塞写操作，若备份从库会影响主从同步，对innodb表备份不会阻塞读写

2.xtrabackup备份原理

 redo log日志？

3.获取xtrabackup备份工具

4.xtrabackup全库备份及恢复

核心思路：1.首先对数据库进行全量备份，备份完成后不能立即恢复（数据不完整，缺少备份过程中的数据）2.预备阶段，备份过程中产生的xtrabackup_log数据应用到全量备份集。

1.数据准备以及账号创建：

1.创建数据库创表（innodb与myisam）
create database db3 default charset=utf8;
create table user1(id int,name varchar(20)) engine=innodb default charset=utf8;
create table user2(id int,name varchar(20)) engine=myisam default charset=utf8;
insert into user1 values (3,'张三');
insert into user2 values (5,'李四');

2.专门创建一个mysql备份账号，开通相应权限
grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
flush privileges;
说明：reload 与lock tables权限：值了执行flush tables with read lock
      replication client权限：为了获取binary log位置
      process权限：查询当前会话信息，例如show engine

2.开始备份

innobackupex --user=admin  --password=123 /fullback

第一次备份过程中出现以下错误 当前mysql.sock文件位于/tmp下，出现以上报错原因可能有两种原因：

原因1.系统中/etc目录下还有my.cnf文件影响了数据库连接

原因2.innobackupex的默认套接字配置在报错目录下

解决方案：

方案1.软连接
mkdir /var/lib/mysql
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

方案2.命令加入-S参数并指定mysql.sock的路径
innobackupex  -S=/tmp/mysql.sock  --user=admin  --password=123 /fullback

3.预备阶段，把备份这段时间内产生的日志整合到全量备份中

innobackupex --user=admin  --password=123  --apply-log  /fullback/2022-09-11_00-49-06/

--apply-log 表示整合备份过程中的预备阶段的产生的日志

4.模拟数据库故障

rm -rf /mysql_3306/data/*
pkill mysqld

5.恢复数据库中的数据

innobackupex --copy-back /fullback/2022-09-11_00-49-06/

第一次恢复时报错

出现原因：innobackupex工具无法找到mysql中的数据目录

解决方案：把mysql.cnf配置文件传递给innobackupex，让其自动识别这个文件的datadir

innobackupex  --defaults-file=/mysql_3306/my.cnf  --copy-back /fullback/2022-09-11_00-49-06/

6.恢复完后，修改/mysql_3306/data目录下文件权限，否则无法启动

chown -R mysql.mysql /mysql_3306/data/*

5.xtrabackup增量备份及恢复

1.原理

2.备份模拟

前两步：参考全量备份前两步

第三步：整合全量备份时产生的日志进行整合

innobackupex --user=admin  --password=123  --apply-log  --redo-only  /fullback/全量备份文件夹名称

--apply-log 表示整合备份过程中的预备阶段的产生的日志
新增--redo-only
--redo-only  表示只应用已提交的事务，不回滚未提交的事务（事务：开启后成功提交会写入硬盘，失败了会回滚）
特别注意：如果已经回滚未提交事务，无法应用增量备份


如果未加入此选项：增量备份时，会产生数据不一致（未提交的事务（有可能未失败）默认回滚，增量备份时事务成功了此事务结果就不一致了）

第四步：做增量备份

 innobackupex --user=admin --password=123 --incremental 增量备份目录 --incremental-basedir=此增量基于的全量备份目录

第五步：整合增量的数据以及日志文件到全量备份中

innobackupex --user=admin --password=123 --apply-log  全量备份目录 --incremental-dir=增量备份目录

后续步骤参考全量备份4-6步