Attività di log DBA comuni per istanze database MySQL

Nel seguente contenuto, puoi trovare le descrizioni delle implementazioni specifiche di Amazon RDS di alcune attività DBA comuni per le istanze DB che eseguono il motore di database MySQL. Per offrire un'esperienza di servizio gestito, Amazon RDS non fornisce accesso shell alle istanze database. Limita anche l'accesso ad alcune procedure di sistema e tabelle che richiedono privilegi avanzati.

Per informazioni sull'uso di file di log MySQL in Amazon RDS, consult File di log del database MySQL.

Comprendere gli utenti predefiniti

Amazon RDS crea automaticamente diversi utenti predefiniti con nuove istanze DB RDS per MySQL. Gli utenti predefiniti e i relativi privilegi non possono essere modificati. Non è possibile eliminare, rinominare o modificare i privilegi per questi utenti predefiniti. Qualsiasi tentativo comporta la generazione di un errore.

• rdsadmin: utente creato per gestire molte delle attività di gestione che l'amministratore con superuser privilegi eseguirebbe su un database MySQL autonomo. Questo utente viene utilizzato internamente da RDS for MySQL per molte attività di gestione.

• rdsrepladmin: utente utilizzato internamente da Amazon RDS per supportare le attività di replica su istanze e cluster RDS for MySQL DB.

Privilegio basato sui ruoli

A partire dalla versione 8.0.36 di RDS for MySQL, non è possibile modificare direttamente le tabelle nel database. mysql In particolare, non è possibile creare utenti del database eseguendo operazioni DML (Data Manipulation Language) sulle tabelle. grant Si utilizzano invece istruzioni di gestione degli account MySQL CREATE USER comeGRANT, REVOKE e per concedere privilegi basati sui ruoli agli utenti. Inoltre, nel database mysql, non è possibile creare altri tipi di oggetti come le stored procedure. È comunque possibile interrogare le tabelle di mysql. Se si utilizza la replica dei log binari, le modifiche apportate direttamente alle mysql tabelle sull'istanza DB di origine non vengono replicate nel cluster di destinazione.

In alcuni casi, l'applicazione potrebbe utilizzare scorciatoie per creare utenti o altri oggetti inserendoli nelle tabelle di mysql. In tal caso, modifica il codice dell'applicazione per utilizzare le istruzioni corrispondenti come CREATE USER.

Per esportare i metadati per gli utenti del database durante la migrazione da un database MySQL esterno, utilizzare uno dei seguenti metodi:

• Utilizza l'utilità di dump delle istanze di MySQL Shell con un filtro per escludere utenti, ruoli e concessioni. L'esempio seguente mostra la sintassi del comando da utilizzare. Assicurati che outputUrl sia vuoto.

  mysqlsh user@host -- util.dumpInstance(outputUrl,{excludeSchemas:['mysql'],users: true})

  Per ulteriori informazioni, vedere Instance Dump Utility, Schema Dump Utility e Table Dump Utility nel MySQL Reference Manual.

• Usa l'utilità client. mysqlpump Questo esempio include tutte le tabelle ad eccezione delle tabelle del database mysql di sistema. Include anche istruzioni CREATE USER e GRANT per riprodurre tutti gli utenti MySQL nel database migrato.

  mysqlpump --exclude-databases=mysql --users

Per semplificare la gestione delle autorizzazioni per molti utenti o applicazioni, è possibile utilizzare l'istruzione CREATE ROLE per creare un ruolo con una serie di autorizzazioni. Puoi quindi utilizzare le istruzioni GRANT e SET ROLE e la funzione current_role per assegnare ruoli a utenti o applicazioni, cambiare il ruolo corrente e verificare quali ruoli sono in vigore. Per ulteriori informazioni sul sistema di autorizzazione basato sui ruoli in MySQL 8.0, consultare Utilizzo di ruoli nel Manuale di riferimento di MySQL.

Importante

Si consiglia di non utilizzare l'utente master direttamente nelle applicazioni. Rispetta piuttosto la best practice di utilizzare un utente del database creato con i privilegi minimi richiesti per l'applicazione.

A partire dalla versione 8.0.36, RDS for MySQL include un ruolo speciale con tutti i seguenti privilegi. Il ruolo è denominato rds_superuser_role. Questo ruolo è già assegnato all'utente amministrativo principale di ogni istanza DB. Il ruolo rds_superuser_role include i seguenti privilegi per tutti gli oggetti del database:

