1、通过物理拷贝备份与恢复
 
         对MyISAM引擎的表来说：如果当前此表在拷贝过程中没有任何写操作时，可以直接拷贝，对单张表拷贝时只需把把表相关的三个以.frm,.MYD,.MYI结尾的文件拷贝到另外的数据库实例对应的数据库目录下即可。
 
         对InnoDB表来说：即使是innodb_file_per_table=on的情况下直接拷贝与表相关的两个文件到目标实例下不行，但可以通过拷贝整个数据目录到目标实例的数据目录下的方式来完成备份和恢复。
 
2、通过mydump工具实现备份与恢复
 
（1）通过mydump备份数据
 
备份的语句格式：mysqldump [options] db_name [tbl_name ...]
 
                             mysqldump [options] --databases db_name ...
 
                             mysqldump [options] --all-databases
 
options中选项：
 
         --host=host_name（h host_name）：指定要导出的目标数据库所在的主机，默认是localhost
 
         --user=user_name（-u user_name）：指定链接目标数据库的数据库用户名
 
         --password[=password]（-p[password]）：指定链接目标数据库的数据库密码
 
         --port=port_num（-P port_num）：指定链接目标数据库的端口
 
         --all-databases（-A）：参数代表导出所有数据库里的所有的表
 
# 导出所有的数据库
]# mysqldump -uroot -p --all-databases > backup.sql
 
         --databases（-B）：参数代表导出指定的一个或多个数据库
 
# 导出一个数据库
]# mysqldump -uroot -p --databases course >course.sql
# 同时导出多个库
mysqldump -uroot -p --databases course test >mysqlbackup.sql
 
         --add-drop-database：表示在使用--databases或者是--all-databases参数时在每个create database命令前都加上drop database的命令
 
         --add-drop-table：表示在每个create table命令前加上drop table命令
 
         --add-drop-trigger：表示在每个create trigger命令前加上drop trigger命令
 
         --replace：表示使用replace命令插入数据而不是使用insert命令
 
         --default-character-set=charset_name：指定默认的字符集，默认是UTF8
 
         --set-charset：表示将SET NAMES default_character_set命令写入到导出备份文件中，默认是开启状态
 
         --dump-slave[=value]：表示从复制的slave从库导出备份，且其中包含了change master to语句，则利用此备份文件可以直接建立另一个复制从库。value参数如果不写或者=1的情况下，则change master to语句写入到dump文件中，如果设置=2则代表也写入到dump文件中，只是会注释掉
 
# 从从库上备份
]# mysqldump -uroot -p --dump-slave --single-transaction course >course.sql
 
         --master-data[=value]：表示从复制的主库上导出备份，和--dump-slave参数配置类似，可以利用此备份文件直接建立另一个复制从库。其value的取值含义也和dump-slave相同。使用该参数会自动打开—lock-all-table参数，除非同时使--single-transaction参数
 
# 从主库备份此备份方式会在备份开始之初在所有表上加一个只读锁(FLUSH TABLES WITH READ LOCK)，当成功获取了该锁并开始备份之后，此锁就会立即释放。而后续的dump过程中不会影响其他的读写操作
]# mysqldump -uroot -p --master-data=2 --single-transaction course >course.sql
 
         --tab=dir_name（-T dir_name）：参数表示将备份文件以文本文件的方式生成，dir_name指定了存放的文件路径，每个表会生成两个文件，一个是.sql文件保存的表结构信息，一个是.txt文件保存的表数据信息，但是需要在my.cnf中先配置secure_file_priv=/usr/local/mysql/backup/参数。以文本的方式导出时还可以加入以下的参数
 
                   --fields-terminated-by=str参数指定每个字段值之间的间隔符，默认是tab
 
                   --fields-enclosed-by=char参数指定每个字段值使用什么字符括起来，默认是没有
 
                   --fields-optionally-enclosed-by=char参数指定对每个非数字类型的字段值使用什么       字符括起来，默认没有
 
                   --lines-terminated-by=str参数指定行之间的结束符，默认是newline
 
# 以文本方式导出数据库
]# mysqldump -uroot -p --tab=/tmp/ course
# 导出时指定分隔符等
]# mysqldump -uroot -p --tab=/tmp/ --fields-terminated-by="===" --fields-enclosed-by="\"" test
]# cat test_index01.txt 
"4"==="DBMS stands for DataBase ..."===\N
"5"==="After you went through a ..."===\N
"6"==="In this tutorial we will show ..."===\N
 
         --ignore-table=db_name.tbl_name：代表导出过程中忽略某个指定表的导出，如果要忽略多个表则这个参数要使用多次
 
