Usage|

2020-04-09

verion	image
10.4.12	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

to enable slow_query_log, use -e SLOW_LOG=true.
Log location is /var/lib/mysql/slow_query.log.

to set mariadb into read only mode, use -e READ_ONLY="--read-only".

server id can be set with -e SERVER_ID=10.

Query Cache can be enabled with -e QUERY_CACHE=ON.

Max allow packets with -e MAX_ALLOW_PACKET=64M.

Max connections with -e MAX_CONNECTIONS=500.

Innodb pool buffer size with -e INNODB_BUFFER_POOL=1G.

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'@'%';