• ALTER

• APPLICATION_PASSWORD_ADMIN

• ALTER ROUTINE

• CREATE

• CREATE ROLE

• CREATE ROUTINE

• CREATE TEMPORARY TABLES

• CREATE USER

• CREATE VIEW

• DELETE

• DROP

• DROP ROLE

• EVENT

• EXECUTE

• INDEX

• INSERT

• LOCK TABLES

• PROCESS

• REFERENCES

• RELOAD

• REPLICATION CLIENT

• REPLICATION SLAVE

• ROLE_ADMIN

• SET_USER_ID

• SELECT

• SHOW DATABASES

• SHOW VIEW

• TRIGGER

• UPDATE

• XA_RECOVER_ADMIN

La definizione del ruolo include anche la WITH GRANT OPTION in modo che un utente amministrativo possa concedere tale ruolo ad altri utenti. In particolare, l'amministratore deve concedere tutti i privilegi necessari per eseguire la replica dei log binari con il cluster MySQL come destinazione.

Suggerimento

Per visualizzare i dettagli completi delle autorizzazioni, utilizzare la seguente dichiarazione.

SHOW GRANTS FOR rds_superuser_role@'%';

Quando concedi l'accesso utilizzando i ruoli in RDS for MySQL versione 8.0.36 e successive, attivi il ruolo anche utilizzando l'istruzione or. SET ROLE role_name SET ROLE ALL L'esempio seguente mostra come. Sostituire il nome del ruolo appropriato per CUSTOM_ROLE.

# Grant role to user
mysql> GRANT CUSTOM_ROLE TO 'user'@'domain-or-ip-address'

# Check the current roles for your user. In this case, the CUSTOM_ROLE role has not been activated.
# Only the rds_superuser_role is currently in effect.
mysql> SELECT CURRENT_ROLE();
+--------------------------+
| CURRENT_ROLE()           |
+--------------------------+
| `rds_superuser_role`@`%` |
+--------------------------+
1 row in set (0.00 sec)

# Activate all roles associated with this user using SET ROLE.
# You can activate specific roles or all roles.
# In this case, the user only has 2 roles, so we specify ALL.
mysql> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)

# Verify role is now active
mysql> SELECT CURRENT_ROLE();
+--------------------------------------------------+
| CURRENT_ROLE()                                   |
+--------------------------------------------------+
| `CUSTOM_ROLE`@`%`,`rds_superuser_role`@`%` |
+--------------------------------------------------+

Terminare una sessione o una query

Puoi terminare query o sessioni utente sulle istanze database utilizzando i comandi rds_kill e rds_kill_query. Connettiti alla tua istanza database MySQL, quindi immetti il comando appropriato come mostrato di seguito. Per ulteriori informazioni, consulta Connessione a un'istanza database che esegue il motore di database di MySQL.

CALL mysql.rds_kill(thread-ID)
CALL mysql.rds_kill_query(thread-ID) 

Ad esempio, per terminare la sessione in esecuzione sul thread 99, dovresti digitare:

CALL mysql.rds_kill(99); 

Per terminare la query in esecuzione sul thread 99, dovresti digitare:

CALL mysql.rds_kill_query(99); 

Ignorare l'errore di replica corrente

È possibile ignorare un errore sulle repliche di lettura se l'errore provoca il blocco della replica di lettura e non compromette l'integrità dei dati.

Nota

Dovrai prima verificare che sia sicuro ignorare l'errore. In una utility MySQL, connettiti alla replica di lettura ed esegui il seguente comando MySQL.

SHOW REPLICA STATUS\G 

Per informazioni sui valori restituiti, vedere la documentazione di MySQL.

Le versioni precedenti di MySQL utilizzavano SHOW SLAVE STATUS anziché SHOW REPLICA STATUS. Se si utilizza una versione MySQL prima della 8.0.23, utilizzare SHOW SLAVE STATUS.

È possibile saltare un errore nella replica di lettura nei seguenti modi.

Argomenti

• Chiamata della procedura mysql.rds_skip_repl_error
• Impostazione del parametro slave_skip_errors

Chiamata della procedura mysql.rds_skip_repl_error