# 导出course库中除了student_bak表外的所有表结构及数据
]# mysqldump -uroot -p --ignore-table course.student_bak course > nostubak_course.sql
 
         --no-data（-d）：参数代表不要导出数据，只导出表结构
 
# 仅导出表结构不导出数据
]# mysqldump -uroot -p --no-data course > nodata_course.sql
 
         --no-create-info：仅导出数据不导出表结构
 
# 仅导出数据不导出表结构
]# mysqldump -uroot -p --no-create-info course > course.sql
 
         --routines（-R）：参数代表也要把存储过程和函数导出来
 
         --triggers：参数代表也要将触发器导出来
 
# 导出course库中的存储过程和触发器
]# mysqldump -uroot -p --routines --triggers course >course_rt.sql
 
         --where='where_condition'（-w ‘where_condition’）：参数代表仅导出符合条件的数据
 
# 导出student表中sid小于5的数据
]# mysqldump -uroot -p --where="sid < 5" course student >course.student.sql
# 导出student表中sid为5或者6的数据
mysqldump -uroot -p --where="sid in (5,6)" course student >course.student.sql
 
         --lock-all-tables（-x）：参数代表在导出过程中对每个数据库的每个表加上一个只读锁
 
         --no-autocommit：参数代表对每个表的数据导出内容用set autocommit=0和commit两个语句包围
 
         --single-transaction：参数代表将事务隔离级别设置为可重复读并在导出开始时执行start transaction开启一个新的事务，在dump的执行过程中也不会阻止任何的读写操作
 
# 备份时开启一个新的事务
]# mysqldump -uroot -p --master-data  --single-transaction  course >course.sql
 
导出命令的其他用法：
 
# 导出course库中的一个表
]# mysqldump -uroot -p course student >course.student.sql
 
（2）FLUSH TABLES WITH READ LOCK
 
         FLUSH TABLES WITH READ LOCK简称(FTWRL)，该命令主要用于备份工具获取一致性备份(数据与binlog位点匹配)。由于FTWRL总共需要持有两把全局的MDL锁，并且还需要关闭所有表对象，因此这个命令的杀伤性很大，执行命令时容易导致库hang住。
 
         FTWRL主要包括3个步骤分别是：上全局读锁(lock_global_read_lock)，上全局读锁会导致所有更新操作都会被堵塞、清理表缓存(close_cached_tables)、上全局COMMIT锁(make_global_read_lock_block_commit) ，上全局COMMIT锁时，会堵塞活跃事务提交。
 
（3）记录导出的具体逻辑
 
         打开在mysql的配置文件中配置gerenal_log的相关参数，可以记录导出过程的具体逻辑，配置如下：
 
# 修改配置文件记录导出过程的具体逻辑
general_log=on
# 记录的文件存放位置
general_log_file=/usr/local/mysql/data/general_log
 
（4）备份的导入
 
         1）通过mysql命令导入
 
# 通过mysql命令导入
]# mysql -uroot -p <test.sql
 
         2）通过source命令导入
 
# 进入备份文件存放目录下
]# cd /data/mysql/backup
# 通过source命令导入备份
mysql> source test.sql;
 
         3）文本文件的导入
 
         对于文本文件的导入，分为两步，先要导入表结构，再导入文件，表结构文件的导入可以使用”mysql test < test.sql”或者source命令导入，数据文件的导入可以使用mysqlimport或者load data infile命令。
 
# 先导入表结构
]# mysql -uroot -p test <test_index01.sql
# 再导入数据
]# mysqlimport -uroot -p --fields-terminated-by="===" --fields-enclosed-by="\"" test /tmp/test_index01.txt
 
         通过load data infile命令导入
 
# 登录mysql数据库，切换到test库，导入表结构
mysql> source test_index02.sql;
# 导入数据
mysql> load data infile "/tmp/test_index02.txt" into table test_index02 FIELDS TERMINATED BY "===" ENCLOSED BY "\"";
 
3、基于时间点的数据的恢复
 
         基于时间点的恢复是将数据库恢复到指定的时间点状态，通常是先通过全量恢复的方式先将数据库恢复到上一个全量恢复的时间点，然后再利用二进制日志恢复到指定的时间点。
 
（1）binglog日志的查看与执行
 
         Mysqlbinlog工具可以用来解析二进制日志的内容，让日志可以被执行或者是查看
 
在数据库中重新执行某个二进制日志文件中的内容，可以使用如下命令：
 
