数据库的备份与恢复

  1. 数据备份:将源数据再次存储到新的位置

  2. 数据恢复:将备份好的数据重新应用到数据库系统

  3. 常见的备份类型:

      a.按照是否备份整个数据集来分

          完全备份:备份从开始到执行备份这一时刻的所有数据集

          增量备份:备份从上次备份到这次备份时刻变化的数据集

          差异备份:备份从上一次完全备份到这一备份时刻变化的数据集

     b.按照备份的数据库对象来分

          物理备份:通过复制数据集在文件系统中的文件来备份

          逻辑备份:在数据库的客户端工具上使用SQL语句,应用程序或其他脚本文件做备份的操作

     c.按照备份时,还能提供的服务分类

         热备份:备份的过程中,可以对备份的数据集做读写操作

         温备份:备份的过程中,只能对备份的数据集只能做读操作,不可有写操作

         冷备份:备份的过程中,直接将服务下线,读写操作均无法执行

4.    备份的数据内容:
        1.数据;
        2.二进制日志
        3.InnoDB存储以前的事务日志文件;
        4.代码——存储过程,存储函数,触发器,事件调度器等;
        5.当前服务器上用于启动数据服务是所使用的配置文件;
        6.操作系统上与MySQL或MariaDB相关的配置——sudo任务、cron任务等;

常用的备份数据的方法:

    1)使用select语句导出数据做备份,然后使用load data命令做数据恢复

         备份类型:因为是使用SQL语句做备份的,所以是温备份

         备份语句:SELECT .....  INTO OUTFILE "file_name" [FIELDS TERMINATED BY 'string'] [ENCLOSED BY 'char']  [ESCAPED BY 'char'] ]   [LINES [STARTING BY 'string']  [TERMINATED BY 'string']]

         数据导入语句(数据恢复):LOAD DATA INFILE “/path/file_name” INTO TABLE table_name  [FIELDS TERMINATED BY 'string'] [ENCLOSED BY 'char']  [ESCAPED BY 'char'] ]   [LINES [STARTING BY 'string']  [TERMINATED BY 'string']]

        FIELDS各选项内容

            TERMINATED BY 'string'  :指定导出或者导入时的字段分隔符

             ENCLOSED BY 'char' :指定导入或导出时的引用符号

             ESCAPED BY 'char'  :定义转义字符默认是  \

        LINES各选项内容

            STARTING BY 'string':指定行的起始符号,默认不指定

            TERMINATED BY 'string':指定行的分隔符,默认换行符

数据备份恢复实例:

MariaDB [hellodb]> lock tables students read;  给表添加读锁,以免在做数据备份的时候,数据发生变化
MariaDB [hellodb]> select * from students into outfile "stud.txt"; //将students表内的所有内容,备份,放到stud.txt文件中
Query OK, 25 rows affected (0.01 sec)
因为此次操作在hellodb数据库中做的,所以默认文件将会创建在hellodb数据库下的stud.txt文件中
查看stud.txt文件内的内容
MariaDB [hellodb]> unlock tables;  释放锁
[root@bogon ~]# cat /var/lib/mysql/hellodb/stud.txt
1    Shi Zhongyu    22    M    2    3
2    Shi Potian    22    M    1    7
3    Xie Yanke    53    M    2    6
4    Ding Dian    32    M    4    4
5    Yu Yutong    26    M    3    1
6    Shi Qing    46    M    5    7
7    Xi Ren    19    F    3    9
8    Lin Daiyu    17    F    7    5

图片.png

MariaDB [hellodb]> delete from students;  //清除students表中的数据
MariaDB [hellodb]> select * from students; //查看students表中是否有数据
Empty set (0.00 sec)

MariaDB [hellodb]> load data infile "/var/lib/mysql/hellodb/stud.txt" into table students; //做数据恢复,使用的文件请用绝对路径
Query OK, 25 rows affected (0.09 sec)                
Records: 25  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [hellodb]> select * from students;  //再次查看表
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |         6 |

注意:1.这种备份操作,属于逻辑备份和温备份,备份时需要提前给表施加读锁;

          2.使用select导出数据,只会导出数据,但不会导出表的格式,所以当我们使用load data导入数据时,要保证表存在,且表结构与之前相同,所以这种备份只能做表数据备份恢复,

