mysqldump最佳实践(1)

1.空间需求

首先，重要的是要确保目标数据库有足够的空间来保存导入的数据。具体来说，如果在目标MySQL数据库上启用了二进制日志，则需要非常谨慎，因为导入数据时生成的二进制日志可能与数据本身的大小几乎相同。如果希望在一台服务器上恢复数据并进行复制，则需要二进制日志。在这种情况下，最好将目标数据库大小规划为源数据库大小的两倍以上。

确保生成mysqldump输出文件的卷上有足够的可用空间也很重要。如果没有这些预防措施，可能会看到转储或恢复在长时间运行后由于空间不足而失败，这将损失生产时间和精力。

2.sql_mode

sql_mode的设置，决定了mysql server的sql语句语法和为操作执行的数据验证检查。确保源MySQL服务器和目标MySQL服务器的sql_mode彼此兼容是很重要的，否则在恢复所获取的dump时可能会遇到失败。

用一个例子来说明这一点。假设在源库有一个表，其中有一个日期列，含有日期为零的条目:​

mysql> show create table sched;
--------------------------------------------------------+
| Table | Create Table                                                                                                        |
--------------------------------------------------------+
| sched | CREATE TABLE `sched` (
  `id` int(11) DEFAULT NULL,
  `ts` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------
 
mysql> select * from sched;
+------+------------+
| id   | ts         |
+------+------------+
|    1 | 2020-01-12 |
|    2 | 0000-00-00 |
+------+------------+

假设在源库没有开启严格的sql_mode(即没有开启no_zero_date)，而目标端却开启了严格模式，数据还原的时候就会发生失败：

ERROR 1292 (22007) at line 40: Incorrect date value: '0000-00-00' for column 'ts’' at row 2

mysqldump执行备份的时候，如果使用compact选项，你可能就会遇到这种典型的错误。

如果禁用了compact(默认就是禁用的)选项，mysqldump就不会遇到这些错误，因为在dump的时候会生成以下语句：​

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

这表示在还原表数据之前，将sql_mode设置成了no_auto_value_on_zero。

3.Unique_checks和foreign_key_checks

缺省情况下(如果你没有使用compact选项)，mysqldump还会做如下设置：​

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

如果注释所示，你可以在会话级别临时关闭唯一性检查，从而加快数据还原。对于大表，可能会节省很多的I/O，因为innodb可以借助change buffer批量写二级辅助索引。

如果表上有外键约束，在还原会话中取消外键检查可以加速还原操作。对于大表，可以节省很多的I/O。

禁用FOREIGN_KEY_CHECKS还将有助于避免在恢复操作中由于外部键约束检查而导致的错误。每当创建一个带有外键约束的表时，MySQL期望由外键引用的父表已经存在。这是一个问题，因为mysqldump实用程序按字母顺序转储表。

来通过一个例子来说明这一点。在源数据库中，有两个表:​

CREATE TABLE `solution_table` (
  `num1` int(11) NOT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`num1`));
 
CREATE TABLE `ref_table` (
  `key` int(11) DEFAULT NULL,
  `ref_num` int(11) DEFAULT NULL,
  KEY `ref_num` (`ref_num`),
  CONSTRAINT `ref_num_ibfk_1` FOREIGN KEY (`ref_num`) REFERENCES `solution_table` (`num1`)
)

ref_table有个外键约束指向表solution_table。按照字母顺序，mysqldump会先dump出ref_table。在还原过程中，就会遇到如下的错误：

ERROR 1215 (HY000) at line 50: Cannot add foreign key constraint - 

在创建表ref_table的时候就会发生上面的错误。

总结来说，如果你指定了--compact选项，就要注意上面的这些问题。

4.权限需求

mysqldump转储数据库所需的最小权限是该数据库上的SELECT。然而，如果你的数据库有视图，你也需要SHOW VIEW权限，因为mysqldump总是转储视图和数据库的表。假设你没有SHOW VIEW权限，那么mysqldump将失败:

mysqldump: Couldn't execute 'show create table `ivew`': SHOW VIEW command denied to user ‘dumpuser’@'172.31.18.79' for table 'iview' (1142)

另一点值得注意的是，如果你的dumpuser仅对数据库的某个特定表具有SELECT权限，mysqldump将仅为该特定表转储数据，并自动忽略任何其他表或视图。因此，请确保执行mysqldump的用户预先拥有所有适当的特权，以避免在以后的时间出现任何意外或失败。

5.Max_allowed_packet

mysql处理的最大通信包由max_allowed_packet决定。在导入上下文中，通信包是在恢复期间发送给MySQL服务器的一条SQL语句，或者是在转储期间发送给客户端的一行。

mysqldump的max_allowed_packet的默认值是24MB。如果mysqldump收到的数据包大于这个值，那么你可能会遇到这样的错误:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `huge1` at row: 2.

因此，确保mysqldump使用与源MySQL实例上配置的max_allowed_packet相同或更大的值。

在调用mysqldump时，可以使用——max-allowed-packet=value标记指定该选项。

在恢复转储时，确保目标服务器的max_allowed_packet大小足够大，可以接收转储文件中的数据包。

否则，在恢复转储的过程中，将看到一个错误消息:

ERROR 2006 (HY000) at line 70: MySQL server has gone away

这个错误可能有点误导性，因为你可能认为MySQL服务器已经关闭或崩溃了。但是，这只是意味着服务器收到了一个比它配置的max_allowed_packet大小更大的数据包。

同样，最佳实践是确保目标服务器的max_allowed_packet值与源服务器中的值相同。这也是一个重要的设置，可以提前检查和适当设置，而不是在以后面对错误。