Fredric News

MariaDB 데이터베이스 설치 및 mysql_secure_installation

💡 핵심 요약 (Executive Summary)

본 가이드는 mariadb 핵심 가이드 및 실전 아키텍처의 핵심 개념, 실전 구성 명령어 및 아키텍처 베스트 프랙티스를 다룹니다. 클라우드 네이티브 및 인프라 엔지니어링 실무에 즉시 활용 가능하도록 체계적으로 정리했습니다.

📑 Table of Contents
Reference & Guidelines (NOTE)

[!IMPORTANT]

본 페이지의 MariaDB 설치 구성 시 사용되는 계정 비밀번호 및 클러스터 SST 인증 정보 등의 민감 정보는 🔒 common-secret 페이지로 격리 이관되었습니다. 실제 Credential 정보가 필요할 경우 해당 페이지를 참고해 주십시오.

설치

Reference & Guidelines (NOTE)

DB default IP: X.X.X.130 …

K8s operator
Helm
Local Container
bash
your_password=
docker run --name mariadb \
  -e MARIADB_ROOT_PASSWORD=$your_password \
  -p 3306:3306 \
  -d mariadb
docker exec -it mariadb mariadb -u root -p$your_password

your_password=
docker run --name mariadb \                                                                                                                                                                                                                        INT ✘  18:08:14 
  -e MARIADB_ROOT_PASSWORD=$your_password \    
  -p 3306:3306 \
  -d mariadb:12.0.2
docker exec -it mariadb mariadb -u root -p$your_password
alias
bash
# ~/.zshrc
your_password=${MARIADB_PORTAL_PASSWORD} 
alias docker-mysql="""
containerID=$(docker ps -a --filter "name=^mariadb$" --quiet)
if [ -n "$containerID" ]; then
  docker start $containerID
else
  docker run --name mariadb \
    -e MARIADB_ROOT_PASSWORD=$your_password \    
    -p 3306:3306 \
    -d mariadb:12.0.2
fi
docker exec -it mariadb mariadb -uroot -p$your_password
"""
pre-setups
bash
create database wordpress;
use wordpress;
CREATE USER IF NOT EXISTS 'wordpress'@'%' IDENTIFIED BY 'wordpress';
GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'%';
FLUSH PRIVILEGES;
bash
docker exec -i mariadb mariadb -uroot -p${MARIADB_ROOT_PASSWORD} <<EOF
  create database OKE_DEVOPS;
  create database ECP_USER;
  create database ECP_ADMIN;
  create database keycloak;
  use keycloak;
  CREATE USER IF NOT EXISTS 'keycloak'@'%' IDENTIFIED BY 'keycloak';
  CREATE USER IF NOT EXISTS 'portal'@'%' IDENTIFIED BY '${MARIADB_PORTAL_PASSWORD}';
  GRANT ALL PRIVILEGES ON *.* TO 'keycloak'@'%';
  GRANT ALL PRIVILEGES ON *.* TO 'portal'@'%';
  FLUSH PRIVILEGES;
EOF
StandAlone
bash
sudo groupadd mysql
sudo useradd -g mysql mysql
sudo mkdir -p /var/log/mysql
sudo mkdir -p /etc/mysql
sudo touch /var/log/mysql/error.log
sudo chmod 660 /var/log/mysql/error.log
sudo chown -R mysql:mysql /var/log/mysql
sudo chown -R mysql:mysql /etc/mysql

cd /usr/local
sudo wget https://downloads.mariadb.com/MariaDB/mariadb-10.4.24/bintar-linux-x86_64/mariadb-10.4.24-linux-x86_64.tar.gz
sudo tar xzvf mariadb-10.4.24-linux-x86_64.tar.gz
sudo ln -s /usr/local/mariadb-10.4.24-linux-x86_64 mysql
sudo chown -R mysql:mysql mariadb-10.4.24-linux-x86_64

cd mysql

sudo chown -R root:root .
mkdir data
sudo chown -R mysql:mysql data

sudo bash -c "cat << EOF > /etc/mysql/my.cnf
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking
bind-address            = 0.0.0.0
max_connections               = 10000
connect_timeout               = 10
wait_timeout                    = 28800
interactive_timeout         = 28800
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
net_buffer_length             = 8K
collation-server              = utf8mb4_general_ci
init-connect                    = 'SET NAMES utf8mb4'
character-set-server      = utf8mb4
log_bin_trust_function_creators = 1

key_buffer_size         = 128M
table_open_cache        = 32000
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 2M
read_rnd_buffer_size    = 1M

query_cache_limit               = 128K
query_cache_size                = 64M

log_warnings            = 2
log-error=/var/log/mysql/error.log
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity      = query_plan

