mariadb/README.md

Usage|

verion	image
10.5.12	registry.gitlab.com/markuman/mariadb:10.5
10.4.13	registry.gitlab.com/markuman/mariadb:10.4
10.3.22	registry.gitlab.com/markuman/mariadb:10.3
10.2.31	registry.gitlab.com/markuman/mariadb:10.2

Start with docker run -d -p 3306:3306 --name mariadb registry.gitlab.com/markuman/mariadb:10.4

Or with persistence shared folder with host: docker run -ti --name mariadb -v $(pwd)/test:/var/lib/mysql/ -p 3306:3306 registry.gitlab.com/markuman/mariadb:10.4

Init with

docker exec -ti mariadb mysql -e "create user 'm'@'%' identified by 'nomysql1';"
docker exec -ti mariadb mysql -e "grant all privileges on *.* to 'm'@'%' with grant option;"

option

ENV	Default Value	Notes
SLOW_LOG	true	Log location is /var/lib/mysql/slow_query.log
READ_ONLY		used to set mariadb into read only mode -e READ_ONLY="--read-only"
SERVER_ID	1	so change the server id
QUERY_CACHE	OFF	Query Cache can be enabled with -e QUERY_CACHE=ON. Cache Size is static 10M
MAX_ALLOW_PACKET	16M	Max allow packets with -e MAX_ALLOW_PACKET=64M
MAX_CONNECTIONS	100	Max connections with -e MAX_CONNECTIONS=500
INNODB_BUFFER_POOL	256M	Innodb pool buffer size with

create replica from writer node

1. create replica user

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

2. dump

docker exec -ti mariadb mysqldump --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > writer_dump.sql

and mv writer_dump.sql into volume/mount of read replica.

3. create read replica

grep informations about bin log position

grep "CHANGE MASTER TO MASTER_LOG_FILE" data/writer_dump.sql |head -n 1
CHANGE MASTER TO MASTER_LOG_FILE='77abd7ecc317-bin.000001', MASTER_LOG_POS=39867;

start read replica

docker run -d --rm -v $(pwd)/data:/var/lib/mysql/  --name mariadb-ro -e READ_ONLY="--read-only" --network db -e SERVER_ID=10 -p 127.0.0.1:3310:3306 markuman/mariadb:alpine-10.4

jump into container and apply dump

mysql < writer_dump.sql
mysql

CHANGE MASTER TO
MASTER_HOST='mariadb',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='77abd7ecc317-bin.000001',
MASTER_LOG_POS=39867,
MASTER_CONNECT_RETRY=10;

start slave;

and you're done.

check with SHOW SLAVE STATUS \G

proxysql

create monitor user for proxysql

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT on sys.* to 'monitor'@'%';