MySQL Replication

La funzionalita' di replica dati di MySQL e' semplice da configurare, non richiede praticamente manutenzione, e' molto flessibile e non aggiunge carico al database Master. E' percio' comprensibile che sia molto utilizzata. Gli impieghi sono diversi: per disporre di copie di database in HA (High Availability: alta affidabilita'), per creare una batteria di database per siti web molto acceduti, per semplificare modalita' e procedure di backup, ...

Nel seguito sono riportate alcune informazioni di interesse organizzate in paragrafi specifici: Introduzione, Configurazione, Architettura, Amministrazione (FAQ), Avvertenze per l'uso, Nuove funzionalita' (Stored Routine (5.0), Row Based Replication (5.1), Semi-synchronous (5.5), Crash safe, delayed, GTID, ... (5.6), Online CHANGE MASTER, Multi-source, ... (5.7), Group Replication, InnoDB Cluster, ... (8.0) ), Backup da Slave, Point in Time Recovery.

Introduzione

La replicazione su MySQL e' statement based ed asincrona.

Il Master si occupa di registrare su file (bin-log file) tutti gli statement che vengono eseguiti sulla base dati e che operano una qualche modifica ai dati (DML e DDL).
Lo Slave si collega con un thread al Master, raccoglie il contenuto dei bin-log, lo trasferisce in locale e quindi si occupa di applicarlo alla base dati.
La parte svolta dal Master e' molto semplice, poco onerosa e "stupida". In pratica ospita una sessione client per ogni Slave configurato. La maggioranza delle opzioni di configurazione e la gestione ricadono sullo Slave. E' questo che si occupa della ricezione dei log, del loro allienamento e della corretta applicazione degli statement SQL sulla base dati.

Sono possibili differenti configurazioni. Un Master puo' servire piu' Slave. Uno Slave puo' essere a sua volta Master di altri Slave. Possono essere esclusi/inclusi dalla replicazione database, tabelle, ... Il Master e gli Slave possono essere differenti praticamente in tutto: Storage Engine, parametri di configurazione/tuning, versioni, struttura, ... insomma: continuate a leggere!

Configurazione

La configurazione della replication e' piuttosto semplice anche se le possibilita' sono molteplici. La documentazione completa e' presente nel sito ufficiale, quindi nel seguito riportiamo solo un esempio di configurazione molto semplice... ma che copre tutti i punti fondamentali!

Attivita' sul Master Attivita' sullo SlaveNote
[mysqld]
server-id=10
log-bin=mysql-bin
 La configurazione del Master e' semplice: basta definire un server-id univoco ed indicare il nome dei file di log binario. Se l'utilizzo del database e' significativo e' opportuno porre il binlog su un file system separato per evitare un file system full. Altri parametri utili sono riportati nel seguito.
  [mysqld]
server-id=20
read_only=1
Anche la configurazione dello Slave e' semplice: server-id univoco e, se non si vuole che vengano modificati i dati sulla replica, parametro read_only. Altri parametri utili sono riportati nel seguito.
grant replication slave on *.* to 'rep'@'%' identified by 'xyz';  Non e' obbligatorio definire un nuovo utente ma e' consigliato sia per sicurezza che per semplicita' di configurazione e controllo
BackupRestore Le modalita' backup/restore sono molteplici e dipendono da molti fattori. Nel caso piu' semplice le basi dati sono vuote e non c'e' nulla da fare! Se non avvengono modifiche sul Master un semplice mysqldump e' sufficiente... Altrimenti e' importante fare in modo che sia garantito l'allineamento delle basi dati (eg. FLUSH TABLES WITH READ LOCK; e backup oppure mysqldump --all-databases --single-transaction --triggers --routines --events --master-data).
show master status;  Con questo comando si ottiene lo stato della replicazione. In particolare sono necessari il nome del file ed il progressivo che servono per configurare la replicazione sullo Slave. Il comando deve essere lanciato prima di modificare i dati sul Master (UNLOCK TABLES).
  change master to master_host='myMaster.MyDomain.it', master_user='rep', master_port=3306, master_password='xyz', master_log_file='mysql-bin.000001', master_log_pos=69; Con questo comando lo Slave sa come connettersi al Master. Vengono creati i file master.info e relay-log.info. I parametri in italico sono quelli ottenuti con il comando show master status.
 start slave; Per far partire i thread dello Slave. Sono utilizzati un thread remoto (sul Master per inviare il bin-log) e due thread locali (per ricevere il bin-log ed applicarlo).
 show slave status\G Gia' fatto!
