BenMorel's questions

Estou no processo de transferir um banco de dados de um servidor para outro. Estou movendo os arquivos de dados diretamente, em vez de usar um dump SQL. Para fazer isso, eu:

• garantiu que ambos os servidores usem a mesma versão do MySQL
• definirinnodb_fast_shutdown = 0
• parou o servidor MySQL
• rsynced os dados para o novo servidor

Estou quase pronto para iniciar o novo servidor, mas tenho uma dúvida. O servidor antigo usa o mesmo diretório /var/lib/mysqlde dados para dados, logs binários, etc., enquanto o novo servidor usa 3 diretórios diferentes para dados, logs binários e tmp:

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

A questão é: para onde deve ir cada um dos arquivos?

Meu raciocínio é:

• /mysql/tmp: não deve haver nenhum arquivo temporário após um desligamento lento (ou mesmo normal?), então nada deve ir para lá agora
• /mysql/binlog: só devo mover os mysql-bin.*arquivos
• /mysql/data: todo o resto deve ir para lá

Isso é correto?

Aqui está a lista de arquivos, para informação ( nxxxxxxxé meu banco de dados principal):

# 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

Eu costumava ver a configuração relacionada ao servidor my.cnflocalizada em [mysqld].

No entanto, agora me deparo com configurações que usam [server]em vez disso.

Parece que ambos são sinônimos, pelo menos no MySQL 8.0, posso ver que as opções em ambos os grupos se aplicam ao servidor MySQL.

Este é um novo recurso do MySQL 8.0? Um deles é preferido sobre o outro? Um deles está obsoleto?

Eu tenho um programa que troca regularmente uma partição com uma tabela.

MyTableé uma tabela particionada e MyTableTemplateé uma cópia vazia dessa tabela, com a mesma estrutura, mas sem particionamento.

O processo é o seguinte:

CREATE TABLE tmp LIKE MyTableTemplate;

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

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

Funcionou bem até agora, até que precisei adicionar uma coluna à tabela:

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

Agora quando executo EXCHANGE PARTITION WITH TABLE, recebo a seguinte mensagem:

Erro geral: 1731 Atributo não correspondente 'INSTANT COLUMN(s)' entre partição e tabela

Estranhamente, se eu não adicionar a coluna na última posição:

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

...então a troca de partição funciona bem!

O que é esse " INSTANT COLUMN(s)atributo" e o que fiz de errado?

Isso é um inseto? Estou usando o MySQL 8.0.12.

Gostaria de criar uma tabela no master, cujos dados não sejam replicados para o slave.

Estou inserindo temporariamente muitos milhões de linhas nesta tabela como parte de um trabalho em lote; os dados nesta tabela podem ser recriados sob demanda, portanto, não precisam ser replicados no escravo (mais lento).

Eu não quero usar TEMPORARYtabelas, pois idealmente quero manter a estrutura da tabela no banco de dados (no escravo também, para que eu possa usá-la de forma confiável para recuperação de backup) e, acima de tudo, quero poder recuperar do estado atual se a tarefa for encerrada por qualquer motivo, portanto, os dados da tabela precisam persistir no mestre se a tarefa for encerrada.

Idealmente, eu gostaria de poder controlar a replicabilidade da tabela a partir da CREATEinstrução, para poder controlar isso do meu fluxo de trabalho SQL normal.

Isso é possível? Caso contrário, alternativas próximas são bem-vindas.

Estou configurando a replicação de uma instância externa do MySQL para RDS .

Tanto a instância externa do MySQL quanto o RDS executam o MySQL 5.7.10.

Eu segui o procedimento, mas ao despejar o banco de dados mysqldumpe enviá-lo para mysqlconectado ao RDS, recebo o seguinte erro:

ERRO 1215 (HY000) na linha 2081: Não é possível adicionar restrição de chave estrangeira

SHOW ENGINE InnoDB STATUSnos programas 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

OK, de fato o arquivo dump recria as tabelas em ordem alfabética, então a Prospecttabela aparece antes Restaurante Zone, o que pode explicar o erro.

Mas NÃO, porque mysqldumpadiciona SET FOREIGN_KEY_CHECKS=0ao topo do arquivo, então deve funcionar de qualquer maneira.

Então, decidi testá-lo em uma instalação vanilla do MySQL 5.7 e, sem surpresa, o dump é importado com sucesso com exatamente os mesmos comandos.

O que causa esse erro especificamente no RDS e como evitá-lo?

Acabei de baixar a primeira versão GA do MySQL 5.7 e notei que o pacote RPM vem com duas versões do pacote do servidor MySQL:

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

Qual é a diferença entre os pacotes de servidor normal e "mínimo"?

Não consegui encontrar essa informação no site do MySQL.

Tenho um script para instalar o MySQL 5.7 e rodar testes no Travis CI (rodando Ubuntu 12).

O script contém essas linhas, para instalar o MySQL em modo não interativo, sem senha:

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

A instalação é executada sem problemas e, usando o mysqlutilitário de linha de comando, posso me conectar ao servidor sem senha:

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

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

Agora, se eu tentar conectar usando PHP PDO :

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

Estou tendo o erro a seguir:

SQLSTATE[HY000] [1045] Acesso negado para o usuário 'root'@'localhost' (usando a senha: NO)

Eu tentei permitir roota conexão de qualquer host:

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

Sem sucesso.

Qual pode ser o problema?

Dada a seguinte tabela:

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

Se eu inserir um valor apenas com o idcampo definido:

INSERT INTO Test (id) VALUES (1);

No meu servidor de desenvolvimento, recebo um aviso:

Aviso: #1366 Valor inteiro incorreto: '' para a coluna 'valor' na linha 1

No meu servidor de produção, recebo um erro:

ERRO 1364 (HY000): O campo 'valor' não tem um valor padrão