# 执行binglog日志
]# mysqlbinlog /data/mysql/data/mysql-bin.000014 | mysql -uroot –p
]# mysqlbinlog /data/mysql/data/mysql-bin.000012 /data/mysql/data/mysql-bin.000013 | mysql -uroot –p
# 对binglog日志的导出也可先将日志识别导出到文件，在导入到数据库
]# mysqlbinlog /data/mysql/data/mysql-bin.000011 > testsql.sql
]# mysql -uroot -p <testsql.sql
 
（2）根据时间段恢复数据
 
         如果是恢复某个日志文件中的一部分内容，可以通过指定—start-datetime或者是—stop-datetime参数来确定开始恢复时间和停止恢复时间。
 
# 跳过某个时间段恢复数据
~]# mysqlbinlog --stop-datetime="2018-11-07 20:19:04" /data/mysql/data/mysql-bin.000013|mysql -uroot -p 
~]# mysqlbinlog --start-datetime="2018-11-08 03:41:04" /data/mysql/data/mysql-bin.000013|mysql -uroot -p
 
（3）依据日志点恢复数据
 
         基于binglog日志恢复数据时也可以通过设置--start-position和--stop-position两个参数指定开始位置和结束位置恢复数据。
 
# 从某个点开始恢复
]# mysqlbinlog --start-position=194 /data/mysql/data/mysql-bin.000014|mysql -uroot -p
 
4、通过Select … into语句导入与导出语句
 
（1）导出数据
 
         select … into outfile命令可以用来导出表中符合条件的数据到文本文件，不会导出表结构，仅用来导出数据。
 
         语句格式：select * from Table into outfile '/PATH/filename' fields terminated by ',' enclosed by '"' lines terminated by ‘\r\n'
 
         各字段说明：
 
                   TERMINATED BY：用于指定字段值之间的符号
 
                   ENCLOSED BY：用来指定包裹文件中字符值的符号
 
                   ESCAPED BY：用来指定转义字符
 
# 将查询的数据导入到文本文件中
mysql> select * from test_index01 into outfile "/tmp/test01.txt" fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
 
（2）导入数据
 
         使用select … into outfile导出的文本文件，可以通过load data infile命令导入到表里，导入语句如下：
 
         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,   )]
 
各字段说明：
 
         terminated by：指定分隔符
 
         enclosed by：字段括起字符
 
         escaped by：转义字符
 
         terminated by：描述字段的分隔符，默认情况下是tab字符（\t）
 
         enclosed by：描述的是字段的括起字符。
 
         escaped by：描述的转义字符。默认的是反斜杠（backslash：\ ） 
 
         lines：指定了每条记录的分隔符默认为'\n'即为换行符
 
         local: 如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上
 
         replace和ignore关键词控制对现有的唯一键记录的重复的处理
 
# 导入备份的数据
mysql> load data infile '/tmp/test01.txt' into table test_index01 fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
 
5、通过Xtrabackup备份恢复数据
 
         Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写）,Xtrabackup有两个主要的工具：xtrabackup、innobackupex，其中xtrabackup只能备份InnoDB和XtraDB两种数据表，innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等。
 
（1）Xtrabackup的特点
 
         1）备份过程快速、可靠；
 
         2）备份过程不会打断正在执行的事务；
 
         3）能够基于压缩等功能节约磁盘空间和流量；
 
         4）自动实现备份检验；
 
         5）还原速度快
 
（2）Xtrabackup的安装
 
         Xtrabackup下载地址：https://www.percona.com/downloads/XtraBackup/LATEST/
 
         Xtrabackup提供了多种安装方式，使用二进制包安装，只需将xtrabackup下载下来将二进制包拷贝至二进制文件目录”/usr/ bin”下即可。
 
# 下载二进制面编译安装包
]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/tarball/percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
# 解压并将可执行文件拷贝至相应目录
]# tar -xf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
]# cp percona-xtrabackup-2.4.8-Linux-x86_64/bin/* /usr/bin/
 
（3）使用Xtrabackup进行全量备份与恢复
 
         全量备份：
 
# 备份所有的库
]# xtrabackup --backup --target-dir=/data/mysql/backup/ -uroot -pdayi123 -P 3306 --host=127.0.0.1
 
         全量恢复：
 
         恢复过程中首先要执行prepare，将所有的数据文件都准备到同一个时间点，因为在备份过程中所有的数据文件备份都是在不同的时间点，如果直接启动可能会导致冲突。
 