Non c'e' altro da configurare, con questo comando si controlla lo stato della replicazione.

I passi riportati e le spiegazioni sono un po' semplificati ma... funzionano e rendono l'idea (spero ;-) E' ovvio che per far leggere i nuovi parametri del my.cnf e' necessario riavviare il server, che per lanciare uno statement SQL e' necessario collegarsi alla base dati...
E' inoltre fondamentale controllare l'utilizzo di spazio da parte dei binlog: possono crescere molto piu' in fretta della base dati. A seconda delle configurazioni utilizzate dovra' essere impostata una pulizia periodica dei binlog obsoleti.

Ulteriori parametri di configurazione

La configurazione vista nel paragrafo precedente e' gia' perfettamente funzionante e richiede l'impostazione di due soli parametri:server-id log-bin. E' pero' possibile configurare ulteriori parametri che forniscono ulteriori funzionalita' alla replicazione MySQL.

I database da replicare (di default vengono tutti replicati) si indicano con il parametro binlog-do-db sul Master o il parametro replicate-do-db sullo Slave. I database da non replicare si indicano con il parametro binlog-ignore-db sul Master o il parametro replicate-ignore-db sullo Slave. Per indicare singole tabelle si utilizzano i parametri replicate-do-table e binlog-do-table che possono utilizzare wildcard.

I parametri di configurazione utilizzabili, come abbiamo visto, sono molteplici... Per riassumere riportiamo una configurazione adatta ad una semplice architettura in cui sono presenti un master ed uno slave configurati in replicazione per HA:

Parametri Master Parametri Slave
[mysqld]
server-id=10
log-bin=mysql-bin
binlog-ignore-db=test
[mysqld]
server-id=20
log-bin=mysql-bin
binlog-ignore-db=test
read_only=1

Per essere sicuri che sullo slave non avvengano accessi e' stato utilizzato il parametro read_only=1 che bisogna aver cura di rimuovere quando lo Slave diventa Master (evento chiamato promote).

Se le autorizzazioni sono differenti (eg. su uno slave utilizzato solo per backup) e' possibile evitare gli errori sugli utenti mancanti con slave_skip_errors=1396. Se i server sono ospitati su un cluster active-passive o comunque vi e' la possibilita' che l'hostname venga cambiato e' opportuno fissare i nomi del file di relay e del relativo indice con i parametri relay-log=relay-bin relay-log-index=relay-bin.index.
Nelle configurazioni su basi dati modificate in modo pesante e' necessario un opportuno dimesionamento dei file systems eventualmente dedicando un file system separato per i binlog. Con il parametro expire_logs_days si definisce la retention dei binlog sul Master. Utili sono anche i parametri max_binlog_size per definire la dimensione massima dei binlog sul Master, max-relay-log-size e relay-log-space-limit per limitare rispettivamente la dimensione dei relay-log e lo spazio totale occupato sullo Slave.

Il parametro log_bin_trust_function_creators=1 rilassa le condizioni per la creazione di stored procedure quando il bin-log e' abilitato.

Per garantire una gestione ACID dei dati e garantire la completa replicazione anche in caso di crash vanno impostati i parametri: innodb_flush_log_at_trx_commit=1 sync_binlog=1 [NdE innodb-safe-binlog se < 5.0.3 ed anche innodb_flush_log_at_timeout se > 5.6.6] ... che rendono significativamente piu' lenti MySQL e la replicazione quando e' presente uno storage con alta latenza e bassi IOP!
Nei casi in cui non sia presente uno stringente requisito di Durability e' possibile migliorare signficativamente le prestazioni su sistemi in replica con un numero elevato di transazioni con l'impostazione dei parametri: innodb_flush_log_at_trx_commit=2 sync_binlog=0

Architettura