log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
expire_logs_days        = 2
max_binlog_size         = 100M

default_storage_engine  = InnoDB
innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

EOF
"

sudo bash -c "echo export PATH=/usr/local/mysql/bin:\$PATH >> /etc/profile"
source /etc/profile

sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld


Ubuntu

bash
sudo apt-get update && sudo apt-get -y upgrade
sudo apt-get install -y libaio1 libaio-dev
sudo apt-get install -y libncurses5

sudo ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --auth-root-authentication-method=normal  --defaults-file=/etc/mysql/my.cnf

sudo systemctl enable mysqld
sudo systemctl start mysqld


RHEL8

bash
sudo dnf install -y http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libaio-0.3.112-1.el8.x86_64.rpm
sudo dnf install -y http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/ncurses-compat-libs-6.1-9.20180224.el8.x86_64.rpm

sudo ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --auth-root-authentication-method=normal  --defaults-file=/etc/mysql/my.cnf

sudo ./bin/mysqld_safe --datadir='/usr/local/mysql/data' &
bash
sudo ./bin/mysqladmin -u root password $new_password
sudo rm /usr/local/mariadb-10.4.24-linux-x86_64.tar.gz


Galera Cluster

  • 위 standalone 메뉴얼대로 ptldb-1, 2, 3 전부 다 설치 완료되면 아래 번호순으로 작업
  • 사용 포트 (4567, 4568, 4444)
1. common config
shell
#!/bin/sh

mysql -uroot -p$password -e "CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql@'"
mysql -uroot -p$password -e "GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mysql'@'localhost'"
mysql -uroot -p$password -e "FLUSH PRIVILEGES"

## Galera Setting
cd ~
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup --mariadb-server-version="mariadb-10.4.24"
sudo apt install -y galera-4
2. master config
bash
#!/bin/sh

sudo bash -c "cat << EOF >> /etc/mysql/my.cnf
[galera]
wsrep_on			            = ON
wsrep_provider			      = /usr/lib/galera/libgalera_smm.so
wsrep_provider_options  	= gcache.size=2048M
wsrep_cluster_name		    = portal-db-galera
wsrep_cluster_address		  = gcomm://
wsrep_sst_method          = rsync
wsrep_sst_auth            = ${MARIADB_SST_AUTH}
wsrep_slave_threads		    = 4
binlog_format			        = row
default_storage_engine		= InnoDB
innodb_autoinc_lock_mode	= 2
bind-address=0.0.0.0
EOF
"
sudo systemctl enable mysqld
sudo systemctl stop mysqld

sudo service mysqld start --wsrep-new-cluster
sql
mysql -uroot -p'${COMMON_ADMIN_PASSWORD}'

GRANT ALL PRIVILEGES ON ECP_ADMIN.* TO mysql@'192.168.50.251' identified by '${MARIADB_PORTAL_PASSWORD}' with grant option;
GRANT ALL PRIVILEGES ON ECP_USER.* TO mysql@'192.168.50.251' identified by '${MARIADB_PORTAL_PASSWORD}' with grant option;
GRANT ALL PRIVILEGES ON OKE_DEVOPS.* TO mysql@'192.168.50.251' identified by '${MARIADB_PORTAL_PASSWORD}' with grant option;
FLUSH PRIVILEGES;
3. slaves config
shell
#!/bin/sh

sudo bash -c "cat << EOF >> /etc/mysql/my.cnf
[galera]
wsrep_on			            = ON
wsrep_provider			      = /usr/lib/galera/libgalera_smm.so
wsrep_provider_options  	= gcache.size=2048M
wsrep_cluster_name		    = portal-db-galera
wsrep_cluster_address		  = gcomm://192.168.50.251,192.168.50.195
wsrep_sst_method          = rsync
wsrep_sst_auth            = ${MARIADB_SST_AUTH}
wsrep_slave_threads		    = 4
binlog_format			        = row
default_storage_engine		= InnoDB
innodb_autoinc_lock_mode	= 2
bind-address=0.0.0.0
EOF
"
sudo systemctl enable mysqld
sudo systemctl stop mysqld

sudo service mysqld start
4. master config 마무리
shell
#!/bin/sh

vi /etc/mysql/my.cnf

--- # 아래 내용대로 변경
wsrep_cluster_address		= gcomm://192.168.50.251,192.168.50.195

---
sudo service mysqld restart
5. 확인
sql
mysql -uroot -p$password

show status like 'wsrep_%';
show global status like 'wsrep_cluster_size';
MaxScale

Trouble Shooting

Posted:

/

Category:

Author:

fredric

Tags:

, , , , , , , , , , , , , , ,

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *