1. Environment preparation
Host name |
IP |
mysql1 |
10.0.0.10 |
mysql2 |
10.0.0.11 |
mysql3 |
10.0.0.12 |
mysql4 |
10.0.0.13 |
ubuntu22.04
1.1 Change host name
hostnamectl set-hostname mysql1
hostnamectl set-hostname mysql2
hostnamectl set-hostname mysql3
hostnamectl set-hostname mysql4
1.2 Configure hosts resolution
cat >> /etc/hosts << EOF
10.0.0.10 mysql1
10.0.0.11 mysql2
10.0.0.12 mysql3
10.0.0.13 mysql4
EOF
1.3 Configure apt source
- all nodes
- You can select the version based on baseurl
cat > /etc/apt/sources.list << EOF
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-updates main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-backports main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-security main restricted universe multiverse
EOF
# load
apt update
2. Install software package
apt install -y mariadb-server
3. Edit configuration file
cd /etc/mysql/mariadb.conf.d/
# Configure startup file
vim 50-server.cnf
[client]
socket=/var/run/mysqld/mysql.sock
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
binlog_expire_logs_seconds=604800
skip-external-locking
skip-name-resolve
max_connections=5000
connect_timeout=5
wait_timeout=600
max_allowed_packet=16M
thread_cache_size=128
sort_buffer_size=4M
bulk_insert_buffer_size=16M
tmp_table_size=32M
max_heap_table_size=32M
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
#Configure cluster file
vim 60-galera.cnf
[galera]
wsrep_causal_reads=ON
wsrep_provider_options="gcache.size=128M"
wsrep_certify_nonPK=ON
log-bin=/data/mariadb/binlog/mysql-bin
log_slave_updates=1
query_cache_size=0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=MGC-Cluster
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.11,10.0.0.12
wsrep_node_name=mysql1
wsrep_node_address=10.0.0.10
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
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=500M
- MariaDB cluster file preparation (mysql1 node)
mkdir -pv /data/mariadb/binlog/
chown -R mysql.mysql /data/
4. Boot the GRC cluster
# Ubuntu will install and start the service. You need to stop the service first.
systemctl stop mariadb
galera_new_cluster
mysql
show status like 'wsrep%';
5. Join the cluster
5.1 Copy cluster files
scp 10.0.0.10:/etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/
scp 10.0.0.10:/etc/mysql/mariadb.conf.d/60-galera.cnf /etc/mysql/mariadb.conf.d/
5.2 Prepare cluster files
mkdir -pv /data/mariadb/binlog/
chown -R mysql.mysql /data/
5.3 Edit configuration file
sed -i "s/mysql1/mysql2/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.11/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
5.4 Start the database and verify
systemctl restart mariadb
mysql
show status like 'wsrep%';
5.5 Copy cluster files
scp 10.0.0.10:/etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/
scp 10.0.0.10:/etc/mysql/mariadb.conf.d/60-galera.cnf /etc/mysql/mariadb.conf.d/
5.6 Prepare cluster files
mkdir -pv /data/mariadb/binlog/
chown -R mysql.mysql /data/
5.7 Edit configuration file
sed -i "s/mysql1/mysql3/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.12/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
5.8 Start the database and verify
systemctl restart mariadb
mysql
show status like 'wsrep%';
6. Verify cluster synchronization
create database t1;
create database t2;
create database t3;
- All node verification
- If there are t1, t2, and t3 libraries, then success
show databases;
7. Add new node
7.1 Redefine cluster files
cd /etc/mysql/mariadb.conf.d/
#Configure cluster file
vim 60-galera.cnf
[galera]
wsrep_causal_reads=ON
wsrep_provider_options="gcache.size=128M"
wsrep_certify_nonPK=ON
log-bin=/data/mariadb/binlog/mysql-bin
log_slave_updates=1
query_cache_size=0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=MGC-Cluster
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13
wsrep_node_name=mysql1
wsrep_node_address=10.0.0.10
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
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=500M
- Synchronize files (mysql1)
for i in {<!-- -->2..4};do scp /etc/mysql/mariadb.conf.d/60-galera.cnf root@mysql$i:/etc/mysql/mariadb .conf.d;done
- mysql2 edit configuration file
sed -i "s/mysql1/mysql2/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.11/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
systemctl restart mariadb
- mysql3 edit configuration file
sed -i "s/mysql1/mysql3/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.12/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
systemctl restart mariadb
- mysql4 edit configuration file
sed -i "s/mysql1/mysql4/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.13/g" /etc/mysql/mariadb.conf.d/60-galera.cnf
7.2 Prepare cluster files
mkdir -pv /data/mariadb/binlog/
chown -R mysql.mysql /data/
7.3 Start the database and verify
systemctl restart mariadb
- verify
- If there are t1, t2, and t3 libraries, it means success.
mysql
show status like 'wsrep%';
8. Full shutdown and recovery of cluster operations
- Delete cluster cache files on mysql1
cd /var/lib/mysql
rm -rf galera.cache
rm -rfgrastate.dat
galera_new_cluster
- Other nodes can be restarted normally.
systemctl restart mariadb