当前位置：Gxlcms > 数据库问题 > MySQL数据库备份与恢复

MySQL数据库备份与恢复

时间：2021-07-01 10:21:17 帮助过：9人阅读

SELECT * FROM test_db.tb_students_info -> INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/file.txt‘ -> FIELDS TERMINATED BY ‘"‘ -> LINES TERMINATED BY ‘?‘; Query OK, 10 rows affected (0.06 sec)

 

 

数据库恢复

数据库恢复是指以备份为基础，与备份相对应的系统维护和管理操作。

系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。

数据库恢复机制设计的两个关键问题是：第一，如何建立冗余数据；第二，如何利用这些冗余数据实施数据库恢复。

建立冗余数据最常用的技术是数据转储和登录日志文件。通常在一个数据库系统中，这两种方法是一起使用的。

数据转储是 DBA 定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的版本成为后备副本或后援副本。

可使用 LOAD DATA…INFILE 语句来恢复先前备份的数据。

【实例】将之前导出的数据备份文件 file.txt 导入数据库 test_db 的表 tb_students_copy 中，其中 tb_students_copy 的表结构和 tb_students_info 相同。

首先创建表 tb_students_copy，输入的 SQL 语句和执行结果如下所示。

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> tb_students_copy
  2.     </span><span style="color: #808080;">-></span> <span style="color: #808080;">LIKE</span><span style="color: #000000;"> tb_students_info;
  3. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.52</span><span style="color: #000000;"> sec)
  4. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> tb_students_copy;
  5. Empty </span><span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

导入数据与查询表 tb_students_copy 的过程如下所示。

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">LOAD</span> DATA INFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">C:/ProgramData/MySQL/MySQL Server 5.7/
  2. Uploads/file.txt</span><span style="color: #ff0000;">‘</span>
  3.     <span style="color: #808080;">-></span> <span style="color: #0000ff;">INTO</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> test_db.tb_students_copy
  4.     </span><span style="color: #808080;">-></span> FIELDS TERMINATED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>
  5.     <span style="color: #808080;">-></span> OPTIONALLY ENCLOSED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">"</span><span style="color: #ff0000;">‘</span>
  6.     <span style="color: #808080;">-></span> LINES TERMINATED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">?</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  7. Query OK, </span><span style="color: #800000; font-weight: bold;">10</span> rows affected (<span style="color: #800000; font-weight: bold;">0.14</span><span style="color: #000000;"> sec)
  8. Records: </span><span style="color: #800000; font-weight: bold;">10</span>  Deleted: <span style="color: #800000; font-weight: bold;">0</span>  Skipped: <span style="color: #800000; font-weight: bold;">0</span>  Warnings: <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">
  9. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> test_db.tb_students_copy;
  10. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+--------+---------+------+------+--------+------------+</span>
  11. <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name   <span style="color: #808080;">|</span> dept_id <span style="color: #808080;">|</span> age  <span style="color: #808080;">|</span> sex  <span style="color: #808080;">|</span> height <span style="color: #808080;">|</span> login_date <span style="color: #808080;">|</span>
  12. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+--------+---------+------+------+--------+------------+</span>
  13. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> Dany   <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">25</span> <span style="color: #808080;">|</span> F    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">160</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2015</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">09</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">10</span> <span style="color: #808080;">|</span>
  14. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> Green  <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">23</span> <span style="color: #808080;">|</span> F    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">158</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2016</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">22</span> <span style="color: #808080;">|</span>
  15. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">|</span> Henry  <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">23</span> <span style="color: #808080;">|</span> M    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">185</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2015</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">05</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">31</span> <span style="color: #808080;">|</span>
  16. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">4</span> <span style="color: #808080;">|</span> Jane   <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">22</span> <span style="color: #808080;">|</span> F    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">162</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2016</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">12</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">20</span> <span style="color: #808080;">|</span>
  17. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">5</span> <span style="color: #808080;">|</span> Jim    <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">24</span> <span style="color: #808080;">|</span> M    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">175</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2016</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">01</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">15</span> <span style="color: #808080;">|</span>
  18. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">6</span> <span style="color: #808080;">|</span> John   <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">21</span> <span style="color: #808080;">|</span> M    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">172</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2015</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">11</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">11</span> <span style="color: #808080;">|</span>
  19. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">7</span> <span style="color: #808080;">|</span> Lily   <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">6</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">22</span> <span style="color: #808080;">|</span> F    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">165</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2016</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">02</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">26</span> <span style="color: #808080;">|</span>
  20. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">8</span> <span style="color: #808080;">|</span> Susan  <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">4</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">23</span> <span style="color: #808080;">|</span> F    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">170</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2015</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">01</span> <span style="color: #808080;">|</span>
  21. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">9</span> <span style="color: #808080;">|</span> Thomas <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">22</span> <span style="color: #808080;">|</span> M    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">178</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2016</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">06</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">07</span> <span style="color: #808080;">|</span>
  22. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">10</span> <span style="color: #808080;">|</span> Tom    <span style="color: #808080;">|</span>       <span style="color: #800000; font-weight: bold;">4</span> <span style="color: #808080;">|</span>   <span style="color: #800000; font-weight: bold;">23</span> <span style="color: #808080;">|</span> M    <span style="color: #808080;">|</span>    <span style="color: #800000; font-weight: bold;">165</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2016</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">08</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">05</span> <span style="color: #808080;">|</span>
  23. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+--------+---------+------+------+--------+------------+</span>
  24. <span style="color: #800000; font-weight: bold;">10</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

 

MySQL数据库备份与恢复

标签：引号   load data   rom   字段   sql   包括   必须   结构   复制   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！