Come riparare il database MySQL

Introduzione

MySQL offre diversi modi per analizzare e riparare i database. Errori e anomalie compaiono per numerosi motivi, alcuni dei quali includono:

• Un aggiornamento o un downgrade di MySQL.
• Cambiamenti rapidi negli indici o nei dati.
• Incoerenze nella tabella.

Quando si verifica la presenza di dati danneggiati e si riparano le tabelle, i passaggi adottati per correggere gli errori dipendono da come ci si connette al database, dallo stato del server del database, dal tipo di errore e dal motore di database utilizzato.

Questo articolo mostra diversi modi per risolvere e riparare un database MySQL.

Prerequisiti

• Terminale o riga di comando con privilegi di root.
• MySQL versione 8.0 installata e configurata.
• Un database o una tabella di database danneggiati.

Come controllare la tabella MySQL per errori

L'esecuzione della diagnostica è il primo passaggio per la risoluzione dei problemi. Esistono due modi per controllare la presenza di errori in una tabella MySQL:

• Attraverso un MySQL CHECK TABLE interrogazione.
• Utilizzo del programma terminale mysqlcheck .

Controlla una tabella con CHECK TABLE

La CHECK TABLE query funziona in MySQL mentre il servizio è in esecuzione. L'uso generale è:

CHECK TABLE <table name> [, table name, table name] [option] [option];

L'uso più semplice è una tabella senza opzioni:

CHECK TABLE <table name>;

L'output della CHECK TABLE query mostra una tabella informativa sui risultati del controllo:

Le opzioni disponibili durante il controllo delle tabelle sono:

• FOR UPGRADE - scopre le incoerenze di versione. Ad esempio, MySQL 8.0 non supporta anni a due cifre, quindi una tabella contenente tali valori viene contrassegnata per l'aggiornamento.
• QUICK - non controlla le righe per collegamenti errati. Questo è utile per evitare timeout.
• QUICK - esegue un controllo sui tavoli non chiusi correttamente.
• CHANGED - controlla solo le tabelle con modifiche dall'ultimo controllo o che non sono state chiuse correttamente.
• MEDIUM - controlla le righe della tabella per verificare la validità dei collegamenti eliminati ed esegue un checksum per le righe.
• EXTENDED - esegue un controllo della completa coerenza.

Senza alcuna opzione, CHECK TABLE esegue un MEDIUM controlla su tabelle e viste MyISAM. Il FOR UPGRADE e QUICK eseguire controlli sulle tabelle e sulle viste del motore InnoDB mentre le altre opzioni vengono ignorate.

Puoi anche combinare le opzioni per un controllo più dettagliato. Ad esempio, per fare un rapido controllo per scoprire se un tavolo ha chiuso adeguatamente, esegui:

CHECK TABLE <table name> FAST QUICK;

Tutte le opzioni che non si applicano alle tabelle vengono ignorate.

Controlla una tabella con mysqlcheck

Il mysqlcheck command è la versione da riga di comando di CHECK TABLE . Il programma viene eseguito nel terminale mentre il servizio MySQL è in funzione. Per eseguire il controllo:

1. Come utente root, vai alla directory in cui sono archiviati i database:

sudo su
cd /var/lib/mysql

2. Controlla l'intero database con:

mysqlcheck <database name>

In alternativa, controlla una tabella specifica all'interno del database fornendo anche il nome della tabella:

mysqlcheck <database name> <table name>

Come riparare il database MySQL

Esistono diversi metodi per riparare un database in MySQL. Tuttavia, tieni presente che questi metodi non sono una soluzione rapida e possono causare la perdita di dati. Se le tabelle del database sono danneggiate spesso, identificare il motivo dell'occorrenza.

Prima di apportare modifiche e iniziare le riparazioni, creare una copia della directory:

cp -rfv /var/lib/mysql /var/lib/mysql_copy

In alternativa, creare una versione di backup del database prima di provare una delle soluzioni di riparazione. Segui la nostra guida:Come eseguire il backup e il ripristino di un database MySQL.

Di seguito troverai tre opzioni su come riparare un database MySQL.

Riparazione del database MySQL con la query REPAIR TABLE

Dopo aver scansionato e individuato il database e le tabelle con problemi, il modo più rapido per risolvere il problema è con la REPAIR TABLE domanda:

Se il server si arresta durante la riparazione, eseguire nuovamente la REPAIR TABLE operazione quando il server si riavvia. Assicurati di farlo prima di eseguire qualsiasi altra operazione sulla stessa tabella. In condizioni normali, una tabella MyISAM non comporterà la perdita di dati dall'utilizzo di questo metodo.

