MariaDB

Install

sudo apt install mariadb-server -y

sudo mariadb --version

# Check status
sudo systemctl status mariadb

Connect

# -p can not allow space
mariadb -h IP -u USERNAME -pPASSW0RD DATABASE_NAME

# Example
mariadb -u root -p123123

Maintain

# Restart
# Ubuntu 22.04
sudo systemctl restart mariadb

SQL

-- '%' login everywhere
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'passw0rd';

-- Change user password
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'Passw0rd';

DROP USER 'user1'@'localhost';

-- Reload the in-memory copy of privileges from the privilege tables
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON * . * TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'localhost';

SHOW DATABASES;

SHOW TABLES;

-- List users.
SELECT User FROM mysql.user;

-- Show grants
SHOW GRANTS FOR 'user1'@'localhost';

SHOW character set;

SHOW variables like "character_set%";

-- Remove grants
REVOKE ALL ON db1.* FROM 'user1'@'localhost'
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost';

CREATE DATABASE db1;

DROP DATABASE db;

# Check port
SHOW GLOBAL VARIABLES LIKE 'PORT';

Cluster

Use Master/Slave Replication if Cluster cause performance issues.

- vmubu2204lab1: db 1
  192.168.100.2
  dev 123123

- vmubu2204lab2: db 2
  192.168.100.3
  dev 123123
  
- vmubu2204lab3: load balance
  192.168.100.4
  dev 123123

Ubuntu 22.04

sudo apt-get install mariadb-server -y

sudo mysql_secure_installation

mariadb -h localhost -u root -p123123

# system service tools (SST)
GRANT USAGE ON *.* to sst_user@'%' IDENTIFIED BY 'pa55word';

GRANT ALL PRIVILEGES on *.* to sst_user@'%';

FLUSH PRIVILEGES;

sudo vim /etc/mysql/my.cnf

sudo vim /etc/mysql/mariadb.conf.d/60-galera.cnf

[galera]
wsrep_on = ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.100.2,192.168.100.3"
wsrep_slave_threads=4 # Default 1, 4, 8
binlog_format=ROW
default_storage_engine=InnoDB
bind-address=0.0.0.0
wsrep_node_name=node1
wsrep_node_address="192.168.100.2"
wsrep_sst_method=rsync
wsrep_sst_auth=sst_user:pa55word
log_error = /var/log/mysql/error.log

wsrep_node_name=node2
wsrep_node_address="192.168.100.3"

sudo systemctl stop mariadb

sudo galera_new_cluster

sudo systemctl restart mariadb

sudo cat /var/lib/mysql/grastate.dat

mariadb -u root -p123123

show status like 'wsrep_cluster_size';

# node 1
create database node1;

# node 1, 2
show databases;

# node 2
create database node2;

sudo apt-get install haproxy -y

sudo vim /etc/haproxy/haproxy.cfg

Insert in top:

listen galera
    bind 192.168.100.4:3306
    balance roundrobin #  load balancer policy
    mode tcp # (tcp is layer 7 , http is layer 4)
    option tcpka # enable keepalive to maintain tcp connection
    option mysql-check user haproxy # enable database server check
    server node1 192.168.100.2:3306 check weight 1
    server node2 192.168.100.3:3306 check weight 1

listen stats
   bind 0.0.0.0:9000
   mode http
   stats enable # Enable
   stats uri /stats   # Url path
   stats realm HAProxy\ Statistics
   stats auth howtoforge:howtoforge
   stats admin if TRUE
   stats refresh 30s # Refresh page each 30 sec

sudo systemctl restart haproxy

# Node 1
CREATE USER 'haproxy'@'192.168.100.4';

FLUSH PRIVILEGES;

# Node 1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123123' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# Node 1
mariadb -u root -p123123 -e "SET GLOBAL server_id=102"

# Node 2
mariadb -u root -p123123 -e "SET GLOBAL server_id=103"

# Node 3
sudo apt install mariadb-client
mariadb -h 192.168.100.4 -u root -p123123 -e "show variables like 'server_id'"

Scenario

Current charset is UTF-8. If password has been set using other charset will make database username or password is not correct.

Remote Access

/etc/mysql/mariadb.conf.d/50-server.cnf

bind-address = 0.0.0.0