MariaDB 10.5 설치

1. Repo 설정

설정 방법 : downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=focal--ubuntu_focal&mirror=yongbok

MariaDB - Setting up MariaDB Repositories - MariaDB

## Ubuntu
apt update && sudo apt upgrade -y
apt-get install curl software-properties-common dirmngr ca-certificates apt-transport-https -y
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.5
apt update

## CentOs
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

 

2. MariaDB설치

## Ubuntu
apt install mariadb-server mariadb-client mariadb-backup

## CentOs
yum install mariadb-server mariadb-client mariadb-backup

 

3. 보안설정

root@Mariadb:~# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]

New password: P@ssw0rd
Re-enter new password: P@ssw0rd
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]

... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]

... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]

- Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]

... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
root@mariadb:~#

4. MariaDB 자동 재시작

## Ubuntu
systemctl start mariadb.service
systemctl enable mariadb.service

## CentOs
systemctl start mysql
systemctl enable mysql

 

5. MariaDB datadir 변경 및 32G메모리 셋팅

# OS 공통
root@Mariadb:~# systemctl stop mariadb
root@Mariadb:~# mkdir -p /data/{db-data,local-data}
root@Mariadb:~# cp -R -p /var/lib/mysql/* /data/db-data
root@Mariadb:~# chown -R mysql:mysql /data/db-data


# Ubuntu
root@Mariadb:~# vi /etc/mysql/mariadb.conf.d/50-server.cnf 
[mysqld]
datadir=/data/db-data

[mariadb-10.5]
socket=/data/mariadb-data/mysql.sock

root@Mariadb:~# vi /etc/mysql/mariadb.conf.d/50-client.cnf
[client]
socket=/data/mariadb-data/mysql.sock

root@Mariadb:~# vi /etc/mysql/my.cnf
[client-server]
# Port or socket location where to connect
# port = 3306
#socket = /run/mysqld/mysqld.sock
socket = /data/mariadb-data/mysqld.sock

root@Mariadb:~# systemctl daemon-reload
root@Mariadb:~# systemctl start mariadb


# Centos
root@Mariadb:~# vi /usr/lib/systemd/system/mariadb.service
ProtectHome=false

root@Mariadb:~# vi /etc/my.cnf.d/server.cnf
[mariadb-10.5]
datadir=/data/db-data
socket=/data/db-data/mysql.sock

[client]
socket=/data/db-data/mysql.sock

root@Mariadb:~# vi /etc/my.cnf.d/mysql-clients.cnf
[mysql]
default-character-set=utf8mb4
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
default-character-set=utf8mb4
[mysqlimport]
[mysqlshow]
[mysqlslap]

root@Mariadb:~# systemctl daemon-reload
root@Mariadb:~# systemctl start mariadb
root@Mariadb:~# chown -R mysql:mysql /data/db-data

root@Mariadb:~# vi /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US

# BASE Setting
skip-external-locking
skip-name-resolve
lower_case_table_names = 1
symbolic-links=0
bind-address            = 0.0.0.0

# LOG FILE Setting
general_log_file       = //data/db-data/logs/mysql_general.log
general_log            = 1
log_error = /data/mariadb-data/logs/mysql.err
slow_query_log_file    = /data/db-data/logs/mysql-slow-query.log
long_query_time        = 10
log-bin=/data/db-data/binlog/mariadb-bin.log
binlog_cache_size=2M
max_binlog_size=100M
expire_logs_days=30

# Character Setting
character-set-client-handshake=FALSE
init_connect = SET collation_connection = utf8_general_ci
init_connect = SET NAMES utf8
character-set-server = utf8
collation-server = utf8_general_ci


# Fine Tuning
# max_connection : 최대 동시 접속자 수, 늘어나면 날수록 메모리가 고갈되고 스케줄링 오버헤드도 증가이전 최대 접속자 수의 2배 정도 잡는다.
# connect_timeout : mysqld 서버가 패킷과 연결하기 위해서 대기하는 시간기본값은 10초
#wait_timeout : 서버가 데이타 패킷과 연결된 후 연결을 유지하는 시간 기본값은 28800초(8시간) DB 서버 접속이 많다면 wait_timeout을 최대한 적게 (20~30 정도를 추천) 설정하여 불필요한 연결을 빨리 정리 필요그러나 Connection Miss Rate(%)가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 가져야 함
. 그렇지 않다면 DB 연결 후 끊고 다시 연결하는 것은 속도를 지연 시키는 요인이기 때문에 연결 시간을 길게 유지하는 것이 성능 향상에 도움이 된다는 의견이 있음
# max_allowed_packet : 허용 패킷 크기. 기본값 16MB이며 최대값은 1GB,. MySQL 서버가 잘못된, 너무 큰 패킷을 제어하는 데는 도움이 되지만규모 이상>으로 큰 패킷을 수신하면 문제가 있다고 판단해 연결을 끊어 버리기 때문에 이를 피하려면 값을 새로 설정하고 mysql을 다시 시작해야 함
# thread_cache_size :. 기본값 8 . Cache Miss Rate(%)가 높다면 기본값보다 높게 잡는다
# sort_buffer_size : 리눅스에는 256K 또는 2MB라는 임계점이 존재하는데이 이상의 값은 메모리 할당이 크게 느려질 수 있으므로 이보다 낮은 값을 사용
하는 것으로 고려
# join_buffer_size : MySqlTunner에서는 최소 1MB이상으로 제안
# tmp_table_size : group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용하는 메모리 크기
# max_heap_table_size : 내부 메모리 임시 테이블이 너무 커지면(tmp_table_size와 max_heap_table_size 를 넘어서는 경우) 자동으로 테이블을 메모리에
서 디스크 내 형식으로 변환
# table_definition_cache : 테이블 오픈 속도를 향상 시키기 위한 캐시 수
# table_open_cache : 각 쓰레드별 오픈할 테이블 수. 기본 값은 2000. max_connection * N개가 되어야 함 여기서 N은 실행하는 쿼리에서 조인 당 최대 >테이블 수. MySql에서 show global status like ‘%table_open_cache%’ ; 명령 결과에서 miss가 있다면 늘려 봄
# open_files_limit : table_open_cache 값의 2배 또는 3배. file-max 값은 리눅스에서 한 번에 운용할 수 있는 파일 수를 의미하며, 보통 4MB 메모리 당
 256개의 파일을 운용할 수 있다고 한다. 대략 1G -> 65536개, 2G -> 131072 개

