主页 / dba / 问题

问题[mysql](dba)

Martin Hope
christerk
Asked: 2024-12-18 20:34:16 +0800 CST
  • 5

我有一个 MariaDb/PHP 应用程序，用于从大型科学数据库中搜索生物名称（拉丁语/瑞典语）。唯一的用户文本输入是在 中提供（部分）名称textfield，由 PHP 读取并插入到 SQL 查询中（缩写）：

    Select ... from Database.table x WHERE Condition AND MATCH 
    (x.scientific_name) AGAINST ('"full name"' IN BOOLEAN 
    MODE) ;

或者

    Select ... from Database.table x WHERE Condition
    AND x.scientific_name LIKE '%partial_name%' AND 
    !isnull(x.scientific_name ;

这些查询从数据库表中返回 0 个或多个结果，定义为：

scientific_namevarchar(255)

我目前没有使用参数化查询（我必须使用吗？）

（MariaDb 版本 11.6.2、PHP 版本 8.2、带有 Apache2 的 Debian 12 服务器）

编辑：PHP代码：

//User input:
<input type='search' name='srcfield' id='srcfield' value='' size='45' maxlength='55' autofocus placeholder="Sök efter organism/taxon... (fågelnamn)" style='font-size:14px;background-color: #DCF7E7;'
oninput='fonblur1(srcfield)'>

//returns srcfield to a PHP var => $safein1 via js:
function fonblur1() {
var x = document.getElementById("srcfield");
...
}

//A large amount of various non-text input is used to build the dynamic SQL, e.g:

         <input type="checkbox"  id="cb02" name ="exakt2" onclick="" >
        <label for="cb02">search exact/whole name</label></span>


//Dynamic SQL (untranslated code):
$sqlsrc = "SELECT f.`TaxonId`,f.`Taxonkategori`,f.`Vetenskapligt_namn0`, 
f.`URL_till_taxoninformation` AS `URL_till_taxoninformation`, 
d.Familj AS Familj, d.Slakte AS Slakte, 
d.TaxonId AS TaxonId2, t.taxid, 1 AS accept , 'nosyn' AS syn
concat(d.Rike,' -› ', d.Fylum,' -› ',IFNULL(d.Klass, '~')) AS grupp, 
FROM namndata.ftextsrc f 
JOIN namndata.taxonid0 t using (Taxonkategori)
JOIN namndata.taxon0_T d USING (TaxonId) 
WHERE f.`Vetenskapligt_namn0` LIKE '%" . $safein1 . "%'  " .
 " AND d.TaxonStatus='Accepterat' AND d.".$roww2['Taxonkategori'] ." = '".$roww2['Vetenskapligt_namn']."' 
 " . $TxIdf . "    
order by grupp, d.radnr, f.`Vetenskapligt_namn0` LIMIT 0,2500;";  



// Actual code, sent to MariaDb:
SELECT f.`TaxonId`,f.`Taxonkategori`,f.`Vetenskapligt_namn0`, 
f.`URL_till_taxoninformation` AS `URL_till_taxoninformation`, 
d.Familj AS Familj, d.Slakte AS Slakte, 'nosyn' AS syn, 
d.TaxonId AS TaxonId2, t.taxid, 1 AS accept, concat(d.Rike,' -› ', d.Fylum,' -› ',IFNULL(d.Klass, '~')) AS grupp 
FROM namndata.ftextsrc f JOIN namndata.taxonid0 t using (Taxonkategori) 
JOIN namndata.taxon0_T d USING (TaxonId) 
WHERE MATCH (f.`Vetenskapligt_namn0`) AGAINST ('"carnivora"' IN BOOLEAN MODE) 
AND d.TaxonStatus='Accepterat' AND d.Fylum = 'Chordata' order by grupp, d.radnr, f.`Vetenskapligt_namn0` LIMIT 0,2500;
mysql
Martin Hope
Nate Glenn
Asked: 2024-12-14 15:39:01 +0800 CST
  • 5

我的数据库使用的是 InnoDB，使用时mysqldump我可以选择设置--single-transaction以避免锁定表，同时仍然保持对它们的一致视图。

我正在尝试切换到推荐的MySQL Shellutil.dumpSchema，但我只看到两个选项：consistent: true和consistent: false。的文档consistent: true指定将锁定表以进行备份。

是否可以使用util.dumpSchema相同的方式通过 InnoDB 在单个事务中转储？或者如果表使用 InnoDB，这是否会自动完成？

mysql
Martin Hope
Black
Asked: 2024-12-09 18:36:55 +0800 CST
  • 7

我们在网上商店收到此警告

report.WARNING：分配给临时表的内存大小超过 innodb_buffer_pool_size 的 20%。请更新 innodb_buffer_pool_size 或减小批处理大小值（这会减少临时表的内存使用量）。当前批处理大小：589；分配的内存大小：1742262000 字节；InnoDB 缓冲池大小：8589934592 字节。

因此我运行了mysqltuner来分析数据库设置，它建议将innodb_buffer_pool_size设置为 >13.7GB。目前设置为 8GB。

我们预计一月份的客户流量会非常高，所以我问我的主管是否允许将该值增加到大约 14 GB（我们有 12 GB 的可用 RAM），但据他说这只是一个警告，我们应该忽略它，直到三月份我们切换到另一个托管商。

根据我的研究，这种错误配置可能会导致性能损失，甚至导致交易失败并最终导致错误。这真的只是一个可以忽略的警告吗？

如果被忽视的话，可能发生的最坏情况是什么？

附加信息：

我们正在使用rabbitMQ将目录数据从PIM（产品信息管理）系统导入 magento 2。当队列消费者运行时，总是会出现警告。这会减慢商店的速度，在此期间平均负载上升到大约 30（10 个 CPU），而平均负载为 6。

mysql
Martin Hope
user2058625
Asked: 2024-11-28 18:19:22 +0800 CST
  • 6

我有两张表：“Flight”和“Person”。Person 有三列：“id”、“last”、“first”。

航班有三列：“id”、“P1_id”、“P2_id”，其中 P1_id 是“人员”的行 id，P2_id 也是“人员”的行 id。

我需要从“航班”中选择每一行，并从“人员”中选择相应的“最后”和“第一个”。表格数据如下所示 -

人员（身份证号、姓氏、名字）

  1. 史密斯，约翰
  2. 弗雷德·琼斯
  3. 玻璃，迈克尔

航班（id、p1_id、p2_id）

  1. 1、2
  2. 2、3
  3. 3、
  4. 3、2

我需要的输出看起来像 -

  1. 史密斯·琼斯
  2. 琼斯，玻璃
  3. 玻璃
  4. 玻璃，琼斯

感谢您的帮助。

mysql
Martin Hope
Vansh Patel
Asked: 2024-11-27 13:34:59 +0800 CST
  • 5

对于数据库管理，我还是个新手，所以请放轻松！

我有一个 MySQL 数据库。其中大约有 100 个表。目前，每个表大约有 50 行。因此，当从这些行中选择一行时，查询时间约为 0.0004 秒。

现在，我的问题是：假设其中一个表增长到大约 500 万行。那么，如果我只想从该表中选择 1 行，它的查询时间会增加吗？大约会增加多少？

如果是，那么表增长后我该怎么做才能减少查询时间？

表的一点背景：每个表大约有 10 到 11 列。其中大约 4 到 5 列是长度为 11 的整数，1 列是 longtext，其他列是长度为 255 的 varchar。

mysql
Martin Hope
Carolina
Asked: 2024-11-27 07:18:41 +0800 CST
  • 6

我需要按周备份特定表的数据，然后恢复它，但不截断表。该表包含 fechaRegistro

我准备了以下脚本，但我不确定声明日期变量的方法，您能帮我一下吗？

#!/bin/bash
# VARIABLES
FECHA=$(date +"%Y%m%d")
SERVIDOR="$(hostname)"
PASSWORD='PASSWORD'
BD=ordenes
TABLA="backlogPedidos"
ARCHIVO="$SERVIDOR-${BD^^}${TABLA^^}-$FECHA.sql"
RUTA_NAS="/tmp/"


# Establecer variables para calcular el rango semanal
INICIO_SEMANA=$(date -d "last sunday - 6 days" +"%Y-%m-%d 00:00:00")
FIN_SEMANA=$(date -d "last saturday" +"%Y-%m-%d 23:59:59")

# Se generar el backup por rangoo de una semana
mysqldump --databases $BD --tables $TABLA --where="fechaRegistro BETWEEN '$INICIO_SEMANA' AND '$FIN_SEMANA'" --skip-comments --compact --single-transaction --default-character-set=UTF8 --insert-ignore --complete-insert --skip-triggers --skip-routines --no-create-info --disable-keys --set-gtid-purged=OFF -q --lock-tables=false --user=backup -p$PASSWORD > /tmp/$ARCHIVO

echo "Backup semanal generado: $ARCHIVO"
mysql
Martin Hope
Eduardo Jiménez
Asked: 2024-11-24 11:07:33 +0800 CST
  • 6

我有一个可以从任何主机访问的用户（我们目前正在开发中）。但我想限制存储过程的执行仅限于本地主机。问题是localhost无法识别该用户。

DROP USER IF EXISTS 'admin'@'%';
CREATE USER IF NOT EXISTS 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE TO 'admin'@'%' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'%' WITH GRANT OPTION;
.
.
.

执行此代码会引发下一个错误。

[2024-11-23 20:59:09] [42000][1410] You are not allowed to create a user with GRANT

更改主机“解决”了错误，但我想将执行限制在本地主机。我不知道是否只需像这样添加新用户。

CREATE USER IF NOT EXISTS 'admin'@'127.0.0.1' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE1 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
.
.
.

或者是否有另一种方法，无需重复用户定义

mysql
Martin Hope
Sidharth Samant
Asked: 2024-11-15 21:47:21 +0800 CST
  • 6

我有这两张桌子

CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=330031656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;


CREATE TABLE `user_meta` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `meta_id` bigint NOT NULL,
  `value` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usermeta_user_id_meta_type_meta_value` (`user_id`,`meta_id`,`value`),
  CONSTRAINT `user_meta_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16728 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

客户要求，他们希望根据value，对特定的进行排序meta_id。例如，假设有 10meta_id个，客户选择meta_id=1111。在这种情况下，所有已关联的用户meta_id=1111都应按其第一顺序排序value，而其他没有关联的用户meta_id=1111可以按任何顺序排在底部。

曾经有人问过类似的问题，我也试图根据他们的答案提出疑问，但对我来说似乎不起作用。

这个答案和这个也讨论了使用if-else和case-when-then语句，但是当我尝试其中任何一个时

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by if(um.meta_id=1111, value, 1);

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case um.meta_id
when 1111 then value else 1 end;

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case 
when um.meta_id = 1111 then value else u.id end;

我明白了

+-----------+---------+------------+
| id        | meta_id | value      |
+-----------+---------+------------+
| 326480529 |     200 | 1730358000 |
| 326850494 |    1111 | 1730185200 |
| 326785127 |    1111 | 1730271600 |
| 326833934 |    1111 | 1730358000 |
| 326467136 |    1111 | 1730358000 |
| 328079379 |    1111 | 1730793600 |
+-----------+---------+------------+

我希望所有带有 的用户都meta_id=1111排在最上面，但他们既没有排在最上面，也没有在他们自己内部进行排序。同样，对于desc顺序，带有 的用户meta_id=1111应该排在最上面，按降序排列，而其他所有用户都可以排在最下面，例如

+-----------+---------+------------+
| id        | meta_id | value      |
+-----------+---------+------------+
| 328079379 |    1111 | 1730793600 |
| 326833934 |    1111 | 1730358000 |
| 326467136 |    1111 | 1730358000 |
| 326785127 |    1111 | 1730271600 |
| 326850494 |    1111 | 1730185200 |
| 326480529 |     200 | 1730358000 |
+-----------+---------+------------+

非常感谢任何帮助或指导，以解决此问题。非常感谢！

我还发布了INSERT两个表的一些语句，以便更轻松地在本地机器上复制

INSERT INTO `users` (`id`,`status`) VALUES (328079379,'active');
INSERT INTO `users` (`id`,`status`) VALUES (326833934,'active');
INSERT INTO `users` (`id`,`status`) VALUES (326467136,'deleted');
INSERT INTO `users` (`id`,`status`) VALUES (326785127,'inactive');
INSERT INTO `users` (`id`,`status`) VALUES (326850494,'removed');
INSERT INTO `users` (`id`,`status`) VALUES (326480529,'active');


INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13155,328079379,1111,1730793600);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13045,326833934,1111,1730358000);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13009,326467136,1111,1730358000);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13010,326785127,1111,1730271600);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13051,326850494,1111,1730185200);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13008,326480529,200,1730358000);
mysql
Martin Hope
SMich
Asked: 2024-11-13 09:55:57 +0800 CST
  • 5

