BenMorel's questions

我正在将数据库从一台服务器传输到另一台服务器。我直接移动数据文件，而不是使用 SQL 转储。为此，我：

• 确保两台服务器使用相同的 MySQL 版本
• 放innodb_fast_shutdown = 0
• 停止 MySQL 服务器
• rsync将数据写入新服务器

我几乎准备好启动新服务器，但我有一个疑问。旧服务器/var/lib/mysql对数据、binlogs 等使用相同的数据目录，而新服务器对数据、binlogs 和 tmp 使用 3 个不同的目录：

datadir=/mysql/data
tmpdir=/mysql/tmp
log-bin=/mysql/binlog/mysql-bin

问题是：每个文件应该放在哪里？

我的理由是：

• /mysql/tmp：在缓慢（甚至正常？）关机后不应该留下任何临时文件，所以现在什么都不应该去那里
• /mysql/binlog: 我应该只移动mysql-bin.*文件
• /mysql/data: 其他的都应该去那里

那是对的吗？

这是文件列表，供参考（nxxxxxxx是我的主数据库）：

# ls -al /var/lib/mysql
total 38518860
drwxr-x--x.  9 mysql mysql       4096 20 nov.  13:50 .
drwxr-xr-x. 37 root  root        4096 29 déc.   2019 ..
-rw-rw----.  1 mysql mysql         56 21 déc.   2014 auto.cnf
-rw-------   1 mysql mysql       1680 27 déc.   2018 ca-key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 ca.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 client-cert.pem
-rw-------   1 mysql mysql       1676 27 déc.   2018 client-key.pem
-rw-r-----   1 mysql mysql     589824 20 nov.  13:50 #ib_16384_0.dblwr
-rw-r-----   1 mysql mysql    8978432 19 nov.  18:28 #ib_16384_1.dblwr
-rw-r-----   1 mysql mysql      91522 20 nov.  13:50 ib_buffer_pool
-rw-r-----.  1 mysql mysql 9143582720 20 nov.  13:50 ibdata1
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile0
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile1
drwxr-x---   2 mysql mysql       4096 20 nov.  13:50 #innodb_temp
drwxr-sr-x.  2 mysql mysql       4096 19 nov.  18:28 mysql
-rw-r-----   1 mysql mysql 1073742002  5 nov.  21:45 mysql-bin.002418
-rw-r-----   1 mysql mysql 1073742033  6 nov.  12:00 mysql-bin.002419
                                    (...)
-rw-r-----   1 mysql mysql     341943 20 nov.  13:50 mysql-bin.002446
-rw-r-----   1 mysql mysql        179 20 nov.  13:50 mysql-bin.002447
-rw-r-----   1 mysql mysql        570 20 nov.  13:50 mysql-bin.index
-rw-r-----   1 mysql mysql   62914560 20 nov.  13:50 mysql.ibd
-rw-r--r--.  1 mysql mysql          6 19 nov.  18:28 mysql_upgrade_info
drwxr-sr-x.  2 mysql mysql      69632 17 nov.  17:15 nxxxxxxx
drwxr-x---   2 mysql mysql       4096 19 nov.  18:28 performance_schema
-rw-------   1 mysql mysql       1676 27 déc.   2018 private_key.pem
-rw-r--r--   1 mysql mysql        452 27 déc.   2018 public_key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 server-cert.pem
-rw-------   1 mysql mysql       1680 27 déc.   2018 server-key.pem
drwxr-x---.  2 mysql mysql      12288 27 déc.   2018 sys
drwxr-x---   2 mysql mysql       4096  1 mars   2019 test
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_001
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_002

我曾经在my.cnf位于[mysqld].

但是，我现在遇到了使用[server]替代的配置。

看起来它们都是同义词，至少在 MySQL 8.0 上，我可以看到这两个组下的选项都适用于 MySQL 服务器。

这是 MySQL 8.0 的新特性吗？其中一个比另一个更受欢迎吗？其中之一是否已弃用？