La replicazione utilizza alcuni processi e file di appoggio. Il processo mysqld sul Master salva sul file bin-log gli statement di DML e DDL che sono eseguiti sulla base dati. Sul file bin-log.index e' mantenuto l'elenco dei bin-log attivi sul master.
Per mantenere allineati i dati lo Slave utilizza piu' thread. Il primo e' una connessione remota al Master ed ha il compito di raccogliere i dati dal bin-log (BinLog Dump) ed e' sempre attivo. Gli altri thread sono locali ed hanno il compito di ricevere il contenuto del bin-log (Slave I/O) sui relay-log e di applicarlo alla base dati (Slave SQL). In caso d'errore nell'inserimento dei dati il thread Slave SQL si interrompe mentre lo Slave I/O continua a raccogliere i dati dal Master. Con show slave status\G si ottiene l'indicazione dell'errore occorso; una volta corretto il problema la replicazione riprende dal punto in cui si era interrotta applicando il relay-log.

Architettura MySQL Replication

La replicazione in MySQL e' molto flessibile e sono possibili diverse alternative.
La replicazione puo' essere eseguita solo su alcuni database o su specifiche tabelle. Quali database replicare si indicano con il parametro replicate-do-db (utilizza la USE) sullo Slave o con il parametro binlog-do-db sul Master. Per replicare singole tabelle si utilizza il parametro replicate-do-table (che puo' utilizzare wildcard). E' anche possibile indicare quali DB o TABLE non replicare o applicare con parametri analoghi in cui il do e' sostituito con ignore (eg. replicate-ignore-table).

Molto utili sono anche: replicate-ignore-db=mysql, binlog-ignore-db=test e slave_skip_errors=1396 per non replicare gli accessi al database delle autorizzazioni o di test e proseguire nella replicazione anche in caso di errori sulle utenze.
In alcuni casi particolari e' necessario proseguire nella replica, impostando slave_skip_errors, se sono presenti diverse tipologie di errori. La tabella seguente riporta i codici degli errori piu' comunemente mascherati:

Error#SymbolMessage
1032 ER_KEY_NOT_FOUND Can't find record in '%s'
1054 ER_BAD_FIELD_ERROR Unknown column '%s' in '%s'
1062 ER_DUP_ENTRY Duplicate entry '%s' for key %d
1136 ER_WRONG_VALUE_COUNT_ON_ROW Column count doesn't match value count at row %ld
1396 ER_CANNOT_USER Operation %s failed for %s
1452 ER_NO_REFERENCED_ROW_2 Cannot add or update a child row: a foreign key constraint fails (%s)

Il nome dei relay log sullo Slave e' basato sull'hostname, se l'hostname e' soggetto a cambiamenti (eg. failover cluster) la replicazione si blocca; e' possibile impostare il nome dei relay log con i parametri relay-log e relay-log-index.
E' semplice comprendere in quale modo agiscono i parametri considerando l'architettura utilizzata da MySQL. Con i parametri binlog-* si governa quali comandi SQL vengono scritti o meno sul file bin-log da parte del Master. Con i parametri replicate-* si indica quali comandi SQL vengono applicati dallo Slave.

Gli Engine utilizzati sullo Slave possono essere diversi da quelli utilizzati sul Master. Questo consente di scegliere l'Engine piu' adatto allo scopo (eg InnoDB sul Master in cui avvengono le transazioni, MyISAM sullo slave su cui vengono effettuate selezioni complesse o backup).

MySQL Cascading Replication Nel caso piu' semplice un Master viene replicato su uno Slave. Se gli accessi in lettura al database sono molto elevati (eg. siti web molto acceduti) e' possibile configurare piu' Slave server e far connettere le sessioni applicative in lettura sugli Slave. Per replicare un Master su piu' Slave non e' necessario alcun cambiamento alla procedura indicata all'inizio: basta applicare i comandi previsti per lo Slave su piu' server!
Uno Slave puo' comportarsi a sua volta da Master e questo puo' essere utilizzato in cascata piu' volte. In particolare, se il numero di Slave che si vuole mantenere allineati e' molto elevato, si utilizza la catena A -> B => Cn dove n e' puo' essere grande a piacere. A e B vanno configurati come Master, su B deve essere definito il parametro log-slave-updates [NdA ed ovviamente il parametro log-bin], B e Cn vanno configurati come Slave di A e B rispettivamente. Il server B puo' essere utilizzato dalle applicazioni in lettura oppure puo' essere dedicato solo alla replica. In questo ultimo caso si utilizza tipicamente l'Engine Blackhole per non utilizzare spazio in locale [NdA naturalmente B deve comunque poter mantenere i bin-log sufficienti per la gestione della replica, quindi serve comunque dello spazio disco] e non avere l'overhead del caricamento dei dati.
In questa configurazione tutte le modifiche vengono applicate sulla base dati A. I log vengono trasferiti sul sistema B con il minimo impatto su A poiche' vi e' un solo server ad accedere ai dati. Poiche' B utilizza l'Engine Blackhole non vi e' alcun rallentamento ed il sistema e' dedicato ad ospitare i demoni dei diversi server C utilizzati in lettura. Una descrizione piu' completa di questa configurazione si trova in questo documento.

