Quickly configure replication using DBdeployer [SandBox]

Quickly configure replication using DBdeployer [SandBox]

Quickly configure replication using DBdeployer [SandBox]

We might have different scenarios when we need a quick setup of replication either between the same version of MySQL (Like 8.0 --> 8.0) or between the different version of MySQL (Like 5.7 --> 8.0) to perform some testings. 


Here in this blog post, I will explain how we can create our replication lab setup quickly using the virtual machine and DBdeployer tool. 


Let's see, how to create replication between the same version and different version of MySQL using DBdeployer step by step. 

Create CentOS VM

Please find my this blog post link where you will get instruction about, how you can create CentOS virtual machine using the vagrant. 

Install DBdeployer tool

-- Run below command to install the latest DBdeployer package.

[root@centos7-test-vm ~]# yum -y install wget
[root@centos7-test-vm ~]# VERSION=1.33.0
[root@centos7-test-vm ~]# OS=linux
[root@centos7-test-vm ~]# origin=https://github.com/datacharmer/dbdeployer/releases/download/v$VERSION
[root@centos7-test-vm ~]# wget $origin/dbdeployer-$VERSION.$OS.tar.gz
[root@centos7-test-vm ~]# tar -xzf dbdeployer-$VERSION.$OS.tar.gz
[root@centos7-test-vm ~]# chmod +x dbdeployer-$VERSION.$OS
[root@centos7-test-vm ~]# sudo mv dbdeployer-$VERSION.$OS /usr/local/bin/dbdeployer 
Note: Once writing these steps, we had the latest Dbdeployer version released 1.33.0. You make sure to check the latest version released on below page and make accordingly value of "VERSION=XX.XX.XX" above in 2nd command. 

-- Verify the installed dbdeployer version

[root@centos7-test-vm ~]# dbdeployer --version
dbdeployer version 1.33.0
[root@centos7-test-vm ~]# 

Download and unpack Percona server tarball

-- Download the latest Percona Server 5.7 & 8.0 binary tarball package from below links under section "Linux-Generic."

Note: Here if you are confused which SSL package tarball you should download, then please check my this blog post.

[root@centos7-test-vm ~]# wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.26-29/binary/tarball/Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz

[root@centos7-test-vm ~]# wget https://www.percona.com/downloads/Percona-Server-8.0/Percona-Server-8.0.15-6/binary/tarball/Percona-Server-8.0.15-6-Linux.x86_64.ssl.tar.gz 

-- Unpack tarball packages using the DBdeployer tool

[root@centos7-test-vm ~]# pwd
/root

[root@centos7-test-vm ~]# ls -lth Percona*tar.gz
-rw-r--r--. 1 root root  64M Jun  1 11:50 Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz
-rw-r--r--. 1 root root 687M May  3 12:50 Percona-Server-8.0.15-6-Linux.x86_64.ssl.tar.gz

[root@centos7-test-vm ~]# mkdir -p /root/opt/mysql

[root@centos7-test-vm ~]# dbdeployer unpack --prefix=ps Percona-Server-5.7.26-29-Linux.x86_64.ssl101.tar.gz

[root@centos7-test-vm ~]# dbdeployer unpack --prefix=ps Percona-Server-8.0.15-6-Linux.x86_64.ssl.tar.gz 

-- Check the list of installed/available tarball binary packages for DBdeployer.

[root@centos7-test-vm ~]# dbdeployer versions
Basedir: /root/opt/mysql
ps5.7.26  ps8.0.15
[root@centos7-test-vm ~]# 



Configure replication between same Percona server version (8.0 --> 8.0)

-- Run below command to configure replication between the Percona Server 8.0 version, i.e. both Master and 2 slaves will be on the same version. 

[root@centos7-test-vm ~]# dbdeployer deploy replication ps8.0
# ps8.0 => ps8.0.15
Installing and starting master
.. sandbox server started
Installing and starting slave1
... sandbox server started
Installing and starting slave2
...... sandbox server started
$HOME/sandboxes/rsandbox_ps8_0_15/initialize_slaves
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_ps8_0_15
run 'dbdeployer usage multiple' for basic instructions'
[root@centos7-test-vm ~]# 

