I need to backup a Drupal database it is huge. So it has over 1500 tables (don't blame me, its a Drupal thing) and is 10GB in size.
我需要备份Drupal数据库，它很大。所以它有超过1500个表(不要怪我，它是Drupal的东西)，大小是10GB。
I couldn't do it with PHPMyAdmin, I just got an error when it started to build the .sql file.
我不能用phmypadmin来做，我刚在它开始构建.sql文件时出错。
I want to make sure I wont break anything or take the server down or anything when I try to back it up.
我要确保当我试图备份服务器时，不会损坏任何东西，也不会损坏服务器。
I was going to attempt a mysqldump on my server and then copy the file down locally but realised that this may cause unforeseen problems. So my question to you is, is it safe to use mysqldump on so many tables at once and even if it is safe are there any problems such a huge file could lead to in the future for rebuilding the database?
我打算在服务器上尝试一个mysqldump，然后在本地复制文件，但我意识到这可能会导致不可预见的问题。我的问题是，在这么多的表上同时使用mysqldump是否安全?即使它是安全的，这么大的文件在将来重建数据库时是否会导致任何问题?
Thanks for the input guys.
谢谢输入人员。
2 个解决方案
#1
10
is it safe to use mysqldump on so many tables at once
在如此多的表上同时使用mysqldump是否安全
I run daily backups with mysqldump on servers literally 10x this size: 15000+ tables, 100+ GB.
我在服务器上使用mysqldump进行每日备份，大小为10x: 15000+表，100+ GB。
If you have not examined the contents of a file produced by mysqldump ... you should, because to see its output is to understand why it is an intrinsically safe backup utility:
如果您还没有检查mysqldump生成的文件的内容……你应该这样做，因为看到它的输出就会明白为什么它本质上是一个安全的备份工具:
The backups are human-readable, and consist entirely of the necessary SQL statements to create a database exactly like the one you backed up.
这些备份是人类可读的，并且完全由必要的SQL语句组成，以创建与您备份的数据库完全相同的数据库。
In this form, their content is easily manipulated with ubiquitous tools like sed and grep and perl, which can be used to pluck out just one table from a file for restoration, for example.
在这种形式下，它们的内容很容易使用诸如sed、grep和perl之类的通用工具进行操作，例如，可以使用这些工具从文件中取出一个表进行恢复。
If a restoration fails, the error will indicate the line number within the file where the error occurred. This is usually related to buggy behavior in the version of the server where the backup was created (e.g. MySQL Server 5.1 allowed you to create views in some situations where the server itself would not accept the output of its own SHOW CREATE VIEW statement. The create statement was not considered -- by the same server -- to be a valid view definition, but this was not a defect in mysqldump, or in the backup file, per se.)
如果恢复失败，错误将指示发生错误的文件中的行号。这通常与创建备份的服务器版本中的错误行为有关(例如，MySQL server 5.1允许您在某些情况下创建视图，在某些情况下，服务器本身不会接受它自己的SHOW创建视图语句的输出)。同一个服务器不认为create语句是有效的视图定义，但这并不是mysqldump或备份文件中的缺陷。
Restoring from a mysqldump-created backup is not lightning fast, because the server must execute all of those SQL statements, but from the perspective of safety, I would argue that there isn't a safer alternative, since it is the canonical backup tool and any bugs are likely to be found and fixed by virtue of the large user base, if nothing else.
从mysqldump-created备份不是闪电快速恢复,因为服务器必须执行这些SQL语句,但是从安全的角度来看,我认为没有一个更安全的选择,因为它是规范化的备份工具,可能会发现的故障和固定的庞大的用户基础,如果没有其他的。
Do not use the --force option, except in emergencies. It will cause the backup to skip over any errors encountered on the server while the backup is running, causing your backup to be incomplete with virtually no warning. Instead, find and fix any errors that occur. Typical errors during backup are related to views that are no longer valid because they reference tables or columns that have been renamed or dropped, or where the user who originally created the view has been removed from the server. Fix these by redefining the view, correctly.
不要使用-force选项，除非在紧急情况下。它将导致备份在运行备份时跳过服务器上遇到的任何错误，导致备份不完整，实际上没有任何警告。相反，查找并修复发生的任何错误。备份期间的典型错误与视图相关，这些视图不再有效，因为它们引用已重命名或删除的表或列，或者最初创建视图的用户已从服务器中删除。通过正确地重新定义视图来修复这些问题。
Above all, test your backups by restoring them to a different server. If you haven't done this, you don't really have backups.
最重要的是，通过将备份恢复到不同的服务器来测试备份。如果没有这样做，就没有备份。
The output file can be compressed, usually substantially, with gzip/pigz, bzip2/bpzip2, xz/pixz, or zpaq. These are listed in approximate order by amount of space saved (gzip saves the least, zpaq saves the most) and speed (gzip is the fastest, zpaq is the slowest). pigz, pbzip2, pixz, and zpaq will take advantage of multiple cores, if you have then. The others can only use a single core at a time.
可以使用gzip/pigz、bzip2/bpzip2、xz/pixz或zpaq对输出文件进行压缩。根据节省的空间数量(gzip保存得最少，zpaq保存得最多)和速度(gzip保存得最快，zpaq保存得最慢)按近似顺序列出。pigz, pbzip2, pixz和zpaq将会利用多个核心，如果你有的话。其他人只能一次使用一个内核。
#2
1
Use mysqlhotcopy it is well working with large databases
使用mysqlhotcopy它可以很好地处理大型数据库
- Work only MyISAM and ARCHIVE-tables.
- 只工作MyISAM和归档表。
- Work only on the server where the database is stored.
- 只在存储数据库的服务器上工作。
- This utility is deprecated in MySQL 5.6.20 and removed in MySQL 5.7
- 此实用程序在MySQL 5.6.20中被废弃，在MySQL 5.7中被删除
#1
10
is it safe to use mysqldump on so many tables at once
在如此多的表上同时使用mysqldump是否安全
I run daily backups with mysqldump on servers literally 10x this size: 15000+ tables, 100+ GB.
我在服务器上使用mysqldump进行每日备份，大小为10x: 15000+表，100+ GB。
If you have not examined the contents of a file produced by mysqldump ... you should, because to see its output is to understand why it is an intrinsically safe backup utility:
如果您还没有检查mysqldump生成的文件的内容……你应该这样做，因为看到它的输出就会明白为什么它本质上是一个安全的备份工具:
The backups are human-readable, and consist entirely of the necessary SQL statements to create a database exactly like the one you backed up.
这些备份是人类可读的，并且完全由必要的SQL语句组成，以创建与您备份的数据库完全相同的数据库。
In this form, their content is easily manipulated with ubiquitous tools like sed and grep and perl, which can be used to pluck out just one table from a file for restoration, for example.
在这种形式下，它们的内容很容易使用诸如sed、grep和perl之类的通用工具进行操作，例如，可以使用这些工具从文件中取出一个表进行恢复。
If a restoration fails, the error will indicate the line number within the file where the error occurred. This is usually related to buggy behavior in the version of the server where the backup was created (e.g. MySQL Server 5.1 allowed you to create views in some situations where the server itself would not accept the output of its own SHOW CREATE VIEW statement. The create statement was not considered -- by the same server -- to be a valid view definition, but this was not a defect in mysqldump, or in the backup file, per se.)
如果恢复失败，错误将指示发生错误的文件中的行号。这通常与创建备份的服务器版本中的错误行为有关(例如，MySQL server 5.1允许您在某些情况下创建视图，在某些情况下，服务器本身不会接受它自己的SHOW创建视图语句的输出)。同一个服务器不认为create语句是有效的视图定义，但这并不是mysqldump或备份文件中的缺陷。
Restoring from a mysqldump-created backup is not lightning fast, because the server must execute all of those SQL statements, but from the perspective of safety, I would argue that there isn't a safer alternative, since it is the canonical backup tool and any bugs are likely to be found and fixed by virtue of the large user base, if nothing else.
从mysqldump-created备份不是闪电快速恢复,因为服务器必须执行这些SQL语句,但是从安全的角度来看,我认为没有一个更安全的选择,因为它是规范化的备份工具,可能会发现的故障和固定的庞大的用户基础,如果没有其他的。
Do not use the --force option, except in emergencies. It will cause the backup to skip over any errors encountered on the server while the backup is running, causing your backup to be incomplete with virtually no warning. Instead, find and fix any errors that occur. Typical errors during backup are related to views that are no longer valid because they reference tables or columns that have been renamed or dropped, or where the user who originally created the view has been removed from the server. Fix these by redefining the view, correctly.
不要使用-force选项，除非在紧急情况下。它将导致备份在运行备份时跳过服务器上遇到的任何错误，导致备份不完整，实际上没有任何警告。相反，查找并修复发生的任何错误。备份期间的典型错误与视图相关，这些视图不再有效，因为它们引用已重命名或删除的表或列，或者最初创建视图的用户已从服务器中删除。通过正确地重新定义视图来修复这些问题。
Above all, test your backups by restoring them to a different server. If you haven't done this, you don't really have backups.
最重要的是，通过将备份恢复到不同的服务器来测试备份。如果没有这样做，就没有备份。
The output file can be compressed, usually substantially, with gzip/pigz, bzip2/bpzip2, xz/pixz, or zpaq. These are listed in approximate order by amount of space saved (gzip saves the least, zpaq saves the most) and speed (gzip is the fastest, zpaq is the slowest). pigz, pbzip2, pixz, and zpaq will take advantage of multiple cores, if you have then. The others can only use a single core at a time.
可以使用gzip/pigz、bzip2/bpzip2、xz/pixz或zpaq对输出文件进行压缩。根据节省的空间数量(gzip保存得最少，zpaq保存得最多)和速度(gzip保存得最快，zpaq保存得最慢)按近似顺序列出。pigz, pbzip2, pixz和zpaq将会利用多个核心，如果你有的话。其他人只能一次使用一个内核。
#2
1
Use mysqlhotcopy it is well working with large databases
使用mysqlhotcopy它可以很好地处理大型数据库
- Work only MyISAM and ARCHIVE-tables.
- 只工作MyISAM和归档表。
- Work only on the server where the database is stored.
- 只在存储数据库的服务器上工作。
- This utility is deprecated in MySQL 5.6.20 and removed in MySQL 5.7
- 此实用程序在MySQL 5.6.20中被废弃，在MySQL 5.7中被删除