Mysql备份工具mysqldump和mysqlhotcopy

我就是我 2022-12-23 09:41 133阅读 0赞

(1).Mysql备份类型

1）按照备份时对数据库的影响分为

Hot backup（热备）：也叫在线备份。指在数据库运行中直接备份，对正在运行的数据库没有任何影响。

Cold backup（冷备）：也叫离线备份。指在数据库停止的情况下备份。

Warm backup（温备）：在数据库运行时备份，会加一个全局锁以保证数据的一致性，会对当前数据库的操作有影响。

2）按照备份后的文件内容分为

逻辑备份：指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句或表内的实际数据（mysqldump和select \* into outfile），一般适用于数据库的升级和迁移，还原时间较长。

裸文件备份：也叫物理备份。拷贝数据库的物理文件，数据库既可以处于运行状态（mysqlhotcopy、ibbackup、xtrabackup一类工具），也可以处于停止状态，还原时间较短。

3）按照备份数据库的内容分为

完全备份：对数据库进行完整的备份。

增量备份：在上一次完整备份的基础上，对更新的数据进行备份（xtrabackup）

日志备份：二进制日志备份，主从同步。

(2).mysqldump

mysqldump是逻辑备份工具，支持MyISAM和InnoDB引擎。数据库运行时，MyISAM引擎只支持温备，InnoDB支持热备和温备。

1）导出数据

导出所有数据库

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> </td> 
   <td> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 -A &gt; all.sql</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 --all-databases &gt; all2.sql</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# ll all.sql all2.sql</code></p> <p><code>-rw-r--r-- 1 root root 773437 6月&nbsp; 14 13:52 all2.sql</code></p> <p><code>-rw-r--r-- 1 root root 773437 6月&nbsp; 14 13:51 all.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

导出指定数据库里的所有数据表

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 mysql &gt; mysql.sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# ll mysql.sql</code></p> <p><code>-rw-r--r-- 1 root root 1089455 6月&nbsp; 14 13:53 mysql.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

导出指定数据库里的指定数据表

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 test_db user_tb &gt; user.sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# ll user.sql</code></p> <p><code>-rw-r--r-- 1 root root 1883 6月&nbsp; 14 13:54 user.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

只导出指定数据库里的所有数据表的表结构

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 -d test_db &gt; test_db_table.sql</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# ll test_db_table.sql</code></p> <p><code>-rw-r--r-- 1 root root 1618 6月&nbsp; 14 13:56 test_db_table.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

只导出数据库里的所有数据表的所有数据

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 -t test_db &gt; test_db_data.sql&nbsp;</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# ll test_db_data.sql</code></p> <p><code>-rw-r--r-- 1 root root 1513 6月&nbsp; 14 13:57 test_db_data.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

导出指定数据库的所有内容

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>[root@youxi1 ~]# mysqldump -uroot -p123456 -B test_db &gt; test_db.sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code></p> <p><code>mysqldump: [Warning] Using a password&nbsp;</code><code>on</code>&nbsp;<code>the command line&nbsp;</code><code>interface</code>&nbsp;<code>can be insecure.</code></p> <p><code>[root@youxi1 ~]# ll test_db.sql</code></p> <p><code>-rw-r--r-- 1 root root 2029 6月&nbsp; 14 14:02 test_db.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

注意：以上几个实例，只有-A、--all-databases和-B选项有创建数据库，并使用数据库的语句。这和后面的导入数据息息相关。

2）导入数据

如果使用-A、--all-databases和-B选项导出的数据库可以使用mysql直接导入

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql -uroot -p123456 &lt; all.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

如果不是则需要进入到mysql内部，选择数据库才能导入数据；或者使用mysql时指定数据库

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>&gt;use mysql</code></p> <p><code>&gt;source mysql.sql</code></p> <p>&nbsp;</p> <p><code>mysql -uroot -p123456 mysql &lt; mysql.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

(3).mysqlhotcopy

mysql5.7已经没有这个命令了，多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），只能用于MyISAM引擎的数据库。本质是使用锁表语句，然后cp或scp。

可以使用yum命令安装

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>yum -y install perl-DBD*;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

1）备份

备份一个数据库到目标目录

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysqlhotcopy -u [用户名] -p [密码] [数据库名] [目标目录]</code></p> </td> 
  </tr> 
 </tbody> 
</table>

备份多个数据库到目标目录

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysqlhotcopy -u [用户名] -p [密码] [数据库名1 数据库名2...] [目标目录]</code></p> </td> 
  </tr> 
 </tbody> 
</table>

备份数据库中的某个表到指定目录

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysqlhotcopy -u [用户名] -p [密码] [数据库名]./[表名]/ [目标目录]</code></p> </td> 
  </tr> 
 </tbody> 
</table>

2）还原

在Linux中Mysql数据库都是以物理文件存在的，数据库对应的是目录。如果想要还原只要将原来的文件拷贝回原目录即可。

单个数据库备份还原方式：如果文件所有这和所属组不是mysql，请使用chown修改。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>cp -ra [目标目录] /</code><code>var</code><code>/lib/mysql/[数据库名]/</code></p> </td> 
  </tr> 
 </tbody> 
</table>

(4).mysqldump和mysqlhotcopy比较

1.mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，数据库大时，占用系统资源较多，支持常用的MyISAM，innodb引擎；mysqlhotcopy只是简单的缓存写入和文件复制的过程，占用资源和备份速度比mysqldump快很多很多，特别适合大的数据库，但需要注意的是：mysqlhotcopy只支持MyISAM 引擎。

2.mysqlhotcopy只能运行在数据库目录所在的机器上，mysqldump可以用在远程客户端。

3.mysqlhotcopy恢复只需要拷贝备份文件到源目录覆盖即可，mysqldump需要导入SQL文件到原来库中。

4.两者都在线执行lock tables（锁表）以及unlock tables（解锁表）。