-- To access each node, go to the installed replication directory "$HOME/sandboxes/rsandbox_ps8_0_15"
[root@centos7-test-vm ~]# cd $HOME/sandboxes/rsandbox_ps8_0_15
--------------------------------------------------------------
[root@centos7-test-vm rsandbox_ps8_0_15]# sh m
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.15-6 Percona Server (GPL), Release 6, Revision 63abd08
*
*
master [localhost:20516] {msandbox} ((none)) >
--------------------------------------------------------------
[root@centos7-test-vm rsandbox_ps8_0_15]# sh s1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.15-6 Percona Server (GPL), Release 6, Revision 63abd08
*
*
slave1 [localhost:20517] {msandbox} ((none)) >
--------------------------------------------------------------
[root@centos7-test-vm rsandbox_ps8_0_15]# sh s2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.15-6 Percona Server (GPL), Release 6, Revision 63abd08
*
*
slave2 [localhost:20518] {msandbox} ((none)) >


-- Test replication using test_replication script which will create test table & insert some data inside test table on the master node and then it verifies created data on master node got replicated on slave node. 

[root@centos7-test-vm rsandbox_ps8_0_15]# sh test_replication
# master log: mysql-bin.000001 - Position: 14696 - Rows: 20
# Testing slave #1
ok - slave #1 acknowledged reception of transactions from master
ok - slave #1 IO thread is running
ok - slave #1 SQL thread is running
ok - Table t1 found on slave #1
ok - Table t1 has 20 rows on #1
# Testing slave #2
ok - slave #2 acknowledged reception of transactions from master
ok - slave #2 IO thread is running
ok - slave #2 SQL thread is running
ok - Table t1 found on slave #2
ok - Table t1 has 20 rows on #2
# Tests :    10
# failed:     0 (  0.0%)
# PASSED:    10 (100.0%)
# exit code: 0
[root@centos7-test-vm rsandbox_ps8_0_15]# 

-- Check replication status using the check_slaves script.

[root@centos7-test-vm rsandbox_ps8_0_15]# sh check_slaves
master
port    20516 - server_id    100
             File: mysql-bin.000001
         Position: 14696
Executed_Gtid_Set:
slave1
port    20517 - server_id    200
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 14696
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 14696
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
slave2
port    20518 - server_id    300
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 14696
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 14696
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
[root@centos7-test-vm rsandbox_ps8_0_15]# 


Configure replication between different Percona server version (5.7 --> 8.0)

To configure replication between different MySQL version using the dbdeployer, we will need to first deploy the individual MySQL 5.7 and 8.0 instances using the dbdeployer, and then we can use dbdeployer's replicate_from script to configure the replication. 

-- Deploy the individual/single instances of Percona server 5.7 and 8.0 versions using their unpacked binary packages. 

[root@centos7-test-vm ~]# dbdeployer versions
Basedir: /root/opt/mysql
ps5.7.26  ps8.0.15

[root@centos7-test-vm ~]# dbdeployer deploy single ps5.7.26
Database installed in $HOME/sandboxes/msb_ps5_7_26
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started

[root@centos7-test-vm ~]# dbdeployer deploy single ps8.0.15
Database installed in $HOME/sandboxes/msb_ps8_0_15
run 'dbdeployer usage single' for basic instructions'
...... sandbox server started

-- Jump to installed database directory and then connect to MySQL prompt of each instance.
[root@centos7-test-vm ~]# cd $HOME/sandboxes/msb_ps5_7_26

[root@centos7-test-vm msb_ps5_7_26]# sh use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-29 Percona Server (GPL), Release 29, Revision 11ad961
*
*
mysql [localhost:5726] {msandbox} ((none)) > select @@global.log_bin,@@global.server_id;
+------------------+--------------------+
| @@global.log_bin | @@global.server_id |
+------------------+--------------------+
|                0 |                  0 |
+------------------+--------------------+
1 row in set (0.00 sec)


[root@centos7-test-vm rsandbox_ps8_0_15]# cd $HOME/sandboxes/msb_ps8_0_15

[root@centos7-test-vm msb_ps8_0_15]# sh use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15-6 Percona Server (GPL), Release 6, Revision 63abd08
*
*
mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_bin,@@global.server_id;
+------------------+--------------------+
| @@global.log_bin | @@global.server_id |
+------------------+--------------------+
|                1 |                  1 |
+------------------+--------------------+
1 row in set (0.02 sec)

