내부망에서 리눅스 MariaDB 설치 방법 / 기존 DB 마이그레이션 방법

2024-06-11

인터넷 연결이 안되어서 yum 명령어를 사용할 수 없는 리눅스 서버에 바이너리 파일로 MariaDB 설치하는 방법입니다.
정보보안팀 방화벽으로 막혀있는 사내 개발서버에 개발DB를 설치하고, 기존 RDS DB 이관을 진행하였습니다.

리눅스 MariaDB 설치

MariaDB 설치파일 다운

https://mariadb.org/download
MariaDB Server 탭 > 프로젝트에 맞는 MariaDB Server Version 선택 > Operating System : Linux 선택 > Architecture : 64비트 서버는 x86_64 선택 > Init System : Systemd 선택 > Download 버튼 클릭

rpms.tar 파일은 yum 명령어로 설치할 때 적합한 것 같아서, Systemd tar.gz 파일로 받았습니다.
압축 해제하면 나오는 scripts 폴더 안에 mariadb-install-db 파일이 있어야 합니다.

파일질라로 파일 업로드

리눅스 서버의 유저 Home에 MariaDB 설치파일을 업로드 합니다.

MariaDB 설치파일 압축 해제

tar -zxvf mariadb-10.6.18-linux-systemd-x86_64.tar.gz

z 옵션은 gzip으로 압축된 파일을 해체할 때 사용됩니다.

폴더 소유자 생성 및 변경

groupadd mariadb
useradd -g mariadb mariadb
chown -R mariadb:mariadb mariadb-10.6.18-linux-systemd-x86_64

그룹 및 유저를 생성하고 MariaDB 설치 폴더의 소유자를 변경합니다.

MariaDB 설치 경로 변경

mv mariadb-10.6.18-linux-systemd-x86_64 /usr/local

/usr/local 폴더 아래로 mariadb 폴더를 이동합니다.

심볼릭 링크 생성

ln -s /usr/local/mariadb-10.6.18-linux-systemd-x86_64 /usr/local/mariadb

심볼릭 링크를 생성하면 실제 폴더명을 변경하지 않고도 간결하게 접근할 수 있습니다.

MariaDB 환경변수 추가

vi /etc/profile

가장 하단에 아래의 내용을 추가하고 저장 후, source 명령어를 통해 수정 내용을 적용합니다.

export MARIADB_HOME=/usr/local/mariadb
export PATH=$PATH:$MARIADB_HOME/bin:.

수정한 환경변수 적용

source /etc/profile

MariaDB 설치

cd /usr/local/mariadb/scripts

./mysql_install_db --user=mariadb --basedir=/usr/local/mariadb --datadir=/var/lib/mysql/data

–user 옵션에 유저명, –basedir 옵션에 MariaDB 설치 경로를 적어주면 됩니다.

정상 설치 완료 메세지

Installing MariaDB/MySQL system tables in '/var/lib/mysql/data' ...
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 mariadb@localhost, it has no password either, but
you need to be the system 'mariadb' 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/mariadb' ; /usr/local/mariadb/bin/mariadbd-safe --datadir='/var/lib/mysql/data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mariadb/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/

MariaDB 설정 파일 생성

vi /etc/my.cnf

아래와 같이 입력 후 저장합니다.

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin

basedir=/usr/local/mariadb
datadir=/var/lib/mysql/data

[mysqld_safe]
log-error=/var/lib/mysql/log/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

basedir은 mariaDB 설치 경로로 수정해야 합니다.

MariaDB 서비스 설정 수정

cd /usr/local/mariadb/support-files/systemd
vi mariadb.service

파일 중간 이하에서 설정 정보를 아래와 같이 수정합니다.

User=mariadb
Group=mariadb

ExecStart=/usr/local/mariadb/bin/mariadbd --defaults-file=/etc/my.cnf

User와 Group은 mariaDB 폴더 소유자로 수정해야 합니다.
ExecStart 경로는 mariaDB 설치 경로에 맞게 수정해주면 됩니다.

MariaDB 서비스 systemd 등록

cp mariadb.service /etc/systemd/system

서비스 설정 재변경 후에는 데몬 재로드를 통해 변경사항을 적용해야 합니다.

데몬 재로드 방법

systemctl daemon-reload

MaraiDB 서비스 실행

systemctl start mariadb.service

/etc/systemd/system 하위 mariadb.service 파일을 통해 MariaDB 서비스를 실행합니다.

MaraiDB 서비스 상태 확인

systemctl status mariadb.service

Active: active (running)으로 나오면 정상 작동하는 것입니다.

MaraiDB 서비스 종료

systemctl stop mariadb.service

MaraiDB 서비스 재시작

systemctl restart mariadb.service

MariaDB 연결 방법

MariaDB 루트 계정 접속

mysql -uroot