我有一个定期与表交换分区的程序。

MyTable是一个分区表，并且MyTableTemplate是这个表的一个空副本，具有相同的结构但没有分区。

过程如下：

CREATE TABLE tmp LIKE MyTableTemplate;

INSERT INTO tmp VALUES (...);
// more inserts...

ALTER TABLE MyTable EXCHANGE PARTITION p1 WITH TABLE tmp;
DROP TABLE tmp;

到目前为止它运行良好，直到我需要向表中添加一列：

ALTER TABLE MyTable         ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE MyTableTemplate ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;

现在，当我运行时EXCHANGE PARTITION WITH TABLE，我收到以下消息：

一般错误：1731 分区和表之间的不匹配属性“INSTANT COLUMN(s)”

奇怪的是，如果我不在最后一个位置添加列：

ALTER TABLE ... ADD COLUMN ... AFTER someField;

...然后分区交换工作正常！

这个“INSTANT COLUMN(s)属性”是什么，我做错了什么？

这是一个错误吗？我正在使用 MySQL 8.0.12。

我想在主服务器上创建一个表，其数据不会复制到从服务器。

作为批处理作业的一部分，我暂时在该表中插入了数百万行；该表中的数据可以按需重新创建，因此不需要在（较慢的）从属服务器上复制。

我不想使用TEMPORARY表，因为理想情况下我希望将表结构保留在数据库中（也在从站上，以便我可以可靠地使用它进行备份恢复），最重要的是我希望能够恢复如果作业因任何原因退出，则从当前状态开始，因此如果作业死亡，表数据需要保留在主服务器上。

理想情况下，我希望能够从CREATE语句中控制表的可复制性，以便能够从我的正常 SQL 工作流程中控制它。

这可能吗？如果没有，欢迎使用接近的替代方案。

我正在设置将外部 MySQL 实例复制到 RDS。

外部 MySQL 实例和 RDS 都运行 MySQL 5.7.10。

我已经按照程序进行了操作，但是在转储数据库mysqldump并将其发送到mysql连接到 RDS 时，出现以下错误：

第 2081 行的错误 1215 (HY000)：无法添加外键约束

