Installation and configuration wikisnips for MySQL/MariaDB.
These are based on Ubuntu 24.04 Installed and logged in with root permissions (use "sudo -i" after loggin in)
Install MariaDB on Ubuntu
apt install mariadb-server mariadb-client
#Check it's running
systemctl status mariadb
#If not running:
systemctl start mariadb
systemctl enable mariadb
Run post-installation security script
mysql_secure_installation
Follow these steps:
Installation Complete.
If the service stops for some reason, set it to automatically restart:
mkdir -p /etc/systemd/system/mariadb.service.d/
nano /etc/systemd/system/mariadb.service.d/restart.conf
Add to file and save:
[Service]
Restart=always
RestartSec=5s
Restart daemons:
systemctl daemon-reload
Complete
Helpful reminders of how to set permissions on databases:
#create a user
create user dbuser@localhost identified by 'your-password';
#Give user full permissions to dbuser
grant all privileges on *.dbname to dbuser@localhost;
#Give only specific permissions to dbuser
GRANT SELECT, INSERT, UPDATE, DELETE on *.dbname to dbuser@localhost;
#Give an admin user full privileges
grant all privileges on *.* to myadmin@localhost with grant option;
#give remote user read only permissions
grant select on *.dbname to [email protected]
This Snip assumes you're running up to date Ubuntu 22.04 with MariaDB server installed as above.
Galera Cluster is a multi-master cluster for MySQL/MariaDB. For more info: https://galeracluster.com/
Replication can take place over LAN or WAN with distributed geo-redundant servers.
REQUIREMENTS:
*Galera Cluster requires at least 3 SQL servers! You can add addtional, but the total number of servers must be an odd number - 3,5,7...
*Databases should be running InnoDB storage engine.
*ALL Database Tables must have Primary Keys.
*Also check https://mariadb.com/kb/en/library/mariadb-galera-cluster-known-limitations/
[galera]
# Mandatory settings
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "MariaDB Galera Cluster"
wsrep_cluster_address = "gcomm://IP_address_of_node1,IP_address_of_node2,IP_address_of_node3"
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
innodb_force_primary_key = 1
innodb_doublewrite = 1
# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0
# Optional settings
wsrep_slave_threads = 4
innodb_flush_log_at_trx_commit = 0
wsrep_node_name = MyNode1
wsrep_node_address = "IP_address_of_this_node"
# By default, MariaDB error logs are sent to journald, which can be hard to digest sometimes.
# The following line will save error messages to a plain file.
log_error = /var/log/mysql/error.log
Replace Cluster Address with IPs of all servers.
ws_cluster_name Must match on all servers
Change wsrep_node_name and wsrep_node_address to the IP of the server you're on.
#replace X.X.X.X with IP of other node:
ufw allow proto tcp from X.X.X.X to any port 3306,4444,4567,4568
systemctl stop mariadb
galera_new_cluster
Before continuing, if there are existing databases on the other nodes, they will be deleted! You can export them and then import them to the primary node you just started first.
systemctl restart mariadb
#Enter MySQL command line
mysql -u root
#Show Cluster Size (number of active nodes)
show status like 'wsrep_cluster_size';
#Show Sync Status:
show status like 'wsrep_local_state_comment';
#Check all stats:
show status like 'wsrep%';
8. IF CLUSTER BREAKS/FAILS:
If the cluster fails or is inconsistent, you basically just recreate the cluster again and can recover quickly.
nano /var/lib/mysql/grastate.dat
safe_to_bootstrap: 1
galera_new_cluster
systemctl start mariadb
All servers should now be running and in sync. Check status with command above.
If replicating over the public Internet you should enable encryption or create VPN mesh between nodes:
-https://www.linuxbabe.com/mariadb/encrypt-replication-traffic-mariadb-galera-cluster-ubuntu
-https://www.linuxbabe.com/mariadb/wireguard-mesh-vpn-encryption-mariadb-galera-cluster
Credit for these snips goes to https://www.linuxbabe.com/mariadb/galera-cluster-ubuntu