configurare uno slave Mysql tramite XtraBackup

Spesso ser scalare le select può essere necessario configurare uno slave.
È una soluzione molto robusta specialmente se non è necessario avere un cluster multimaster, sincrono e se non c'e necessita di scalare le write sul db.

Un'altra criticità di mysql in configurazione master-slave riguarda l'alta affidabilità con il master come sigle point of faliure.

Ma partiamo con la configurazione vera e propria

Questo tutorial necessita di 2 host, con queste caratteristiche minime: 2vcpu, 2G di ram 25G di disco.

Hostname

IP

mysql-1

10.10.0.6

mysql-2

10.10.0.7

Gli host andranno dimensionati opportunamente, in genere con il master leggeremente più potente dello slave.

Setup Iniziale da eseguire su tutti gli host

Come primo passo procediamo ad aggiornare i due server, ubuntu.

apt-get update && apt-get dist-upgrade

Installiamo percona-server  e percona-xtrabackup su entrambi i server seguendo la guida ufficiale di percona

https://www.percona.com/doc/percona-server/LATEST/installation/apt_repo.html

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-server-server-5.7 percona-xtrabackup-24 -y

Durante l'installazione ci verrà chiesto di configurare la password di root, questo passaggio è strettamente necessario sul master ma non sullo slave.

Congiuriamo l'accesso password-less per mysql

vim ~/.my.cnf
[client]
user=root
password=your-root-password

Configurare lo slave per l'accesso password less al master

1. Creiamo una nuova chiave sul uno degli slave
ssh-keygen -t rsa -b 4096 -C "your_email@example.com"
Generating public/private rsa key pair.

Quando ti verra chiesto inserisci un file in cui salvare la password schiaccia invio per usare quello predefinito $HOME/.ssh/id_rsa

Enter a file in which to save the key (/home/you/.ssh/id_rsa): [premi enter]

Il promont ti chiedera di inserire una passphrase lascia vuoto questo campo

Enter passphrase (empty for no passphrase): [Type a passphrase]
Enter same passphrase again: [Type passphrase again]

A questo punto verra creata la chiave otteremo un output simile a questo

Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:qmljilQ582mMhKIzbWGDfNy+kjyfXfeTmLeH+jzSYok mysql@test-slave
The key's randomart image is:
+---[RSA 4096]----+
|                 |
|                 |
|                 |
|..o o            |
|oo+O .  S        |
|oo+oB ..         |
|+.+..*.  ...= o  |
|.= ==o+ .E.BoB . |
|. .+*= .  ..*==  |
+----[SHA256]-----+

2. Copiamo la chiave pubblica dallo slave cosi la potremo aggiungere alla lista delle chiavi autorizate per accedere in ssh al master

# Slave where i generate new rsa key
root@mysql-test-2:~# cat .ssh/id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDoKkzbYd4XIvAafqx5uialPNSJU+Xhue3iMlbCMdfUM4FO51TUQoTGz4fRROF5aqJceg66OzpJa21elXzDS3+Zh4Ik+YUM+GkBFfHg96cngyOHr2oy+jMjnKyjIiY0Wr4YOfloYDQNbAmgCuxRmm5d41JXhGBFGJh/ICXfIwVr0XHD9IYIZ88aYWOkS9jUqwjnGyaLoSobGwibdWQvCeiMUIwFfACCMKDm19SvxQ3p6kQrXpml646QOMRLmFzFge1ilHwyGFpT6E+XvAfZcTXWexjhBgH0RwEGyhFnNFpg26MJlms1Q0eL/Ee7c+lftcLRCdLdTOCTCVL44OPnphpTJy3z38/yoHLvll6JVOmKhZae+yCHrXLz3u1Dt2rv24PsaFZ+zv/Ho+KrzoBhLCiH6B61NT5VaYFDvLcF82cR9rlH1Z5YBb2llcqbeFyIR/ZisTXkKYiIKIjqSloj4K18Zoh7CFz0t/aAOt6fHNFJamk3Wf3D1V6drkkBYcSf5144PQ65lPejwOuPibwyoft0pXAD1dL62ZNKB/seOm1gTAyw6hjVkl9Q3iGkgr9ZIZS9UqVzvd7bN3Q3mciZuEt5a1vH2hnL94T0HsVuL+ivdLobsHlCXhP4MQIP595oSGqRC1HR1c5nFZIg5mmnzJyQsNb06EWMfjxLy1vyEnvT2w== mysql@test-slave

