MySQL数据库备份还原
一、 数据的备份恢复
mysqldump (逻辑备份 只可全备)
mysqlbinlog(逻辑备份)
xtrabackup(物理备份 全备,增量,差异)
物理备:直接复制数据文件进行备份,占用空间大,速度快
逻辑备:从数据库中'导出'进行备份,占用空间小,速度慢,有可能丢失精度
mysqldump
ps:mysqldump是以覆盖的形式恢复数据
#用法
Usage: mysqldump [OPTIONS] database [tables] #还原时需手动创建库
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
#连接数据库端口号
--port, -P
#指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
--socket, -S
#指定数据库
--database, -B
#所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库
--all--database，-A
# 导出全部数据 --all-databases, -A
mysqldump -uroot -p -A > mysql.sql
#不加参数备份(备份表,但是不会备份库,恢复时要手动创建库)
mysqldump -uroot -p test > /tmp/test.sql
#导出指定数据库信息 --databases, -B (只能指定库,不能指定表)
mysqldump -uroot -p -B test>mysql.sql
#指定需要导出的表名
--tables
mysqldump -h127.0.0.1 -uroot -ppasswd -B test --tables test >mysql.sql
#导出某张表的某个字段(使用 where 条件的时候，引号是必须的。where 条件的使用和 sql 语句中 where 是相同的。)
-w, --where="name"
#备份触发器数据(有则备份)。该选项默认启用；用--skip-triggers禁用它
--triggers
#不查询缓存,直接输出,加快备份速度 (defaults to on ,--skip-quick to disable)
--quick, -q
#忽略错误继续执行
--force, -f
#使用16进制转储二进制列,当有BINARY,VARBINARYY,BLOB,BIT的数据类型时使用,防止乱码
--hex-blob
#不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。
--ignore-table
例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
#备份事件
--events, -E
#在每个CREATE DATABASE语句前添加DROP DATABASE语句
--add-drop--database
#导出全部表空间
--all-tablespaces , -Y
#在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用–skip-add-locks取消选项)
--add-locks
#仅备份表结构
--no-data, -d
#不导出任何数据，只导出数据库表结构。
--no-create-info, -t
#备份函数和存储过程(有则备份)
--routines, -R
#设置默认字符集，默认值为utf8
--default-character-set
#备份时刷新binlog
-F --flush-logs (备份时刷新binlog (每个库1次)配合--databse|--all-database 时会多次刷新,可以通过--single-transaction| -x ,--master-data 一起使用此时之刷新一次binlog)
mysqldump -uroot -p -A -F > /tmp/full.sql
#用于在master端dump数据，用于建立slave,备份时加入change master语句 0 没有,1不注释,2注释(备份时记录binlog的文件和新增数据的起点) 添加从库时设置为1 让从库和主库的文件和位置点一致 (Mysqldump 8.0.26以后为 source-data )
--master-data=1 将change master 语句写入dump文件中在从库导入后，配置主从无需再指定文件名和位置
--master-data=2 将change master 语句写入dump文件中，只不过会被注释掉在从库导入后，配置主从需要指定文件名和位置(会自动关闭--Lock-tables,开启-x全局读锁,除非加上--single-transaction)
#用户在slave端dump数据，建立新的slave,至少是第2个slave,也就是已经有A-->B,现在从B上导出数据建立A-->C的复制
--dump-slave=1 |--source-data=1 直接导入备份文件，start slave不需要指定file和position
--dump-slave=2 |--source-data=2 file和position是被注释掉的，需要手动指定file和position
mysqldump -uroot -p -A -R --triggers --master-data=2 > /tmp/full.sql (温备可读不可写)
--single-transaction (以事务的方式开启备份,备份前会先执行begin开启事务, 又称快照备份,把当前要备份的数据做瞬间做快照(其实就是记录binlog日志的结束位置点和文件),只备份快照内的数据适用于innodb,不适用myisam)
mysqldump -uroot -p -A -R --triggers --single-transaction --master-data=2 > /tmp/full.sql (热备可读可写)
-gzip 压缩备份
mysqldump -uroot -p -A -R --triggers --single-transaction --master-data=2 |gzip > /tmp/full.sql.gz(热备压缩)
#不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。
例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
mysqldump -h127.0.0.1 -uroot -ppasswd -A --ignore-table=mysql.user > mysql.sql
#不导出任何数据，只导出数据库表结构。
--no-data, -d
mysqldump -h127.0.0.1 -uroot -ppasswd -A -d > mysql.sql
#提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
--lock-all-tables, -x
例如:
mysqldump -h127.0.0.1 -uroot -ppasswd -A -x > mysql.sql
mysqldump -AR -uroot -p --socket=/data/mysqldata/mysql.sock --triggers -x | gzip >./sql_full_$(date +%F_%X).sql.gz
#对于每个要备份的数据库,在启动备份前分别锁定其所有表,默认为on,可以使用--skip-lock-tables禁用(多库备份时,只会对当前备份库中所有表进行锁定,其余表不受影响,当前库备份完成后会解锁,然后对下一个备份库加锁)
--lock-tables, -l
#分库备份脚本测试
#!/bin/bash
Mysql_user=root
Mysql_password=USER_PASSWORD
Mysql_sock=/data/mysqldata/mysql.sock
Data_dir=/backup
if [ ! -d ${Data_dir} ];then
mkdir -p {Data_dir}
fi
for db in `mysql -u${Mysql_user} -p${Mysql_password} -e "show databases"`; do
if [[ "$db" =~ ^(info|sys|perf).* ]]; then
continue
else
mysqldump -u${Mysql_user} -p${Mysql_password} -S ${Mysql_sock} --single-transaction -F -B $db |gzip > ${Data_dir}/${db}_`date +%F_%H:%M:%S`.sql.gz"
fi
done企业故障恢复案例
背景：
正在运行的网站系统，MySQL数据库，数据量20G，日业务增量10-15M。
备份策略：
每天00：00，计划任务调用mysqldump执行全备脚本
故障时间点：
上午10点误删除一个核心业务表
思路：
1）停对外业务避免数据的二次伤害
2）找一个临时的库，恢复前一天的全备数据
3）截取前一天00：00到第二天10点误删除之间的binlog，恢复到临时库
4）测试可用性和完整性
5）开启业务前的两种方式
a.直接使用临时库顶替原生产库，前端应用割接到新库
b.将误删除的表单独导出，然后导入到原生产环境
6）开启业务
模拟演练
准备环境
创建数据库和表
create database backup;
create table t1 select * from test.student;
create table t2 select * from test.student;
create table t3 select * from test.student;
create table t4 select * from world.city;
全备数据
mysqldump -uroot -pecho -A -R --triggers --single-transaction --master-data=2 | gzip > /tmp/full_$(date +%F).sql.gz
模拟数据变化
create table user select * from mysql.user;
create table country select * from world.country;
update t4 set countrycode='CHN' where 1=1;
delete from t4 where id>200;
drop table t4;
drop table user;
开始恢复
首先停止对外服务
systemctl stop nignx php tomcat
准备一个新库
修改 新库配置文件 将binlog改为row模式(与故障机一致)
binlog-format=row
mysqld_safe --defaults-file=/data3309/my.cnf & #启动一个新实例
zcat /tmp/full_2021-07-04.sql.gz > /tmp/full_2021-07-04.sql #解压数据
mysql -uroot -p -S /data3309/data/mysql.sock < /tmp/full_2021-07-04.sql #导入全备数据
mysql -uroot -p -S /data3309/data/mysql.sock #连接3309查看已导入数据
截取3306 binlog数据
1).找起点(查看全备的数据文件)
cat /tmp/full_2021-07-04.sql|more
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000005', MASTER_LOG_POS=136030;
2).找终点
mysqlbinlog --base64-output=decode-rows -vvv /data/mysql_bin.000005 | tail -200 | grep -C5 -i "drop"
终点为 535950
3).开始截取
mysqlbinlog --start-position=136030 --stop-position=535950 /data/mysql_bin.000005 > /tmp/mysql.sql
导入数据
mysql -uroot -p -S /data3309/data/mysql.sock
set sql_log_bin=0 临时关闭binlog
source /tmp/mysql.sql
将误删除的表单独导出，然后导入到原生产环境
mysqldump -uroot -p -S /data3309/data/mysql.sock -B backup --tables user > /tmp/user.sql 导出数据
mysql -uroot -p backup < /tmp/user.sql 导入到源数据库
xtrabackup安装使用(物理备份)
安装:
#下载xtrabackup 包
wget -c https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
#安装包(需要安装依赖)
yum localinstall -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm #(解决依赖安装)
[innobackupex参数说明]
常用参数
--user：该选项表示备份账号。
--password：该选项表示备份的密码。
--port：该选项表示备份数据库的端口。
--host：该选项表示备份数据库的地址。
--socket：该选项表示mysql.sock所在位置，以便备份进程登录mysql。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："db1 db2"，同时，在指定某数据库时，也可以只指定其中的某张表。 如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。压缩参数
--compress：该选项表示压缩innodb数据文件的备份。
--compress-threads：该选项表示并行压缩worker线程的数量。
--compress-chunk-size：该选项表示每个压缩线程worker buffer的大小，单位是字节，默认是64K。加密参数
--encrypt：该选项表示通过ENCRYPTION_ALGORITHM的算法加密innodb数据文件的备份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-key：该选项使用合适长度加密key，因为会记录到命令行，所以不推荐使用。
--encryption-key-file：该选项表示文件必须是一个简单二进制或者文本文件，加密key可通过以下命令行命令生成：openssl rand -base64 24。
--encrypt-threads：该选项表示并行加密的worker线程数量。
--encrypt-chunk-size：该选项表示每个加密线程worker buffer的大小，单位是字节，默认是64K。增量备份参数
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir。
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用。
--incremental-lsn：该选项表示指定增量备份的LSN，与--incremental选项一起使用。
--incremental-dir：该选项表示增量备份的目录。
--incremental-force-scan：该选项表示创建一份增量备份时，强制扫描所有增量备份中的数据页。
--incremental-history-name：该选项表示存储在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录的名字。Percona Xtrabackup搜索历史表查找最近 （innodb_to_lsn）成功备份并且将to_lsn值作为增量备份启动出事lsn.与innobackupex--incremental-history-uuid互斥。如果没有检测到有效的lsn，xtrabackup会返回error。
--incremental-history-uuid：该选项表示存储在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID。主从
--slave-info：该选项表示对slave进行备份的时候使用，打印出master的名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件。可以通过基于这份备份启动一个从库。
--safe-slave-backup：该选项表示为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭知道没有打开的临时表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启。--include：该选项表示使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份的表的完整名称，即databasename.tablename。
--tables-file：该选项表示指定含有表列表的文件，格式为database.table，该选项直接传给--tables-file。
--no-timestamp：该选项可以表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹。
--rsync：该选项表示通过rsync工具优化本地传输，当指定这个选项，innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表的时候会快很多，不能--stream一起使用。
--stream：该选项表示流式备份的格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream。
--ibbackup：该选项指定了使用哪个xtrabackup二进制程序。IBBACKUP-BINARY是运行percona xtrabackup的命令。这个选项适用于xtrbackup二进制不在你是搜索和工作目录，如果指定了该选项,innoabackupex自动决定用的二进制程序。
--kill-long-queries-timeout：该选项表示从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数。默认值为0，不会kill任何查询，使用这个选项xtrabackup需要有Process和super权限。
--kill-long-query-type：该选项表示kill的类型，默认是all，可选select。
--ftwrl-wait-threshold：该选项表示检测到长查询，单位是秒，表示长查询的阈值。
--ftwrl-wait-query-type：该选项表示获得全局锁之前允许那种查询完成，默认是ALL，可选update。
--galera-info：该选项表示生成了包含创建备份时候本地节点状态的文件xtrabackup_galera_info文件，该选项只适用于备份PXC。
--defaults-extra-file：该选项指定了在标准defaults-file之前从哪个额外的文件读取MySQL配置，必须在命令行的第一个选项的位置。一般用于存备份用户的用户名和密码的配置文件。
--defaults-group：该选项表示从配置文件读取的组，innobakcupex多个实例部署时使用。
--no-lock：该选项表示关闭FTWRL的表锁，只有在所有表都是Innodb表并且不关心backup的binlog pos点，如果有任何DDL语句正在执行或者非InnoDB正在更新时（包括mysql库下的表），都不应该使用这个选项，后果是导致备份数据不一致，如果考虑备份因为获得锁失败，可以考虑--safe-slave-backup立刻停止复制线程。
--tmpdir：该选项表示指定--stream的时候，指定临时文件存在哪里，在streaming和拷贝到远程server之前，事务日志首先存在临时文件里。在 使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--history：该选项表示percona server 的备份历史记录在percona_schema.xtrabackup_history表。 --close-files：该选项表示关闭不再访问的文件句柄，当xtrabackup打开表空间通常并不关闭文件句柄目的是正确的处理DDL操作。如果表空间数量巨大，这是一种可以关闭不再访问的文件句柄的方法。使用该选项有风险，会有产生不一致备份的可能。
--compact：该选项表示创建一份没有辅助索引的紧凑的备份。
--throttle：该选项表示每秒IO操作的次数，只作用于bakcup阶段有效。apply-log和--copy-back不生效不要一起用。
备份方式（物理备份）
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份时读取配置文件/etc/my.cnf
备份时刻，立即将已经commit过的内存中的数据页刷新到磁盘
备份时刻有可能会有其他数据写入，已备走的数据文件就不会再发生变化了
在备份过程中，备份软件会一直监控着redo和undo，一旦有变化会将日志一并备走
全备使用:
innobackupex --user=root --socket=/data/mysql.sock --password=echo /backup
innobackupex --user=root --socket=/data/mysql.sock --password=echo --no-timestamp /backup/full #不让目录按时间命名(全备)
[root@db02 full]\# ll /backup/full/
total 180252
drwxr-x--- 2 root root 202 Jul 5 21:56 backup
-rw-r----- 1 root root 494 Jul 5 21:56 backup-my.cnf mysql配置文件中默认参数
-rw-r----- 1 root root 79691776 Jul 5 21:56 ibdata1 共享表空间
-rw-r----- 1 root root 104857600 Jul 5 21:56 ibdata2 共享表空间(undo)
drwxr-x--- 2 root root 4096 Jul 5 21:56 mysql
drwxr-x--- 2 root root 4096 Jul 5 21:56 performance_schema
drwxr-x--- 2 root root 204 Jul 5 21:56 test
drwxr-x--- 2 root root 184 Jul 5 21:56 world
-rw-r----- 1 root root 21 Jul 5 21:56 xtrabackup_binlog_info 记录binlog名和位置点
-rw-r----- 1 root root 141 Jul 5 21:56 xtrabackup_checkpoints 记录备份信息
-rw-r----- 1 root root 513 Jul 5 21:56 xtrabackup_info 备份汇总信息
-rw-r----- 1 root root 2560 Jul 5 21:56 xtrabackup_logfile redo
[root@db02 full]\# cat xtrabackup_checkpoints
#备份类型:full全备
backup_type = full-backuped
#日志版本号 备份到哪
from_lsn = 0
to_lsn = 554795923
#更新到哪
last_lsn = 554795923
compact = 0
recover_binlog_info = 0
flushed_lsn = 554795923
[root@db02 full]# cat xtrabackup_info
#记录当前实例的uuid
uuid = db1c00df-dd98-11eb-b558-000c299bba30
name =
#记录用什么工具备份 命令,参数,版本
tool_name = innobackupex
tool_command = --user=root --socket=/data/mysql.sock --password=... --no-timestamp /backup/full
tool_version = 2.4.23
ibbackup_version = 2.4.23
server_version = 5.6.40-log
#备份开始结束时间
start_time = 2021-07-05 21:56:51
end_time = 2021-07-05 21:56:54
#锁定时间
lock_time = 0
#binlog位置点
binlog_pos = filename 'mysql_bin.000008', position '120'
#日志版本号 备份到哪
innodb_from_lsn = 0
innodb_to_lsn = 554795923
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
全备恢复
准备备份
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚 ，模拟CSR的过程
[root@db01 full]\# innobackupex --user=root --password=echo --apply-log /backup/full #模拟csr过程
[root@db02 ~]# ll /backup/full/
total 299040
drwxr-x--- 2 root root 202 Jul 5 21:56 backup
-rw-r----- 1 root root 494 Jul 5 21:56 backup-my.cnf
-rw-r----- 1 root root 79691776 Jul 5 22:23 ibdata1
-rw-r----- 1 root root 104857600 Jul 5 22:23 ibdata2
-rw-r----- 1 root root 50331648 Jul 5 22:23 ib_logfile0
-rw-r----- 1 root root 50331648 Jul 5 22:23 ib_logfile1
-rw-r----- 1 root root 12582912 Jul 5 22:23 ibtmp1
drwxr-x--- 2 root root 4096 Jul 5 21:56 mysql
drwxr-x--- 2 root root 4096 Jul 5 21:56 performance_schema
drwxr-x--- 2 root root 204 Jul 5 21:56 test
drwxr-x--- 2 root root 184 Jul 5 21:56 world
备份恢复
前提1：被恢复的目录是空的
前提2：被恢复的数据库的实例是关闭的
basedir = /usr/local/mysqld
datadir = /data
[root@db01 full]\# /etc/init.d/mysqld stop #停库 保证被恢复的数据库的实例是关闭的
[root@db01 full]\# cd /data/ #进入数据目录
[root@db01 full]\# mv /data/ /tmp #移动下现有数据目录 保证被恢复的目录是空的
[root@db01 full]\# innobackupex --copy-back /backup/full 将数据copy回/data目录下(前提:因为xtrabackup会读取配置文件,所以要指定好basedir和 datadir路径 也可以手动cp )
[root@db01 full]\# chown -R mysql.mysql /data #修改权限
增量备份和恢复
备份方式
1）基于上一次备份进行增量
2）增量备份无法单独恢复，必须基于全备进行恢复
3）所有增量必须要按顺序合并到全备当中
模拟实验
先做一次增量备份
innobackupex --user=root --password=echo --no-timestamp /backup/full
模拟第一次数据变化
mysql> create database data1;
mysql> use data1
mysql> create table t1(id int);
mysql> insert into t1 values (1),(2),(3),(4);
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
进行第一次增备
innobackupex --user=root --password=echo --no-timestamp (不以时间名) --incremental(增量备份打开) --incremental-basedir=/backup/full (incremental-basedir=/backup/full 上次备份的目录) /backup/inc1
innobackupex --user=root --password=echo --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 #增量备份
[root@db02 backup]# ll
total 0
drwxr-x--- 7 root root 244 Jul 6 21:02 full
drwxr-x--- 8 root root 309 Jul 6 21:13 inc1
[root@db02 backup]\# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 554815036 结束位置
last_lsn = 554815036
compact = 0
recover_binlog_info = 0
flushed_lsn = 554815036
[root@db02 backup]\# cat inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 554815036 增备开始位置
to_lsn = 554820687
last_lsn = 554820687
compact = 0
recover_binlog_info = 0
flushed_lsn = 554820687
模拟第二次数据变化
mysql> create database data2;
mysql> use data2
mysql> create table t2(id int);
mysql> insert into t2 values (1),(2),(3),(4);
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
进行第二次增量备份
[root@db02 backup]\# innobackupex --user=root --password=echo --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2
[root@db02 backup]\# cat inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 554815036
to_lsn = 554820687 *******
last_lsn = 554820687
compact = 0
recover_binlog_info = 0
flushed_lsn = 554820687
root@db02 backup]\# cat inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 554820687 *******
to_lsn = 554826923
last_lsn = 554826923
compact = 0
recover_binlog_info = 0
flushed_lsn = 554826923
破坏数据
[root@db02 backup]\# /etc/init.d/mysqld stop
[root@db02 backup]\# rm -rf /data
恢复数据
准备备份
1）full+inc1+inc2
2）需要将inc1和inc2按顺序合并到full中
3）分步骤进行--apply-log在全备中apply-log时，只做redo，不做undo(不管有多少次增备) 只有最后一次增备合并和最后一次整体full目录做redo和undo
原因:如果在第一次增备时最后一个请求没有commit,恰巧commit做到了第二次增备中,那么如果执行undo 数据就会回滚,此数据会丢失
1.全备中做redo 不做undo 模拟csr
[root@db02 ~]\# innobackupex --apply-log --redo-only /backup/full
2.将inc1增量备份合并到full中，并且apply-log，只做redo 不做undo
[root@db02 ~]\# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full/
3.将inc2增量备份合并到full中,因为是最后一次增量合并 应用apply-log时,redo和undo都要应用
[root@db02 backup]\# innobackupex --apply-log --incremental-dir=/backup/inc2/ /backup/full/
4.整体full目录做一次apply-log redo和undo都要应用
[root@db02 backup]\# innobackupex --apply-log /backup/full/
5.恢复数据
[root@db02 backup]\# innobackupex --user=root --password=echo --copy-back /backup/full
6.修改所有者和所属组
chown -R mysql.mysql /data
7.启动mysqld查看是否有数据
差异备份
差异备份--incremental-basedir=/backup/full/ 指定为上次全备的目录,每次差异备份后,差异备份的起始lsn位置都是全备的结束lsn
1.准备一次全备
innobackupex --user=root --password=echo --no-timestamp /backup/full
[root@db02 backup]\# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 554832329 *******
last_lsn = 554832329
compact = 0
recover_binlog_info = 0
flushed_lsn = 554832329
2.模拟第一次数据变化
mysql> create database data5;
mysql> use data5
mysql> create table t5(id int);
mysql> insert into t5 values (1),(2),(3),(4);
3.做第一次差异备份
[root@db02 backup]\# innobackupex --user=root --password=echo --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/chayi1
[root@db02 backup]\# cat chayi1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 554832329 与全备的结束lsn相同
to_lsn = 554838592
last_lsn = 554838592
compact = 0
recover_binlog_info = 0
flushed_lsn = 554838592
4.模拟第二次数据变化
mysql> create database data6;
mysql> use data6
mysql> create table t6(id int);
mysql> insert into t6 values (1),(2),(3),(4);
5.做第二次差异备份
[root@db02 backup]\# innobackupex --user=root --password=echo --no-timestamp --incremental --incremental-basedir=backup/full /backup/chayi2
[root@db02 backup]\# cat chayi2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 554832329 *****与全备的结束lsn相同
to_lsn = 554844842
last_lsn = 554844842
compact = 0
recover_binlog_info = 0
flushed_lsn = 554844842
6.破坏数据
[root@db02 backup]\# /etc/init.d/mysqld stop
root@db02 backup]\# rm -rf /data
7.apply-log
1).全备中做redo 不做undo 模拟csr
[root@db02 ~]\# innobackupex --apply-log --redo-only /backup/full
2).合并最后一次差异备份 且redo和undo都要做(差异数据恢复=全备+差异备份)
[root@db02 backup]\# innobackupex --apply-log --incremental-dir=/backup/chayi2/ /backup/full
3).整体full目录做一次apply-log redo和undo都要应用
[root@db02 backup]\# innobackupex --apply-log /backup/full
8.恢复数据且修改所有者和所属组
[root@db02 backup]\# innobackupex --user=root --password=echo --copy-back /backup/full/
[root@db02 backup]\# chown -R mysql.mysql /data
9. 启动mysql查看数据是否恢复二、 数据导入
MySQL数据命令导入方式常用基本有四种
mysql命令导入 (系统终端)
source 命令导入(mysql终端)
load 命令导入(mysql 终端)
mysqlimport命令导入(系统终端)
mysql命令导入
#格式
mysql -u用户名 -p密码 [-D|--database] < 要导入的数据库数据(test_db.sql)
mysql -uroot -p123456 < test_db.sql
mysql命令导入
#格式
mysql> source < 要导入的数据库数据(test_db.sql)使用 load data 导入数据
MySQL 中提供了load data infile语句来插入数据。 将从当前目录中读取文件 ，并将该文件中的数据插入到当前数据库的指定表中
mysql8需要在登陆时手动指定--local-infile=1,开启load data
#语法格式
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
mysql> load data local infile '文件路径/文件名' into table 指定表;
例如:
# mysql8需要在登陆时手动指定--local-infile=1,开启load data
mysql -uroot -p --local_infile=1 #客户端登录
mysql> set global local_infile=1; #开启服务端load data
Query OK, 0 rows affected (0.00 sec)
mysql> use test_db; #使用test_db库
Database changed
mysql> create table t_table(id int not null auto_increment,name varchar(30),primary key (id)); #创建一个测试表
mysql> select * from t_table;
Empty set (0.01 sec)
mysql> load data local infile '/root/test.txt' into table t_table fields terminated by ',' lines terminated by '\n'; #导入表
Query OK, 10000 rows affected (0.59 sec)
Records: 10000 Deleted: 0 Skipped: 0 Warnings: 0
#查看导入数据是否成功
mysql> select count(*) from t_table;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.12 sec)
mysql> select * from t_table limit 10;
+----+----------+
| id | name |
+----+----------+
| 1 | 用户1 |
| 2 | 用户2 |
| 3 | 用户3 |
| 4 | 用户4 |
| 5 | 用户5 |
| 6 | 用户6 |
| 7 | 用户7 |
| 8 | 用户8 |
| 9 | 用户9 |
| 10 | 用户10 |
+----+----------+
mysql> set global local_infile=0; 关闭load data选项如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。
其中local是关键词,表示从客户主机路径读取文件,如果local没指定，文件必须位于服务器上。
replace和ignore关键词控制对现有的唯一键记录的重复的处理。
如果你指定replace，新行将代替有相同的唯一键值的现有行 (覆盖)。
如果你指定ignore，跳过有唯一键的现有行的重复行的输入
如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略
load data语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。两个命令的fields和 lines 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，fields 子句必须出现在 lines 子句之前,fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL至少需要下面的一个选项：
terminated by字段的分隔符，默认情况下是tab字符（\t）
enclosed by字段的界定符。
escaped by转义字符。默认的是反斜杠（backslash：\ ）