对于 Rocky Linux 上的 MySQL 8.0.40，我的数据库目前使用 keyring 插件。我想迁移到 keyring 组件。它显示

[错误] [MY-013106] [服务器] 无法执行密钥环迁移：无法初始化目标密钥环。

我利用以下资源进行工作：

我看到有人成功关闭了加密，但我想避免这种情况。

我创建了清单：/usr/sbin/mysqld.my。它包含：

    {
      "components": "file://component_keyring_file"
    }

我检查了组件文件是否存在：

ls /usr/lib64/mysql/plugin/component_keyring_file.so #success

我创建了密钥环配置文件：**/usr/lib64/mysql/plugin/component_keyring_file.cnf**。其内容如下：

    {
      "path": "/var/lib/mysql/mysql-keyring/component_keyring_file",
      "read_only": false
    }

我为密钥环文件创建了目录：

    mkdir /var/lib/mysql/mysql-keyring
    chown mysql:mysql /var/lib/mysql/mysql-keyring

之后，我停止了 mysqld 并尝试运行迁移服务器。我尝试了以下变化：

    mysqld --user=mysql --defaults-file=/etc/my.cnf --keyring-migration-to-component  --keyring-migration-source=keyring_file.so  --keyring-migration-destination=component_keyring_file.so
    
    mysqld --user=mysql --keyring-migration-to-component  --keyring-migration-source=keyring_file.so  --keyring-migration-destination=component_keyring_file.so

我理解迁移用户不能是root，所以用户是mysql。另外component_keyring_file不能在数据目录中。我尝试了两种路径：

/var/lib/mysql/mysql-keyring # dir for mysql files

/var/lib/mysql-keyring

我更新了 component_keyring_file.cnf 并确保 mysql 用户拥有该目录。

mysql
Martin Hope
user216085
Asked: 2024-11-12 15:56:07 +0800 CST
  • 6

PostgreSQL您可以创建一个包含 4 个分区的表 ( hash)，并将每个分区定位在不同的服务器（远程 PostgreSQL 服务器）上，它被称为 ( )。现在在 MySQL中Foreign Data Wrapper，我听说有一个联合引擎，您可以使用它连接到远程服务器，但它只适用于表，而不是它的分区。我知道我可以使用NDB，但我想拥有多个 MySQL 服务器并自行管理分布的数据（基于表分区）。MySQL 中是否有任何解决方案可以创建一个表 ( Innodb) 并将其分区分布在多个远程 MySQL 服务器上？

mysql