Docker Haproxy及MariaDB 10.3 Galera Cluster建置

docker pull mariadb:10.3

下載 haproxy1.7.9

docker pull haproxy:alpine

haproxy設定檔

global 
chroot /usr/local 
daemon 
nbproc 1 
group nobody 
user nobody 
#pidfile /opt/haproxy/logs/haproxy.pid 
ulimit-n 65536 
#spread-checks 5m 
#stats timeout 5m 
#stats maxconn 100 



########預設配置############ 

defaults 
mode tcp 
retries 3 #兩次連接失敗就認為是伺服器不可用，也可以通過後面設置 
option redispatch #當serverId對應的伺服器掛掉後，強制定向到其他健康的伺服器 
option abortonclose #當伺服器負載很高的時候，自動結束掉當前佇列處理比較久的連結 
maxconn 32000 #默認的最大連接數 
timeout connect 5000ms #連接逾時 
timeout client 30000ms #用戶端超時 
timeout server 30000ms #伺服器超時 
#timeout check 2000 #心跳檢測超時 
log 127.0.0.1 local0 err #[err warning info debug] 

########MariaDB配置################# 

listen mariadb 
bind 0.0.0.0:3306 
mode tcp 
balance roundrobin 
server s1 192.168.1.38:3307 weight 1 maxconn 10000 check inter 10s 
server s2 192.168.1.38:3308 weight 1 maxconn 10000 check inter 10s 
server s3 192.168.1.38:3309 weight 1 maxconn 10000 check inter 10s

vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost 
127.0.0.1 DKnetw 
127.0.0.1 localhost 
127.0.0.1 DKnetw.localdomain4.localdomain4 
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 

192.168.1.43 zabbix.tony 
192.168.1.38 docker.tony 

192.168.1.38 mariadb-0 
192.168.1.38 mariadb-1 
192.168.1.38 mariadb-2

mkdir /home/docker/mariadb/cluster0/ 
mkdir /home/docker/mariadb/cluster0/conf 
mkdir /home/docker/mariadb/cluster0/data 
mkdir /home/docker/mariadb/cluster1/ 
mkdir /home/docker/mariadb/cluster1/conf 
mkdir /home/docker/mariadb/cluster1/data 
mkdir /home/docker/mariadb/cluster2/ 
mkdir /home/docker/mariadb/cluster2/data 
mkdir /home/docker/mariadb/cluster2/conf

[server] 
[mysqld] 
server_id=130 
pid-file=/var/run/mysqld/mysqld.pid 
socket=/var/run/mysqld/mysqld.sock 
basedir=/usr 
datadir=/var/lib/mysql 
tmpdir=/tmp 
user=mysql 
skip-external-locking 
skip-name-resolve 
character-set-server=utf8 
port=3306 


# 
# localhost which is more compatible and is not less secure. 
#bind-address = 127.0.0.1 
[server] 
[mysqld] 
server_id=130 
pid-file=/var/run/mysqld/mysqld.pid 
socket=/var/run/mysqld/mysqld.sock 
basedir=/usr 
datadir=/var/lib/mysql 
tmpdir=/tmp 
user=mysql 
skip-external-locking 
skip-name-resolve 
character-set-server=utf8 
port=3306 


