Configure mysql slave wit XtraBackup

Same time is useful scale the select, a good solution is mysql in configuration master-slave.

What we need

For this tutorial, we need 2vcpu + 2G di ram 25G HD, my configuration.

Hostname

IP

mysql-1

10.10.0.6

mysql-2

10.10.0.7

In a production environment, the host will be dimensioned, typically the master is bigger than the slave.

Update all server

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

Install Percona-server, I will report an easy way to install percona in a production environment is better to follow the official guides Percona Guides

My Brief Guides

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

During the installation, we configure the root password,
Now we can edit mysql configuration

vim /etc/mysql/percona-server.conf.d/mysqld.cnf

Add this 3 line to mysql configuration.

log-bin = /var/lib/mysql/bin-log

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

The configuration will like this.

#
# 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
~                    

Create a user for replication

$ mysql

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

Restart mysql

systemctl restart mysql

Slave Configuration

Edit mysql configuration

vim /etc/mysql/percona-server.conf.d/mysqld.cnf

Add this 4 line to the configuration

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

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

The configuration will like this

#
# 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
~                    

Restart mysql

systemctl restart mysql

Configure master, for access to master passwordless, for how to follow this guides

 

 

Configure access to mysql password-less
vim .my.cnf

[client]
user=root
password=your-root-password

Setup replication

Configure this 3 paramenter, for me
MASTER="10.10.0.6"
REPLICA_USER="replica"
REPLICA_PASSWORD="veryS3cr3t"

# 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;"

Check slave status

mysql> show slave status\G;

We will get an output like this


*************************** 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

Create a test database, it will be replicated on the slave

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)
);

Use this small script for insert same line in the database, they will be replicated on the slave

#!/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","suka","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, 999999):
    #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()
Data is update in all databases