E' possibile configurare due nodi in modo che ciascuno agisca da Slave dell'altro (two-way replication); tuttavia la replica di MySQL non ha alcun meccanismo di locking distribuito o di risoluzione delle contese: in caso di modifica degli stessi dati la replica va in errore e si blocca. La replica multi-master va quindi adottata solo in casi molto particolari in cui e' presente un robusto meccanismo di routing degli accessi nell'applicazione client [NdA personalmente la sconsiglio vivamente].

Come abbiamo visto utilizzando la replicazione e' possibile costruire complesse architetture di database MySQL che soddisfano ai piu' stringenti requisiti di alta affidabilita', continuita' di servizio, prestazioni, trasparenza applicativa, scalabilita', ... La figura che segue mostra un'architettura con un cluster Active/Passive per la base dati Master e una batteria di Slave e spero sia abbastanza chiara:

MySQL Massive Replication

FAQ Amministrazione

La replicazione su MySQL richiede un impegno molto limitato da parte del DBA. Ma qualcosa bisogna comunque fare...
Cosa si deve controllare quando e' attiva la replicazione e come e' possibile agire quando si verificano dei problemi? In questo capitolo vengono riportate le indicazioni relative ai casi piu' comuni.

Avvertenze per l'uso

Anche se molto potente, la replicazione di MySQL ha alcuni limiti che non consentono di replicare tutto in tutti i casi... come nei bugiardini delle medicine i rischi ed i limiti si trovano scritti in piccolo nelle avvertenze per l'uso (eg. System Functions, Triggers).

IMPORTANTE: non fate scoppiare il file system del bin log!
Se le modifiche su una base dati sono significative e' opportuno utilizzare un file system separato per i bin-log, in caso d'errore almeno il DB continua a funzionare...

Alcuni comandi e funzioni, per le funzionalita' particolari che implementano, non vengono sempre replicati correttamente. Ad esempio: LOAD_FILE() UUID() USER() FOUND_ROWS() SYSDATE() GET_LOCK() RAND() VERSION() ... [NdE in realta' con il MIXED MODE alcune funzioni vengono trattate correttamente nelle ultime versioni ma e' comunque opportuno evitarle]
Alcune sintassi DML non sono deterministiche e quindi vanno evitate con la replication. La INSERT...SELECT o la DELETE...LIMIT debbono utilizzare la clausola ORDER BY per essere deterministiche.
L'utilizzo di Engine differenti all'interno della stessa transazione puo' generare problemi con la replicazione... in realta' puo' generare problemi anche senza la replica!
Alcune applicazioni sono piu' replicabili di altre: dipende dall'uso dell'SQL. Se la vostra applicazione utilizza Stored Routine, con la gestione di SIGNAL, scatena triggers, che agiscono su colonne in autoincrement, con tabelle partizionate, ... e' meglio controllarla con attenzione!
E' possibile utilizzare versioni differenti, Engine differenti e (non ricordo da quando ma si puo') strutture dati differenti tra Master e Slave... Ma pensateci bene prima di crearvi dei problemi da soli!
Sebbene siano perfettamente replicabili modifiche massive di dati generalmente non e' conveniente effettuare variazioni pesanti sul Master. Queste infatti introducono un lag significativo nella replicazione. Le alternative sono diverse: ad esempio spezzare le istruzioni massive in piu' statemente SQL oppure disabilitare la replicazione con SET SQL_BIN_LOG=0 ed effettuare allineamenti manuali...