MariaDB [hellodb]> alter table students drop TeacherID;  //删除最后一个字段
MariaDB [hellodb]> load data infile "/var/lib/mysql/hellodb/stud.txt" into table students;
Query OK, 25 rows affected, 25 warnings (0.01 sec)   
Records: 25  Deleted: 0  Skipped: 0  Warnings: 25

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+
| StuID | Name          | Age | Gender | ClassID |
+-------+---------------+-----+--------+---------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |
|     2 | Shi Potian    |  22 | M      |       1 |
|     3 | Xie Yanke     |  53 | M      |       2 |
|     4 | Ding Dian     |  32 | M      |       4 |
做恢复时,会有警告,但是不影响前面几个字段的数据恢复,最后一个字段无法恢复
MariaDB [hellodb]> alter table students drop Name;删除中间字段
MariaDB [hellodb]> load data infile "/var/lib/mysql/hellodb/stud.txt" into table students;
Query OK, 25 rows affected, 100 warnings (0.01 sec)  
Records: 25  Deleted: 0  Skipped: 0  Warnings: 100

MariaDB [hellodb]> select * from students;
+-------+-----+--------+---------+
| StuID | Age | Gender | ClassID |
+-------+-----+--------+---------+
|     1 |   0 |        |       0 |
|     2 |   0 |        |       0 |
|     3 |   0 |        |       0 |
|     4 |   0 |        |       0 |
|     5 |   0 |        |       0 |
|     6 |   0 |        |       0 |
出现警告,但数据恢复操作执行完成,但是由于多个字段间数据类型等不一致,会导致表数据无法正常恢复;


删除表操作
MariaDB [hellodb]> drop table students;
Query OK, 0 rows affected (0.04 sec)

MariaDB [hellodb]> load data infile "/var/lib/mysql/hellodb/stud.txt" into table students;
ERROR 1146 (42S02): Table 'hellodb.students' doesn't exist
会显示错误,表不存在无法恢复

          3.当我们使用哪个数据库做数据导出时,其导出的文件会默认存在该数据库的目录下;当然我们可以使用变量 secure_file_priv修改保存路径,但是此变量是只读变量;

          4.若我们导出的数据内容,变换了默认格式,则在导入的时候也应该使用相同的格式导入,否则表内容出错;

MariaDB [hellodb]> select * from hellodb.students into outfile "students1.txt" fields terminated by ":";  //导出数据时,变换默认的分隔符
Query OK, 25 rows affected (0.02 sec)

[root@bogon ~]# cat /var/lib/mysql/hellodb/students1.txt
1:Shi Zhongyu:22:M:2:3
2:Shi Potian:22:M:1:7
3:Xie Yanke:53:M:2:6
4:Ding Dian:32:M:4:4
5:Yu Yutong:26:M:3:1

导入时,不变换分隔符
MariaDB [hellodb]> load data infile "/var/lib/mysql/hellodb/students1.txt" into table students;
Query OK, 25 rows affected, 150 warnings (0.02 sec)  
Records: 25  Deleted: 0  Skipped: 0  Warnings: 150

MariaDB [hellodb]> select * from students;
+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
|     1 |      |   0 |        |    NULL |      NULL |
|     2 |      |   0 |        |    NULL |      NULL |
|     3 |      |   0 |        |    NULL |      NULL |
|     4 |      |   0 |        |    NULL |      NULL |

        5.在做数据导入,可以使用mysqlimport命令

            用法mysqlimport [options] db_name textfile1

[root@bogon ~]# mysqlimport -d hellodb /var/lib/mysql/hellodb/students.txt 
hellodb.students: Records: 25  Deleted: 0  Skipped: 0  Warnings: 0

mysqlimport原理和load data相同,只是执行方式不同

    mysqlimport选项:

        --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...
            --lines-terminated-by=...
            --use-threads=N:使用多mysql线程实现并发导入;
            --lock-tables, -l:给表施加锁;
            --local, -L:读取客户端本地的某文件,将其中的内容恢复之表中;
            --ignore-lines=N:导入数据时忽略前N行记录;

 2)使用mysqldump工具做数据的备份和恢复;

            此备份恢复是针对数据库及数据库中数据集做恢复的

格式:mysqldump [options] [db_name [tbl_name ...]]
        shell> mysqldump [options] db_name [tbl_name ...] 
        shell> mysqldump [options] --databases db_name ...
        shell> mysqldump [options] --all-databases

mysqldump原理:会将你要导出的表,或数据库,写成SQL语句并且标准输出,此时需要我们用重定向,将这些内容,写入一个新的文件之中,将文件后缀改为.sql,  使得其可以在mysql之中运行,从而达到数据恢复的效果

        简单示例:

[root@bogon ~]# mysqldump hellodb students > stud.sql
vim stud.sql

图片.png

然后我们删掉表并且做恢复

MariaDB [hellodb]> drop table students;
Query OK, 0 rows affected (0.02 sec)

MariaDB [hellodb]> \. stud.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......

    mysqldump的常用选项

          连接选项:
            -u, -h, -p, -P, -S, --protocol;用法和mysql相同
        筛选选项:
            --all-databases, -A   备份所有的数据库
            --databases, -B  指定需要备份的数据库;一旦写上此选项,则该选项后面的所有内容都被当成数据库名称,而且在输出的文件中,每个数据库前都会加上创建数据库和use语句;

加--databases和不加--databases的区别
不加--databases ,不会创建数据库,只会恢复其中的表,所以我们需要依赖一个指定的数据库才行

图片.png         

加上--databases 
会创建出数据库,并且在创建数据库之中创建相应的表

图片.png

  --ignore-table=db_name.tbl_name 指定不导出哪个表,一个该选项只能写一个表
            --no-data, -d   不再导出表中的数据,仅仅导出表结构

图片.png          

         --routines, -R
                导出存储函数和存储过程,但不会导出它们的属性值;
            --events, -E
                导出事件调度器;
            --triggers
                导出触发器,此功能默认开启,想要关闭可以使用--skip-triggers选项;
            --tables
                覆盖了--databases选项,使得其后面所有的参数都被当作表名处理;使用了此选项,每次mysqldump仅能导出一个库中表的数据;
                    格式为:--tables db_name tbl_name1 tbl_name2 ...

示例:导出某个数据的某个表
[root@bogon ~]# mysqldump --database hellodb students > hello.sql   //若不加--tables 怎会将students表,当数据库处理,显然这不是数据库,则报错
mysqldump: Got error: 1049: "Unknown database 'students'" when selecting the database
[root@bogon ~]# mysqldump --database hellodb --tables students > hello.sql  所以需要加一个--tables做分隔

            --where=´where_condition´, -w ´where_condition´
                指定筛选条件并导出表中符合筛选条件的数据;
                如:--where='Name=James'

        DDL选项:
            --add-drop-database
                在导出的数据文件中的每一个create databases语句前加上drop database if exists的语句先删除指定的数据库;

/*!40000 DROP DATABASE IF EXISTS `hellodb`*/;   //会在创建数据库前一行添加此行

    

            --add-drop-table
                在导出的数据文件中的每一个create table语句前加上drop table if exists语句,先删除指定表;默认已经开启的功能;
            --no-create-db, -n
                在使用了--databases或者--all-databases选项时,在导出的文件中回加上创建数据库的语句;而一旦使用的此选项,前两个选项的功能会被抑制;