3. Aggiungiamo la chiave alla lista delle chiavi autorizate sul master

#Master
root@mysql-test-1:~# vim .ssh/authorized_keys

...
list my autorized key

...

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDoKkzbYd4XIvAafqx5uialPNSJU+Xhue3iMlbCMdfUM4FO51TUQoTGz4fRROF5aqJceg66OzpJa21elXzDS3+Zh4Ik+YUM+GkBFfHg96cngyOHr2oy+jMjnKyjIiY0Wr4YOfloYDQNbAmgCuxRmm5d41JXhGBFGJh/ICXfIwVr0XHD9IYIZ88aYWOkS9jUqwjnGyaLoSobGwibdWQvCeiMUIwFfACCMKDm19SvxQ3p6kQrXpml646QOMRLmFzFge1ilHwyGFpT6E+XvAfZcTXWexjhBgH0RwEGyhFnNFpg26MJlms1Q0eL/Ee7c+lftcLRCdLdTOCTCVL44OPnphpTJy3z38/yoHLvll6JVOmKhZae+yCHrXLz3u1Dt2rv24PsaFZ+zv/Ho+KrzoBhLCiH6B61NT5VaYFDvLcF82cR9rlH1Z5YBb2llcqbeFyIR/ZisTXkKYiIKIjqSloj4K18Zoh7CFz0t/aAOt6fHNFJamk3Wf3D1V6drkkBYcSf5144PQ65lPejwOuPibwyoft0pXAD1dL62ZNKB/seOm1gTAyw6hjVkl9Q3iGkgr9ZIZS9UqVzvd7bN3Q3mciZuEt5a1vH2hnL94T0HsVuL+ivdLobsHlCXhP4MQIP595oSGqRC1HR1c5nFZIg5mmnzJyQsNb06EWMfjxLy1vyEnvT2w== mysql@test-slave

copiamo la chiave priavata su ogni slave se ne abbiamo più di uno io salto questo passaggio perchè ho solo uno slave.

Set UpMaster

Procediamo modificando la configurazione di mysql

vim /etc/mysql/percona-server.conf.d/mysqld.cnf
Aggiungendo queste linee alla configurazione in [mysqld]
# put your bin log in a better place
log-bin = /var/lib/mysql/bin-log
expire-logs-days = 14
max-binlog-size  = 500M
server-id        = 1

server-id = 1
gtid-mode =ON
enforce-gtid-consistency
Apportata la modifica la conf apparirà simile a questa.
#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
# put your bin log in a better place
log-bin = /var/lib/mysql/bin-log
expire-logs-days = 14
max-binlog-size  = 500M
server-id        = 1

server-id = 1
gtid-mode =ON
enforce-gtid-consistency


log-error    = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
~                    
Procediamo creando un utente per la replica, in questo caso forniamo i permessi di replica su tutti i database [ *.* ]
$ mysql

mysql> CREATE USER 'replica'@'%' IDENTIFIED BY 'veryS3cr3t';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%' IDENTIFIED BY 'veryS3cr3t';
Riavviamo mysql
systemctl restart mysql

slave

Configurazione mydql da replicare su ogni slave nel mio caso solo 1

vim /etc/mysql/percona-server.conf.d/mysqld.cnf
Aggiungiamo queste righe, Attenzione il server-id DEVE essere univoco
# put your bin log in a better place
log-bin = /var/lib/mysql/bin-log
expire-logs-days = 14
max-binlog-size  = 500M

server-id = 2
gtid-mode =ON
enforce-gtid-consistency
La cofigurazione apparirà simile a questa.
#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp

server-id = 2
gtid-mode =ON
enforce-gtid-consistency


log-error    = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
~                    
Riavviamo mysql
systemctl restart mysql


Avviamo lo slave

