오픈소스 비즈니스 컨설팅 CMS : 마크다운

MariaDB
데이터 폴더 위치 변경
MariaDB JDBC Driver
Galera Cluster
제한 사항
MariaDB 설치
Monitoring
Connection 갯수 설정
Partition Table 관리
참고 문헌

MariaDB

MariaDB를 정리 합니다.

홈페이지 : https://mariadb.org/
다운로드 : https://downloads.mariadb.org/
플랫폼 :
라이선스 :

데이터 폴더 위치 변경

#---   /data/database/mariadb
#---   SELinux 설정 변경
mkdir -p /data/database/mariadb

systemctl stop mariadb.service
vi /etc/my.cnf
    [mysqld]
    datadir=/data/database/mariadb

cd /var/lib/mysql
tar cvf /data/mariadb.tar *

chown mysql:mysql /data/database/mariadb
cd /data/database/mariadb
tar xvf /data/mariadb.tar

yum -y install policycoreutils-python
semanage fcontext -a -t mysqld_db_t "/data/database/mariadb(/.*)?"
restorecon -Rv /data/database/mariadb
chcon --reference=/var/lib/mysql /data/database/mariadb

systemctl start mariadb.service

MariaDB JDBC Driver

다운로드 사이트 : https://downloads.mariadb.org/client-java/

Galera Cluster

Galera Cluster에서 1개의 node만 write로 구성(DeadLock 회피)하고 Auto Increment (Table Lock 발생)를 사용하지 않으면 무단하게 구성할 수 있다고 합니다.

yum install MariaDB-Galera-server MariaDB-client galera

제한 사항

https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html
https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
Table with InnoDB
Row with InnoDB
- 최대 컬럼 갯수 : 1,017개
- 최대 레코드 크기 : Page Size의 1/2 (Default 8 KB (8,196 bytes))
  - Default page size (innodb_page_size) : 16 KB (16,384 bytes)
  - 별도로 저장되는 column은 8~ 12 bytes를 차지 한다.
Row
- 최대 컬럼 갯수 : 4,096개
- 최대 레코드 크기 : 64 KB (65,536 bytes)
Column
- varchar : 64 KB. 동일한 공간에 저장. 인덱스 생성 가능
- varbinary : 64 KB. 동일한 공간에 저장
- text : 별도 공간에 text 데이터 저장. Full text 검색 가능
  - tinytext : 256 bytes
  - text : 64 KB (65,536 bytes)
  - mediumtext : 16 MB
  - longtext : 4GB
- blob : 별도 공간에 바이너리 데이터를 저장
  - tinyblob : 256 bytes
  - blob : 64 KB (65,536 bytes)
  - mediumblob : 16MB
  - longbolb : 4GB

MariaDB 설치

#--- MariaDB 설치
# yum  -y  install  mariadb  mariadb-server
yum  -y  install  mariadb103  mariadb103-server  mariadb103-backup
# yum  -y  install  mariadb103-server-galera

systemctl  enable   mariadb.service
systemctl  restart  mariadb.service

mysql_secure_installation

vi   /etc/my.cnf
    [mysqld]
    #--- Charset Setting
    init_connect="SET collation_connection=utf8_general_ci"
    init_connect="SET NAMES utf8"
    character-set-server=utf8
    collation-server=utf8_general_ci
    skip-character-set-client-handshake
    innodb_log_file_size=5M

    #--- InnoDB Setting
    default-storage-engine=InnoDB
    innodb_file_per_table=1

    #--- binlog setting
    log-bin=/var/lib/mysql/binlog
    max_binlog_size=1G                  #--- 로그 파일 하나당 크기
    # expire_log_days=30                  #--- 30일간 보관

vi  /etc/my.cnf.d/client.cnf
    [client]
    default-character-set=utf8

    [client-mariadb]
    default-character-set=utf8

vi  /etc/my.cnf.d/mysql-clients.cnf
    [mysql]
    default-character-set=utf8

    [mysqldump]
    default-character-set=utf8

systemctl  restart  mariadb.service

mysql  -uroot  -p${PASSWD}  mysql  -e  "show variables like 'c%'"
mysql  -uroot  -p${PASSWD}  mysql  -e  "show variables like 'log_bin'"
mysql  -V


#--- 방화벽 설정
firewall-cmd  --permanent  --add-service=mysql
firewall-cmd  --reload
firewall-cmd  --list-all

Monitoring

-- scadadb 데이터베이스에 포함된 테이블별 크기
SELECT TABLE_NAME, round(((data_length + index_length) / 1024 / 1024), 2) "MB"
  FROM information_schema.TABLES
 WHERE table_schema = "scadadb"
 ORDER BY (data_length + index_length) DESC;

-- scadadb 데이터베이스에 포함된 테이블별 레코드 수
SELECT TABLE_NAME, TABLE_ROWS
  FROM information_schema.TABLES
 WHERE table_schema = "scadadb"
 ORDER BY TABLE_ROWS DESC;

Connection 갯수 설정