Quando si sviluppa un'applicazione che deve operare con una configurazione di MySQL in replica, oltre a tenere conto dei limiti cui abbiamo accennato (eg. non usare la RAND()) e' possibile sfruttarne le caratteristiche per ottenere migliori prestazioni ed una maggiore scalabilita': basta utilizzare connessioni differenti per le scritture e le letture. In questo modo e' possibili limitare il carico sul master alle sole scritture distribuendo le attivita' di lettura sugli slave.

Vi sono differenze significative tra le versioni di MySQL (ed i fork ;-) sia nelle funzionalita' (eg. ROW BASED) che nell'elenco di Bug presenti e risolti. Meglio convergere su una versione recente, consolidata ed allineata per tutti i nodi che partecipano alla replication.

Nuove funzionalita'

La replicazione su MySQL e' disponibile dalla versione 3.23.15 (2000) e si e' dimostrata da subito veloce, semplice da configurare/amministrare e di basso impatto sulle prestazioni.
Le diverse nuove funzionalita' inserite nel tempo a livello di database hanno richesto, in qualche caso, un aggiornamento della parte di replicazione. Gli aggiornamenti piu' importanti sono riportati nei paragrafi seguenti.

Stored Routines (5.0)

Per le Stored Routines (disponibili dalla 5.0) un'importante considerazione e' relativa all'utilizzo del binary-log necessario per la replication, ma anche per il recovery point-in-time. Per rendere replicabili le azioni svolte dalle stored routine e' utilizzata una sintassi specifica per indicare le modifiche svolte sui dati. Infatti sul binary log viene riportata la stored routine richiamata ed il risultato di questa deve essere replicabile per poter riapplicare il log. Per tale ragione e' importante dichiarare se il comportamento della stored routine e' deterministico (ovvero si ripete sempre uguale a fronte degli stessi dati) e se vengono modificati dati.

CREATE PROCEDURE test1
...
DETERMINISTIC 
MODIFIES SQL DATA
...
BEGIN 
...
Poiche' il thread di replicazione gira in stato privilegiato sugli Slave e' richiesto il privilegio SUPER oppure l'impostazione del parametro --log-bin-trust-routine-creators.

Row based replication (5.1)

La replicazione su MySQL e' storicamente Statement Based. Questo la rende particolarmente efficiente ma, in qualche caso, vi sono dei limiti. Ad esempio nell'utilizzo di trigger e stored routine, con statement SQL che utilizzano valori random, con alcuni comandi quali LOAD_FILE(), SYSDATE, con le UDF (user defined functions), ... Per questo e' stata introdotta la possibilita' di replicare i dati anche con un meccanismo Row Based.
Dalla versione 5.1 (5.1.8 per essere precisi) e' possibile utilizzare tre differenti modalita' per registrare sul binary log (binlog_format):

La modalita' di logging e' configurata nel file my.cnf ma, dalla release 5.1, puo' essere modificata dinamicamente a livello di server o di singola sessione. Analogamente avviene per molti altri parametri globali... tra questi anche l'impostazione read_only spesso utilizzata sugli slave.

Tipicamente la modalita' piu' efficiente ed affidabile e' STATEMENT [NdE in questa specifica versione MySQL]. La modalita' ROW e' utile per replicare correttamente stored procedures e trigger non deterministici.

Semi-synchronous replication (5.5)

La replicazione su MySQL e' asincrona, e' quindi possibile che, per causa di un fault, le ultime transazioni eseguite sul Master non siano ancora state replicate sugli Slave. La conseguenza e' un'effettiva perdita di dati fino all'ultima transazione trasmessa agli Slave.

Dalla versione 5.5 e' possibile impostare la replicazione semi-sincrona nella quale il Master, prima di effettuare il commit locale, attende che almeno uno slave abbia ricevuto i dati della transazione. In questo modo almeno uno Slave ha ricevuto l'ultima transazione committata e quindi non si possono verificare perdite di dati.
Sebbene si tratti di un protocollo piu' semplice e veloce rispetto al Two Phase Commit, le transazioni sono comunque rallentate. Per impostare la replicazione semi-sincrona e' quindi importante che la latenza nella comunicazione tra Master e Slave sia molto bassa. Le figure seguenti riportano le differenze in termini di scambio di messaggi tra replica asincrona e semi-sincrona:

MySQL Replication Life Lines MySQL Semi-Synchronous Replication Life Lines

La replicazione semi-sincrona e' implementata mediante due plug-in (uno per il master ed il secondo per gli slave). Nel caso in cui nessuno Slave abbia il plug-in attivo o quando si verificano problemi, il master, dopo un timeout, ritorna nella modalita' asincrona di replicazione senza bloccare o ritardare ulteriormente le transazioni.

La configurazione della replica semi sincrona e' abbastanza semplice: richiede l'attivazione del plugin e l'impostazione delle variabili sul master:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_master_timeout = 10000;
E la seguente configurazione sugli slave:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled = 1;

A questo punto e' tutto configurato ma... bisogna riavviare gli slave per farli registrare come semi-sincroni. Si puo' fare on-line con:
 STOP SLAVE IO_THREAD;
 START SLAVE IO_THREAD;

Crash safe, delayed replica, table logging, GTID, ... (5.6)

La versione 5.6 di MySQL [N.d.E in produzione da Febbraio 2013] contiene diverse utili nuove funzionalita' per la replicazione dei dati:

Online CHANGE MASTER, Multi-source Replication, ... (5.7)

La versione 5.7 di MySQL [NdE 2015-10], prevede diverse novita' per la replication:

Group Replication (5.7.17)

La versione 5.7.17 di MySQL, appena rilasciata in produzione [NdE 2016-12] contiene il plugin per la Group Replication che consente di gestire la replica multimaster.

I protocolli di rete della Group Replication si occupano della connessione e disconnessione dei nodi e di mantenere la sincronizzazione dei dati... il tutto in modo automatico. Nella Group Replication la configurazione piu' semplice e' quella con un single-primary ma e' supportata anche la configurazione multi-master o, per essere allineati con la terminologia ufficiale, la configurazione multi-primary.
Al momento del commit viene eseguita la certificazione della transazione che verifica se le modifiche possono avvenire senza conflitti. In caso d'errore viene semplicemente eseguito il rollback della transazione.

InnoDB Cluster