Amazon RDS fornisce una stored procedure che puoi chiamare per saltare un errore nelle repliche di lettura. In primo luogo, collegati alla replica di lettura, quindi emetti i comandi appropriati come mostrato qui di seguito. Per ulteriori informazioni, consulta Connessione a un'istanza database che esegue il motore di database di MySQL.

Per ignorare l'errore, puoi emettere il seguente comando.

CALL mysql.rds_skip_repl_error; 

Questo comando non ha alcun effetto se lo esegui sull'istanza database di origine o in una replica di lettura che non ha riscontrato un errore di replica.

Per ulteriori informazioni, ad esempio per conoscere le versioni di MySQL che supportano mysql.rds_skip_repl_error, consulta mysql.rds_skip_repl_error.

Importante

Se tenti di chiamare mysql.rds_skip_repl_error e incontri questo errore: ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist, aggiorna l’istanza database MySQL alla versione secondaria più recente o a una delle versioni secondarie minime indicate in mysql.rds_skip_repl_error.

Impostazione del parametro slave_skip_errors

Per saltare uno o più errori, puoi impostare il parametro statico slave_skip_errors sulla replica di lettura. Puoi impostare questo parametro per saltare uno o più codici di errore di replica specifici. Attualmente, puoi impostare questo parametro solo per le istanze DB per RDS for MySQL 5.7. Dopo aver modificato l'impostazione per questo parametro, accertati di riavviare l'istanza database per rendere effettiva la nuova impostazione. Per ulteriori informazioni su questo parametro, consulta la documentazione di MySQL.

Consigliamo di impostare questo parametro in un gruppo di parametri database a parte. Puoi associare questo gruppo di parametri database solo alle repliche di lettura che devono saltare gli errori. Seguendo questa best practice riduci il potenziale impatto su altre istanze database e sulle repliche di lettura.

Importante

L'impostazione di un valore non predefinito per questo parametro può causare incoerenza nella replica. Impostare questo parametro su un valore non predefinito solo se sono state esaurite le altre opzioni per risolvere il problema e si è sicuri del potenziale impatto sui dati della replica di lettura.

Lavorare con gli spazi tabella InnoDB per migliorare i tempi di ripristino dopo un arresto anomalo

Ogni tabella in MySQL è formata da una definizione della tabella, dati e indici. Il motore di storage InnoDB MySQL salva gli indici e i dati della tabella in uno spazio tabella. InnoDB crea uno spazio tabella globale condiviso che contiene un dizionario di dati e altri metadati rilevanti e che può inoltre contenere indici e dati della tabella. InnoDB può anche creare degli spazi tabella per ciascuna tabella e partizione. Questi spazi tabella separati vengono salvati in file con estensione .ibd e l'intestazione di ciascuno spazio tabella contiene un numero identificativo univoco.

Amazon RDS fornisce un parametro in un gruppo di parametri MySQL denominato innodb_file_per_table. Questo parametro controlla se InnoDB aggiunge nuovi dati e indici di tabella allo spazio di tabella condiviso (impostando il valore del parametro su 0) o a singoli spazi di tabella (impostando il valore del parametro su 1). Amazon RDS imposta il valore predefinito per il parametro innodb_file_per_table su 1, che consente di eliminare singole tabelle InnoDB e recuperare l'archiviazione utilizzata da tali tabelle per l'istanza database. Nella maggior parte dei casi d'uso l'impostazione del parametro innodb_file_per_table su 1 rappresenta l'opzione consigliata.

Dovresti impostare il parametro innodb_file_per_table su 0 quando hai un numero elevato di tabelle, ad esempio oltre 1.000 tabelle quando utilizzi lo storage standard (magnetico) o lo storage General Purpose SSD oppure oltre 10.000 tabelle quando utilizzi lo storage Provisioned IOPS Quando imposti questo parametro su 0, non vengono creati singoli spazi tabella, pertanto il ripristino dopo un arresto anomalo del database viene completato in minor tempo.