# 恢复前将所有数据准备到同一个时间点
]# xtrabackup --prepare --target-dir=/data/mysql/backup
# 恢复数据
]# xtrabackup --copy-back --target-dir=/data/mysql/ --datadir=/data/mysql/data/
# 确保回复后的数据的权限为mysql
]# chown mysql:mysql /data/mysql/data –R
# 回复后启动mysql
]# /etc/init.d/mysqld start
 
（4）使用Xtrabackup进行增量备份与恢复
 
         增量备份：
 
# 先进行一次增量备份
]# xtrabackup --backup --target-dir=/data/mysql/backup/20181115_LVO/ -uroot -pdayi123 -P 3306 --host=127.0.0.1
# 基于全量备份的增量备份
]# xtrabackup --backup --target-dir=/data/mysql/backup/20181116_LV1/ --incremental-basedir=/data/mysql/backup/20181115_LVO/ -uroot -pdayi123 -P 3306 --host=127.0.0.1
# 基于上一次增量备份的增量备份
]# xtrabackup --backup --target-dir=/data/mysql/backup/20181117_LV2/ --incremental-basedir=/data/mysql/backup/20181116_LV1/ -uroot -pdayi123 -P 3306 --host=127.0.0.1
 
         增量备份的恢复大体为3个步骤：恢复完全备份——>恢复增量备份到完全备份（开始恢复的增量备份要添加--redo-only参数，到最后一次增量备份去掉--redo-only参数）——>对整体的完全备份进行恢复，回滚那些未提交的数据。
 
# 恢复完全备份，并通过--redo-only参数应用xtrabackup日志中已提交的事务数据，不回滚还未提交的数据
]# xtrabackup --prepare --apply-log-only --target-dir=/data/mysql/backup/20181115_LVO/ --datadir=/data/mysql/data
]# xtrabackup --prepare --apply-log-only --target-dir=/data/mysql/backup/20181115_LVO/ --incremental-dir=/data/mysql/backup/20181116_LV1/ --datadir=/data/mysql/data
# 将两份增量备份引用到完全备份
]# xtrabackup --prepare --apply-log-only --target-dir=/data/mysql/backup/20181115_LVO/ --incremental-dir=/data/mysql/backup/20181117_LV2/ --datadir=/data/mysql/data
# 使用全备恢复数据
]# xtrabackup --copy-back --target-dir=/data/mysql/backup/20181115_LVO/ --datadir=/data/mysql/data
# 更改数据目录权限为mysql
]# chown mysql:mysql /data/mysql/data -R
 
（5）使用innobackupex进行全量备份与恢复
 
         全量备份：
 
# 使用innobackupex进行全量备份
]# innobackupex --user=root --password=dayi123 --host=127.0.0.1 --port=3306 /data/mysql/back/
 
         全量备份恢复：
 
# 生成ib_logfile文件        
]# innobackupex --apply-log --user=root --password=dayi123 --host=127.0.0.1 /data/mysql/back/2018-11-09_04-43-50/
#还原数据到mysql的数据目录，确保在my.cnf中设置datadir
]# innobackupex --copy-back --user=root --password=dayi123 --host=127.0.0.1 /data/mysql/back/2018-11-09_04-43-50/
# 修改数据目录权限
]# chown mysql:mysql /data/mysql/data –R
# 启动mysql
]# /etc/init.d/mysqld start
 
（6）使用innobackupex进行增量备份与恢复
 
         使用增量备份时，首先要有一个全量备份：
 
# 先进行一次全量备份
]# innobackupex --user=root --password=dayi123 --host=127.0.0.1 --port=3306 /data/mysql/back/
# 基于全量备份的增量备份
]# innobackupex --user=root --password=dayi123 --host=127.0.0.1 --incremental-basedir=/data/mysql/back/2018-11-09_05-30-52/ --incremental /data/mysql/back/
# 基于上次增量备份进行增量备份
]# innobackupex --user=root --password=dayi123 --host=127.0.0.1 --incremental-basedir=/data/mysql/back/2018-11-09_05-39-07/ --incremental /data/mysql/back/
 
         增量备份的恢复：
 