max_connections                = 500
connect_timeout                = 20
wait_timeout                   = 7200
max_allowed_packet             = 100M
max_connect_errors             = 1000000
thread_stack                   = 192K
thread_cache_size              = 128
sort_buffer_size               = 100M
join_buffer_size               = 100M
bulk_insert_buffer_size        = 100M
tmp_table_size                 = 256M
max_heap_table_size            = 256M
query_cache_type               = 0
query_cache_size               = 0
open_files_limit               = 7260000
table_definition_cache         = 4096
table_open_cache               = 10240
key_buffer_size                = 768M
read_buffer_size               = 100M
read_rnd_buffer_size           = 100M

# INNODB Setting
# default_storage_engine = InnoDB 기본 데이타베이스 엔진으로 InnoDB를 사용한다는 것 표시
# innodb_buffer_pool_size : 운영중인 시스템의 DB 크기 이상을 할당. 시스템 메모리의 65%~75% 권장, 시스템 메모리 8GB RAM라면 일반적으로 5~6GB 정>도 할당. buffer pool이 너무 작으면 페이지가 buffer pool에서 플러시 되어 잠시 후 다시 필요하게 되므로 과도한 I/O 가 발생할 수 있으며, 너무 큰 경
우 메모리 경쟁으로 스와핑이 발생할 수 있음
# innodb_log_file_size : 데이타베이스 충돌 발생 시 다시 실행하거나 이전으로 되돌릴 때 사용하는 메모리. 지나치게 크면 복구 시간이 길어지면서 비>효율적이 될 수 있음. 위에서 설정한 innodb_buffer_pool_size의 25% 정도 할당
# innodb_buffer_pool_instances: 인스턴스 수를 늘리면 트랜잭션 간 Lock 경합을 줄일 수 있음. 기본값은 8. 메모리가 많은 시스템에서는 buffer pool을
 여러 개 buffer pool instance로 나누어 동시성을 향상 시키는 것이 가능
# innodb_flush_log_at_trx_commit : 0은 성능 중심, 1은 안정성 중심
# innodb_flush_method : O_DIRECT – 데이터 읽기/쓰기에 OS 캐시를 사용하지 않다 바로 MySql/MariaDB에서 가져 오겠다는 설정. 쓰기 성능은 나빠질 수 있지만 더블 버퍼링을 막아 메모리를 효율적으로 사용하겠다는 것. O_DSYNC – 데이터 읽기/쓰기에 OS 캐시를 사용 속도는 더 빠르지만 대기 시간, 충돌로
 데이타가 일관적이지 않을 수 있다고 함
# innodb_io_capacity : InnoDB 변경 성능은 플러쉬 속도, 즉 스토리지 I/O 속도에 의존하므로 빠른 스토리지 사용 필요. 현재 사용하고 있는 디스크의 IOPS와 유사한 값 설정. SSD와 같이 속도가 빠른 스토리지는 값을 올리고, 일반 HDD라면 값을 내린다.

default_storage_engine = InnoDB
innodb_buffer_pool_size        = 20G
innodb_buffer_pool_instances   = 4
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_log_buffer_size         = 8M
innodb_open_files              = 400
innodb_stats_on_metadata       = 0
innodb_io_capacity             = 15000

innodb                         = FORCE
innodb_strict_mode             = 1


# Query Cache Setting
query_cache_limit              = 100M
query_cache_size               = 512M
query_cache_type               = 1

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]
max_connect_errors             = 1000000

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.5]
datadir=/data/db-data
socket=/data/db-data/mysql.sock

[client]
socket=/data/db-data/mysql.sock

 

6. Maria Backup을 위한 계정생성

root@Mariadb:~# mysql -u root -p mysql
mysql> CREATE USER 'mariabackup'@'127.0.0.1' IDENTIFIED BY 'password';
mysql> CREATE USER 'mariabackup'@'%' IDENTIFIED BY 'password';
mysql> CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, BINLOG MONITOR, REPLICATION CLIENT ON *.* TO 'mariabackup'@'%';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, BINLOG MONITOR, REPLICATION CLIENT ON *.* TO 'mariabackup'@'127.0.0.1';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, BINLOG MONITOR, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
mysql> GRANT CREATE, INSERT ON PERCONA_SCHEMA.* TO 'mariabackup'@'%';
mysql> GRANT CREATE, INSERT ON PERCONA_SCHEMA.* TO 'mariabackup'@'127.0.0.1';
mysql> GRANT CREATE, INSERT ON PERCONA_SCHEMA.* TO 'mariabackup'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> exit