The architecture is 2 servers with an app and db on each using tomcat session replication and mariadb galera cluster. The architecture writes from the app to the local db node and replicates this across subnets in aws over a private network (no internet gateway on the private subnets) to the second node. Each server can act independently as a standalone node or expand later by cloning the node and making minor config changes using a few scripts or a chef / automation environment of your choice. Expansion of this enviornment can go two ways. Either you can clone a node out and have another standalone node with clustering or as time evolves the architecture it can be expanded by separating the db and app tiers to allow for a two tier architecture of app and db. For now the solution looks like this:
Here's the simple of it for the impatient:
Using the AWS CentOS7 AMI with a few tweaks. The key is to create a VPC with 4 subnets
2 public and 2 private in one vpc across 2 availability zones (east-1a and east-1b) the private for cluster traffic and the public with elastic ip addresses only available from the company network and the loadbalancer.
Repo installed from MariaDB site:
[root@ip-172-31-2-124 tomcat]# cat /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install MariaDB 10.1 and supporting packages. Don't worry, package dependencies will work themselves out.
yum install mariadb-server socat rsync
Important for AWS static routes:
Created 2 AWS private Subnets, one in each availability zone.
Created 2 Private static interfaces.
Created a static host route from one subnet to the other on the private side.
Interface Config:
[centos@ip-172-31-1-126 ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE="eth1"
BOOTPROTO="static"
ONBOOT="yes"
TYPE="Ethernet"
USERCTL="yes"
PEERDNS="yes"
IPV6INIT="no"
IPADDR="172.31.10.10"
NETMASK="255.255.255.0"
[centos@ip-172-31-2-124 ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE="eth1"
BOOTPROTO="static"
ONBOOT="yes"
TYPE="Ethernet"
USERCTL="yes"
PEERDNS="yes"
IPV6INIT="no"
IPADDR="172.31.20.10"
NETMASK="255.255.255.0"
Routing Config:
[centos@ip-172-31-1-126 ~]$ cat /etc/sysconfig/network-scripts/route-eth1
172.31.20.0/24 via 172.31.10.1 dev eth1
[centos@ip-172-31-2-124 ~]$ cat /etc/sysconfig/network-scripts/route-eth1
172.31.10.0/24 via 172.31.20.1 dev eth1
Ensure you can ping the other node with your routing configuration.
Now setup you Mariadb Cluster.
On each Node:
Start Mysql:
sudo service mysql start
Setup Mysql:
sudo mysql_secure_installation
Setup priv users:
Login to mysql and execute:
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'USERPASS';
GRANT USAGE ON *.* to repl_user@'%' IDENTIFIED BY 'replpass';
GRANT ALL PRIVILEGES on *.* to repl_user@'%';
FLUSH PRIVILEGES;
quit;
Shutdown and configure for glaera:
sudo service mysql stop
GRANT USAGE ON *.* to repl_user@'%' IDENTIFIED BY 'replpass';
GRANT ALL PRIVILEGES on *.* to repl_user@'%';
FLUSH PRIVILEGES;
quit;
Shutdown and configure for glaera:
sudo service mysql stop
vi /etc/my.cnf
[mysql] # CLIENT #
port = 3306 socket = /data/mysql.sock [mysqld]
# GENERAL #
user = mysql default-storage-engine = InnoDB socket = /data/mysql.sock
pid-file = /data/mysql.pid
lower_case_table_names = 1
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
# DATA STORAGE #
datadir = /data/
# BINARY LOGGING #
log-bin = /data/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 5G
# LOGGING #
log-error = /data/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/mysql-slow.log
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://172.31.10.10,172.31.20.10"
binlog_format=row
innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
wsrep_cluster_name="Orbit_Data_DB"
wsrep_node_address="172.31.20.10"
wsrep_node_name="orb_db2"
wsrep_sst_method=rsync
wsrep_slave_threads=1
On each node, change the wsrep_node_address and wsrep_node_name.
Once done, start the first node with:
galera_new_cluster
Check The cluster with a query:
mysql -uroot -p -e "show status like 'wsrep%'
Complete.
Complete.
No comments:
Post a Comment