# 准备基础的全量备份
]# innobackupex --apply-log --redo-only /data/mysql/back/2018-11-09_05-30-52/
# 准备增量备份1
]# innobackupex --apply-log --redo-only /data/mysql/back/2018-11-09_05-30-52/ --incremental-dir=/data/mysql/back/2018-11-09_05-39-07/
# 准备增量备份2，最后一个增量备份恢复不需要指定—redo-only
]# innobackupex --apply-log /data/mysql/back/2018-11-09_05-30-52/ --incremental-dir=/data/mysql/back/2018-11-09_05-40-55/
# 把所有的备份放在一起，准备全部备份来回滚暂停的事务
]# innobackupex --apply-log /data/mysql/back/2018-11-09_05-30-52/
# 恢复数据
]# innobackupex --copy-back --user=root --password=dayi123 --host=127.0.0.1 /data/mysql/back/2018-11-09_05-30-52/
# 修改mysql数据目录的权限并启动mysql
]# chown mysql:mysql /data/mysql/data –R
]# /etc/init.d/mysqld start
 
 
 
 
 
 

在实际项目开发过程中，团队协同工作会对开发环境的数据库进行频繁的新增表、修改表结构等操作，时经常会遇到开发环境的数据库与每一位开发者本地数据库不一致的情形，可能连接本地数据库跑项目出现错误的问题，或者防止团队人员对数据库进行严重错误的操作，开发环境的数据库也需要定时的数据备份。本文以Oracle数据库为例使用数据泵进行数据库的备份(导出)与恢复(导入)。
 生产环境数据库周期性备份请参看：https://blog.csdn.net/weixin_39699061/article/details/103101292
 
数据备份(expdp)
 
expdp命令：
 expdp userid=userName/password@IP:port/instance schemas=SCHEMAS directory=DIRECTORY dumpfile=xxx.dmp logfile=xxx.log version=VERSION
 
参数说明：
 userid=userName/password@IP:port/instance
 userName :用户名
 password :用户密码
 IP：主机Ip
 port:端口号
 instance:数据库实例
 
schemas=SCHEMAS 使用dblink导出的用户不是本地的用户，需要加上schema来确定导出的用户，类似于exp中的owner，但还有一定的区别
 directory=DIRECTORY 指定转储文件和日志文件所在的目录(详见下文)
 dumpfile=xxx.dmp 指定导出文件
 logfile=xxx.log 指定导出日志文件
 version=VERSION 指定导出的版本信息
 
数据备份(执行expdp)
 
服务端执行expdp命令，如下则表明数据导出成功，则可以到到导出DMP文件在先前设置的directory目录下：
 
expdp userid=study/study_2019@127.0.0.1:1521/orcl schemas=study directory=EXPDP_IMPDP_DIR 
dumpfile=study-2019-11-26.dmp logfile=study-2019-11-26.log version=11.2.0.1.0
 
 
数据恢复(impdp)
 
impdp命令：
 impdp userName/password@IP:port/instance directory=DIRECTORY dumpfile=xxx.dmp remap_schema=user1:user2 logfile=xxx.log table_exists_action=replace version=VERSION
 参数说明：
 table_exists_action含义：
 skip 是如果已存在表，则跳过并处理下一个对象；
 append是为表增加数据;
 truncate是截断表，然后为其增加新数据；
 replace是删除已存在表，重新建表并追加数据;
 
REMAP_SCHEMA = user1:user2 可以更改该表的用户，由前者user1到后者user2。
 
数据恢复(执行impdp)
 
服务端执行impdp命令，如下提示已完成，但是有3个错误(ORA-31604:)，可以不予理会，登录用户既可以查看到已恢复的数据。
 
impdp study/study_2019@127.0.0.1:1521/orcl DIRECTORY=EXPDP_IMPDP_DIR DUMPFILE=STUDY-2019-11-26.dmp 
REMAP_SCHEMA = study:study logfile=IMPDP-2019-11-26.log table_exists_action=replace version=11.2.0.1.0
 
 
DIRECTORY查询与创建
 
expdp和impdp命令中都使用到此属性，可以查询已有的也可自己创建完成，并且需要给用户授权读写权限。
 
查询已建立的directory
 
select * from dba_directories;
 
 
创建directory并给用户授权
 
--创建directory
create or replace directory directory_name as 'url';
--用户授权 
grant read, write on directory directory_name to userName;

-- directory_name 目录名称 比如：EXPDP_IMPDP_DIR
-- url 物理路径 比如：D:\Oracle\admin\orcl\dpdump
-- userName 数据库用户 比如：study

 

 
 
删除directory
 
drop directory directory_name;

目录
 
 
前言
 
一、MySQL 完全备份
 
1.数据库备份方式精讲
 
1.1 数据库备份的重要性
 
1.2 数据库备份的分类
 
1.3 MySQL 完全备份概念解读
 
2.mysqldump 备份演练
 
2.1 使用 tar 打包文件夹备份
 
