Esportare ed importare un database Mysql grande

L'altro giorno ho avuto la necessità di migrare un database mysql da un server ad un altro.
Normalmente, se i dati non sono troppi, è possibile compiere questa operazione esportando ed importando dei dump da phpmyadmin, se però il database è grosso diversi giga e contiene file blob per gli allegati, allora questa procedura non è utilizzabile.
Per fortuna però, è possibile esportare una copia esatta del database da riga di comando, salvando già direttamente il file sul nuovo server.
Vediamo subito come fare.

Come esportare un database Mysql grande da riga di comando

Per poter clonare un database mysql, facendo un dump puntuale dei dati, basta seguire i seguenti passaggi:

1. Aprire il prompt dei comandi di dos (se si è su windows)
2. Posizionarsi nel percorso in cui è presente l'eseguibile di mysql con il comando:
   cd C:\wamp64\bin\mysql\mysql8.0.16\bin
   Dove ovviamente va indicato il path del proprio mysql
3. Digitare il seguente comando e premere invio:
   mysqldump -u nome-utente -p nome-db > C:\percorso\file.sql 

Verrà chiesto di inserire la password dell'utente sopra indicato, e dopo aver premuto invio occorrerà aspettare pazientemente che l'operazione vada a termine, anche se a video apparentemente non succede niente.
Quando la copia del database mysql sarà terminata, il cmd restituirà semplicemente la possibilità di digitare altri comandi, senza un vero e proprio messaggio di conferma copia avvenuta, se però andrete a vedere nel percorso indicato, troverete il file con estensione .sql contenente la copia del database mysql.

Come importare un database Mysql grande da riga di comando

Per importare un database mysql grosso, dopo averlo esportato con la comoda funzione mysqldump sarà necessario importarlo nel nuovo server con il seguente comando:

mysqldump -u nome-utente -p test > C:\percorso\file.sql

Come nel caso dell'import, il prompt dei comandi risulterà bloccato fino a che l'operazione non sarà terminata (e a seconda del peso potrebbero volerci anche molti minuti), ma una volta finito, il database mysql sul nuovo server sarà identico a quello del server di provenienza e potrete sfogliarlo e fare le verifiche del caso con phpmyadmin.

Questa era la procedura corretta per duplicare un database mysql pesante.
Non provate invece a copiare brutalmente i file contenuti nella cartella data di mysql, perchè la cosa potrebbe non funzionare correttamente anche se prima stoppate mysql.

Da ora in poi l'export e l'import di database mysql grandi non sarà più un problema, basta solo avere una buona banda ed un po' di pazienza.

Rifiuto persistente del computer di destinazione (Mysql)

L'altro giorno, dopo aver reinstallato da capo wamp su un nuovo computer sono incappato in un problema relativo a mysql che non mi era mai capitato prima.
Praticamente, nonostante la mia app php fosse rimasta invariata e nonostante avessi installato e configurato correttamente il database, il sito a cui cercavo di accedere mi dava il seguente errore:
Warning: mysqli::__construct(): (HY000/2002): Impossibile stabilire la connessione. Rifiuto persistente del computer di destinazione.

Dopo aver cercato un po' online, ho trovato la soluzione a questo errore di connessione a mysql, ed ora lo condividerò in questo breve post per i posteri.

Come risolvere l'errore mysql su wamp "Rifiuto persistente del computer di destinazione"

Per chi non lo sapesse, wamp sta per windows apache mysql e php, ed il problema riscontrato che generava questo fatidico messaggio di impossibilità di connessione al database, era legato proprio ad uno di questi strumenti.

In prima battuta, e lo scrivo per i posteri perchè potrebbe essere un metodo di risoluzione per l'errore mysql "Rifiuto persistente del computer di destinazione", ho provato a seguire la seguente procedura:

1. Stoppare mysql (banalmente, tasto sinistro del mouse sull'icona di wamp, poi stop all services)
2. Andare nella cartella C:\wamp64\bin\mysql\mysql8.0.18\data (dove il percorso ovviamente cambia in base a dove avete installato wamp ed alla versione di mysql) e cancellare i file (prima farne un backup di sicurezza): auto.cnf, ib_logfile0 e ib_logfile1.
3. Riavviare wamp (tasto sinistro restart all services)

Questa soluzione risolve la problematica tipica legata a questo genere d'errore.

Nel mio caso però, il problema era un altro, dato che con phpmyadmin la connessione al db funzionava mentre con il mio script php no.
L'indizio principale lo dava la latenza prima dell'errore, che in questo caso può stare a significare che la porta usata da mysql era bloccata dal firewall (nel mio caso windows firewall).

Quindi, per risolvere il terribile errore "Rifiuto persistente del computer di destinazione" ci sono due strade.
La prima consiste nell'aprire la porta usata da mysql (3306 o 3308) andando ad agire sulle regole del firewall.
La seconda invece è usare una porta aperta, ovvero nel mio caso, cambiare la porta usata da mysql da 3306 a 3308.

Come faccio a sapere che la porta 3308 va e la 3306 no? Semplice, come dicevo poco sopra, phpmyadmin funzionava, mentre il mio script php no, ed andando a vedere le impostazioni del primo strumento ho scoperto che questo usava proprio la 3308, mentre il php usava di default la 3306.

Ora quindi, o si risolve agendo direttamente sui file di configurazione di mysql e del php.ini di wamp, oppure più banalmente tramite script php, con una sintassi del genere:

$mysqli=new mysqli($server.":".$porta,$user,$pwd,$db);

Dove, rispetto al solito, si va ad aggiungere la porta (in questo caso la 3308) dopo l'indirizzo del server, con i canonici due punti davanti.
Tutto qui, fatta questa modifica sono riuscito di nuovo a far funzionare la connessione a mysql dal mio script php sotto windows wamp, e quindi niente più rifiuto persistente.

Qui di seguito un video tutorial di quanto appena spiegato:
https://youtu.be/x4fXhSVxhSU

Come aggiornare Mysql su Wamp

Se avete installato wamp da un po' di tempo, probabilmente alcuni prodotti risulteranno un po' datati e potrebbe essere necessario un aggiornamento.
Tra le operazioni più delicate da fare in questo caso, c'è proprio l'aggiornamento di mysql, ovvero del database che contiene tutte le tabelle con i dati di tutti i siti web presenti sulla macchina wamp.

Questa delicata operazione però, fortunatamente è abbastanza semplice da fare, a patto che non si esageri troppo nel passaggio da una versione ad un'altra.
Nella mia ultima esperienza ad esempio, sono passato da mysql versione 5.7.14 alla versione 8.0.16, un bel salto in effetti, ma per fortuna fino alla 7.x la cosa risulta ancora fattibile.
Quando si compiono queste operazioni però, è tassativo poi controllare tutto il codice php, le viste e le varie query sql, in modo tale da non trovarsi davanti a qualche funzione o sintassi deprecata.

Vediamo dunque come compiere questo aggiornamento in maniera safe.

Come aggiornare Mysql su Wamp

Per poter aggiornare mysql su wamp bisogna seguire i seguenti passaggi:

1. Scaricare l'ultima versione di mysql dal sito ufficiale (https://www.mysql.com/it/), in formato zip
2. Estrarre lo zip appena scaricato nella cartella c:/wamp/bin/mysql/mysqlX.x.x (o wamp64, insomma il percorso dove avete installato wamp)
3. Rinominare la cartella dell'ultima versione in modo tale che rispetti la sintassi utilizzata nella versione precedente
4. Stoppare wamp in modo tale che nessuno possa scrivere sul database
5. Entrare nel prompt di dos ed andando nella cartella bin dove c'è mysql digitare il comando: mysqld.exe --initialize-insecure
   Questo genererà la giusta cartella data nel nuovo mysql
6. Copiare le cartelle con i nomi dei database che si vuole migrare presenti nella cartella data dell'ultima versione attiva di mysql, nella cartella del nuovo mysql
7. Copiare il file my.ini presente nell'ultima versione attiva di mysql dentro la cartella del nuovo
8. Cercare dentro il file my.ini tutte le occorrenze alla vecchia versione e rinominarle con il percorso della nuova, come ad esempio: c:/wamp64/bin/mysql/mysql8.0.16/data
9. Copiare il file wampserver.conf presente nell'ultima versione attiva di mysql dentro la cartella del nuovo
10. Verificare che i percorsi che sono presenti dentro il file wampserver.conf siano corretti per il nuovo mysql
11. Riavviate wamp 
12. Cliccare con il tasto sinistro sull'icona di wamp, poi cliccare su mysql e poi su version, e selezionare l'ultima versione appena installata

E' tutto.
Se wamp non dovesse ripartire al primo colpo, provate un paio di riavvii, e se finalmente diventerà verde la sua icona, allora avrete aggiornato il mysql correttamente e non vi resterà altro che iniziare a testare tutte le vostre applicazioni.

Nel caso anche a voi venga fuori l'errore "Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.x", che di fatto vi impedirà di far partire il nuovo mysql, dovrete svuotare i file di log presenti nella cartella data di mysql (ib_logfile0 e ib_logfile1).
Potete provare a cancellare questi file a mano, che di fatto vi siete portati dietro dalla precedente versione, e vedere se al rilancio di mysql tutto parte senza alcun problema.

MYSQL: cancellare record su più tabelle con i trigger

Tempo fa avevo spiegato come utilizzare i trigger in mysql, sia da codice sql, sia utilizzando l'interfaccia grafica di phpmyadmin.
Oggi invece vediamo un esempio concreto su una delle casistiche più ricorrenti, il delete after delete.

Quando capita di dover cancellare i record presenti in delle tabelle figlie, soprattutto se si sta sviluppando una nuova applicazione e si fanno molte prove, a volte può risultare scomodo andare ad eliminare a mano tutti i record di sottotabelle per evitare di lasciare righe orfane nel database mysql.
Ed è proprio in questi casi che torna utile saper utilizzare al meglio i trigger mysql.

Vediamo subito con un esempio pratico come ripulire in automatico il nostro database.

Come cancellare automaticamente record su più tabelle con i trigger di MYSQL

La sintassi sql da utilizzare per rimuovere automaticamente da una tabella mysql tutti i record legati ad una tabella non più esistente è la seguente:

CREATE TRIGGER `nome-trigger` AFTER DELETE ON `tabella-padre` 
FOR EACH ROW 
DELETE FROM tabella-figlia WHERE tabella-figlia.id-chiave=old.id;

La sintassi sopra indicata si spiega da sola, ma vediamo comunque di andare ad analizzare cosa ho appena scritto.
Il comando sql CREATE TRIGGER serve ovviamente a creare il nostro trigger, al quale bisogna dare un nome, bisogna indicare un'azione (in questo caso dopo l'operazione di cancellazione, ovvero AFTER DELETE), e bisogna dire qual'è la tabella dalla quale parte il comando.