O que devo configurar no meu servidor de desenvolvimento para que ele se comporte como meu servidor de produção?

Eu executo um aplicativo (site de comparação de preços) onde preciso atualizar uma tabela de preços com dezenas de milhões de linhas todos os dias.

Basicamente, cada feed de produto que eu baixo de um comerciante resulta em vários milhões de linhas para excluir da tabela de preços ( WHERE merchantId = x) e vários milhões de novas linhas para inserir no lugar das antigas.

Até agora, eu costumava baixar todos os feeds sequencialmente, inserir todos eles em uma tabela temporária e depois trocar a tabela antiga pela nova em uma única RENAME TABLEconsulta.

Isso me forçou a atualizar todos os comerciantes de uma vez, portanto, gostaria de abandonar esse método e ter a capacidade de processar um comerciante por vez, excluindo todos os registros WHERE merchantId = xe inserindo todos os novos registros em uma consulta de subsegundo.

Meu primeiro pensamento foi usar o particionamento por lista , uma partição por comerciante. Isso resolveria metade do meu problema: posso usar TRUNCATE PARTITIONpara remover rapidamente todos os preços existentes para um determinado comerciante, mas a outra metade do problema permanece: como inserir rapidamente os milhões de novas linhas, sem tempo de inatividade e bloqueios desnecessários e sem executar com a mesa parcialmente carregada por vários minutos?

Então, pensei: é possível criar uma tabela temporária com a mesma estrutura da tabela particionada, preenchê-la com as novas linhas e, depois de concluída, trocar uma partição da tabela de preços por esta tabela temporária?

Qualquer sugestão para resolver este problema seria apreciada, incluindo sugestões que não envolvam particionamento. Também pensei no MRG_MYISAMmotor, mas não tenho certeza se ele atende às minhas necessidades. Em uma observação final, qualquer mecanismo de armazenamento é adequado para esta tabela de preços, incluindo mecanismos não transacionais.

Eu li vários artigos sobre eficiência de chaves primárias, dependendo do mecanismo de armazenamento, e estou confuso.

Dada uma tabela muitos-para-muitos simples com dois campos storeIde zoneId, qual dos seguintes designs é o mais eficiente com o InnoDB e por quê?

• usando os dois campos como uma chave primária composta:

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

• Usando uma chave primária específica de incremento automático:

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

Notas:

• Eu preciso de uma chave única no par ( storeId, ) de qualquer maneirazoneId
• Eu tenho chaves estrangeiras para as tabelas storee zone, não mostradas aqui para facilitar a leitura , portanto, em ambos os casos, há um índice extra necessário zoneIdtambém

Eu gostaria de executar um CHECK TABLEem todas as minhas tabelas da linha de comando do MySQL.

Eu posso fazer isso para uma única tabela:

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

Existe um comando equivalente que verificaria todas as tabelas no banco de dados atual ?

Tentei criar esta função no 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;

Isso só seria usado em uma consulta única para atualizar uma tabela de banco de dados existente.

Executá-lo me deu o seguinte erro:

ERRO 1419 (HY000): Você não tem o privilégio SUPER e o registro binário está ativado (você pode querer usar a variável menos segura log_bin_trust_function_creators)

Essa restrição também se aplica a procedimentos armazenados ou apenas a funções?

Se também se aplicar a procedimentos armazenados, existe alguma alternativa para executar um código equivalente sem definir log_bin_trust_function_creators=1? Observe que sei como alterar essa configuração no RDS, gostaria apenas de evitar isso, se possível .

Eu procurei por soluções para bloquear um banco de dados inteiro, e o único que encontrei até agora é FLUSH TABLES WITH READ LOCK .

Isso é bom para backups, mas infelizmente não posso usar isso para impedir outros acessos ao banco de dados enquanto estou corrigindo meu banco de dados com alterações de esquema. Se eu executar um ALTER TABLEafter FLUSH TABLES WITH READ LOCK, recebo a seguinte mensagem:

Não é possível executar a consulta porque você tem um bloqueio de leitura conflitante

Existe uma maneira de impedir que outras conexões de banco de dados acessem o banco de dados temporariamente enquanto estou corrigindo o banco de dados?

Não quero recusar ativamente as outras conexões, gostaria apenas de colocá-las "em espera" até que o patch seja concluído (uma questão de segundos).

Como eu posso fazer isso?

Ao usar o comando restore-db-instance-to-point-in-time da AWS CLI , não consigo descobrir como definir o grupo de segurança. O documento nesta página diz:

O banco de dados de destino é criado a partir do banco de dados de origem com a mesma configuração do banco de dados original, exceto que a instância de banco de dados é criada com o security group de banco de dados padrão.

É possível substituir esse parâmetro para usar o mesmo security group da instância original?

Estou tentando executar o MySQL DBT2 Benchmark Tool , descompactei o arquivo e executei os seguintes comandos:

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

Mas obteve a seguinte saída:

...

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.

Durante a execução, SHOW PROCESSLISTmostrei que 20 clientes estavam conectados, todos dormindo.

Alguma ideia de por que nenhuma transação é executada?

Até o MySQL 5.6.7, o RPM oficial vinha com estes arquivos:

/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

No 5.6.8, esses arquivos se foram e o único que resta é:

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

Não sou um especialista em ajuste do MySQL e costumava começar sempre com my-innodb-heavy-4G.cnf. Agora me resta uma única opção de configuração:

# 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

O ajuste dessa única variável é a chave mágica para a maioria das otimizações ou existem outros parâmetros relevantes que devo ajustar ao instalar um novo servidor?

Nesse caso, onde posso encontrar um arquivo equivalente, que conteria padrões sensatos para um servidor MySQL dedicado apenas para InnoDB e 4 GB de RAM?