Con il rilascio di MySQL Router 2.1 e MySQL Shell in GA [NdE 2017-04] e' disponibile l'architettura MySQL InnoDB Cluster che integra la replication in una completa soluzione in HA (alta affidabilita').

La componente di replica utilizzata da MySQL Cluster InnoDB e' la Group Replication (introdotta in MySQL 5.7.17) e le applicazioni utilizzano MySQL Router per connettersi ai nodi attivi del Cluster. La configurazione e la gestione del cluster sono semplificate dall'AdminAPI di MySQL Shell.

... (8.0)

Sono molte le evoluzioni sulla replica introdotte nella versione 8.0. In effetti alcune sono risultate cosi' importanti che e' stato poi eseguito un backport sulla versione 5.7 (eg. il supporto dei savepoint nella group replication, necessario per l'opzione –-single-transaction di mysqldump, e' stato rilasciato prima nella 8.0.1 e poi nella versione 5.7.19).

Appena avro' tempo descrivero' le piu' importanti [NdE che pigro questo Autore].

Backup da Slave

Per attivare la replica MySQL e' necessario partire da un backup consistente. Per qualche applicazione potrebbe non essere possibile effettuare un backup consistente del Master a causa della durata dei lock. In questo caso e' possibile utilizzare il backup uno slave gia' attivo. Ecco i passi corretti per dare il minor impatto alla replica ed alle applicazioni:

mysql_s1> STOP SLAVE SQL_THREAD; mysql_s1> SHOW SLAVE STATUS \G ... Master_Log_File: mysql-bin.020069 Read_Master_Log_Pos: 1032164998 Relay_Log_File: relay-bin.001963 Relay_Log_Pos: 1022702159 Relay_Master_Log_File: mysql-bin.020069 Slave_IO_Running: Yes Slave_SQL_Running: No ... Skip_Counter: 0 Exec_Master_Log_Pos: 0200802015 ... shell_s1# mysqldump --master-data=2 --all-databases > bck.sql mysql_s1> START SLAVE; shell_s2# mysql < bck.sql mysql_s2> CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.020069', MASTER_LOG_POS = 0200802015 ... mysql_s2> START SLAVE;

Chiaro? Penso di si!
[NdA nelle ultime versioni e' stata inserita l'opzione --dump-slave che riporta i valori dei binlog del master ed interrompe l'SQL thread] [NdE l'opzione --dump-slave e' disponibile dalla versione 5.5.3].

Attenzione!
Le indicazioni riportate sopra sono da seguire se si vuole agganciare il nuovo slave allo stesso master. Se invece si vuole utilizzare la cascading replication, ovvero agganciare il nuovo slave allo slave da cui si sta effettuando il backup, gli estremi per la configurazione della replica si ottengono con SHOW MASTER STATUS.

L'istruzione CHANGE MASTER ha molti altri parametri... sono descritti prima in questo documento.
Il parametro --master-data=2 funziona solo se e' stato abilitato il binary logging sullo slave. Per la sincronizzazione la cosa piu' semplice e' sospendere l'SQL thread, comunque consigliato il parametro per il locking --single-transaction.
In caso di DB di grandi dimensioni conviene comprimere il dump con gzip.

Per aggiungere un altro Slave identico ai precedenti un'altra semplice tecnica e': STOP SLAVE SQL_THREAD; backup/restore fisico di tutto il DB, modificare del server_id sul nuovo slave; START SLAVE su entrambe gli slave.

Point-in-Time Recovery

Quanto visto fino ad ora sui bin-log ad uso della replication puo' essere applicato con uno scopo differente: il Point-in-time Recovery (PITR). Il PITR consente di ripristinare un database fino al momento desiderato (eg. appena prima di una DROP TABLE lanciata per errore).
Il database va semplicemente configurato con il bin-log attivo (parametro log-bin nel file my.cnf). Per il restore l'idea di base e' quella di partire da un salvataggio completo ed affidabile, su cui vengono applicati i comandi contenuti nei file di bin-log fino al momento desiderato. I bin-log sono in formato binario, il comando mysqlbinlog trasforma il contenuto binario nelle istruzioni SQL corrispondenti. Per ottenere le variazioni occorse il comando e':

mysqlbinlog binlog.000069 binlog.000070 binlog.000071 > to_apply.sql

Potrebbe anche essere utilizzato in pipe con mysql... ma un minimo di controllo da parte del DBA di solito e' opportuno prima di lanciare i comandi SQL sul database [NdA per analizzare i binlog sono anche utili le opzioni di mysqlbinlog -v, -vv o --base64-output=DECODE-ROWS].

Per indicare il momento fino a cui arrivare o da cui partire si utilizzano i parametri --start-datetime --stop-datetime. La data va indicata con formato std ISO: --stop-datetime="2012-04-01 10:00:00". E' anche possibile indicare la transazione precisa da cui partire o arrivare con i parametri --start-position --stop-position.
In caso di ripristino di un DB lo spazio disponibile e' spesso un problema... Nella conversione da binario ad SQL la dimensione indicativamente aumenta del 50%.

La configurazione ed i comandi di amministrazione per la gestione di un DB su cui si utilizza il binlog per il PITR sono gli stessi gia' descritti per la gestione del Master...
Il solo parametro log-bin nel file my.cnf e' sufficiente per attivare il salvataggio dei binlog. Il parametro non e' dinamico e quindi e' necessario il riavvio del server. Altro parametro utile, introdotto dalla 5.1, e' binlog_format=mixed che permette di utilizzare la modalita' piu' conveniente di logging tra STATEMENT e ROW.
Nel caso del PITR non serve attendere che tutti gli Slave siano sincronizzati (non ci sono Slave) per il purge dei binlog ed e' sufficiente arrivare all'ultimo full backup affidabile. Disponendo di un backup ogni sera il comando per effettuare la cancellazione dei binlog e':
 PURGE MASTER LOGS BEFORE DATE_SUB(now(), INTERVAL 2 day);

Un altro approccio, sicuramente piu' flessibile perche' consente di aggirare eventuali problemi con lo skip, e' quello di utilizzare uno slave e di far interrompere la replica fino ad uno specifico momento con:
 START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
oppure
 START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = xxx
se si utilizza la replica GTID.


Titolo: MySQL Replication
Livello: Avanzato (3/5)
Data: 1 Aprile 2008
Versione: 1.0.23 - 12 Aprile 2017
Autore: mail [AT] meo.bogliolo.name