2.2 使用 mysqldump 工具备份 
 
二、MySQL 完全恢复
 
1.恢复整库操作
 
1.1 source 命令整库恢复
 
1.2 MySQL 命令整库恢复
 
 2.恢复表操作
 
2.1 使用 source 命令恢复表
 
总结
 

前言
 
随着办公自动化和电子商务的飞速发展，企业对信息系统的依赖性越来越高，数据库作为信息系统的核心，担当者重要的角色
 数据库备份，是在数据丢失的情况下，能及时恢复重要数据，防止数据丢失的一种重要手段
 一个合理的数据库备份方案，能够在数据丢失时，有有效地恢复数据，而且也需要考虑技术实现难度和有效地利用资源
 
一、MySQL 完全备份
 
1.数据库备份方式精讲
 
1.1 数据库备份的重要性
 
生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果
 
 
 
数据库备份的重要性主要体现在：
 
 
提高系统的高可用性和灾难可恢复性，在数据库系统崩溃时，没有数据库备份就没法找到数据
使用数据库备份还原数据库，是数据库崩溃时提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价未免太大
没有数据就没有一切，数据库备份是一种防范灾难的强力手段
 
 
 
 使用数据库的过程中，有多种原因造成数据的丢失：
 
 
程序错误：指对数据库操作的程序难免有些错误，造成数据丢失
人为错误：指由于使用人员的误操作造成的数据被破坏，还有可能是黑客对系统攻击造成的数据丢失等
计算机失败：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏
磁盘失败：指硬盘等存储数据的硬件设备，长时间运行后可能损坏，造成数据丢失
灾难（如火灾、地震）和偷窃：指自然灾害等的发生，有可能造成数据丢失
 
数据丢失会造成严重的经济损失，如携程网2015年5月28日数据库崩溃，每小时损失大概106万美元
所以在企业信息系统建设中，数据库的备份管理是非常重要的内容
1.2 数据库备份的分类
 
 
 
备份方式分很多种，从物理与逻辑的角度，备份可分为以下几类：
 
 
 1.2.1 物理备份
 
指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份是磁盘块为基本单位将数据从主机复制到备机
物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
冷备份（脱机备份）：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性
热备份（联机备份）：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件
1.2.2 逻辑备份
 
逻辑备份是以文件为基本单位将数据从主机复制到备机
指对数据库逻辑组件（如表等数据库对象）的备份
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份
 
1.2.3 完全备份
 
每次对数据进行完整的备份
可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象
但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些
1.2.4 差异备份
 
备份那些自从上次完全备份之后被修改过的文件，即只备份数据库部分的内容
它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库
它的优点是存储和恢复速度快
1.2.5 增量备份
 
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
1.2.6 备份方式比较
 
备份方式
完全备份
差异备份
增量备份
完全备份时的状态
表1、表2
表1、表2
表1、表2
第1次添加内容
创建表3
创建表3
创建表3
备份内容
表1、表2、表3
表3
表3
第2次添加内容
创建表4
创建表4
创建表4
备份内容
表1、表2、表3、表4
表3、表4
表4
完全备份每次都是把整个数据库中的内容进行备份
差异备份在第1次添加内容后备份，即备份新添加的内容；第2次添加内容后备份，把第1次添加的内容也进行了备份，它是根据完全备份时的状态决定的
差异备份每次都是把完全备份之后所有的修改进行备份，而不管之前是否做过差异备份
增量备份只备份每次新修改的内容，第1次创建表3，就对表3备份，第2次添加表4后，因为之前已经备份过表3，所以只备份表4的内容
1.3 MySQL 完全备份概念解读
 
MySQL 的备份方式主要有完全备份与增量备份
完全备份是对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是增量备份的基础
完全备份的优点是备份与恢复操作简单方便，缺点是数据存在大量重复，占用大量的备份空间，备份的时间长
在生产环境中，这两种备份方式都会使用，需要制定合理高效的方案达到备份数据的目的，避免数据丢失造成严重的后果
2.mysqldump 备份演练
 
MySQL 数据库的备份可以采用两种方式
因为数据库实际上就是文件，直接打包数据库文件夹，或者是使用专用备份工具 mysqldump 都可以进行备份工作
2.1 使用 tar 打包文件夹备份
 
MySQL 的数据库文件默认都是保存在安全目录的 data 文件夹下，可以直接保存 data 文件夹，因为占用空间较大，可以使用data 打包压缩进行保存
 
yum -y install xz
#数据库文件很大，可以使用压缩率较大的xz格式压缩，首选需要安装xz压缩格式工具

tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/
#对数据库文件夹进行打包操作

ls /opt
#查看打包命令是否运行成功，已经生成了备份文件

du -sh /opt/mysql-2021-10-26.tar.xz 
#对比打包前后的文件大小，可以看到压缩的备份文件所占用空间很小

tar -Jxvf /opt/mysql-2021-10-26.tar.xz -C /usr/local/mysql/data/
systemctl restart mysql
#如果数据库文件损坏数据丢失，可以解压缩文件，相当于数据恢复
 
 
 
 
2.2 使用 mysqldump 工具备份 
 
mysqldump是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库，对其进行备份或传输到远程SQL服务器。mysqldump更为通用，因为它可以备份各种表。
使用 mysqldump 可以更加灵活地控制备份的内容，比如某几个表或库都可以单独备份
 mysqlhotcopy仅适用于某些存储引擎（MyISAM和ARCHIVE）
#开始之前，创建库和表，用作例子

mysql -uroot -p123123
#输入密码进入数据库，以上为我的密码

create database SCHOOL;
#创建库
use SCHOOL;

create table CLASS01 ( id int(2) not null auto_increment, name varchar(10) not null, sex char(5) not null, hobby varchar(10), primary key (id));
#创建表结构
#字段一：id，最大显示长度2，不能为空，自动递增从1开始
#字段二：name，可变长度字段，10，不能为空
#字段三：sex，固定长度5，不能为空
#字段四：hobby，可变长度，最多10字符
#字段四：主键（id）

insert into CLASS01 values(1,'wangyi','man','reading');
insert into CLASS01 values(2,'wanger','woman','singing');
#插入2条数据

select * from CLASS01;
#检查一下

quit
#退出数据库
 
 
 