Per concludere il setup creiamo un breve script che oltre a essere pratico nel setup delle slave sara riutilizzabile, ogni qual volta che a causa di qualche problema sara necessario ricreare lo slave.
Questo Script va creato ed eseguito su ogni master.
Sono necessari 3 Parametri
Master = MYSQL Master Host
REPLICA_USER = L'utente che usiamo per replicare il database
REPLICA_PASSWORD = La password del utente che usiamo per la replica
Nel mio esempio:

MASTER="10.10.0.6"
REPLICA_USER="replica"
REPLICA_PASSWORD="veryS3cr3t"

vim setup_slave.sh

#!/bin/bash

# RUNNING FROM THE OUTDATED OR BROKEN NODE
# require apt-get install percona-xtrabackup-24 -y
MASTER="10.10.0.6"
REPLICA_USER="replica"
REPLICA_PASSWORD="veryS3cr3t"
service mysql stop
cd /var/lib/mysql
rm -rfv *
ssh $MASTER 'innobackupex --parallel=3 --rebuild-threads=3 --no-timestamp --slave-info --stream=xbstream ./' | xbstream -x
innobackupex --parallel=3 --rebuild-threads=3 --no-timestamp --apply-log /var/lib/mysql
chown -R mysql: .
service mysql start
mysql -e "SELECT @@GTID_PURGED;"
mysql -e "RESET MASTER;"
mysql -e "SELECT @@GTID_PURGED;"
mysql -e "SET GLOBAL gtid_purged=\"$(cat xtrabackup_binlog_info | cut -f3)\";"
mysql -e "SELECT @@GTID_PURGED;"
mysql -e "CHANGE MASTER TO MASTER_HOST=\"$MASTER\", MASTER_USER=\"$REPLICA_USER\", MASTER_PASSWORD=\"$REPLICA_PASSWORD\", MASTER_AUTO_POSITION = 1;"
mysql -e "SHOW SLAVE STATUS\G;"
mysql -e "START SLAVE\G;"
mysql -e "SHOW SLAVE STATUS\G;"

 Sistemiamo i permessi, e avviamo lo script

chmod +x setup_slave.sh
./setup_slave.sh

Verifichiamo lo stato dello slave tramite il comando

mysql> show slave status\G;
Se tutto funziona correttamente otterremo un output simile a questo

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.0.6
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 330518234
               Relay_Log_File: mysql-2-relay-bin.000002
                Relay_Log_Pos: 330517761
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 330517554
              Relay_Log_Space: 330518650
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: e7b4e8c6-5065-11e8-93c8-fa163eeaa9f7
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: e7b4e8c6-5065-11e8-93c8-fa163eeaa9f7:1-486056
            Executed_Gtid_Set: e7b4e8c6-5065-11e8-93c8-fa163eeaa9f7:1-486055
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Testing

Possiamo procedere ad un test creando sul master un nuovo utente e un nuovo database con una semplice tabella

Test1

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'verys3cr37';
CREATE DATABASE testdb1;
GRANT ALL PRIVILEGES ON testdb1.* TO 'test_user'@'localhost';
USE testdb1;
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Test2

Per rendere più interessante il test possiamo usare questo semplice applicativo in python per scrivere un po di dati sul DB
installiamo i pacchetti necessari

apt-get install python python-mysqldb -y

Creiamo lo Script

vim test_mysql.py

 

#!/usr/bin/python
import string
import random
import MySQLdb

def id_generator(size=100, chars=string.ascii_uppercase + string.digits):
  return ''.join(random.choice(chars) for _ in range(size))

# Open database connection
db = MySQLdb.connect("localhost","test_user","verys3cr37","testdb1" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

LastName = id_generator()
FirstName = id_generator()
Address = id_generator()
City = id_generator()

for id in range(0, 1000):
    #SQL query to INSERT a record into the table FACTRESTTBL.
#    print id
    sql_query = ( """INSERT into Persons (PersonID, LastName, FirstName, Address, City) values (%s, %s, %s, %s, %s);""" )
    sql_data  =  (id, LastName, FirstName, Address, City)
    cursor.execute(sql_query,sql_data)
    # Commit your changes in the database
    db.commit()
Avviamo lo script, se tutto funziona correttamente troveremo gli stessi dati in tutti gli slave