-- Above we can see, by default, in MySQL 5.7 version binary log is disabled as well as the server-id is also not set for the instance. So to setup replication, we need to enable binary log on MySQL 5.7, and also we need to set server-id. It is straightforward to change in dbdeployer using the add_option script, which will add the mentioned parameters inside the cnf file and then restart the mysqld service. 

[root@centos7-test-vm msb_ps5_7_26]# sh add_option log-bin server-id=10
# option 'log-bin' added to configuration file
# option 'server-id=10' added to configuration file
stop /root/sandboxes/msb_ps5_7_26
. sandbox server started

[root@centos7-test-vm msb_ps5_7_26]# sh use -e"select @@global.log_bin,@@global.server_id"
+------------------+--------------------+
| @@global.log_bin | @@global.server_id |
+------------------+--------------------+
|                1 |                 10 |
+------------------+--------------------+
[root@centos7-test-vm msb_ps5_7_26]# 

-- Go to the installed database directory of Percona server 8.0 and then configure the replication with Percona Server 5.7 using the replicate_from script.

[root@centos7-test-vm ~]# dbdeployer sandboxes
msb_ps5_7_26             :   single         ps5.7.26   [5726 ]
msb_ps8_0_15             :   single         ps8.0.15   [8015 18015 ]

[root@centos7-test-vm ~]# cd $HOME/sandboxes/msb_ps8_0_15

[root@centos7-test-vm msb_ps8_0_15]# sh replicate_from msb_ps5_7_26
Connecting to /root/sandboxes/msb_ps5_7_26
--------------
CHANGE MASTER TO master_host="127.0.0.1",
master_port=5726,
master_user="rsandbox",
master_password="rsandbox"
, master_log_file="centos7-test-vm-bin.000001", master_log_pos=154
--------------


--------------
start slave
--------------


              Master_Log_File: centos7-test-vm-bin.000001
          Read_Master_Log_Pos: 154
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 154
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
[root@centos7-test-vm msb_ps8_0_15]# 

-- Here we go, our replication setup is ready between the different Percona server version 5.7 and 8.0. We can see from the file active_replication present on Percona server 8.0 directory that this sandbox is connected to Percona server 5.7.

[root@centos7-test-vm msb_ps8_0_15]# cat active_replication
Sat Jun  1 13:18:01 UTC 2019
Connected to /root/sandboxes/msb_ps5_7_26
[root@centos7-test-vm msb_ps8_0_15]# 

-- We can also test our replication setup by creating a test table and inserting some random data on master 5.7 and then check data on the slave 8.0 and verify the replication status.

MASTER: 5.7

mysql [localhost:5726] {msandbox} ((none)) > use test
Database changed
mysql [localhost:5726] {msandbox} (test) > create table testing (id int);
Query OK, 0 rows affected (0.03 sec)

mysql [localhost:5726] {msandbox} (test) > insert into testing values (1),(2),(3);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql [localhost:5726] {msandbox} ((none)) > show master status\G
*************************** 1. row ***************************
             File: centos7-test-vm-bin.000001
         Position: 590
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

SLAVE: 8.0

mysql [localhost:8015] {msandbox} ((none)) > select * from test.testing;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql [localhost:8015] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5726
                Connect_Retry: 60
              Master_Log_File: centos7-test-vm-bin.000001
          Read_Master_Log_Pos: 590
               Relay_Log_File: centos7-test-vm-relay-bin.000002
                Relay_Log_Pos: 767
        Relay_Master_Log_File: centos7-test-vm-bin.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: 590
              Relay_Log_Space: 985
              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: 10
                  Master_UUID: 00005726-0000-0000-0000-000000005726
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
1 row in set (0.00 sec)


Hope this blog post will be helpful for you to play with dbdeployer and create different replication setups quickly for you inside your test VM. 

*******


Photo by Gabriel Gusmao on Unsplash

Comments

Post a Comment

Popular

SLEEP thread causing "Waiting for table metadata lock"

MySQL Memory Calculator

errno: 24 - Too many open files

How to properly shutdown MySQL before any maintenance activity

How to create CentOS VM using Vagrant

set --server-id to enable either a master or a slave

ERROR 1040 (HY000): Too many connections

GTID Replication (Skip Transaction using empty transaction)

Master has purged binary logs containing GTIDs that the slave requires

How to Prepare for MySQL Certification Exam