MySQL elabora ciascun file dei metadati, che include spazi tabella durante il ciclo di recupero dopo un arresto anomalo. Il tempo richiesto da MySQL per elaborare le informazioni dei metadati negli spazi tabella condivisi è trascurabile rispetto al tempo necessario per elaborare migliaia di file di spazi tabella quando sono presenti più spazi tabella. Poiché il numero di spazi tabella viene salvato nell'intestazione di ciascun file, il tempo complessivo per leggere tutti i file degli spazi tabella può essere di diverse ore. Ad esempio, per elaborare un milione di spazi tabella InnoDB nello storage standard per un ciclo di ripristino dopo un arresto anomalo potrebbero essere necessarie da cinque a otto ore. In alcuni casi, InnoDB può reputare la necessità di una pulizia aggiuntiva dopo un ciclo di ripristino dopo un arresto anomalo che attiverà un altro ciclo di ripristino dopo un arresto anomalo, rendendo più lungo il tempo di ripristino. Ricorda che un ciclo di ripristino dopo un arresto anomalo, oltre all'elaborazione delle informazioni degli spazi tabella, implica transazioni di rollback, riparazioni delle pagine non funzionanti e altre operazioni.

Poiché il parametro innodb_file_per_table risiede in un gruppo di parametri, puoi cambiare il valore del parametro modificando il gruppo di parametri utilizzato dalla tua istanza database senza riavviarla. Dopo aver cambiato l'impostazione, ad esempio da 1 (per creare tabelle individuali) a 0 (per utilizzare gli spazi tabella condivisi), allo spazio tabella condiviso saranno aggiunte altre tabelle InnoDB, mentre quelle esistenti continueranno ad avere degli spazi tabella individuali. Per spostare una tabella InnoDB in uno spazio tabella condiviso, devi utilizzare il comando ALTER TABLE.

Migrazione di più spazi tabella in uno spazio tabella condiviso

Puoi spostare i metadati di una tabella InnoDB dal loro spazio tabella allo spazio tabella condiviso che ricompilerà i metadati della tabella secondo l'impostazione del parametro innodb_file_per_table. Connettiti innanzitutto alla tua istanza database MySQL, quindi utilizza i comandi appropriati come mostrato di seguito. Per ulteriori informazioni, consulta Connessione a un'istanza database che esegue il motore di database di MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Ad esempio, la seguente query restituisce un'istruzione ALTER TABLE per ogni tabella InnoDB non presente nello spazio tabella condiviso.

Per le istanze database MySQL 5.7:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Per le istanze database MySQL 8.0:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

La ricompilazione di una tabella MySQL per spostare i metadati della tabella nello spazio tabella condiviso richiede uno spazio di storage ulteriore disponibile nell'istanza database. Durante la ricompilazione, la tabella è bloccata e inaccessibile alle query. Per le tabelle di piccole dimensioni o le tabelle a cui non si accede di frequente, questo potrebbe non essere un problema. Per tabelle di grandi dimensioni o tabelle a cui si accede di frequente in un ambiente con molti processi simultanei, puoi ricompilare le tabelle su una replica di lettura.

Puoi creare una replica di lettura e migrare i metadati della tabella nello spazio tabella condiviso sulla replica di lettura. Mentre l'istruzione ALTER TABLE blocca l'accesso sulla replica di lettura, l'istanza database di origine non viene interessata. L'istanza database di origine continuerà a generare i suoi log binari, mentre la replica di lettura sarà in ritardo durante il processo di ricompilazione della tabella. Poiché la ricompilazione richiede spazio di storage aggiuntivo e il file di log di riproduzione può essere di grandi dimensioni, dovresti creare una replica di lettura con uno storage allocato più grande rispetto all'istanza database di origine.

Per creare una replica di lettura e ricompilare le tabelle InnoDB per utilizzare lo spazio tabella condiviso, procedere come indicato di seguito:

1. Assicurarsi che la retention dei backup sia abilitata sull'istanza database di origine in modo che sia abilitato il log binario.

2. Utilizzate AWS Management Console o AWS CLI per creare una replica di lettura per l'istanza DB di origine. Poiché la creazione di una replica di lettura richiede molti degli stessi processi di un ripristino dopo un arresto anomalo, il processo di creazione potrebbe richiedere diverso tempo se sono presenti numerosi spazi tabella InnoDB. Allocare più spazio di storage sulla replica di lettura rispetto a quello attualmente utilizzato sull'istanza database.

3. Una volta creata la replica di lettura, creare un gruppo di parametri con le impostazioni dei parametri read_only = 0 e innodb_file_per_table = 0. Quindi, associare il gruppo di parametri alla replica di lettura.

