[MariaDB] MHA 구성(Part 1. MariaDB 10.11.8 설치)

1. MariaDB 바이너리 설치

아래 설치 파일들을 모두 다운로드 받습니다.

• OS : Red Hat Enterprise Linux release 8.10 (Ootpa)
• MariaDB 10.11.8 엔진 다운로드(master, slave 서버 필요)
  https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.11.7&os=Linux&cpu=x86_64&i=systemd&m=blendbyte
• MHA 0.58 다운로드
  https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
  
  

selinux, firewalld 비활성화(mhamanager, master, slave)

• selinux 비활성화 후 반드시 서버 재부팅

# vi /etc/selinux/config

...
SELINUX=disabled
...

# reboot

 

# 
systemctl stop firewalld
systemctl disable firewalld

 

/etc/hosts 내용 추가(mhamanager, master, slave)

• 모든 서버의 hosts 파일에 hostname 정보 추가

# vi /etc/hosts

...
192.168.137.170 mhamanager
192.168.137.171 maria1
192.168.137.172 maria2

 

유저 생성 및 사전 준비(master, slave)

• 일반적인 mysql 유저 설치법이 아닌 다른 유저(ex. maria) 설치법입니다.
• maria user 생성 후 패스워드를 입력하여 줍니다.
• adduser : 해당 유저 명으로 된 그룹과 홈디렉토리 생성

# 
adduser maria
passwd maria

• 이후, data 및 로그 저장 폴더 or 파일을 생성합니다.

# 
mkdir /home/maria/log
chown -R maria.maria /home/maria/log

mkdir -p /data/mariadb/master
mkdir -p /data/mariadb/ibdata
mkdir -p /data/mariadb/iblog
mkdir -p /data/mariadb/log-bin
chown -R maria.maria /data/mariadb

• mysqld.log 파일을 현재 시간으로 생성하고 권한 설정을 해줍니다.

# 
touch /var/log/mysqld.log
chmod 644 /var/log/mysqld.log
chown maria.maria /var/log/mysqld.log

 

기본 패키지 설치(mhamanager, master, slave)

• 로컬 Repository 설정 방법

# 
yum install -y sysstat 
yum install -y wget 
yum install -y lrzsz 
yum install -y lsof 
yum install -y htop 
yum install -y iftop 
yum install -y rsync 
yum install -y bzip2 
yum install -y unzip
yum install -y patch 
yum install -y syslog
yum install -y gcc
yum install -y perl
yum install -y ncurses*
yum install -y perl-devel 
yum install -y perl-CPAN 
yum install -y perl-DBD-MySQL 
yum install -y perl-Config-Tiny
cpan Module::Install

 

설치파일 업로드 및 압축해제(master, slave)

• 다운로드한 MariaDB 바이너리 파일(mariadb-10.11.8-linux-systemd-x86_64.tar.gz)을 /root 경로로 업로드해 줍니다.
• 압축을 해제해 줍니다.

# pwd
/root

# ll
total 339800
-rw-------. 1 root root      1346 Feb 16 16:01 anaconda-ks.cfg
-rw-r--r--. 1 root root      1715 Feb 16 16:03 initial-setup-ks.cfg
-rw-r--r--. 1 root root 347943913 Feb 16 16:13 mariadb-10.11.8-linux-systemd-x86_64.tar.gz

• 압축 해제 후 mariadb-10.11.8-linux-systemd-x86_64을 /usr/local로 mv한 뒤 심볼릭 링크를 설정해 줍니다.
  • /usr/local 말고 다른 경로로 지정 가능

#
tar -zxvf mariadb-10.11.8-linux-systemd-x86_64.tar.gz
mv mariadb-10.11.8-linux-systemd-x86_64 /usr/local
cd /usr/local
ln -s mariadb-10.11.8-linux-systemd-x86_64 mysql

 

데몬스크립트 복사 및 변경(master, slave)

• 45, 46번째 줄에 basedir, datadir 부분을 위와 같이 입력해 줍니다.
• 185 번째 줄에 user를 maria로 변경합니다.
• 310번째 줄에 --user="maria"를 추가합니다.
• my.cnf 파일을 default 경로(/etc/my.cnf)가 아닌 다른 경로를 싶은 경우 --defaults-file=my.cnf경로 옵션을 mysqld_safe 명령어 제일 앞에 줍니다.
  (중간이나 뒤쪽에 줄 경우 에러가 발생합니다)

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

...
45 basedir=/usr/local/mysql
46 datadir=/data/mariadb/master
...
185 user='maria'
...
310 $bindir/mysqld_safe --defaults-file=/home/maria/my.cnf --user="maria" --datadir="$datadir" --pid-file="$mariadbd_pid_file_path" "$@" &
...

# systemctl daemon-reload

.bash_profile 변경(master, slave)

• 기존에 있었던 PATH 값을 주석처리하고 14번째 줄처럼 입력해 줍니다.

# vi ~/.bash_profile

...
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/scripts