# 
# Instead of skip-networking the default is now to listen only on 
# localhost which is more compatible and is not less secure. 
#bind-address = 127.0.0.1 
# 
# * Fine Tuning 
# 
max_connections=1000 
connect_timeout=5 
wait_timeout=600 
max_allowed_packet=100M 
thread_cache_size=128 
sort_buffer_size=4M 
bulk_insert_buffer_size =16M 
tmp_table_size=32M 
max_heap_table_size =32M 
[galera] 
wsrep_causal_reads=ON #節點應用完事務才返回查詢請求 
wsrep_provider_options="gcache.size=128M"#同步複製緩衝集區 
wsrep_certify_nonPK=ON #為沒有顯式申明主鍵的表生成一個用於certificationtest的主鍵，默認為ON 
#log-bin=/app/galera/mysql-bin #如果不接從庫，注釋掉 
#log_slave_updates=1 #如果不接從庫，注釋掉 
query_cache_size=0 #關閉查詢緩存 
wsrep_on=ON #開啟全同步複製模式 
wsrep_provider=/usr/lib/galera/libgalera_smm.so #galera library 
wsrep_cluster_name=MariaDB-Galera-Cluster 
#wsrep_cluster_address="gcomm://192.168.1.38:4567,192.168.1.38:4568,192.168.1.38:4569" #galera cluster URL 
wsrep_node_name=mariadb-0 
#wsrep_node_address=172.18.0.4 
wsrep_sst_auth=syncuser:syncuser 
#wsrep_sst_method=xtrabackup-v2 
wsrep_sst_method=rsync 
binlog_format=row 
default_storage_engine=InnoDB 
innodb_autoinc_lock_mode=2 #主鍵自增模式修改為交叉模式 
wsrep_slave_threads=8 #開啟並行複製執行緒，根據CPU核數設置 
innodb_flush_log_at_trx_commit=0 #事務提交每隔1秒刷盤 
innodb_buffer_pool_size=2G 
[embedded] 
[mariadb] 
[mariadb-10.3]

wsrep_node_name=mariadb-1

wsrep_node_name=mariadb-2

啟動第一個節點

docker run -d --name mariadb-cluster0 --expose 4567 -p 4567:4567 -p 3307:3306 -e TIMEZONE=Asia/Taipei -v /home/docker/mariadb/cluster0/conf:/etc/mysql/conf.d -v /home/docker/mariadb/cluster0/data:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yse mariadb:10.3

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '' WITH GRANT OPTION; 
GRANT ALL PRIVILEGES ON *.* TO 'syncuser'@'%'IDENTIFIED BY 'syncuser' WITH GRANT OPTION; 
flush privileges; 
shutdown;

第二個節點，重復以上加用戶的操作

docker run -d --name mariadb-cluster1 --expose 4567 -p 4568:4567 -p 3308:3306 -e TIMEZONE=Asia/Taipei -v /home/docker/mariadb/cluster1/conf:/etc/mysql/conf.d -v /home/docker/mariadb/cluster1/data:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yse mariadb:10.3

docker run -d --name mariadb-cluster2 --expose 4567 -p 4569:4567 -p 3309:3306 -e TIMEZONE=Asia/Taipei -v /home/docker/mariadb/cluster2/conf:/etc/mysql/conf.d  -v /home/docker/mariadb/cluster2/data:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yse mariadb:10.3

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '' WITH GRANT OPTION; 
GRANT ALL PRIVILEGES ON *.* TO 'syncuser'@'%'IDENTIFIED BY 'syncuser' WITH GRANT OPTION; 
flush privileges; 
shutdown;

停止以及刪除所有以運行的容器

docker stop $(docker ps -a -q) 
docker rm $(docker ps -a -q)

編輯設定檔 解開wsrep_cluster_address注釋,再次啟動3個節點  --wsrep-new-cluster 只有在第一次集群,第一個節點,才需要加上,下次啟動不需要此參數

vi /home/docker/mariadb/cluster0/conf/server.cnf 
vi /home/docker/mariadb/cluster1/conf/server.cnf 
vi /home/docker/mariadb/cluster2/conf/server.cnf

啟動集群

cluster0

docker run --name mariadb-cluster0 --expose 4567 -p 4567:4567 -p 3307:3306 -v /home/docker/mariadb/cluster0/conf:/etc/mysql/conf.d -v /home/docker/mariadb/cluster0/data:/var/lib/mysql mariadb:10.3 --wsrep-new-cluster

cluster1

docker run --name mariadb-cluster1 --expose 4567 -p 4568:4567 -p 3308:3306 -v /home/docker/mariadb/cluster1/conf:/etc/mysql/conf.d -v /home/docker/mariadb/cluster1/data:/var/lib/mysql mariadb:10.3