C'è poi una sintassi che serve per indicare che l'operazione sql che andremo a fare sarà effettuata su tutte le righe della tabella bersaglio, ovvero: FOR EACH ROW.

Infine basta indicare la sintassi mysql desiderata per compiere l'azione, in questo caso la DELETE, mettendo nel WHERE un legame tra un id chiave della tabella bersaglio, con l'id della tabella padre, indicata con la sintassi old.

Tutto molto semplice no?
Da ora in poi quindi, cancellando una riga della tabella padre, verranno rimosse in automatico dal database tutte le righe della tabella figlia che rispondono ai requisiti indicati nel nostro trigger.

Importare un dump mysql da riga di comando

Oggi mi sono trovato a dover superare un antipatico problema durante l'importazione di un file dump su un database mysql.
Praticamente avevo questo file sql contenente tutte le tabelle ed i dati del mio nuovo database mysql, ma quando andavo ad importare tramite la comodissima funzionalità del phpmyadmin mi dava il seguente errore: "Stai probabilmente cercando di caricare sul server un file troppo grande" (You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit).
Vediamo subito come risolvere questo problema con un metodo alternativo.

Come importare un dump mysql da riga di comando

La prima cosa che si fa in questi casi, quando si ha questo genere d'errore, è di andare ad aumentare la dimensione massima dei file caricabili tramite php, nel php.ini per l'appunto.
Tuttavia, stanco di andare a dover modificare parametri di configurazione solo per gestire delle eccezioni, ho deciso di risolvere questo problema di file troppo grossi per essere importati in mysql, scegliendo un altro approccio.
Ho infatti scelto di provare a bypassare questo problema passando da riga di comando, tramite prompt dei comandi di dos (si, siamo sotto windows).