root 계정에는 -p 옵션 없이 패스워드를 입력하지 않고 접속 가능합니다.

DB 접속 불가 에러 해결

mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

위와 같이, libncurses.so.5 라이브러리를 찾을 수 없어 MariaDB 접속이 불가하면 아래 명령어를 실행합니다.

ln -s /usr/lib64/libncurses.so.6.1 /usr/lib64/libncurses.so.5

libncurses.so.6.1 (상위 버전)으로 5 버전 호환이 되므로, 심볼릭 링크 등록만 해주면 해결됩니다.

MariaDB 계정 생성

CREATE USER 'admin'@'%' IDENTIFIED BY '비밀번호';

보안을 위해 root 계정은 직접 사용하지 않고, 관리용 계정을 새로 생성하는 것이 좋습니다.

계정 접속 권한 설정

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '비밀번호';
FLUSH PRIVILEGES;

admin 계정에 외부 접속할 수 있게 모든 권한을 부여하며 비밀번호를 설정하고, 변경사항을 적용합니다.

권한 정상 적용 메세지

Query OK, 0 rows affected (0.001 sec)

DBeaver에서 DB 연결

좌측 Database Navigator 상단 새 데이터베이스 연결 아이콘 > MariaDB 선택 > 다음 >

Server Host	서버IP(연결하는 컴퓨터와 서버가 같은 내부망에 위치하면 내부IP로 연결 가능)
Database
Username	계정명
Password	암호

위와 같이 DB 정보 입력 > Test Connection 시 Connected 나오면 > 확인

DB 연결 실패 시
리눅스 서버에 MariaDB가 정상적으로 실행 중이고, 내부 연결도 가능한데 외부에서 접근이 불가하다면?
로컬IP에서 서버IP:3306으로 요청할 수 있게 사내 보안팀에 방화벽 허용 요청을 해야 합니다.

DBeaver에서 기존 DB 마이그레이션 방법

신규 DB 생성

신규 DB 연결 하위 Databases 우클릭 > Create New Database >

Database name	기존 DB명 입력
Charset	기존 DB Charset 입력
Collation	기존 DB Collation 입력

위와 같이 DB 정보 입력 > 확인

기존 DB 인코딩 확인 방법
기존 DB 우클릭 > View Database > Default Charset, Default Collation 확인

기존 테이블 DDL 생성

기존 DB에서 데이블 다중 선택 > 우클릭 > SQL 생성 > DDL > Copy

DDL으로 신규 테이블 생성

신규 DB 우클릭 > SQL 편집기 > 새 SQL 편집기 > 복사한 DDL 붙여넣기 > Ctrl + A (전체선택) > Ctrl + Shift + Alt + X (다중 쿼리 일괄 실행) > 테이블 제약조건까지 잘 옮겨졌는지 확인

기존 테이블 INSERT문 내보내기 (.sql 파일)

기존 DB에서 데이블 다중 선택 > 우클릭 > 데이터 내보내기 > SQL INSERT문으로 내보내기 > 다음 > 다음 > Include generated columns 체크 (Auto Increment 컬럼 포함), INSERT 구문 당 로우 개수 1 입력 > 다음 > sql 파일 저장할 로컬 Directory 선택, Write to the single file 체크 해제 > 진행

INSERT문으로 데이터 이관

notepad++로 sql 파일 열어서 모든 쿼리 전체 복사 > 신규 DB SQL 편집기에 붙여넣기 > 전체 선택 후 다중 쿼리 실행 (Ctrl + Alt + Shift + X)

폴인키 제약조건으로 인해 중간에 실패가 날 수 있으니, 한 테이블씩 끊어서 INSERT하는 것이 좋습니다.
DBeaver가 힙 메모리 초과로 종료될 수 있으므로 오토커밋 설정을 체크 후 INSERT 쿼리 실행을 권장합니다.

대용량 Insert 방법
https://0songha0.github.io/tool/2023-02-16-1#대용량-sql-파일-실행-방법

View 이관

기존 DB에서 View 우클릭 > SQL 생성 > DDL > Copy > 옮길 DB에서 쿼리 실행

프로시저 이관

기존 DB에서 Procedure 우클릭 > SQL 생성 > DDL > Copy > 옮길 DB에서 실행

MariaDB 설정 파일 수정

vi /etc/my.cnf

my.cnf 파일 수정하면 반드시 DB를 재시작해야 적용됩니다.

최대 허용 패킷 수 변경

[mysqld]
max_allowed_packet=64M

대용량 INSERT 쿼리 실행 시, 최대 허용 패킷 수를 충분히 높이면 패킷 초과 에러가 나지 않습니다.

쿼리 대소문자 오류 해결

[mysqld]
lower_case_table_names=1

쿼리는 대문자인데 실제 테이블이 소문자여서 테이블 doesn’t exist 에러가 날 때 설정하면 좋은 옵션입니다.