Mysqldump - 数据库备份程序

来自泡泡学习笔记
BrainBs（讨论 | 贡献）2023年12月19日 (二) 09:47的版本 （→‎示例）
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索

mysqldump客户端实用程序执行逻辑备份，生成一组可以执行以恢复原始数据库对象定义和表数据的SQL语句。它将一个或多个MySQL数据库备份或传输到另一个SQL服务器。mysqldump命令还可以生成CSV、其他定界文本或XML格式的输出。


提示

考虑使用MySQL Shell转储实用程序，它提供多线程并行转储、文件压缩和进度信息显示功能，以及诸如Oracle Cloud Infrastructure Object Storage流式传输、MySQL HeatWave服务兼容性检查和修改等功能。可以使用MySQL Shell加载转储实用程序将转储轻松导入到MySQL服务器实例或MySQL HeatWave服务DB系统中。


mysqldump至少需要转储表的SELECT权限、转储视图的SHOW VIEW权限、转储触发器的TRIGGER权限，如果未使用–single-transaction选项，则还需要LOCK TABLES权限。如果未使用–no-tablespaces选项，则需要在8.0.21及更高版本中使用PROCESS（）权限，并在8.0.32及更高版本中使用RELOAD或FLUSH_TABLES权限（如果gtid_mode=ON且–set-gtid=purged=ON|AUTO）。


要重新加载转储文件，您必须具有执行其中包含的语句所需的权限，例如为由这些语句创建的对象分配适当的CREATE权限。


mysqldump输出可能包括更改数据库排序规则的ALTER DATABASE语句。这些可能在转储存储程序时保留其字符编码。要重新加载包含此类语句的转储文件，需要对受影响的数据库的ALTER权限。


注意

使用Windows上的PowerShell通过输出重定向创建的文件具有UTF-16编码： 

mysqldump [options] > dump.sql

但是，UTF-16不是允许作为连接字符集的（参见不允许的客户端字符集），因此无法正确加载该转储文件。要解决此问题，请使用–result-file选项，它在ASCII格式中创建输出： 

mysqldump [options] --result-file=dump.sql


不建议在服务器上启用GTID时（gtid_mode=ON）加载包含系统表的转储文件。mysqldump为使用非事务性MyISAM存储引擎的系统表发出DML指令，当启用GTID时，这不允许。


性能和可扩展性考虑

mysqldump的优势包括在恢复之前查看或编辑输出的便利性和灵活性。您可以克隆开发和DBA工作所需的数据库，或为测试生成现有数据库的轻微变化。它不是用于大量数据备份和恢复的快速或可扩展解决方案。对于大量数据，即使备份步骤花费合理时间，恢复数据也可能非常慢，因为重新播放SQL语句涉及插入、索引创建等磁盘I/O。


对于大规模备份和恢复，物理备份更合适，以复制数据文件的原始格式，以便可以快速恢复。


如果您的表主要是InnoDB表，或者您有一个混合了InnoDB和MyISAM表的表，可以考虑使用mysqlbackup，它是MySQL Enterprise的一部分。此工具为InnoDB备份提供了高性能，最小干扰；它可以备份来自MyISAM和其他存储引擎的表；它还提供了一些方便的选项来适应不同的备份场景。


mysqldump可以按行检索和转储表内容，也可以从表中检索整个内容并将其缓冲在内存中再转储。缓冲在内存中可能是一个问题，如果您正在转储大型表。要按行转储表，请使用–quick选项（或–opt，后者启用–quick）。–opt选项（因此–quick）默认启用，因此要启用内存缓冲，请使用–skip-quick。


如果您使用较新版本的mysqldump生成要重新加载到非常旧的MySQL服务器的转储，请使用–skip-opt选项而不是–opt或–extended-insert选项。


调用语法

通常有三种方法可以使用mysqldump——为了转储一个或多个表的集合、一个或多个完整的数据库集合或整个MySQL服务器——如上所示： 

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases


要转储整个数据库，不要在db_name后命名任何表，或使用–databases或–all-databases选项。


要查看支持您的mysqldump版本的选项列表，请输入命令mysqldump –help。


示例

要备份整个数据库： 

mysqldump db_name > backup-file.sql


将dump文件重新加载到服务器： 

mysql db_name < backup-file.sql


另一种重新加载dump文件的方法： 

mysql -e "source /path-to-backup/backup-file.sql" db_name


mysqldump还非常适用于通过从一个MySQL服务器复制数据来填充数据库： 

mysqldump --opt db_name | mysql --host=remote_host -C db_name


您可以使用一条命令备份多个数据库： 

mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql


要备份所有数据库，请使用–all-databases选项： 

mysqldump --all-databases > all_databases.sql


对于InnoDB表，mysqldump提供了一种在线备份的方法： 

mysqldump --all-databases --master-data --single-transaction > all_databases.sql


或者从MySQL 8.0.26开始： 

mysqldump –all-databases –source-data –single-transaction > all_databases.sql


此备份在dump开始时获取对所有表的全局读锁（使用FLUSH TABLES WITH READ LOCK）。一旦获得此锁，就会读取二进制日志坐标并释放锁。如果在发出FLUSH语句时正在运行长时间更新语句，MySQL服务器可能会在那些语句完成之前停滞不前。在那之后，dump变为无锁，对表中的读写操作不受影响。如果MySQL服务器接收到的更新语句执行时间很短（以执行时间为准），则初始锁定期不应引起注意，即使有大量更新。


对于时点恢复（也称为“回滚”，当您需要恢复旧备份并重放自该备份以来发生的变化时），通常有用的是轮换二进制日志或至少知道dump对应的二进制日志坐标： 

mysqldump --all-databases --master-data=2 > all_databases.sql


或者从MySQL 8.0.26开始： mysqldump –all-databases –source-data=2 > all_databases.sql


或者： 

mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql


或者从MySQL 8.0.26开始： 

mysqldump --all-databases --flush-logs --source-data=2 > all_databases.sql


–source-data或–master-data选项可以与–single-transaction选项同时使用，提供一种方便的方法，用于在存储使用InnoDB存储引擎的表之前进行在线备份，以便进行时点恢复。


要选择–opt除了某些功能之外的效果，请为每个功能使用–skip选项。要禁用扩展插入和内存缓冲区，请使用–opt –skip-extended-insert –skip-quick。（实际上，–skip-extended-insert –skip-quick足够了，因为–opt默认启用。）


要反转–opt的所有功能，除了禁用索引和表锁定，请使用–skip-opt –disable-keys –lock-tables。


限制

  • mysqldump默认不会转储performance_schema或sys schema。要转储这些中的任何一个，请在命令行上明确指定它们。您还可以使用–databases选项指定它们。对于performance_schema，还要使用–skip-lock-tables选项。
  • mysqldump不会转储INFORMATION_SCHEMA模式。
  • mysqldump不会转储InnoDB CREATE TABLESPACE语句。
  • mysqldump不会转储NDB Cluster ndbinfo信息数据库。
  • mysqldump包括用于mysql数据库的dump的general_log和slow_query_log表的重新创建语句。Log表内容不会被转储。


mysqlimport - 数据导入程序

mysqlimport客户端提供了LOAD DATA SQL语句的命令行界面。大多数mysqlimport选项直接对应于LOAD DATA语法的子句。


调用mysqlimport的方式如下： 

mysqlimport [options] db_name textfile1 [textfile2 ...]


对于命令行上命名的每个文本文件，mysqlimport会从文件名中删除任何扩展名，并使用结果来确定要导入文件内容的表的名称。例如，命名为patient.txt、patient.text和patient的文件都将导入到一个名为patient的表中。


以下是演示mysqlimport用法的示例会话： 

$> mysql -e ‘CREATE TABLE imptest(id INT, n VARCHAR(30))’ test 
$> ed 
a 
100 Max Sydow 
101 Count Dracula 
. 
w imptest.txt 
32 
q 
$> od -c imptest.txt 
0000000 1 0 0  a x S y d o w  0 
0000020 1  o u n t D r a c u l a  
$> mysqlimport –local test imptest.txt 
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 
$> mysql -e ‘SELECT * FROM imptest’ test 
+——+—————+ 
| id | n | 
+——+—————+ 
| 100 | Max Sydow | 
| 101 | Count Dracula | 
+——+—————+
取自“http://bubblestudy.info/wiki/index.php?title=Mysqldump_-_数据库备份程序&oldid=2501”