MariaDB Standard DBA Lab Exercises

Oct 13, 2024

13 min read

Ready to become a MariaDB pro? Our blog series on MariaDB Standard DBA Lab Exercises is your gateway to mastering this powerful open-source database. Whether you're a seasoned DBA looking to sharpen your skills or a newcomer eager to learn, these hands-on exercises will guide you through essential tasks, advanced techniques, and everything in between. Join us on this journey to elevate your database management prowess and harness the full potential of MariaDB. Let's dive in!

This is an excellent refresher on MariaDB

Lab Exercises for Lesson 1: Getting Started

Lab Exercise 1-1: Installing MariaDB

Lab Exercise 1-2: Basic Configuration

Lab Exercise 1-3: Enabling Logging in MariaDB

Lab Exercise 1-4: Upgrading MariaDB

Lab Exercises for Lesson 2: Server Internals

Lab Exercise 2-1: Collecting Server Information

Lab Exercises for Lesson 3: Schema Objects

Lab Exercise 3-1: Databases and Tables

Lab Exercise 3-2: Loading Data

Lab Exercise 3-3: Default Character Set

Lab Exercise 3-4: Views, Triggers & Events

Lab Exercise 3-5: Create a Stored Procedure

Lab Exercises for Lesson 4: Storage Engines

Lab Exercise 4-1: Changing the Default Storage Engine

Lab Exercise 4-2: Creating and Comparing Tables for Multiple Storage Engines

Lab Exercises for Lesson 5: Transactions

Lab Exercise 5-1: Rollback a Transaction

Lab Exercise 5-2: Observing a Deadlock

Lab Exercises for Lesson 6: User Management

Lab Exercise 6-1: Creating and Managing Users and Roles

Lab Exercises for Lesson 7: Security

Lab Exercise 7-1: Enabling & Configuring Password Validation

Lab Exercise 7-2: Installing the Audit Plug-In

Lab Exercises for Lesson 8: Business Continuity

Lab Exercise 8-1: Creating and Restoring Backups with mariadb-dump

Lab Exercise 8-2: Creating and Restoring Backups with MariaDB Backup

Lab Exercise 8-3: Point-in-Time Recovery

Lab Exercises for Lesson 9: Resource Usage

Lab Exercise 9-1: Memory Consumption

MariaDB Standard DBA Lab Exercises

Lab Exercise 9-2: Benchmarking with mariadb-slap

Lab Exercises for Lesson 10: Tuning & Optimization

Lab Exercise 10-1: Query Performance

Lab Exercise 10-2: Using the Information Schema’s Profiling Table

Lab Exercise 10-3: Indexing for Performance Optimization

Lab Exercises for Lesson 11: HA Fundamentals

Lab Exercise 11-1: Setting Up MariaDB Replication

Lab Exercises for Lesson 12: Advanced Features

Lab Exercise 12-1: Using Common Table Expressions

Lab Exercise 12-2: Using Window Functions

##################################################################

Lab Exercises for Lesson 1: Getting Started

Lab Exercise 1-1: Installing MariaDB

Lab Exercise 1-2: Basic Configuration

Lab Exercise 1-3: Enabling Logging in MariaDB

Lab Exercise 1-4: Upgrading MariaDB

############### Ch1

systemctl start mariadb

systemctl stop mariadb

systemctl status mariadb

yum -y install nano

yum -y install git

yum -y install MariaDB-server

mysql_secure_installation

journalctl -u mariadb

systemctl status mariadb.service -l

cat /etc/yum.repos.d/mariadb.repo

nano /etc/yum.repos.d/mariadb.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.3/rhel7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

sudo yum install MariaDB-server MariaDB-client

sudo systemctl enable mariadb

sudo systemctl start mariadb

sudo mysql_secure_installation

mysql -u root -p

###############

ps -ef | grep MySQL

mysql -p

exit

show variables like 'innodb_buffer%';

SET GLOBAL innodb_buffer_pool_size= 2550136832;

SHOW VARIABLES LIKE 'innodb_buffer%';

SHOW GLOBAL VARIABLES LIKE '%_table_size';

SET GLOBAL tmp_table_size=64*1024*1024;