Riparazione del database MySQL con mysqlcheck

Il mysqlcheck comando è un'alternativa alla riparazione delle tabelle del database nel terminale.

1. Innanzitutto, vai alla cartella del database MySQL come utente root:

cd /var/lib/mysql

2. Aggiungi il -r opzione per mysqlcheck comando per riparare la tabella del database:

mysqlcheck -r <database> <table name>

Il mysqlcheck -r command è la versione terminale della REPAIR TABLE interrogazione.

Riparazione del database MySQL con ALTER TABLE

Usa il ALTER TABLE query per ricostruire la tabella utilizzando lo stesso motore di archiviazione. Ad esempio, se hai una tabella InnoDB, esegui:

ALTER TABLE <table name> ENGINE = InnoDB;

Per ricostruire una tabella MyISAM, usa:

ALTER TABLE <table name> ENGINE = MyISAM;

Se non sei sicuro del motore di archiviazione utilizzato dalla tabella, esegui la query seguente per scoprirlo:

SHOW CREATE TABLE <table name>;

Esecuzione di diagnostica e riparazioni specifiche del motore al database MySQL

A seconda del motore di database utilizzato, MySQL fornisce approcci specifici del motore per l'analisi e la riparazione di una tabella di database.

Le tabelle MyISAM sono soggette a danneggiamenti. Tuttavia, la maggior parte delle volte gli errori sono facili da correggere.

Quando si lavora con i database InnoDB, vengono visualizzati danneggiamenti quando i valori di checksum non corrispondono. Gli errori si verificano in genere a causa di un'interruzione di corrente o di altri problemi hardware.

Controllo e riparazione delle tabelle MyISAM con myisamchk

MySQL fornisce un controllo specifico per le tabelle MyISAM.

1. Spegnere il server MySQL. Apri il terminale ed esegui:

systemctl mysql stop

2. Come utente root, vai alla posizione del database:

cd /var/lib/mysql/<database name>

3. Per controllare una tabella specifica, eseguire:

myisamchk <table name>

4. Ripara una tabella eseguendo:

myisamchk --recover <table name>

5. Riavvia il server MySQL:

systemctl restart mysql

6. Infine, controlla la tabella in MySQL dopo il ripristino:

sudo mysql -u <user> -p <password>
USE <database name>;
SELECT * FROM <table name> LIMIT 4;

Esecuzione del processo di ripristino di InnoDB

La documentazione MySQL suggerisce di eseguire il processo di ripristino di InnoDB per i database InnoDB.

1. Individua e apri il file di configurazione globale utilizzando il tuo editor di testo preferito. Per i sistemi Ubuntu e Debian, la posizione è:

cd /etc/mysql/my.cnf

2. Aggiungi la seguente opzione a my.cnf file:

[mysqld]
innodb_force_recovery=4

Per impostazione predefinita, l'opzione di ripristino forzato è impostata su 0 (disattivata) e sale a 6. Ogni nuovo livello include le funzionalità di ripristino dei numeri inferiori.

3. Salva e chiudi il file.

4. Riavvia il server MySQL per assicurarti che il nuovo file di configurazione si applichi:

systemctl restart mysql

5. Nella cartella home, esporta tutti i database in un file di backup con mysqldump comando:

sudo mysqldump --all-databases --add-drop-database --add-drop-table --routines > <file name>.sql

6. Avviare il client MySQL ed eliminare tutte le tabelle del database interessate. Per eliminare una tabella del database, prova:

DROP TABLE IF EXISTS <table name>;

Se la tabella non viene eliminata, provare a rimuovere manualmente la tabella del database. Accedi come utente root e vai alla directory in cui si trovano i database:

sudo su

cd /var/lib/mysql/<database name>

Rimuovere la tabella del database:

rm -r <table name>

7. Arrestare il server MySQL:

systemctl stop mysql

8. In my.cnf file, commenta innodb_force_recovery opzione aggiunta nel passaggio 2 per disabilitare la modalità di ripristino. Quindi, salva il file e chiudi.

9. Riavvia il server MySQL:

systemctl start mysql

10. Ripristinare il database dal backup .sql file nella shell MySQL. Accedi al client MySQL:

sudo mysql -u <username> -p <password>

11. Eseguire la seguente query per importare i database:

USE <database name>;
# Turning off auto commit is optional and speeds things up for larger databases
SET autocommit=0; 
SOURCE <file name>.sql;

12. Infine, testa il database per verificare che tutto funzioni correttamente. In caso contrario, ripristina il database dal backup e prova un metodo alternativo.