--- title: 'Ubuntu 22.04 MariaDB Galera Cluster' tags: ubuntu,Mariadb description: Ubuntu 22.04 MariaDB Galera Cluster --- # Ubuntu 22.04 MariaDB Galera Cluster --- [TOC] --- ## Install Mariadb 安裝三台機器 ``` terminal sudo apt update && sudo apt upgrade -y sudo apt install mariadb-server -y ``` --- ## Mariadb Settings ``` terminal sudo mysql_secure_installation ``` ``` terminal Enter current password for root (enter for none): Switch to unix_socket authentication [Y/n] n Change the root password? [Y/n] Y New password: Re-enter new password: Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] n Remove test database and access to it? [Y/n] Y Reload privilege tables now? [Y/n] Y ``` --- ## Mariadb Galera 設定流程 先關閉所有節點 Database stop ``` terminal sudo systemctl stop mariadb ``` 修改 galera.cnf ``` terminal sudo vim /etc/mysql/conf.d/galera.cnf ``` Node 1(Main) ``` conf [mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera_cluster" wsrep_cluster_address="gcomm://192.168.10.232,192.168.10.80,192.168.10.101" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="192.168.10.232" wsrep_node_name="node1" ``` Node 2(Sub) ``` conf [mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera_cluster" wsrep_cluster_address="gcomm://192.168.10.232,192.168.10.80,192.168.10.101" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="192.168.10.80" wsrep_node_name="node2" ``` Node 3(Sub) ``` conf [mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera_cluster" wsrep_cluster_address="gcomm://192.168.10.232,192.168.10.80,192.168.10.101" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="192.168.10.101" wsrep_node_name="node3" ``` Node 1 Server 執行Main指令 ``` terminal sudo galera_new_cluster ``` ``` terminal mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'" ``` ``` terminal +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 1 | +--------------------+-------+ ``` Node 2 & Node 3 ``` terminal sudo systemctl start mariadb ``` Node 1 指令 ``` terminal mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'" ``` ``` terminal +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ ``` --- ## Mariadb Remote ``` terminal mysql -u root -p mysql> GRANT ALL ON *.* to 'root'@'%' IDENTIFIED BY '*****' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> exit; ``` 開防火牆 ``` terminal sudo ufw allow 3306/tcp ``` 修改config ``` terminal sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf ... bind-address = 0.0.0.0 ... ``` --- ## 參考鏈結： https://webdock.io/en/docs/how-guides/database-guides/how-enable-remote-access-your-mariadbmysql-database https://0xzx.com/zh-tw/2021060800161498438.html