SET GLOBAL max_heap_table_size=64*1024*1024;

SHOW GLOBAL VARIABLES LIKE 'max_connections';

SET GLOBAL max_connections=50;

SHOW GLOBAL VARIABLES LIKE 'max_connections';

###

# vi /etc/my.cnf.d/server.cnf

[mariadb]

innodb_buffer_pool_size=2550136832

tmp_table_size=64M

max_heap_table_size=64M

max_connections=50

######

ps aux | grep mysqld

cat /proc/$(pidof mysqld)/limits

cat /proc/<PID>/limits

cat /proc/$(43571)/limits

# vi /etc/my.cnf.d/server.cnf

[mariadb]

log_warnings=4

####

mysql

SHOW GLOBAL VARIABLES LIKE '%general%';

SET GLOBAL general_log=1;

SHOW GLOBAL VARIABLES LIKE '%general%';

exit

ls -al /var/lib/mysql/*.log

rm -rf /var/lib/mysql/mariadb-xray.log

lsof -p $(pidof mysqld)|grep -i deleted

mysql

FLUSH LOGS;

exit

ls -al /var/lib/mysql/*.log

###

mysql

INSTALL PLUGIN sql_error_log SONAME 'sql_errlog';

less /var/lib/mysql/sql_errors.log

#########################

upgrad lab not done as latset installed , will try on centos lab as somecommands no working on rocky

#########################

mysql

SELECT VERSION();

exit

##################################################################################

Lab Exercises for Lesson 2: Server Internals

Lab Exercise 2-1: Collecting Server Information

################c2

SHOW VARIABLES;

SHOW GLOBAL STATUS;

SHOW STATUS LIKE '%sort%';

SHOW STATUS LIKE 'handler%';

######################################################################################

Lab Exercises for Lesson 3: Schema Objects

Lab Exercise 3-1: Databases and Tables

Lab Exercise 3-2: Loading Data

Lab Exercise 3-3: Default Character Set

Lab Exercise 3-4: Views, Triggers & Events

Lab Exercise 3-5: Create a Stored Procedure

######################c3

mariadb

CREATE DATABASE sales;

SHOW DATABASES;

USE sales;

CREATE TABLE offices (

officeCode VARCHAR(10) NOT NULL,

city VARCHAR(50) NOT NULL,

addressLine1 VARCHAR(50) NOT NULL,

addressLine2 VARCHAR(50) DEFAULT NULL,

state VARCHAR(50) DEFAULT NULL,

country VARCHAR(50) NOT NULL,

postalCode VARCHAR(15) NOT NULL,

territory VARCHAR(10) NOT NULL,

PRIMARY KEY (officeCode)) ENGINE=Aria;

SHOW TABLES;

ALTER TABLE offices ADD COLUMN phone VARCHAR(50) NOT NULL AFTER

city;

DESCRIBE offices;

exit

ls -al /var/lib/mysql/sales

mariadb sales < /root/mariadb-training/sample_databases/sales/sales_offices.sql

mariadb sales

SELECT * FROM offices;

CREATE TABLE employees (

employeeNumber INT(11) NOT NULL,

lastName VARCHAR(50) NOT NULL,

firstName VARCHAR(50) NOT NULL,

extension VARCHAR(10) NOT NULL,

email VARCHAR(100) NOT NULL,

officeCode VARCHAR(10) NOT NULL,

reportsTo INT(11) DEFAULT NULL,

jobTitle VARCHAR(50) NOT NULL,

PRIMARY KEY (employeeNumber));

ALTER TABLE employees ADD fullName varchar(100)

GENERATED ALWAYS AS (CONCAT(firstName," ",lastName))

VIRTUAL AFTER employeeNumber;

exit

mariadb sales < /root/mariadb-training/sample_databases/sales/sales_employees.sql

mariadb sales

SELECT employeeNumber,fullName,lastName,firstName FROM employees;

DESCRIBE employees;

SHOW CREATE TABLE employees\G

mariadb -e "CREATE DATABASE world"

mariadb

SHOW DATABASES;

exit

mariadb world < /root/mariadb-training/sample_databases/world/world.sql

cd /root/mariadb-training/sample_databases/employees

gzip -d *.gz

mariadb < /root/mariadb-training/sample_databases/employees/employees.sql

mariadb

SHOW DATABASES;

use world;

show tables;

use employees;

show tables;

exit

###

CREATE DATABASE metropolis DEFAULT CHARACTER SET latin1;

CREATE DATABASE gotham DEFAULT CHARACTER SET utf8;

SHOW CREATE DATABASE metropolis;

SHOW CREATE DATABASE gotham;

USE gotham;

CREATE TABLE test_from_gotham (id SERIAL, str VARCHAR(100) NOT NULL);

SHOW CREATE TABLE test_from_gotham\G

RENAME TABLE test_from_gotham TO metropolis.test_from_gotham;

SHOW CREATE TABLE metropolis.test_from_gotham\G

USE metropolis;

CREATE TABLE gotham.test_from_metropolis (id serial, str varchar(100) NOT NULL);

SHOW CREATE TABLE gotham.test_from_metropolis\G

SHOW CREATE TABLE test_from_gotham\G

ALTER TABLE test_from_gotham ADD str2 VARCHAR(100) CHARACTERSET latin1 NOT NULL;

SHOW CREATE TABLE test_from_gotham\G

INSERT INTO test_from_gotham(str,str2) VALUES ("HËLLÖ", "HËLLÖ");

SELECT * FROM test_from_gotham;

exit

###

mariadb world

CREATE VIEW CityView AS SELECT Id,Name FROM City;

SHOW FULL TABLES;

SHOW FULL TABLES WHERE Table_type = 'VIEW';

SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';

SELECT * FROM CityView;

ALTER VIEW CityView AS

SELECT Id,Name,Population FROM City

WHERE Population >= 999999;

SELECT * FROM CityView;

SHOW CREATE TABLE CityView\G

DESCRIBE CityView;

USE world;

CREATE PROCEDURE hello_world() SELECT 'This is the procedure you are looking for!';

SHOW CREATE PROCEDURE hello_world\G

CALL hello_world();

#####################################################################################

Lab Exercises for Lesson 4: Storage Engines

Lab Exercise 4-1: Changing the Default Storage Engine

Lab Exercise 4-2: Creating and Comparing Tables for Multiple Storage Engines

####################c4

mariadb

CREATE DATABASE just_testing;

USE just_testing;

CREATE TABLE engines (id serial);

SHOW CREATE TABLE engines\G

ALTER TABLE engines ENGINE=Aria;

SHOW CREATE TABLE engines\G

SET SESSION default_storage_engine=MyISAM;

CREATE TABLE engines2 (id serial) ENGINE=InnoDB;

SHOW CREATE TABLE engines2\G

SET SESSION enforce_storage_engine=Aria;

CREATE TABLE engines3 (id serial) ENGINE=InnoDB;

exit

USE just_testing;

DROP TABLE engines;

DROP TABLE engines2;

exit

mariadb just_testing

CREATE TABLE t_aria(a int) ENGINE=Aria;

CREATE TABLE t_innodb(a int) ENGINE=InnoDB;

exit

yum -y install MariaDB-rocksdb-engine

mariadb just_testing

INSTALL SONAME 'ha_rocksdb';

SHOW PLUGINS;

SHOW ENGINES;

CREATE TABLE t_myrocks(a int) ENGINE=RocksDB;

SHOW CREATE TABLE t_aria;

SHOW CREATE TABLE t_innodb;

SHOW CREATE TABLE t_myrocks;

SHOW TABLES;

CREATE TABLE t_number (number INT) ENGINE=Aria;

INSERT INTO t_number

VALUES

(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO t_aria

SELECT hundredthousands.number 100000 + tenthousands.number 10000 +

thousands.number 1000 + hundreds.number 100 + tens.number * 10 + ones.number

FROM t_number hundredthousands, t_number tenthousands, t_number thousands,

t_number hundreds, t_number tens, t_number ones

LIMIT 1000000;

INSERT INTO t_innodb

SELECT hundredthousands.number 100000 + tenthousands.number 10000 +

thousands.number 1000 + hundreds.number 100 + tens.number * 10 + ones.number

FROM t_number hundredthousands, t_number tenthousands, t_number thousands,

t_number hundreds, t_number tens, t_number ones

LIMIT 1000000;

SET SESSION rocksdb_max_row_locks=10000000;

INSERT INTO t_myrocks

SELECT hundredthousands.number 100000 + tenthousands.number 10000 +

thousands.number 1000 + hundreds.number 100 + tens.number * 10 + ones.number

FROM t_number hundredthousands, t_number tenthousands, t_number thousands,

t_number hundreds, t_number tens, t_number ones

LIMIT 1000000;

exit

ls -lrth /var/lib/mysql/just_testing/t_aria.MAD

ls -lrth /var/lib/mysql/just_testing/t_innodb.ibd

ls -lrth /var/lib/mysql/#rocksdb/*.log

#############################################################################

Lab Exercises for Lesson 5: Transactions

Lab Exercise 5-1: Rollback a Transaction

Lab Exercise 5-2: Observing a Deadlock

#############c5

mariadb world

SHOW TABLE STATUS\G

SELECT SUM(Population) FROM City;

START TRANSACTION;

UPDATE City SET Population=0;

SELECT SUM(Population) FROM City;

ROLLBACK;

SELECT SUM(Population) FROM City;

exit

mariadb world

UPDATE City SET Name='Londres' where name='London';

DELETE FROM Country where Code='ITA';

UPDATE City SET Name='Londres' where name='London';

DELETE FROM Country where Code='ITA';

DELETE FROM City where name='London';

UPDATE Country SET Population=900 WHERE code='ITA';

COMMIT;

SHOW ENGINE innodb STATUS\G

exit

cat /var/lib/mysql/sql_errors.log

###################################################################################

Lab Exercises for Lesson 6: User Management

Lab Exercise 6-1: Creating and Managing Users and Roles

################c6

mariadb

CREATE USER 'me'@'%' identified by 'mariadb';

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'mariadb';

GRANT SELECT, RELOAD, LOCK TABLES, SUPER, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON . TO 'backupuser'@'localhost';

mariadb -hlocalhost -ume -pmariadb

exit

CREATE ROLE dba;

CREATE ROLE developer;

GRANT SUPER, SHUTDOWN ON . to 'dba';

GRANT SELECT ON . to 'developer';

SELECT * FROM information_schema.APPLICABLE_ROLES \G

GRANT dba to 'me'@'%';

GRANT developer to 'me'@'%';

SELECT CONCAT(User,'@',Host) AS user FROM mysql.roles_mapping WHERE role='dba';

SELECT Role FROM mysql.roles_mapping WHERE User='me' AND Host='%';

SHUTDOWN;

SELECT * FROM employees.salaries LIMIT 10;

SET ROLE dba;

SELECT * FROM employees.salaries LIMIT 10;

SET DEFAULT ROLE developer;

exit

mariadb -hlocalhost -ume -pmariadb

SELECT CURRENT_ROLE();

SELECT * FROM employees.salaries LIMIT 10;

ALTER USER me@'%' WITH MAX_USER_CONNECTIONS 20 MAX_CONNECTIONS_PER_HOUR 300;

###############################################################################

Lab Exercises for Lesson 7: Security

Lab Exercise 7-1: Enabling & Configuring Password Validation

Lab Exercise 7-2: Installing the Audit Plug-In

#############c7

mariadb

CREATE USER 'passtest'@'localhost' IDENTIFIED BY 'mariadb';

INSTALL SONAME 'simple_password_check';

exit

vi /etc/my.cnf.d/server.cnf

[mariadb]

simple_password_check_digits=1

simple_password_check_letters_same_case=1

simple_password_check_minimal_length=8

simple_password_check_other_characters=1

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

mariadb

SET PASSWORD FOR 'passtest'@'localhost' = PASSWORD('abc');

SET PASSWORD FOR 'passtest'@'localhost' = PASSWORD('abcd1234');

SET PASSWORD FOR 'passtest'@'localhost' = PASSWORD('aBcd123%');

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME like '%AUDIT%';

INSTALL PLUGIN server_audit SONAME 'server_audit';

SHOW GLOBAL VARIABLES like 'server_audit%';

exit

vi /etc/my.cnf.d/server.cnf

[mariadb]

server_audit_logging=ON

server_audit_events=connect,table

server_audit_excl_users=backupuser

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

mariadb

select user,host from mysql.user;

exit

less /var/lib/mysql/server_audit.log

#####################################################################################

Lab Exercises for Lesson 8: Business Continuity

Lab Exercise 8-1: Creating and Restoring Backups with mariadb-dump

Lab Exercise 8-2: Creating and Restoring Backups with MariaDB Backup

Lab Exercise 8-3: Point-in-Time Recovery

###########c8

mkdir /var/log/mariadb

chown -R mysql:mysql /var/log/mariadb

vi /etc/my.cnf.d/server.cnf

[mariadb]

log_bin= /var/log/mariadb/master1-bin

log_bin_index= /var/log/mariadb/master1-bin.index

binlog_format=ROW

expire_logs_days=10

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

ls -las /var/log/mariadb/

mariadb-dump --master-data=2 --all-databases --single-transaction > /root/dump.sql

mariadb< /root/dump.sql

MariaDB [(none)]> SOURCE /root/dump.sql;

###

which mariabackup

yum -y install MariaDB-backup

which mariabackup

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'Q1q2w3e4r999#';

Q1q2w3e4r999#

GRANT SELECT, RELOAD, LOCK TABLES, SUPER, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON . TO 'backupuser'@'localhost';

mariabackup --backup --no-lock --target-dir /root/backups --user backupuser --password Q1q2w3e4r999# --port 3306 --socket /var/lib/mysql/mysql.sock

ls -al /root/backups

mariabackup --prepare --target-dir /root/backups

systemctl stop mariadb

ls -la /var/lib/mysql/

rm -rf /var/lib/mysql/*

ls -la /var/lib/mysql/

mariabackup --copy-back --target-dir /root/backups/ --datadir /var/lib/mysql/

ls -la /var/lib/mysql/

chown -R mysql:mysql /var/lib/mysql

ls -la /var/lib/mysql/

systemctl start mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

mariadb

exit

mariadb world

SHOW BINARY LOGS;

FLUSH LOGS;

SHOW BINARY LOGS;

SELECT Name,Population from City where ID=456;

DROP TABLE City;

UPDATE Country SET Population=82000000 WHERE Code='DEU';

SELECT Name,Population from City where ID=456;

exit

cat /root/dump.sql | less

systemctl stop mariadb

cd /var/log/mariadb/

mariadb-binlog master1-bin.000002 | grep -i DROP

mariadb-binlog master1-bin.000003 | grep -i DROP

mariadb-binlog master1-bin.000004 | grep -i DROP

mariadb-binlog master1-bin.000004 | grep -B 12 -A 4 -i DROP

[root@mariadb-xray mariadb]# nano RECOVER

1. recover from backup

2. replay master1-bin.000001 330 # the position from where the backup was taken

3. stop replaying master1-bin.000004 pos 431

4. start replaying master1-bin.000004 pos 551

nano /etc/my.cnf.d/server.cnf

[mariadb]

read-only

systemctl start mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

mariadb < /root/dump.sql

mariadb-binlog --start-position=330 /var/log/mariadb/master1-bin.000001 | mariadb

mariadb-binlog --stop-position=431 /var/log/mariadb/master1-bin.000004 | mariadb

mariadb-binlog --start-position=551 /var/log/mariadb/master1-bin.000004 | mariadb

mariadb world

SELECT Name,Population from City where ID=456;

SELECT Name,Population from Country WHERE Code='DEU';

nano /etc/my.cnf.d/server.cnf

vi /etc/my.cnf.d/server.cnf

[mariadb]

# read-only

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

############################################################################################

Lab Exercises for Lesson 9: Resource Usage

Lab Exercise 9-1: Memory Consumption

MariaDB Standard DBA Lab Exercises

Lab Exercise 9-2: Benchmarking with mariadb-slap

#############c9

free -h

cat /proc/sys/vm/swappiness

echo 1 > /proc/sys/vm/swappiness

nano /etc/sysctl.conf

vm.swappiness=1

free -h

swapoff -a

free -h

swapon -a

free -h

nano /etc/my.cnf.d/server.cnf

[mariadb]

## key_buffer_size is used by MyISAM tables so if you have MariaDB

## server that does not have MyISAM tables then this can be set to

## 8M or, if you wish to be conservative since key_buffer_size is

## only used for the mysql database tables, 16M

key_buffer_size=8M

## table_open_cache is counted in slots so if you have 400 tables

## and your join depth is a maximum of 3 joins then 1200 will be

## an adequate size because every tables takes 2 slots in the

## table_open_cache

table_open_cache=1200

## table_definition_cache is the number of .FRM files plus the

## schema definition files so if you have 400 tables then set

## table_definition_cache to 512

table_definition_cache=512

## sort-buffer-size is an on-demand or schema buffer. If your

## indexes cannot deal with GROUP BY or ORDER BY then the sort-buffer

## is used. Its default is 2MB, but make sure this is set to the

## max connections times the sum of the session buffers.

sort_buffer_size=16M

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

top

mariadb-slap -a

mariadb-slap -a -i 10

mariadb-slap -a -i 10 -e InnoDB,MyISAM

mariadb-slap -a -i 10 -e InnoDB,MyISAM -c 4

vmstat

mariadb-slap -a -i 10 -e InnoDB,MyISAM -c 64

mariadb-slap --delimiter=";" --create-schema=slappy --create="CREATE TABLE t (a int);INSERT INTO t VALUES (5)" --query="SELECT * FROM t" --concurrency=100 --iterations=150

mariadb-slap --delimiter=";" --create-schema=slappy --create="CREATE TABLE t (a int);INSERT INTO t VALUES (5)" --query="SELECT * FROM t" --concurrency=100 --iterations=150 > /tmp/load_test.txt

##################################################################################

Lab Exercises for Lesson 10: Tuning & Optimization

Lab Exercise 10-1: Query Performance

Lab Exercise 10-2: Using the Information Schema’s Profiling Table

Lab Exercise 10-3: Indexing for Performance Optimization

##########ch10

nano /etc/my.cnf.d/server.cnf

[mariadb]

slow_query_log=1

slow_query_log_file=slow_query.log

long_query_time=1.0

log_queries_not_using_indexes=true

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

mariadb

SELECT SLEEP(5);

exit

cat /var/lib/mysql/slow_query.log

mariadb

SET PROFILING=ON;

SELECT Name,Population FROM world.City

WHERE CountryCode='USA'

ORDER BY Population DESC LIMIT 5;

SHOW PROFILES;

SHOW PROFILE FOR QUERY 1;

SHOW PROFILE CPU FOR QUERY 1;

exit

mariadb world

SHOW CREATE TABLE City\G

SELECT Name,Population FROM world.City WHERE CountryCode='USA' ORDER BY Population DESC LIMIT 5;

EXPLAIN SELECT Name,Population FROM world.City WHERE CountryCode='USA' ORDER BY Population DESC LIMIT 5\G

ALTER TABLE City ADD INDEX (CountryCode);

EXPLAIN SELECT Name,Population FROM world.City WHERE CountryCode='USA' ORDER BY Population DESC LIMIT 5\G

ALTER TABLE City ADD INDEX (CountryCode, Population);

EXPLAIN SELECT Name,Population FROM world.City WHERE CountryCode='USA' ORDER BY Population DESC LIMIT 5\G

ALTER TABLE City ADD INDEX (CountryCode,Population,Name);

EXPLAIN SELECT Name,Population FROM world.City WHERE CountryCode='USA' ORDER BY Population DESC LIMIT 5\G

ALTER TABLE City DROP INDEX CountryCode,DROP INDEX CountryCode_3;

###############################################################################################

Lab Exercises for Lesson 11: HA Fundamentals

Lab Exercise 11-1: Setting Up MariaDB Replication

############11

## need to add firwall config and port testing for red hat as lab is centos base wih no firwall ####

nano /etc/my.cnf.d/server.cnf

[mariadb]

server_id=1

log_basename=master1

mariadb

GRANT REPLICATION SLAVE ON . TO repuser@'%' IDENTIFIED BY 'aBcd123%';

SELECT * FROM mysql.user WHERE user='repuser'\G

exit

systemctl restart mariadb

journalctl -u mariadb

systemctl status mariadb.service -l

mariadb

SHOW MASTER STATUS;

exit

mariadb-dump --master-data=2 --all-databases --single-transaction > /root/fulldump.sql

ip a

192.168.0.147

scp /root/fulldump.sql root@192.168.0.147:~/fulldump.sql

rsync -av --progress /root/fulldump.sql centos@192.168.0.147:~/fulldump.sql

mkdir /var/log/mariadb

chown -R mysql:mysql /var/log/mariadb

nano /etc/my.cnf.d/server.cnf

[mariadb]

read_only

server_id=2

log_basename=slave1

log_bin=/var/log/mariadb/slave1-bin

log-bin-index=/var/log/mariadb/slave1-bin.index

systemctl restart mariadb

ls -l

mariadb < fulldump.sql

mariadb

show databases;

exit

head -30 fulldump.sql | tail -5

mariadb

SET GLOBAL gtid_slave_pos = '0-1-1';

MariaDB [(none)]> CHANGE MASTER TO

MASTER_HOST='192.168.0.146',

MASTER_USER='repuser',

MASTER_PASSWORD='aBcd123%',

MASTER_USE_GTID = slave_pos;

START SLAVE;

SHOW SLAVE STATUS\G

mariadb

CREATE DATABASE mordor;

show databases;

SHOW PROCESSLIST;

SHOW SLAVE STATUS\G

##############################################################################################

Lab Exercises for Lesson 12: Advanced Features

Lab Exercise 12-1: Using Common Table Expressions

Lab Exercise 12-2: Using Window Functions

#############c12

systemctl rstart mariadb

mariadb employees

SHOW TABLES;

WITH BiggestDeptCTE AS (SELECT dept_no,

COUNT(*) OVER (PARTITION BY dept_no)

FROM dept_emp ORDER BY 2 DESC LIMIT 1)

SELECT employees.*, dept_emp.dept_no

FROM employees INNER JOIN dept_emp

ON employees.emp_no = dept_emp.emp_no

INNER JOIN BiggestDeptCTE

ON dept_emp.dept_no = BiggestDeptCTE.dept_no

LIMIT 10;

WITH SalesStaff AS (SELECT

CONCAT(UPPER(last_name), ' ', first_name) AS FullName, gender

FROM employees

JOIN dept_emp ON employees.emp_no = dept_emp.emp_no

JOIN departments ON dept_emp.dept_no = departments.dept_no

AND departments.dept_name="Sales" )

SELECT * FROM SalesStaff

WHERE SalesStaff.gender = "F"

ORDER BY FullName;

CREATE DATABASE family;

USE family;

CREATE TABLE members (

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

name varchar(50) DEFAULT NULL,

motherid bigint(20) DEFAULT NULL,

fatherid bigint(20) DEFAULT NULL,

FamilyName varchar(50) DEFAULT NULL,

UNIQUE KEY id (id))

ENGINE=InnoDB

AUTO_INCREMENT=0

DEFAULT CHARSET=latin1;

INSERT INTO members

(id, name, FamilyName, motherid, fatherid)

VALUES

(1,'Esme','Reynolds',4,3),

(2,'Aidan','Reynolds',4,3),

(3,'Kevin','Reynolds',5,6),

(4,'Sam','Little',8,7),

(5,'Rosie','Reynolds',10,9),

(6,'David','Reynolds',11,12),

(7,'Mark','Little',14,13),

(8,'Heather','Little',16,15),

(9,'Eric','Donaldson',NULL,NULL),

(10,'Vera','Donaldson',NULL,NULL),

(11,'Rebe', 'Reynolds',NULL,NULL),

(12,'Tommy', 'Reynolds',NULL,NULL),

(13,'Mike','Little',NULL,NULL),

(14,'Julia','Little',NULL,NULL),

(15,'Leo','Jones',NULL,NULL),

(16,'Francis','Jones',NULL,NULL),

(17,'Morgan','Reynolds',5,6),

(18,'Duncan','Reynolds',5,6),

(19,'Jordan','Reynolds',5,6);

WITH RECURSIVE ParentCTE AS

(SELECT Child.* FROM members Child

WHERE Child.name='Esme'

UNION

SELECT Parent.*

FROM members AS Parent, ParentCTE AS a

WHERE Parent.id = a.fatherid OR Parent.id = a.motherid)

SELECT ParentCTE.id,

concat(ParentCTE.name,' ',ParentCTE.FamilyName) AS

'Child Name',

MotherName.name as 'Mother Name',

FatherName.name as 'Father Name'

FROM ParentCTE

LEFT JOIN members MotherName

ON ParentCTE.motherid = MotherName.id

LEFT JOIN members FatherName

ON ParentCTE.fatherid = FatherName.id;

WITH RECURSIVE ParentCTE AS

(SELECT Child.* FROM members Child

WHERE Child.name='Morgan'

UNION

SELECT Parent.*

FROM members AS Parent, ParentCTE AS a

WHERE Parent.id = a.fatherid OR Parent.id = a.motherid)

SELECT ParentCTE.id,

concat(ParentCTE.name, ' ', ParentCTE.FamilyName) AS

'Child Name',

MotherName.name as 'Mother Name',

FatherName.name as 'Father Name'

FROM ParentCTE

LEFT JOIN members MotherName

ON ParentCTE.motherid = MotherName.id

LEFT JOIN members FatherName

ON ParentCTE.fatherid = FatherName.id;

WITH RECURSIVE ParentCTE AS

(SELECT Child.* FROM members Child

WHERE Child.name='Aidan'

UNION

SELECT Parent.*

FROM members AS Parent, ParentCTE AS a

WHERE Parent.id = a.fatherid

OR Parent.id = a.motherid )

SELECT ParentCTE.id,

concat(ParentCTE.name,' ', ParentCTE.FamilyName)

AS 'Child Name',

IFNULL(MotherName.name,'Maternal Parent Not Recorded')

AS 'Mother Name',

IFNULL(FatherName.name,'Paternal Parent Not Recorded')

AS 'Father Name'

FROM ParentCTE

LEFT JOIN members MotherName

ON ParentCTE.motherid = MotherName.id

LEFT JOIN members FatherName

ON ParentCTE.fatherid = FatherName.id;

######

mariadb

use employees;

SELECT dept_no AS "Department Number",

dept_name AS "Department Name"

FROM departments;

SELECT ROW_NUMBER() OVER() AS RowNum,

dept_no as "Department Number",

dept_name as "Department Name"

FROM departments;

SELECT ROW_NUMBER() OVER(ORDER BY dept_no ASC) AS RowNum, dept_no as "Department Number", dept_name as "Department Name" FROM departments;

SELECT ROW_NUMBER() OVER(ORDER BY dept_no) AS RowNum,

dept_no, emp_no, from_date, to_date

FROM dept_manager ORDER BY RowNum;

SELECT ROW_NUMBER() OVER(PARTITION BY dept_no

ORDER BY dept_no) AS RowNum,

dept_no, emp_no, from_date, to_date

FROM dept_manager ORDER BY 2,1;

CREATE TABLE events (

id serial,

EventTime timestamp,

eventValue decimal(10,5)

);

INSERT INTO events (EventTime, eventValue)

SELECT Current_Timestamp() + INTERVAL RAND()*100 HOUR,

RAND()*100 FROM information_schema.columns X;

SELECT eventTime, eventValue, AVG(eventValue)

OVER(ORDER BY eventTime) AS Average

FROM events

ORDER by eventTime limit 20;

SELECT eventTime, eventValue, AVG(eventValue)

OVER(ORDER BY eventTime ROWS

BETWEEN 5 PRECEDING AND 5 FOLLOWING) AS RelativeAverage

FROM events

ORDER by eventTime limit 20;

SELECT eventTime, eventValue, SUM(eventValue)

OVER(ORDER BY eventTime ROWS

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal

FROM events ORDER by eventTime limit 20;

######################################################################################

MariaDB Standard DBA Lab Exercises

Related Posts

Windows laptop only boots from USB-C and not from a standard USB

Verify VPN and test on Windows 11

Intune Onboarding: The Power of the Hardware Hash Script