Perguntas[mysql](dba)

Tenho um aplicativo MariaDb/PHP usado para pesquisar nomes biológicos (latino/sueco) de um grande banco de dados científico. A única entrada textual do usuário é fornecer um nome (parcial) em um textfield, lido pelo PHP e inserido em uma consulta SQL (abreviada):

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

ou

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

Essas consultas retornam 0 ou mais resultados do database.table, definidos por:

scientific_namevarchar(255)

Atualmente não estou usando consultas parametrizadas (preciso fazer isso?)

(MariaDb ver. 11.6.2, PHP ver. 8.2, servidor Debian 12 com Apache2)

EDIT: Código 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;

Meu banco de dados usa InnoDB e, ao usá-lo, mysqldumptenho a opção de configurar --single-transactionpara evitar o bloqueio das tabelas e, ao mesmo tempo, manter uma visualização consistente delas.

Estou tentando mudar para o MySQL Shellutil.dumpSchema recomendado , e só vejo duas opções: consistent: truee consistent: false. A documentação para consistent: trueespecifica que as tabelas serão bloqueadas para backup.

É possível usar util.dumpSchemapara despejar em uma única transação via InnoDB da mesma forma? Ou isso é feito automaticamente se as tabelas usarem InnoDB?

Recebemos este aviso em nossa loja online

report.WARNING: O tamanho da memória alocada para a tabela temporária é maior que 20% do innodb_buffer_pool_size. Atualize o innodb_buffer_pool_size ou diminua o valor do tamanho do lote (o que diminui o uso da memória para a tabela temporária). Tamanho atual do lote: 589; Tamanho da memória alocada: 1742262000 bytes; Tamanho do buffer pool do InnoDB: 8589934592 bytes.

Então eu executei o mysqltuner para analisar as configurações do banco de dados e ele recomendou definir o innodb_buffer_pool_size para >13.7GB. Ele está definido para 8GB atualmente.

Esperamos um tráfego muito alto de clientes em janeiro, então perguntei ao meu supervisor se eu poderia aumentar o valor para cerca de 14 GB (temos 12 GB de RAM livre), mas, segundo ele, é apenas um aviso e devemos ignorá-lo até mudarmos para outro hoster em março.

De acordo com minha pesquisa, essa configuração incorreta pode levar à perda de desempenho e até mesmo causar falhas nas transações e, por fim, causar bugs. Isso é realmente apenas um aviso que se pode ignorar?

Qual é o pior caso que pode acontecer quando ignorado?

Informações adicionais:

Estamos usando o rabbitMQ para importar dados de catálogo de um sistema PIM (Product Information Management) para o magento 2. O aviso está sempre aparecendo, quando os consumidores da fila estão em execução. Isso desacelera a loja e a média de carga sobe para cerca de 30 (10 cpus) durante esse tempo, enquanto está em 6 em média.

Tenho duas tabelas: "Flight" e "Person". Person tem três colunas: "id", "last", "first".

O voo tem três colunas: "id", "P1_id", "P2_id", onde P1_id é o id da linha de uma "Pessoa" e P2_id também é o id da linha de uma "Pessoa".

Preciso selecionar cada linha de "Flight" com o "last" e "first" apropriados de "Person". Os dados da tabela parecem algo como -

Pessoa (id, último, primeiro)

1. ferreiro, joão
2. jones, fred
3. vidro, michael

Voo (id, p1_id, p2_id)

1. 1, 2
2. 2, 3
3. 3,
4. 3, 2

Preciso que a saída pareça -

1. ferreiro, jones
2. jones, vidro
3. vidro
4. vidro, jones

Obrigado pela ajuda.

Sou um pouco novato quando se trata de administração de banco de dados, então, vá com calma!

Eu tenho um banco de dados MySQL. No qual há cerca de 100 tabelas. Agora mesmo, cada tabela tem aproximadamente 50 linhas. Então, quando se trata de selecionar uma linha dessas linhas, o tempo de consulta é de cerca de 0,0004 segundos.

Agora, para minha pergunta: Suponha que uma dessas tabelas cresça e tenha cerca de 5 milhões de linhas. Então, se eu quiser selecionar apenas 1 linha dessa tabela, seu tempo de consulta aumentará? Em aproximadamente quanto?

Se sim, o que posso fazer para reduzir o tempo de consulta depois que a tabela crescer?