#--- 최대 접속 수 확인과 Online 설정
mysql  -uroot  -p비밀번호  mysql
    #--- open_files_limit >= 10 + max_connections + table_cache_size * 2
    #---     open_files_limit가 1024이고 table_cache_size가 400인 경우 
    #---     최대 214 max_connections이 가능
    show  variables  like  'open_files_limit';
    show  variables  like  'table_open_cache';
 
    show  variables  like  'max_connections';
    set  global  max_connections=256;          #--- Online에서 최대 접속 수 설정
    exit;

#--- 설정 파일에서 최대 접속 수 설정
vi  /etc/my.cnf.d/server.cnf
    [mysqld]
    max_connections = 256

#--- open_files_limit 수정 방법
ulimit -a | grep 'open files'

vi  /etc/security/limits.conf
    *            soft    nofile          65536
    *            hard    nofile          65536

vi  /lib/systemd/system/mariadb.service
    [Service]
    LimitNOFILE=4096
systemctl daemon-reload
systemctl mariadb restart

Partition Table 관리

~.frm : 스토리지 엔지 헤더
~.ibd : 테이블 데이터와 인덱스 (테이블 스페이스용 파일)

MariaDB 5.5.44 (MySQL 5.5, SCADA에서 사용하는 버전)
MariaDB 10.0 (MySQL 5.6)
MariaDB 10.1 (MySQL 5.7)
MariaDB 10.8.3 (최신 버전)
- https://mariadb.com/downloads/

-- 파티션 지원 여부 확인
--     partition이 Active이면 지원
SHOW PLUGINS;
SHOW VARIABLES LIKE '%partition%';

-- 파티션 생성 1
CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteKey` varchar(8) NOT NULL,
  `deviceKey` varchar(8) NOT NULL,
  `statusDatetime` varchar(14) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`statusDatetime`),
  KEY `idx001` (`statusDatetime`,`siteKey`,`deviceKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(statusDatetime)
(PARTITION p2022 VALUES LESS THAN ('20229') ENGINE = InnoDB,
 PARTITION p2023 VALUES LESS THAN ('20239') ENGINE = InnoDB,
 PARTITION p9999 VALUES LESS THAN ('99999') ENGINE = InnoDB);

-- 파티션 생성 2
ALTER TABLE tbl1 PARTITION BY RANGE COLUMNS(statusDatetime) (
    PARTITION p2022 VALUES LESS THAN ('20229') ENGINE = InnoDB,
    PARTITION p2023 VALUES LESS THAN ('20239') ENGINE = InnoDB,
    PARTITION p9999 VALUES LESS THAN ('99999') ENGINE = InnoDB
);

-- 테이블의 파티션 정보 조회 1
SHOW CREATE TABLE 'tbl1';

-- 테이블의 파티션 정보 조회 2
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, 
       PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tbl1';
 
-- 파티션 재구성 : 추가
ALTER TABLE tbl1 ADD PARTITION(
    PARTITION p2031 VALUES LESS THAN ('20319') ENGINE = InnoDB,
    PARTITION p2032 VALUES LESS THAN ('20329') ENGINE = InnoDB
);

-- 파티션 재구성 : 분리 (p9999 > p2031, p2032, p999)
ALTER TABLE tbl1 REORGANIZE PARTITION p9999 INTO (
    PARTITION p2031 VALUES LESS THAN ('20319') ENGINE = InnoDB,
    PARTITION p2032 VALUES LESS THAN ('20329') ENGINE = InnoDB,
    PARTITION p9999 VALUES LESS THAN ('99999') ENGINE = InnoDB
);

-- 파티션 재구성 : 병합 (p2031, p2032 > p9999)
ALTER TABLE tbl1 REORGANIZE p2031, p2032 INTO (
    PARTITION p9999 VALUES LESS THAN ('99999') ENGINE = InnoDB
);
  
-- 파티션 내의 데이터 조회  
SELECT *
  FROM tbl1 PARTITION (p2029, p2030);

-- 파티션 삭제 (파티션 내의 데이터도 삭제 된다)
ALTER TABLE tbl1 DROP PARTITION p2019;
ALTER TABLE tbl1 TRUNCATE PARTITION p2019;

-- 파티션 해제 (데이터는 그대로 유지)
ALTER TABLE tbl1 REMOVE PARTITIONING;

-- -----------------------------------------------------------
-- 교체 테이블을 생성하여 Partition 삭제
-- NySQL 5.6부터 지원
-- Lock이나 부하를 최소화하고자 하는 경우 사용
--    1.  교체 테이블 생성
SHOW CREATE TABLE `zztbl1`;
CREATE TABLE `zztbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteKey` varchar(8) NOT NULL,
  `deviceKey` varchar(8) NOT NULL,
  `statusDatetime` varchar(14) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`statusDatetime`),
  KEY `idx001` (`statusDatetime`,`siteKey`,`deviceKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- CREATE TABLE `zztbl1` like `tbl1`;
-- ALTER TABLE `zztbl1` REMOVE PARTITIONING;

-- 파티션과 테이블간의 교체
ALTER TABLE tbl1 EXCHANGE PARTITION p2019 WITH TABLE zztbl1;

SELECT count(*) FROM tbl1 PARTITION(p2019);
SELECT count(*) FROM zztbl1;

-- 파티션 삭제
ALTER TABLE tbl1 DROP PARTITION p2019; 
DROP TABLE zztbl1;

참고 문헌

최종 수정일: 2022-10-24 19:17:28