图片.png           


        --no-create-info, -t
                不会在导出的文件中添加创建表的语句;
            --replace
                所有向表中添加数据的操作语句均被替换成REPLACE语句,而不是INSERT语句;

        字符集选项:
            --default-character-set=charset_name
                在导出数据的过程中,指定导出时使用的字符集;
                此选项有时很重要,迁移数据的目标主机上和当前服务器的字符集如果不相同,可能会导致乱码;
                默认的字符集:utf8
            --set-charset
                在导出的数据结果中加上SET NAMES statement语句,默认此功能已被启用。
                想要关闭此功能,可以使用选项:--skip-set-charset;

        与主从复制相关的选项:
            --delete-master-logs
                在执行了一次dump操作之后,会向主服务器发送一个PURGE BINARY LOGS statement,以删除其二进制日志;并且会自动激活--master-data选项的功能;
            --master-data[=value]
                该选项主要用来建立一个复制操作;
                    当值为1时,导出的数据文件中会记录CHANGE MASTER语句;
                    当值为2时,导出的数据文件中会记录CHANGE MASTER语句,但该语句被注释;
                    默认值为无任何值,意味着在导出的数据文件中不会记录CHANGE MASTER语句;

                    在使用此选项时,会自动忽略--lock-tables选项;
                    当使用了--single-tranaction选项时,也会自动激活--lock-all-tables;

        格式化选项:
            --compact
                紧凑格式输出,即:简化输出的数据内容,几乎所有的注释类信息都不会被输出到文件中;
                相当于:--skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset选项的组合;
            --complete-insert, -c
                在INSERT语句中加入各个被操作的列的名称;
            --tab=path, -T path
                将每个表的结构定义用的SQL语句和表中存储的数据内容,分别导出到指定的目录中,使用不同的文件进行保存;其中TBL_NAME.sql文件存放表的定义格式,TBL_NAME.txt文件存放表中的数据;在导出数据内容的时候,实质上就是使用了SELECT ... OUTFILE 'tbl_name.txt'语句,默认的字段分隔符就是"\t";

                使用此选项操作时,必须要有一个前提条件:client和server必须是同一台主机,且mysql用户对指定的目录要有写权限,同时连接数据库的用户必须有FILE权限;而且还要求不能和--databases或--all-databases选项一起使用。

                    --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=..., --lines-terminated-by=...
                上述选项可以在此时应用,用于指定各种分隔符;
            --quote-names, -Q
                引用表名或列名时,使用何种引用符号,默认的引用符号为反向单引号,即:`

        性能选项:
            --delayed-insert
                在非事务型存储引擎管理的表中,在INSERT操作时可以支持DELAYED功能;
                在MySQL的5.6.6开始已经被废弃;
            --disable-keys, -K
                在INSERT语句前后加上禁用和启用索引的语句;
                在大量的数据插入时,此选项很有用,默认开启的;
            --insert-ignore
                使用INSERT IGNORE语句代替INSERT语句;
            --quick, -q
                快速导出数据,在导出大表时非常有用;
                默认导出数据时会一次性检索表中所有数据并加入到内存中,而使用该选项则是检索一行导出一行;

        加锁和事务有关的选项:
            --add-locks
                在INSERT语句的前后加上LOCK TABLES和UNLOCK TABLES语句,默认开启;
            --flush-logs, -F
                在开始导出数据前事先刷写二进制日志(二进制日志手动的滚动);
                如果使用了--all-databases选项,则依次在导出各个数据库之前都会执行FLUSH LOGS;
                如果使用了--lock-all-tables或--master-data选项,则刷写日志仅执行一次;等价于FLUSH TABLES WITH READ LOCK;锁定所有表,可以保证数据的一致性;
            --flush-privileges
                在导出所有数据库之后在数据文件的结尾加上FLUSH PRIVILEGES语句;
                一般会在导出mysql库或者依赖于mysql库时都应该使用此选项;
            --lock-all-tables, -x
                为所有的表施加一个一直持续到数据导出结束的全局读锁。该选项仅在数据导出开始时施加一次读锁,而该锁锁所有表且永久锁,直到数据导出结束才会释放;
                该选项开启后会自动关闭--single-transaction和--lock-tables选项;
            --lock-tables, -l
                在导出每个数据库的数据之前依次对该数据库中的所有表施加读锁(多次的锁施加过程),对于非事务型的存储引擎(如:MyISAM)的表可以进行并行锁操作;但对于事务型存储引擎(如:InnoDB)的表来说,使用--single-tranaction选项更好,因为InnoDB无需完全锁表;
            --single-transaction
                该选项在导出数据前将设置一个事务,且隔离级别为REPEATABLE-READ,并且同时发送一个START TRANSACTION语句给服务器端;
                该选项在导出InnoDB存储引擎的表时非常有用,一旦开启事务,则意味着数据库会有一致性和持久性的安全保护,不会阻塞任何其他会话或线程的数据处理。
            --no-autocommit
                在INSERT语句前后加上SET @@autocommit=0及SET @@autocommit=1的语句,并且在需要提交事务时添加COMMIT语句;
            --order-by-primary
                如果被导出的表中存在主键索引或唯一键索引,则排序后按照顺序导出数据;
                对于导出MyISAM表比导出InnoDB表更有用;


完整示例:

    1.导出一个innodb引擎的表

        [root@bogon ~]# mysqldump --database hellodb --no-create-db --tables students --single-transaction --flush-logs > hello.sql
  MariaDB [hellodb]> drop table students;   //破坏数据
Query OK, 0 rows affected (0.04 sec)

    2.做数据恢复

MariaDB [hellodb]> set @@sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> \. hello.sql
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> set @@sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

    注意:

        1.在做备份的时候,同样需要我们给表施加锁,面对MyISAM表我们添加--lock-tables,面对innodb表需要添加,--single-transaction

        2.在做数据导出时,尽量添加--flush-logs选项,刷写二进制日志,这样以后可以更方便的使用该二进制日志做增量恢复;

        3.在做数据恢复时,也会产生二进制日志,但是这种日志内容,是多余的,我们可以手动关闭二进制日志记录功能,等待恢复完成后,再手动开启

     3)使用lvm2做备份恢复;

            是物理备份,温备份

            备份原理:使用快照技术,将此事此刻的数据库系统内容做标记,若数据库某一内容发生变化,则将其原数据内容复制到快照空间中,没发生变化的则不会做快照。

            恢复原理:将快照后的内容保存到其他文件中,若数据库丢失,则可以将快照的内容复制到数据库目录中,实现恢复;

具体操作:

        1.添加新的磁盘(做快照方便)

            为其创建卷组,及逻辑卷,并格式化逻辑卷,建议文件系统类型为ext4

pvcreate /dev/sdb
vgcreate -s 16M testvg /dev/sdb
lvcreate -L 10GiB -n testlv testvg
mkfs -t ext4  /dev/testvg/testlv
mkdir /data/mysql
mount /dev/testvg/testlv /data/mysql/   //将做好的逻辑卷挂载到/data/mysql下

            2.关闭mysql服务,修改mysql数据库目录为新目录,并将原来的数据库内容复制一份到新数据库目录下

cp -a /var/lib/mysql/* /data/mysql/
chown -R mysql. /data/mysql/
修改/etc/my.cnf(设置selinxu策略)
[mysqld]
datadir=/data/mysql
开启服务

            3.进入mysql交互命令中,施加全局锁

MariaDB [(none)]> flush tables with read lock;

            4.为数据库目录创建快照,

lvcreate -s -L 1GiB -p r -n mysql_photo /dev/testvg/testlv
创建完成之后,解锁
MariaDB [(none)]> unlock tables;

            5.挂载快照,并将其内容复制出去,然后删除快照

mount /dev/testvg/mysql_photo /mydata/photo/
cp -a /mydata/photo/* /mysql/

            6.做数据恢复(线下恢复),所以需要关闭mysql服务

[root@bogon ~]# systemctl stop mariadb.service
[root@bogon ~]# cp -a /mysql/* /data/mysql/
[root@bogon ~]# chown -R mysql. /data/mysql/  修改权限
[root@bogon ~]# systemctl start mariadb.service 启动服务
[root@bogon ~]# mysql -S /data/mysql/mysql.sock连接数据库

注意:快照恢复是物理备份,和完全备份,若想实现对快照后数据变化的恢复,则需要我们使用二进制日志文件


    4)使用xtrabackup工具做恢复

            物理备份

            对MyISAM引擎的表做温备份(施加全局锁,或备份锁)

            对innodb引擎的表做热备份(备份并监控redo log,在其备份期间若redo log还在变化,则会将变化的内容写入缓存,并依次备份,等备份结束还会对备份内容重写,实现热备份)

        步骤:

            备份过程:备份redo log文件以及获取二进制文件坐标点,并做备份

            准备过程:若引擎是innodb,则对备份好的redo log文件做检测,主要是对一些事物,实现前滚或回滚

            恢复过程:物理恢复,需要关闭服务

        语法:

            innobackupex [options] /path/file

option:
                    连接选项:
                        --user=USERNAME
                        --host=HOSTNAME
                        --port=PORT
                        --password=PASSWORD
                        --socket=SOCKET_FILE_NAME
                    功能选项:
                        --backup:实施数据备份过程;(默认)
                        --prepare:实施准备过程;
                        --copy-back:实施数据恢复过程;
                        --apply-log:在准备过程中,用于应用redo log;
                        --redo-only:在准备过程中,仅应用redo log;
                        --incremental:在数据备份的过程中,实施增量备份;
                        --incremental-basedir:在实施增量备份时,用于指定增量备份的参考目录;

innobackupex /ttt
......
180804 05:45:17 completed OK!
[root@bogon ~]# ll /ttt/
总用量 4
drwx------. 12 root root 4096 8月   4 05:45 2018-08-04_05-45-15  默认完全备份

            增量备份

[root@bogon 2018-08-04_05-52-36]# innobackupex --incremental --incremental-basedir /ttt/2018-08-04_05-45-15 /ttt   //基于完全备份的目录做增量备份

            准备(需要安照步骤依次准备):每次准备都会把增量内容添加到完全备份上

[root@bogon ttt]# innobackupex --apply-log --redo-only /ttt/2018-08-04_05-45-15/  /完全备份准备
innobackupex --apply-log --redo-only /ttt/2018-08-04_05-45-15/ --incremental-dir /ttt/2018-08-04_05-52-36/  //增量备份准备
                                       完全备份目录                                    增量备份目录

            恢复:(需要停止mysql服务,并且保证datadir下为空),否则报错

Original data directory /var/lib/mysql is not empty!

[root@bogon ttt]# innobackupex --copy-back /ttt/2018-08-04_05-45-15/   //恢复只需要写完全备份目录
[root@bogon ttt]# chown -R mysql. /var/lib/mysql/  //修改权限

            启动服务即可;

相关文章
相关标签/搜索