4. Immettere la seguente istruzione SQL per tutte le tabelle di cui si desidera eseguire la migrazione nella replica:

   ALTER TABLE name ENGINE = InnoDB

5. Quando tutte le istruzioni ALTER TABLE sono state completate sulla replica di lettura, verificare che quest'ultima sia connessa all'istanza database di origine e che le due istanze siano sincronizzate.

6. Utilizzare la console o CLI per promuovere la replica di lettura a istanza. Assicurarsi che il gruppo di parametri utilizzato per la nuova istanza DB standalone abbia il parametro innodb_file_per_table impostato su 0. Modificare il nome della nuova istanza DB standalone e puntare tutte le applicazioni alla nuova istanza DB standalone.

Gestione della cronologia di stato globale

Suggerimento

Per analizzare le prestazioni del database, puoi anche utilizzare la funzionalità Approfondimenti sulle prestazioni su Amazon RDS. Per ulteriori informazioni, consulta Monitoraggio del carico DB con Performance Insights su Amazon RDS.

MySQL mantiene molte variabili di stato che forniscono informazioni sul suo funzionamento. Il loro valore può aiutarti a rilevare problemi di blocco o di memoria su un'istanza database. I valori di queste variabili di stato sono cumulativi dal momento dell'ultimo avvio dell'istanza database. Puoi reimpostare la maggiore parte delle variabili di stato su 0 utilizzando il comando FLUSH STATUS.

Per consentire il monitoraggio di questi valori nel tempo, Amazon RDS fornisce un insieme di procedure che effettuano una snapshot dei valori di queste variabili di stato nel tempo e li scrivono su una tabella insieme alle modifiche eseguite dall'ultima snapshot. Questa infrastruttura, denominata GoSH (Global Status History, cronologia di stato globale), viene installata in tutte le istanze database MySQL a partire dalle versioni 5.5.23. La funzione GoSH è disabilitata per impostazione predefinita.

Per abilitare la funzione GoSH dovrai prima abilitare il pianificatore di eventi da un gruppo di parametri database impostando il parametro event_scheduler su ON. Anche per le istanze database MySQL su cui è in esecuzione MySQL 5.7, imposta il parametro show_compatibility_56 su 1. Per informazioni sulla creazione e la modifica di un gruppo di parametri database, consulta Utilizzo di gruppi di parametri. Per informazioni sugli effetti collaterali dell'abilitazione di questo parametro, consulta show_compatibility_56 nel Manuale di riferimento di MySQL 5.7.

Puoi utilizzare le procedure riportate nella seguente tabella per abilitare e configurare la funzione GoSH. Connettiti innanzitutto alla tua istanza database MySQL, quindi utilizza i comandi appropriati come mostrato di seguito. Per ulteriori informazioni, consulta Connessione a un'istanza database che esegue il motore di database di MySQL. Per ogni procedura, digita:

CALL procedure-name; 

Dove procedure-name è una delle procedure nella tabella.

Procedura	Descrizione
mysql.rds_enable_gsh_collector	Abilita la funzione GoSH per acquisire le snapshot per impostazione predefinita a intervalli specificati da rds_set_gsh_collector.
mysql.rds_set_gsh_collector	Specifica l'intervallo, in minuti, tra gli snapshot. Il valore predefinito è 5.
mysql.rds_disable_gsh_collector	Disabilita gli snapshot.
mysql.rds_collect_global_status_history	Acquisisce una snapshot a richiesta.
mysql.rds_enable_gsh_rotation	Abilita la rotazione dei contenuti della tabella mysql.rds_global_status_history su mysql.rds_global_status_history_old a intervalli specificati da rds_set_gsh_rotation.
mysql.rds_set_gsh_rotation	Specifica l'intervallo, in giorni, tra le conversioni delle tabelle. Il valore predefinito è 7.
mysql.rds_disable_gsh_rotation	Disabilita la rotazione delle tabelle.
mysql.rds_rotate_global_status_history	Ruota i contenuti della tabella mysql.rds_global_status_history su mysql.rds_global_status_history_old a richiesta.

Quando la funzione GoSH è in esecuzione, puoi inviare query alle tabelle su cui viene completata la scrittura. Ad esempio, per inviare una query per il numero di riscontri del buffer pool InnoDB, dovresti inviare la seguente query:

select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" 
    from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end
    where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'