# . .bash_profile

 

my.cnf 설정 파일을 수정(master, slave)

• 백업 후 새로 만들어서 작업합니다.
  
• 만약 default 경로(/etc/my.cnf)가 아닌 다른 경로로 my.cnf를 이용할 경우 default 경로의 my.cnf 삭제

# cd /etc
# mv my.cnf my.cnf_bak
# vi my.cnf

• 아래 내용을 입력합니다.

# Mariadb
[server]

# mysqld standalone daemon
[mysqld]
port                            = 3306
datadir                         = /data/mariadb/master
socket                          = /tmp/mysql.sock

# Character set (utf8mb4)
character_set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation_server                = utf8mb4_general_ci
init_connect                    = set collation_connection=utf8mb4_general_ci
init_connect                    = set names utf8mb4

# Common
table_open_cache                = 2048
max_allowed_packet              = 32M
binlog_cache_size               = 1M
max_heap_table_size             = 64M
read_buffer_size                = 64M
read_rnd_buffer_size            = 16M
sort_buffer_size                = 8M
join_buffer_size                = 8M
ft_min_word_len                 = 4
lower_case_table_names          = 1
default-storage-engine          = innodb
thread_stack                    = 240K
transaction_isolation           = READ-COMMITTED
tmp_table_size                  = 32M

# Connection
max_connections                 = 200
max_connect_errors              = 50
back_log                        = 100
thread_cache_size               = 100

# Query Cache
query_cache_size                = 32M
query_cache_limit               = 2M

log-bin                         = /data/mariadb/log-bin/mysql-bin
binlog_format                   = mixed
max_binlog_size                 = 128M
expire_logs_days                = 7

slow_query_log                  = 1
long_query_time                 = 5
log_output                      = FILE,TABLE
log_error                       = /var/log/mysqld.log
slow_query_log_file             = /home/maria/log/mysql_slow_query.log
general_log_file                = /home/maria/log/mysql_general.log

key_buffer_size                 = 32M
bulk_insert_buffer_size         = 32M
myisam_sort_buffer_size         = 64M
myisam_max_sort_file_size       = 256M
myisam_repair_threads           = 1

# Innodb
innodb_buffer_pool_size         = 1G   # 현재 서버 사양의 70~80%를 설정한다.
innodb_file_per_table           = 1
innodb_data_home_dir            = /data/mariadb/ibdata
innodb_data_file_path           = ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M:autoextend:max:2000M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_thread_concurrency       = 16
innodb_flush_log_at_trx_commit  = 1
innodb_log_group_home_dir       = /data/mariadb/iblog
innodb_log_buffer_size          = 32M
innodb_log_file_size            = 128M
innodb_log_files_in_group       = 10
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120

 

DB install 및 서비스 시작(master, slave)

• 아래의 명령어를 수행하여 설치를 진행합니다.

# mysql_install_db --user=maria --basedir=/usr/local/mysql --datadir=/data/mariadb/master --defaults-file=/etc/my.cnf

Installing MariaDB/MySQL system tables in '/data/mariadb/master' ...
OK

To start mariadbd at boot time you have to copy
support-files/mariadb.service to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is maria@localhost, it has no password either, but
you need to be the system 'maria' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mariadbd-safe --datadir='/data/mariadb/master'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

# systemctl start mysqld
# systemctl status mysqld

● mysqld.service - LSB: start and stop MariaDB
   Loaded: loaded (/etc/rc.d/init.d/mysqld; generated)
   Active: active (running) since Tue 2024-02-20 11:17:30 KST; 12min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2252 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 10 (limit: 12284)
   Memory: 215.8M
   CGroup: /system.slice/mysqld.service
           ├─2441 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=maria --datadir=/data/mariadb/master --pid-file=/data/mariadb/master/maria2.pid
           └─2716 /usr/local/mysql/bin/mariadbd --basedir=/usr/local/mysql --datadir=/data/mariadb/master --plugin-dir=/usr/local/mysql/lib/plugin --user=maria --log-error=/var/log/mysqld.log --pid-file=/data/mariadb/master/maria2.pid --socket=/tmp/mysql.sock --port=3306

Feb 20 13:40:07 maria1 systemd[1]: Starting LSB: start and stop MariaDB...
Feb 20 13:40:07 maria1 mysqld[40225]: Starting MariaDB.240220 13:40:07 mysqld_safe Logging to '/var/log/mysqld.log'.
Feb 20 13:40:07 maria1 mysqld[40225]: 240220 13:40:07 mysqld_safe Starting mariadbd daemon with databases from /data/mariadb/master
Feb 20 13:40:08 maria1 mysqld[40225]: /etc/rc.d/init.d/mysqld: line 262: log_success_msg: command not found
Feb 20 13:40:08 maria1 systemd[1]: Started LSB: start and stop MariaDB.

접근 확인(master, slave)

# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.11.2-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

---

다음 포스트

 

[MariaDB] MHA 구성(Part 2. Replication 설정)