RSS

MariaDB - Understanding Database Replication by Example

Replication from an active database to a Replica using MariaDB

🖥 In this video, we create an active Primary database that completes one transaction every 2 seconds. Then, we create a replica database. The notes used in the video are listed below.

Steps taken on the Primary node:

## fix problem with pasting with right click:
echo "set mouse-=a" >> ~/.vimrc
## turn on syntax highlighting
echo ":syntax on" >> ~/.vimrc

# CERTIFICATE:
vi ~/.ssh/2020-key
chmod 600 ~/.ssh/2020-key

# add to ~/.bashrc
echo "force_color_promt=yes" >> ~/.bashrc
echo 'PS1="\[\033[01;31m\]\u\[\033[00;37m\]@\h:\w\$ "' >> ~/.bashrc
echo "alias addkey='eval \`ssh-agent\`; ssh-add ~/.ssh/2020-key'" >> ~/.bashrc

. ~/.bashrc

# Create a small swap file:
fallocate -l 1G /swapfile # that is a lower-case L
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
cp /etc/fstab /etc/fstab.bak
echo '/swapfile none swap sw 0 0' | tee -a /etc/fstab

# Review swappiness from page: https://mariadb.com/kb/en/configuring-swappiness/
# probably returns 60
sysctl vm.swappiness

echo 'vm.swappiness = 5' >> /etc/sysctl.conf

# reload system variables from /etc/sysctl.conf - without reboot! yay!
sysctl --system 

# check swappiness again
sysctl vm.swappiness

# Verify that you have 1G of swap available to the kernel:
free -m

apt update
apt upgrade -y
apt install -y gnupg gnupg2 
apt install -y software-properties-common dirmngr curl

# To install MariaDB Community Edition, start here: https://downloads.mariadb.org/mariadb/repositories/
# that page will create the following commands:

apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.5/debian buster main'

apt update
apt install -y mariadb-server mariadb-backup

# check the running version of software, should be 10.5
service mariadb status 

mysql_secure_installation

#get the internal network IP address and put it in the parameter file:
ip address | grep 'global eth1'

#change some parameter values:
CNF=/etc/mysql/mariadb.conf.d/90-custom.cnf
vi $CNF
[mysqld]
innodb_buffer_pool_size = 536870912
innodb_log_buffer_size = 33554432
log_error = /var/log/mysql/mariadb.err
bind_address = 10.137.64.201
port = 3306
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = testdb
log_bin_trust_function_creators = ON
event_scheduler = ON
service mariadb restart

mysql -u root -p
show variables like 'innodb_buffer_pool_size';
show variables like 'innodb_log_buffer_size';
show variables like '%bind%';
show variables like '%charac%'; -- make sure utf8mb4 is the character set
show variables like 'log_b%';
# create a script to run a list of commands:
SC1=~/create_users.sql
vi $SC1
create database testdb;
create user `tester`@`localhost` identified by 'password';
create user `tester`@`10.137.64.201`  identified by 'password';
grant all on testdb.* to `tester`@`localhost`;
grant all on testdb.* to `tester`@`10.137.64.201`;
create user `replica_user`@`%` identified by 'password';
grant replication slave on *.* TO `replica_user`@`%`;
flush privileges;
mysql -u root -p < $SC1
-- ############ IF YOU NEED TO START OVER ON THE DATABASE: ##############
   drop database testdb;
   drop user `tester`@`localhost`;
   drop user `tester`@`10.137.64.201`;
   drop user `replica_user`@`%`;
-- ######################################################################
SC2=~/create_objects.sql
vi $SC2
------------------------------------
USE `testdb`;

DROP TABLE IF EXISTS `important_data`;

CREATE TABLE `important_data` (
  `my_sequence` int(11) NOT NULL AUTO_INCREMENT,
  `my_value` varchar(50) DEFAULT '0',
  `activity` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`my_sequence`)
) COMMENT='Created by Edward Stoever'
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

DELIMITER //
CREATE DEFINER=`tester`@`localhost` FUNCTION `F_RAND_ST`(
    `len` SMALLINT(3)
) RETURNS varchar(100) CHARSET utf8
COMMENT 'created by Edward Stoever'
begin
    SET @returnStr = '';
    SET @allowedChars = 'abcdefghjkmnpqrstuvwxyz';
    SET @i = 0;

    WHILE (@i < len) DO
        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END;
//


DELIMITER //
CREATE DEFINER=`tester`@`localhost` PROCEDURE `P_INSERT_IMPORTANT_DATA`()
COMMENT 'created by Edward Stoever'
BEGIN
declare c int;
   set c = 0;
   WHILE c < 30 DO
      insert into important_data (`my_value`,`activity`)
      values
      (F_RAND_ST(33),now());
   do sleep(2);
   SET c = c + 1;
   END WHILE;
END;
//

DELIMITER //
CREATE DEFINER=`tester`@`localhost` EVENT `E_INSERT_IMPORTANT_DATA`
        ON SCHEDULE
                EVERY 1 MINUTE
        ON COMPLETION PRESERVE
        ENABLE
        COMMENT 'Created by Edward Stoever'
        DO BEGIN
 call P_INSERT_IMPORTANT_DATA();
END;
//
DELIMITER ;
------------------------------------
mysql -u tester -ppassword < $SC2

mysql -u tester -ppassword
use testdb
select * from important_data;
exit;
mysqldump -u root -ppassword --databases testdb --add-drop-database --complete-insert --routines --force --master-data > ~/testdb_full_dump.sql

Steps taken on the Replica node:

## fix problem with pasting with right click:
echo "set mouse-=a" >> ~/.vimrc
## turn on syntax highlighting
echo ":syntax on" >> ~/.vimrc

# CERTIFICATE:
vi ~/.ssh/2020-key
chmod 600 ~/.ssh/2020-key

# add to ~/.bashrc
echo "force_color_promt=yes" >> ~/.bashrc
echo 'PS1="\[\033[01;31m\]\u\[\033[00;37m\]@\h:\w\$ "' >> ~/.bashrc
echo "alias addkey='eval \`ssh-agent\`; ssh-add ~/.ssh/2020-key'" >> ~/.bashrc

. ~/.bashrc


# Create a small swap file:
fallocate -l 1G /swapfile # that is a lower-case L
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
cp /etc/fstab /etc/fstab.bak
echo '/swapfile none swap sw 0 0' | tee -a /etc/fstab

# Review swappiness from page: https://mariadb.com/kb/en/configuring-swappiness/
# probably returns 60
sysctl vm.swappiness

echo 'vm.swappiness = 5' >> /etc/sysctl.conf

# reload system variables from /etc/sysctl.conf - without reboot! yay!
sysctl --system 

# check swappiness again
sysctl vm.swappiness

# Verify that you have 1G of swap available to the kernel:
free -m

apt update
apt upgrade -y
apt install -y gnupg gnupg2 
apt install -y software-properties-common dirmngr curl

# To install MariaDB Community Edition, start here: https://downloads.mariadb.org/mariadb/repositories/
# that page will create the following commands:

apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.5/debian buster main'

apt update
apt install -y mariadb-server mariadb-backup

# check the running version of software, should be 10.5
service mariadb status 

mysql_secure_installation

#get the internal network IP address and put it in the parameter file:
ip address | grep 'global eth1'

CNF=/etc/mysql/mariadb.conf.d/90-custom.cnf
vi $CNF
[mysqld]
innodb_buffer_pool_size = 536870912
innodb_log_buffer_size = 33554432
log_error = /var/log/mysql/mariadb.err
bind_address = 10.137.96.126
port = 3306
server_id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = testdb
log_bin_trust_function_creators = ON
event_scheduler = ON
service mariadb restart

mysql -u root -ppassword
show variables like 'bind_address';
addkey
scp root@10.137.64.201:/root/testdb_full_dump.sql ~/
# paste the following lines at the top of ~/testdb_full_dump.sql 
vi ~/testdb_full_dump.sql
vi ~/testdb_full_dump.sql
CHANGE MASTER TO MASTER_HOST='10.137.64.201',
       MASTER_USER='replica_user', 
       MASTER_PASSWORD='password';
mysql -u root -ppassword < ~/testdb_full_dump.sql
mysql -u root -ppassword
select count(*) from testdb.important_data;

start replica;

select count(*) from testdb.important_data;

-- use a \G statement terminator for command line readable output:
show replica status \G

-- REDO THE REPLICA IF THINGS GO WRONG...
stop replica;
drop database testdb;
mysql -u root -ppassword < ~/testdb_full_dump.sql
mysql -u root -ppassword
select count(*) from testdb.important_data;

start replica;

select count(*) from testdb.important_data;
select * from testdb.important_data;
select count(*) from testdb.important_data;
-- wait a few seconds
select count(*) from testdb.important_data;

Thank you for watching!