Ecco dunque cosa bisogna fare per superare i limiti delle configurazioni del php o del phpmyadmin:

1. Aprire il prompt di dos
2. digitare:  mysql -u username -p nomedatabase < dump.sql
3. Premere Invio

Ovviamente, occorre sostituire username con il nome di un utente in grado di compiere queste operazioni (idealmente un amministratore), mettere il giusto nome del database bersaglio (da creare quindi prima) ed inserire il path completo del file dump contenente tutte le query d'importazione dati.

Grazie a questo semplice script dos, che lancia direttamente il comando mysql (bisogna dunque essere posizionati nel giusto percorso del binario di mysql, o averlo mappato come variabile d'ambiente) passandogli già tutti i parametri per la connessione e per fare l'operazione, sarà possibile importare in un colpo solo e anche molto velocemente un intero database mysql compreso di tabelle e viste.

Tra l'altro ho notato che questo metodo di import sql da linea di comando sembra essere più veloce dello script usato dalla pagina del phpmyadmin, e questo non può che non essere una cosa positiva.

Ottenere elenco tabelle mysql

Dopo aver imparato come scoprire l'elenco dei database presenti, e come selezionarne uno, è arrivato il momento di apprendere come ottenere l'elenco delle tabelle in mysql.

Se infatti non si dispone di una comoda interfaccia grafica in stile phpmyadmin ma si deve operare solo da riga di comando, è giusto conoscere la sintassi necessaria per ottenere la lista di tabelle presenti all'interno di un database.
Va da se che per poter avere una lista di tabelle di un db, bisogna prima entrare nel db desiderato, con il metodo linkato poco sopra.

Bene, andiamo subito sul pratico e vediamo come compiere questa semplice operazione in sql.

Come ottenere l'elenco delle tabelle di un database mysql

Dopo essere entrati nel nostro database, se per caso non ci ricordiamo il nome esatto della tabella mysql che vogliamo andare ad interrogare, ecco il comando necessario per ricavare la lista delle tabelle presenti in un db:

show tables;

Con questo semplice comando seguito da punto e virgola ed un bell'invio, potremo vedere subito a video tutte le tabelle esistenti in un determinato database mysql.

Non penso ci sia molto altro da dire sull'argomento.
Ora si hanno infatti tutte le informazioni necessarie per poter iniziare a fare le prime query sulle tabelle, che siano di inserimento, modifica, o anche sola lettura.

Come cambiare database in mysql

Oggi vediamo un comando base per chi si avvicina per la prima volta nel fantastico mondo dei database relazionali come mysql.
E si, perchè prima di poter fare query complesse, selezionare dati, inserire nuove righe in tabelle o eliminarle, che cosa occorrerà mai fare?
E' semplice ed ovvio, bisogna sempre prima selezionare il database mysql corretto in cui sono contenute le informazioni che vogliamo andare a guardare o manipolare.
Vediamo subito come fare.

Come cambiare database in mysql

Dopo aver fatto accesso al nostro server mysql, magari tramite console, dopo aver messo dunque user e password, finiremo di default dentro un database predefinito.
Se però vogliamo cambiare database mysql, occorre conoscere il comando da digitare.

Per fortuna è tutto molto semplice anche se si vuole fare tutto da riga di comando, ecco la sintassi da usare:

use nome-database;

Il punto e virgola come al solito segna la fine dei comandi sql, e quindi dopo un bel invio si potrà subito ottenere il risultato desiderato, ovvero iniziare ad utilizzare il db appena selezionato.

A video vedrai un bel messaggio con scritto "database changed".
Tutto qui.

Come come? Non ti ricordi il nome esatto del database che vuoi utilizzare?
Nessun problema, leggi questo post.