#使用 mysqldump 对某些表进行完全备份，命令格式如下：
mysqldump -u[用户名] -p[密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名.sql
#导出的为数据库脚本文件

例：对库SCHOOL中的表class进行备份至/opt/目录下
mysqldump -uroot -p[密码] SCHOOL CLASS01 > /opt/test01.sql
 
 
#对某些库进行完全备份
mysqldump -uroot -p[密码] [数据库名] > /目录/备份文件名.sql

例：
mysqldump -uroot -p123123 SCHOOL > /opt/test02


#对多个库进行完全备份
mysqldump -uroot -p123123 --databases SCHOOL sys > /opt/test03.sql


#对所有库进行完全备份
mysqldump -uroot -p123123 --all-databases > /opt/test04.sql


#直接备份表结构
mysqldump -uroot -p123123 -d SCHOOL CLASS01 > /opt/test05.sql


ls /opt/
#查看打包命令是否运行成功，已经生成了备份文件
 
 
 
 
 
 
cat /opt/test05.sql
#分析 mysqldump 工具生成的备份文件的内容
 
 
 
 上面使用了 -d 选项，说明只保存数据库的表结构，且表中只有一个库，可以看到先删除了表（同名），再创建了它
删除和创建都是最普通的 MySQL 命令，任何一条在 MySQL 中都是可以执行的语句，有了这些语句就可以创建出和现在的表结构相同的表
如果不使用 -d 选项，会把数据也进行备份，下面看看数据时如何保存
cat /opt/test01
 
 
可以看到与使用 -d 参数的差别是最后出现了 insert into 语句，往数据库中插入了两条数据，也就是说，数据的备份是保存的插入语句操作
通过以上分析，很容易理解，备份的本质就是保存数据库当前表结构的创建语句和现有数据的插入语句，有了它们就可以直接恢复数据库的状态
二、MySQL 完全恢复
 
前面介绍了数据库的完全备份，使用 mysqldump 工具可以把数据的创建语句保存在备份的脚本文件中
当出现错误时，可以使用以下几种方法恢复它们
1.恢复整库操作
 
1.1 source 命令整库恢复
 
mysql -uroot -p123123

show databases;
drop database SCHOOL;
show databases;

source /opt/test03.sql
#之前备份过的，该备份为SCHOOL和sys两个库的完整备份
#注意！选项得加“--databases”才是完整的备份，不然会恢复不了
 
 
 
 
1.2 MySQL 命令整库恢复
 
quit

mysql -uroot -p123123 -e 'drop database SCHOOL;'
mysql -uroot -p123123 -e 'SHOW DATABASES;'

mysql -uroot -p123123 < /opt/test03.sql

 mysql -uroot -p123123 -e 'SHOW DATABASES;'
 
 
 2.恢复表操作
 
恢复数据表同样可以使用 source 命令和 mysql 命令操作
2.1 使用 source 命令恢复表
 
mysql -uroot -p123123
use SCHOOL;
show tables;
drop table CLASS01;
 
source /opt/test01.sql
#恢复表

show tables;
select * from CLASS01;
#查看
 
 
 
 2.2 使用 mysql 命令恢复表
 
quit

mysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'

mysql -uroot -p123123 -e 'DROP TABLES SCHOOL.CLASS01;'
mysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'

mysql -uroot -p123123 SCHOOL < /opt/test01.sql
mysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'
 
 
 
总结
 
MySQL 需要定期实施备份，指定合适的备份计划或策略，并严格遵守
 除了进行完全备份，开启 MySQL 服务器的日志功能也很重要，完全备份加上日志，可以对 MySQL 进行最大化还原
 备份文件的名字还需钥使用统一的易于理解的名称，推荐使用库名或表名加上时间的命名规则，在需要恢复数据库时能很容易的定位到相应的所需备份文件
  

 
一、确保mysql开启了binlog日志功能
 
在/etc/my.cnf文件里的[mysqld]区块添加：
 
#这个是存储的位置为mysql配置文件的位置
 
log-bin=mysql-bin
 
然后重启mysql服务生效
 
二、创建数据库
 
先创建一个ops数据库
 
create database ops;
 
use ops;
 
创建一个customers表并写上表结构
 
create table customers(
 
id int not null auto_increment,
 
name char(20) not null,
 
age int not null,
 
primary key(id)
 
)engine=InnoDB;
 
查看创建好的表
 
desc customers;
 

 
表中插入数据：
 
insert into customers values(1,"wangbo","24");
 
insert into customers values(2,"guohui","22");
 
insert into customers values(3,"zhangheng","27");
 
检查插入数据：
 
select * from customers;
 

 
三、进行数据库全量备份
 
mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/alidata/mysql_bakcup/ops_$(date +%F).sql.gz
 
ls /alidata/mysql_bakup
 

 
参数说明：
 
-B: 指定数据库
 
-F：刷新日志
 
-R：备份存储过程等
 
-x：锁表
 
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置信息
 
四、再次插入数据
 
insert into customers values(4,"liupeng","21");
 
insert into customers values(5,"xiaoda","31");
 
insert into customers values(6,"fuaiai","26");
 
查看最新插入的数据
 
select * from customers；
 

 
五、模拟误操作、删除ops数据库
 
drop database ops;
 
此时,全备之后到误操作时刻之间，用户写入的数据在binlog中，需要恢复出来！
 
查看全备之后新增的binlog文件
 
cd /alidata/mysql_bakcup
 
#解压出已经备份好的数据库
 
gzip -d ops_2019-04-23.sql.gz
 
ls
 

 
查看数据库变化的时刻
 
grep CHANGE ops_2019-04-23.sql
 

 
这是全备时候的binlog文件位置 mysql-bin.000006的第106行
 
因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了
 
移动到binlog文件，并导出sql文件，提出其中的drop语句
 
查看mysql数据存放目录，由如下信息可知在/var/lib/mysql目录下
 
ps -ef |grep mysql
 

 
cd /var/lib/mysql
 
cp mysql-bin.000006 /alidata/mysql_bakcup/
 
cd /alidata/mysql_bakcup/
 
mysqlbinlog mysql-bin.000006 >006bin.sql #即将binlog日志转化为可正常导入的sql文件
 

 
vim 006bin.sql 删除里面drop语句
 
注意：在恢复全备数据之前必须将binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变的比较混乱
 
六、恢复数据
 
mysql -uroot -p 
 
查看数据库，看看ops库在不在
 
show databases
 

 
use ops；
 
select * from customers;
 
#此时恢复了全备时刻时候的数据
 

 
接着，使用006bin.sql文件恢复全备时刻到删除ops数据库之前，新增的数据
 
mysql -uroot -p ops <006bin.sql
 
再次查看数据库，发现备份到删除数据库之前的那部分数据也恢复了！
 
select * from customers;
 

 
以上就是mysql数据库增量数据恢复的实例过程！
 
最后总结几点：
 
1)本案例适合认为SQL语句造成的误操作或者没有主从复制等热备情况宕机时的修复
 
2)恢复条件为mysql要开启binlog日志功能，并且要全备和增量所有数据
 
3)恢复时建议对外定制更新，即禁止更新数据库
 
4)先恢复全量，然后把全备时刻点以后增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除，在恢复到数据库