MySQL
replicazione
Ricerca…
Osservazioni
La replica viene utilizzata per copiare i dati [di backup] da un server di database MySQL a uno o più server di database MySQL.
Master - Il server di database MySQL, che serve i dati da copiare
Slave - Il server di database MySQL, copia i dati che sono serviti da Master
Con MySQL, la replica è asincrona per impostazione predefinita. Ciò significa che gli slave non devono essere collegati in modo permanente per ricevere gli aggiornamenti dal master. Ad esempio, se il tuo slave è spento o non connesso al master e stai commutando slave su ON o si connette con Master in un secondo momento, allora si sincronizzerà automaticamente con il master.
A seconda della configurazione, è possibile replicare tutti i database, i database selezionati o anche le tabelle selezionate all'interno di un database.
Formati di replica
Esistono due tipi principali di formati di replica
Statement Based Replication (SBR) - che replica intere istruzioni SQL. In questo, il master scrive istruzioni SQL nel registro binario. La replica del master allo slave funziona eseguendo le istruzioni SQL sullo slave.
Row Based Replication (RBR) - che replica solo le righe modificate. In questo, il master scrive gli eventi nel log binario che indicano come vengono modificate le singole righe della tabella. La replica del master allo slave funziona copiando gli eventi che rappresentano le modifiche alle righe della tabella nello slave.
È anche possibile utilizzare una terza varietà, Mixed Based Replication (MBR) . In questo, viene utilizzata la registrazione basata su istruzioni e basata su riga. Il registro verrà creato in base a quale è più appropriato per la modifica.
Il formato basato su istruzioni era il predefinito nelle versioni di MySQL precedenti alla 5.7.7. In MySQL 5.7.7 e versioni successive, il formato basato su file è l'impostazione predefinita.
Master - Slave Replication Setup
Considerare 2 server MySQL per l'installazione della replica, uno è un master e l'altro è uno slave.
Configureremo il Master che dovrebbe tenere un registro di ogni azione eseguita su di esso. Configureremo il server Slave che dovrebbe guardare il log sul Master e ogni volta che si verificano cambiamenti nel log sul Master, dovrebbe fare la stessa cosa.
Configurazione principale
Prima di tutto, dobbiamo creare un utente sul Master. Questo utente verrà utilizzato da Slave per creare una connessione con il master.
CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password';
GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%';
FLUSH PRIVILEGES;
Cambia user_name e user_password base al tuo nome utente e password.
Ora il file my.inf (my.cnf in Linux) dovrebbe essere modificato. Includere le seguenti righe nella sezione [mysqld].
server-id = 1
log-bin = mysql-bin.log
binlog-do-db = your_database
La prima riga viene utilizzata per assegnare un ID a questo server MySQL.
La seconda riga indica a MySQL di iniziare a scrivere un log nel file di log specificato. In Linux questo può essere configurato come log-bin = /home/mysql/logs/mysql-bin.log . Se si sta iniziando la replica in un server MySQL in cui è già stata utilizzata la replica, assicurarsi che questa directory sia vuota di tutti i registri di replica.
La terza riga viene utilizzata per configurare il database per il quale scriveremo il log. Dovresti sostituire your_database con il nome del tuo database.
Assicurati che skip-networking non sia stato abilitato e riavvia il server MySQL (Master)
Configurazione slave
my.inf file my.inf deve essere modificato in slave. Includere le seguenti righe nella sezione [mysqld].
server-id = 2
master-host = master_ip_address
master-connect-retry = 60
master-user = user_name
master-password = user_password
replicate-do-db = your_database
relay-log = slave-relay.log
relay-log-index = slave-relay-log.index
La prima riga viene utilizzata per assegnare un ID a questo server MySQL. Questo ID dovrebbe essere unico.
La seconda riga è l'indirizzo IP del server master. Cambia questo in base al tuo IP del sistema master
La terza riga viene utilizzata per impostare un limite di tentativi in secondi.
Le prossime due righe indicano lo username e la password allo Slave, usando il quale connette il Master.
La riga successiva imposta il database che deve replicare.
Le ultime due righe utilizzate per assegnare i nomi dei file relay-log e relay-log-index .
Assicurati che skip-networking non sia stato abilitato e riavvia il server MySQL (Slave)
Copia i dati nello slave
Se i dati vengono costantemente aggiunti al Master, dovremo impedire a tutti gli accessi al database sul Master in modo che nulla possa essere aggiunto. Questo può essere ottenuto eseguendo la seguente dichiarazione in Master.
FLUSH TABLES WITH READ LOCK;
Se nessun dato viene aggiunto al server, è possibile saltare il passaggio precedente.
Stiamo andando a prendere il backup dei dati del Master usando mysqldump
mysqldump your_database -u root -p > D://Backup/backup.sql;
Cambia your_database e la directory di backup in base alla configurazione. Ora avrai un file chiamato backup.sql nella posizione indicata.
Se il tuo database non esiste nel tuo Slave, crealo eseguendo quanto segue
CREATE DATABASE `your_database`;
Ora dobbiamo importare il backup nel server MySQL slave.
mysql -u root -p your_database <D://Backup/backup.sql
--->Change `your_database` and backup directory according to your setup
Avvia la replica
Per avviare la replica, è necessario trovare il nome del file di registro e la posizione del registro nel master. Quindi, esegui quanto segue in Master
SHOW MASTER STATUS;
Questo ti darà un'uscita come sotto
+---------------------+----------+-------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+-------------------------------+------------------+
| mysql-bin.000001 | 130 | your_database | |
+---------------------+----------+-------------------------------+------------------+
Quindi eseguire quanto segue in Slave
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='user_name',
MASTER_PASSWORD='user_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=130;
SLAVE START;
Per prima cosa fermiamo lo schiavo. Quindi diciamo esattamente dove cercare nel file di log principale. Per il nome MASTER_LOG_FILE e MASTER_LOG_POS , utilizzare i valori ottenuti eseguendo il comando SHOW MASTER STATUS sul master.
Dovresti modificare l'IP del Master in MASTER_HOST e modificare l'utente e la password di conseguenza.
Lo schiavo ora aspetterà. Lo stato dello slave può essere visualizzato eseguendo quanto segue
SHOW SLAVE STATUS;
Se in precedenza sono state eseguite le FLUSH TABLES WITH READ LOCK in Master, rilasciare le tabelle dal blocco esegui quanto segue
UNLOCK TABLES;
Ora il Master tiene un log per ogni azione eseguita su di esso e il server Slave guarda il log sul Master. Ogni volta che si verificano cambiamenti nel log del Master, lo slave lo replica.
Errori di replica
Ogni volta che si verifica un errore durante l'esecuzione di una query sullo slave, MySQL interrompe automaticamente la replica per identificare il problema e risolverlo. Questo principalmente perché un evento ha causato la mancata individuazione di una chiave duplicata o di una riga e non può essere aggiornato o eliminato. È possibile saltare tali errori, anche se ciò non è raccomandato
Per saltare solo una query che sta appeso allo slave, utilizzare la seguente sintassi
SET GLOBAL sql_slave_skip_counter = N;
Questa istruzione salta i successivi N eventi dal master. Questa istruzione è valida solo quando i thread slave non sono in esecuzione. Altrimenti, produce un errore.
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;
In alcuni casi va bene. Ma se l'istruzione è parte di una transazione multi-statement, diventa più complessa, perché saltando l'istruzione di produzione dell'errore si salterà l'intera transazione.
Se vuoi saltare più query che producono lo stesso codice di errore e se sei sicuro che saltare quegli errori non porterà lo slave incoerente e vuoi saltarli tutti, dovrai aggiungere una riga per saltare quel codice di errore nel tuo my.cnf .
Ad esempio potresti voler saltare tutti gli errori duplicati che potresti ottenere
1062 | Error 'Duplicate entry 'xyz' for key 1' on query
Quindi aggiungi il seguente al tuo my.cnf
slave-skip-errors = 1062
Puoi saltare anche altri tipi di errori o tutti i codici di errore, ma assicurati che saltare questi errori non porti lo slave incoerente. Quanto segue sono la sintassi e gli esempi
slave-skip-errors=[err_code1,err_code2,...|all]
slave-skip-errors=1062,1053
slave-skip-errors=all
slave-skip-errors=ddl_exist_errors