Um pouco de contexto da tabela: Cada tabela tem cerca de 10 a 11 colunas. Das quais cerca de 4 a 5 são inteiros de comprimento 11, e 1 coluna é longtext e outras são varchar de comprimento 255.

Preciso fazer backup dos dados de uma tabela específica por semana e depois restaurá-la, mas sem truncar a tabela. Esta tabela contém o fechaRegistro

Eu preparei o seguinte script, mas não tenho certeza sobre como declarar variáveis ​​de data. Você poderia me ajudar, por favor?

#!/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"

Tenho um usuário que pode ser acessado de qualquer host (estamos atualmente em desenvolvimento). Mas quero limitar a execução de procedimentos armazenados apenas para localhost. O problema é que o localhostusuário não é reconhecido.

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;
.
.
.

Executar esse código me gera o próximo erro.

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

Alterar o host "resolve" o erro, mas quero limitar a execução ao localhost. Não sei se preciso apenas adicionar um novo usuário assim.

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;
.
.
.

Ou se houver outra maneira sem repetir a definição do usuário

Eu tenho essas 2 mesas

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 ;

O requisito do cliente é que ele queira classificar com base em value, para um meta_id. Por exemplo, digamos que há 10 meta_ids, e o cliente seleciona meta_id=1111. Nesse caso, todos os usuários que se meta_id=1111associaram devem ser classificados pelo valueprimeiro, e os outros usuários que não se associaram meta_id=1111podem vir em qualquer ordem na parte inferior.

Perguntas semelhantes foram feitas e tentei formular perguntas com base nas respostas, mas elas não parecem funcionar para mim.

Esta resposta e esta também falam sobre o uso de instruções if-elseand case-when-then, mas quando tento qualquer uma delas

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;

Eu entendo isso

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

Quero que todos os usuários com meta_id=1111venham no topo, mas eles não vêm no topo, nem são classificados dentro de si mesmos. Da mesma forma, para descordem, os com meta_id=1111devem vir no topo, classificados em ordem decrescente, e todos os outros podem vir na parte inferior, como este

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

Eu realmente apreciaria qualquer ajuda ou direção para fazer isso direito. Muito obrigado!

Também estou postando algumas INSERTinstruções para ambas as tabelas para facilitar a replicação em suas máquinas locais

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);

Para MySQL 8.0.40 no Rocky Linux, meu banco de dados atualmente usa o plugin keyring. Quero migrar para o componente keyring. Ele mostra

[ERRO] [MY-013106] [Servidor] Não é possível executar a migração do chaveiro: Falha ao inicializar o chaveiro de destino.

Estou trabalhando com estes recursos:

• https://dev.mysql.com/doc/refman/8.0/en/keyring-key-migration.html
• https://blogs.oracle.com/mysql/post/keyring-components
• https://bugs.mysql.com/bug.php?id=108197

Vejo que alguém conseguiu desativar a criptografia , mas gostaria de evitar isso.

Criei o manifesto: /usr/sbin/mysqld.my . Ele contém:

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

Verifiquei se o arquivo do componente existe:

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

Criei o arquivo de configuração do chaveiro: **/usr/lib64/mysql/plugin/component_keyring_file.cnf**. Ele diz:

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

Criei o diretório para o arquivo do chaveiro:

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

Depois disso, parei o mysqld e tentei executar o servidor de migração. Tentei variações:

    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

Entendo que o usuário que está migrando não pode ser root, então o usuário é mysql. Além disso, o component_keyring_file não pode estar no diretório de dados. Tentei dois caminhos:

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

/var/lib/mysql-keyring

Atualizei component_keyring_file.cnf e certifiquei-me de que o usuário mysql era o proprietário do diretório.

em PostgreSQLvocê pode criar uma tabela com digamos 4 partições ( hash) e localizar cada partição em servidores diferentes (servidores PostgreSQL remotos) e é chamado ( Foreign Data Wrapper). Agora no MySQL eu ouvi que há um mecanismo federado que você pode conectar a um servidor remoto com ele, mas ele só funciona para uma tabela, não para suas partições. Eu sei que posso usar NDB, mas quero ter vários servidores MySQL e gerenciar dados espalhados por mim mesmo (com base nas partições da tabela). Existe alguma solução no MySQL para fazer uma tabela ( Innodb) e espalhar suas partições em vários servidores MySQL remotos?