SHOW ENGINE InnoDB STATUS在 RDS 上显示：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-03-25 01:38:56 0x2ad07ddcf700 Error in foreign key constraint of table db/Prospect:
 FOREIGN KEY (`restaurantId`) REFERENCES `Restaurant` (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci:
Cannot resolve table name close to:
 (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

好的，确实转储文件按字母顺序重新创建了表格，因此Prospect表格出现在Restaurantand之前Zone，这可以解释错误。

但是不，因为mysqldump添加SET FOREIGN_KEY_CHECKS=0到文件的顶部，所以无论如何它应该可以工作。

所以我决定用一个普通的 MySQL 5.7 安装来测试它，毫不奇怪，转储是用完全相同的命令成功导入的。

是什么导致此错误专门在 RDS 上，以及如何避免它？

我刚刚下载了 MySQL 5.7 的第一个 GA 版本，并注意到 RPM 包附带了两个版本的 MySQL 服务器包：

• mysql-community-server-5.7.9-1.el7.x86_64.rpm
• mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm

普通和“最小”服务器包有什么区别？

我在 MySQL 网站上找不到此信息。

我有一个脚本来安装 MySQL 5.7 并在Travis CI（运行 Ubuntu 12）上运行测试。

该脚本包含这些行，以非交互模式安装 MySQL，无需密码：

add-apt-repository 'deb http://repo.mysql.com/apt/ubuntu/ precise mysql-5.7-dmr'
apt-get update

echo mysql-community-server mysql-community-server/root-pass password "" | debconf-set-selections
echo mysql-community-server mysql-community-server/re-root-pass password "" | debconf-set-selections

DEBIAN_FRONTEND=noninteractive apt-get -o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold -q -y install mysql-server libmysqlclient-dev

安装运行顺利，使用mysql命令行实用程序，我可以不使用密码连接到服务器：

mysql --user=root -e "SELECT VERSION();"

+-----------+
| VERSION() |
+-----------+
| 5.7.8-rc  |
+-----------+

现在，如果我尝试使用 PHP PDO进行连接：

new PDO('mysql:host=localhost', 'root', '');

我收到以下错误：

SQLSTATE [HY000] [1045] 用户'root'@'localhost'的访问被拒绝（使用密码：否）

我试图允许root从任何主机连接：

GRANT ALL ON *.* to root@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

没有成功。

可能是什么问题？

给定下表：

CREATE TABLE Test (
 id int(10) unsigned NOT NULL PRIMARY KEY,
 value int(10) unsigned NOT NULL
) ENGINE=InnoDB;

如果我插入一个仅包含id字段集的值：

INSERT INTO Test (id) VALUES (1);

在我的开发服务器上，我收到警告：

警告：#1366 不正确的整数值：第 1 行的列 'value' 的 ''

在我的生产服务器上，我收到一个错误：

错误 1364 (HY000)：字段“值”没有默认值

我应该在我的开发服务器上配置什么以使其表现得像我的生产服务器？

我运行一个应用程序（价格比较网站），每天需要更新包含数千万行的定价表。

基本上，我从商家下载的每个产品提要都会导致从定价表 ( WHERE merchantId = x) 中删除数百万行，并插入数百万个新行来代替旧行。

到目前为止，我习惯于顺序下载所有提要，将它们全部插入一个临时表中，然后在单个RENAME TABLE查询中将旧表与新表交换。

这迫使我一次更新所有商家，因此我想放弃这种方法，并且能够一次处理一个商家，删除所有记录WHERE merchantId = x并在亚秒级查询中插入所有新记录。

我的第一个想法是使用按列表分区，每个商家一个分区。这将解决我一半的问题：我可以使用TRUNCATE PARTITION快速删除给定商家的所有现有价格，但问题的另一半仍然存在：如何快速插入数百万个新行，而不会停机和不必要的锁定，并且不会运行桌子部分加载了几分钟？

所以我在想：是否可以创建一个与分区表结构相同的临时表，用新行填充它，一旦完成，用这个临时表交换定价表的一个分区？

任何解决此问题的建议都将不胜感激，包括不涉及分区的建议。我也考虑过MRG_MYISAM引擎，但我不确定它是否满足我的需求。最后一点，任何存储引擎都适用于此定价表，包括非事务引擎。

我已经阅读了许多关于取决于存储引擎的主键效率的文章，我感到很困惑。

storeId给定一个具有两个字段和的简单多对多表，zoneId以下哪种设计对 InnoDB 最有效，为什么？

• 使用这两个字段作为复合主键：

CREATE TABLE store_zone(
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(storeId, zoneId)
);

• 使用特定的自增主键：

CREATE TABLE store_zone(
    id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(storeId, zoneId)
);

笔记：

• 无论如何，我需要 ( storeId, ) 对上的唯一键zoneId
• store我有和表的外键，zone为了便于阅读，这里没有显示zoneId，所以在这两种情况下，还有一个额外的必需索引

我想CHECK TABLE从 MySQL 命令行对我的所有表运行 a 。

我可以为一张桌子做：

mysql> check table Restaurant;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| foodbox.Restaurant | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.02 sec)

是否有一个等效的命令可以检查当前数据库中的所有表？

我尝试在 Amazon RDS for MySQL 上创建此函数：

CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT READS SQL DATA
BEGIN
    DECLARE _id, _parentId INT;
    SELECT zoneId INTO _parentId;

    my_loop: LOOP
        SELECT id, parentId
        INTO _id,_parentId
        FROM Zone
        WHERE id = _parentId;

        IF _parentId IS NULL THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    RETURN _id;
END;

这只会在一次性查询中用于更新现有数据库表。

运行它给我以下错误：

错误 1419 (HY000)：您没有超级权限并且启用了二进制日志记录（您可能希望使用不太安全的 log_bin_trust_function_creators 变量）

此限制是否也适用于存储过程，还是仅适用于函数？

如果它也适用于存储过程，是否有任何替代方法可以在不设置的情况下执行等效代码log_bin_trust_function_creators=1？请注意，我知道如何在 RDS 上更改此设置，我只想尽可能避免这种情况。

我四处寻找锁定整个数据库的解决方案，到目前为止我发现的唯一一个是FLUSH TABLES WITH READ LOCK。

这对于备份来说很好，但不幸的是，当我使用模式更改修补数据库时，我不能使用它来阻止对数据库的其他访问。如果我运行ALTER TABLEafter FLUSH TABLES WITH READ LOCK，我会收到以下消息：

无法执行查询，因为您有一个冲突的读锁

有没有办法在我修补数据库时阻止其他数据库连接临时访问数据库？

我不想主动拒绝其他连接，我只想让它们“暂停”直到修补完成（几秒钟）。

我怎样才能做到这一点？

使用 AWS CLI restore-db-instance-to-point-in-time命令时，我不知道如何设置安全组。此页面上的文档说：

目标数据库是从源数据库创建的，具有与原始数据库相同的配置，除了数据库实例是使用默认数据库安全组创建的。

是否可以覆盖此参数以使用与原始实例相同的安全组？

我正在尝试运行 MySQL DBT2 Benchmark Tool，我已解压缩存档并运行以下命令：

./configure --with-mysql
make
make install
mkdir /tmp/dbt
datagen -w 3 -d /tmp/dbt --mysql
scripts/mysql/mysql_load_db.sh --path /tmp/dbt --mysql-path /usr/bin/mysql
scripts/run_mysql.sh --connections 20 --time 300 --warehouses 3

但得到以下输出：

...

Stage 3. Starting of the test. Duration of the test 300 sec
STARTING DRIVER COMMAND:   /root/dbt2-0.37.50.3/src/driver -d 127.0.0.1 -l 300 -wmin 1 -wmax 3 -spread 1 -w 3 -sleep 300 -tpw 10 -outdir /root/output/14/driver -p 30000 -warmup 0 -cooldown 20 -intermediate_timer_resolution 0
Shutdown clients. Send TERM signal to 5154.
Shutdown driver. Send TERM signal to 5190.

Stage 4. Processing of results...
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   0.00          N/A                      0                0  100.00
   New Order   0.00          N/A                      0                0  100.00
Order Status   0.00          N/A                      0                0  100.00
     Payment   0.00          N/A                      0                0  100.00
 Stock Level   0.00          N/A                      0                0  100.00

0.00 new-order transactions per minute (NOTPM)
0.0 minute duration
0 total unknown errors
0.00 rollback transactions
0 second(s) ramping up

scripts/run_mysql.sh: line 632:  5154 Killed                  nohup $CLIENT_COMMAND > $OUTPUT_DIR/client.out 2>&1
scripts/run_mysql.sh: line 632:  5190 Killed                  nohup $DRIVER_COMMAND > $OUTPUT_DIR/driver.out 2>&1
Test completed.

在执行过程中，SHOW PROCESSLIST确实显示有 20 个客户端已连接，均处于休眠状态。

知道为什么不执行交易吗？

直到 MySQL 5.6.7，官方 RPM都附带这些文件：

/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-small.cnf

在 5.6.8 中，这些文件都没有了，只剩下一个：

/usr/share/mysql/my-default.cnf

我不是 MySQL 调优专家，并且习惯于总是以my-innodb-heavy-4G.cnf. 现在我只剩下一个配置选项：

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

调整这个唯一变量是大多数优化的关键，还是在安装新服务器时我应该调整其他相关参数？

在这种情况下，我在哪里可以找到一个等效文件，该文件将包含仅适用于 InnoDB、4GB RAM 专用 MySQL 服务器的合理默认值？