라즈베리파이(21) MariaDB 구축
MariaDB
마리아DB(MariaDB) 이란?
: MariaDB는 MySQL 개발자들이 오라클에서 뛰쳐나와 만든 MySQL의 클론 버전이다.
마리아DB(MairaDB) vs 오라클 차이점
MariaDB 구축
MariaDB 설치
: apt-get 명령어로 설치를 진행한다.
- 패키지 업데이트 선행
sudo apt-get update sudo apt-get upgrade - apt-get 명령어로 설치
sudo apt-get update sudo apt-get upgrade sudo apt-get install mariadb-server - 정상 테스트 ```bash //mariaDB 서비스 시작 명령어 $ service mariadb start
//mariaDB 상태확인 ps -ef | grep mysql netstat -antp - grep mysql
//mariaDB 시작 명령어 //mysql -u [유저명] $ mysql -u root -p
// mysql 이라는 데이터베이스의 root계정 // 비밀번호 셋팅을 확인! MariaDB [(none)]> select user, host, password from mysql.user;
4. 설치완료
## MariaDB 보안 초기설정
: 설치가 완료된 후에는 다음 명령어를 사용하여 DB의 root 패스워드 및 각종 보안 설정을 해준다.
```bash
$ sudo mysql_secure_installation
> 설정 1 : Set root password? [Y/n] y
> - ⇒ DB의 root #초기 패스워드를 설정합니다. 기본적으로 설치시에는 #DB의 패스워드가 없기때문에 y를 눌러서 무조건 #설정해줍시다
>
> 설정 2 : Remove anonymous users? [Y/n] y
> - ⇒ #익명 사용자를 제거합니다. 보안상 y 해줍시다
>
> 설정 3 : Disallow root login remotely? [Y/n] n
> - ⇒ 원격 제어 여부입니다. 우리는 원격 제어를 위해 n #을 해줍니다
>
> 설정 4 : Remove test database and access to it? [Y/n] y
> - ⇒ 초기 테스트 DB 삭제 여부입니다. 사용을 #할꺼면 n 아니면 y 을 해줍시다. 저는 제가 만들어서 #테스트 할꺼라서...삭제!!
>
> 설정 5 : Reload privilege tables now? [Y/n] y
> - ⇒ #DB reload 를 통한 지금까지의 설정 저장 여부입니다. #당연히 y!!
MariaDB 외부접속허용처리 초기설정
: 애플리케이션 서버와 디비 인스턴스를 분리하면서,
원격으로 DB에 커넥션 연결을 요청해야했다.
그렇게 하기 위해서는 DB 인스턴스에 mysql을 설치하고,
mysql.cnf 파일에서 보안 그룹에 알맞은 port와
bind-address를 설정해주어야한다.
bind-address란?
데이터베이스 서버가 어떤 주소로의 요청을 허용할 것인지 설정하는 것이다. 그래서 0.0.0.0/0을 설정하게 되면 네트워크 전체 대역, 즉 모든 트래픽에 대해서 요청을 허용한다 뜻이기에 문제없이 원격 커넥션이 이루어진다.
//Mysql, MariaDB는 보안정책으로 127.0.0.1 으로
//바인딩되어있는것을 확인 가능하다.
$ netstat -an | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
//50-server.cnf 파일 열기.
$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
//내용중 아래 라인을 주석처리.
bind-address = 127.0.0.1 //AS-IS
#bind-address = 127.0.0.1 //TO-BE
//서버 재기동으로 적용
$ sudo service mysql restart
//다시 netstat 명령으로 확인해보면
//mariadb의 3306 포트가 0.0.0.0 에
//바인딩 된 것을 알 수 있다.
$ netstat -antp | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 10927/mysqld
MariaDB 포트포워딩 설정
: 현재기준 DB서버 라즈베리파이측으로 포트포워딩이 필요하다
내부IP: 192.168.0.13
포트외부: 3306 / 내부: 3306
프로토콜: TCP
MariaDB 방화벽처리
: MariaDB 기본 포트(3306)를 허용한다.
// 방화벽(3306 TCP포트) 허용
$ ufw allow 3306/tcp
//서버 재기동으로 적용
$ sudo service mysql restart
MariaDB 사용자 계정 생성
: 사용자 계정을 생성 + 권한부여
// ------------------------------
// 사용자 삭제
// ------------------------------
// drop user '계정아이디'@'접속위치';
// drop user 'INSTC'@'%';
// ------------------------------
// 사용자 생성
// ------------------------------
//create user '사용자'@'IP' identified by '비밀번호';
// 사용자 : 사용자 아이디 입력
// IP : 접근을 허용할 IP 입력
// -(case1). localhost(로컬 접속),
// -(case2). 1.2.3.4(1,2,3,4 IP만 허용)
// -(case3). 1.2.3.%(1,2,3 번대 IP 허용)
// -(case4). %(모든 IP 허용)
// 비밀번호 : 사용자 비밀번호 입력
MariaDB [(none)]> create user 'MT01301'@'%' identified by 'a';
// ------------------------------
// 생성한 계정으로 db 접속가능한지 확인
// ------------------------------
$ mysql -u MT01301 -p;
Enter password: [패스워드 입력]
Mysql 버전별 비권장 사용자생성 문법 MySQL 8부터는 더 이상 GRANT명령을 사용하여 (암시적으로) 사용자를 생성할 수 없다. 대신 CREATE USER 를 사용하고 그 뒤에 GRANT 문이 온다.
// MySQL 10버전 이상부터 다음과 같이 사용 MariaDB [(none)]> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD'; MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB 권한
MariaDB 권한조회
: 권한을 조회하는 방법
// 기본권한 조회
// select * from mysql.user where user = [사용자ID]
// 기본권한조회 (원격접속권한보기)
// 초기설치에는 root계정 1개이며 localhost 만 접속할 수 있음
// SELECT Host,User,authentication_string FROM mysql.user;
// 사용자별 권한 조회
// SHOW GRANTS FOR '사용자계정'@'호스트';
// 접속된 계정 권한 확인
// SHOW GRANTS FOR CURRENT_USER;
MariaDB 계정별 권한추가
: 생성한 사용자 계정에게 권한을 부여하는 방법.
// 등록된 사용자에게 권한추가하기
// ------------------------------
// DB, 테이블, 원격접속IP 권한추가 방법
// ------------------------------
// IP(접속위치): IP부분에는 접근을 허용할 IP 입력
// -(case1). localhost(로컬 접속),
// -(case2). 1.2.3.4(1,2,3,4 IP만 허용)
// -(case3). 1.2.3.%(1,2,3 대역대 IP 허용)
// -(case4). %(모든 IP 허용)
// (유형1). 특정DB(특정테이블)에 대해 권한허용
// grant all privileges on DB이름.테이블 to '계정아이디'@'IP';
// (유형2). 특정DB(전체 테이블)에 대해 권한허용
// grant all privileges on DB이름.* to '사용자'@'IP';
// (유형3). 전체 DB(전체 테이블에)에 대한 권한허용
// grant all privileges on *.* to '사용자'@'IP';
// ------------------------------
// DML 권한추가 방법
// ------------------------------
// (유형1) 특정 데이터베이스의 특정 테이블에 select 권한허용
// grant select on DB이름.테이블명 to '사용자'@'localhost';
// (유형2) 특정 데이터베이스의 특정 테이블에 select, insert 권한허용
// grant select, insert on DB이름.테이블명 to '사용자'@'localhost';
// (유형3) 특정 데이터베이스의 특정 테이블의 컬럼1과 컬럼2의 update 권한허용
// grant update(컬럼1, 컬럼2) on DB이름.테이블명 to '사용자'@'localhost';
// ------------------------------
// 원상복구 방법
// ------------------------------
//(유형1)
MariaDB [(none)]> DELETE FROM mysql.user WHERE Host='%' AND User='root';
MariaDB [(none)]> FLUSH PRIVILEGES;
// (유형2)
revoke all on DB이름.테이블명 from userid@host
MariaDB [(none)]> grant all privileges on *.* to INSTC@'%';
MariaDB [(none)]> grant all privileges on *.* to root@'%';
MariaDB [(none)]> flush privileges;
flush privileges 명령어
현재 사용중인 MySQL의 캐시를 지우고 새로운 설정을 적용하기 위해 사용합니다. 이 명령어를 사용하려는 사용자는 reload권한을 가지고 있어야 합니다.즉 말그대로 MySQL의 환경 설정을 변경할경우, MySQL의 재시작 없이 변경한 설정부분을 적용시키고자 할 때 사용합니다. 데이터베이스의 TABLE의 추가, 삭제 등의 변경은 MySQL 환경 설정이 아니므로 해당 명령어가 필요가 없습니다. 하지만 id, 패스워드가 추가 & 수정 되었을 경우, 환경 설정이 변경되었기에 해당 명령어가 필요합니다.
*2건 권한 반영 후 조회 *
SQL클라이언트 접속테스트
: 포트포워딩, 방화벽, bind-address주석처리, public한 계정이 준비되었다면 sql 클라이언트로 외부에서 접속이가능한지 테스트해본다.
SQL Client라는 안드로이드 앱을 사용했다.
정상적으로 접속이 된다^^!
DDL, DML
DB, 테이블을 생성한다
데이터베이스 생성
// ------------------------------
// 새로만든 계정으로 접속
// ------------------------------
MariaDB [(none)]> mysql -u INSTC -p
Enter password: [비밀번호 입력]
// ------------------------------
// DB 생성
// DB표준명명설계
// DS###@#
// > DS: 데이터베이스를 나타내는 DS
// > ###: 대표업무코드 3자리
// > @: 환경구분(D:개발, S:스테이징, C:운영, E:교육, M:전환)
// > #[일련번호]: 0부터시작
// DB명은 대문자로한다.
// 시스템코드가아닌 대표업무코드 기준으로 명명한다.
// 대표업무코드 별로 다수의 DB가 필요한 경우 "맨끝에 #+숫자" 를 추가입력 및 구분하여 '0'부터 시작하도록한다.
// ex) DSSZCD0 상품처리 개발 DB
// ------------------------------
MariaDB [(none)]> CREATE DATABASE DSDBDO0;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| DSDBDO0 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
테이블 생성
// ------------------------------
// 새로 만든 DB 접속
// ------------------------------
MariaDB[(none)]> use DSDBDO0
// ------------------------------
// 테이블 생성
// ------------------------------
MariaDB [DSDBDO0]> --테이블생성
MariaDB [DSDBDO0]> CREATE TABLE TBDBDW001
-> (
-> 회원일련번호 VARCHAR(8) NOT NULL
-> ,업체명 VARCHAR(130)
-> ,사업자등록번호 VARCHAR(10)
-> ,우편번호 VARCHAR(6)
-> ,사업장기본주소 VARCHAR(100)
-> ,사업장상세주소 VARCHAR(150)
-> ,사업장전화번호 VARCHAR(20)
-> ,업체이메일주소 VARCHAR(70)
-> ,유효기간년월일 VARCHAR(8)
-> ,회원신규가입년월일 VARCHAR(8)
-> ,유료서비스여부 VARCHAR(1)
-> ,서비스탈퇴여부 VARCHAR(1)
-> ,시스템최종갱신일시 TIMESTAMP(6) NOT NULL
-> ,시스템최종갱신식별자 VARCHAR(12) NOT NULL
-> ,시스템최종거래일시 VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.093 sec)
인덱스(index) 생성
// ------------------------------
// 인덱스생성
// ------------------------------
MariaDB [DSDBDO0]> -- 인덱스설정 MariaDB [DSDBDO0]> CREATE UNIQUE INDEX XBDBDW001P
CREATE UNIQUE INDEX XBDBDW001P
-> ON TBDBDW001 (회원일련번호);
Query OK, 0 rows affected (0.107 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [DSDBDO0]>
기본키(primary key) 설정
: 테이블생성 시점에 못한경우 진행한다.
// ------------------------------
// 기본키설정
// ------------------------------
MariaDB [DSDBDO0]> ALTER TABLE TBDBDW001 ADD CONSTRAINT XBDBDW001P PRIMARY KEY (회원일련번호);
Query OK, 0 rows affected, 1 warning (0.106 sec)
Records: 0 Duplicates: 0 Warnings: 1
MariaDB [DSDBDO0]>
코멘트(Comment) 설정
: 테이블생성 시점에 못한경우 진행한다.
- MySQL 에서 컬럼 코멘트만 깔끔하게 변경하는 방법은 없다.
- MODIFY 또는 CHANGE 로 전체 컬럼을 변경해야한다.
- 컬럼 comment 설정시 적용하는게 10000만배 쉽다… 꼭 선행하자..
// ------------------------------
// 코멘트 설정
// ------------------------------
// 테이블코멘트
ALTER TABLE TBDBDW001 COMMENT = 'DBD회원기본';
// 적용한 테이블코멘트 확인
SELECT table_name, table_comment
FROM information_schema.tables
WHERE table_schema = 'DSDBDO0' AND table_name = 'TBDBDW001';
// 컬럼코멘트
/*ALTER TABLE [테이블명] MODIFY [컬럼명] [데이터타입] [제약조건] COMMENT 'column1 comment';*/
ALTER TABLE DSDBDO0.TBDBDW001 MODIFY COLUMN 회원일련번호 varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '회원일련번호';
ALTER TABLE DSDBDO0.TBDBDW001 MODIFY COLUMN 회원신규가입년월일 varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL NULL COMMENT '회원신규가입년월일';
..(중략)
시퀀스(Sequence) 설정
:ID 값을 숫자가 아닌 다양한 방식으로 저장하고 싶은 경우 사용한다. Thread-safety 하게 ID를 발급 받을 수 있으며, AUTO_INCREMENT 대신 사용할 수 있다. Sequence는 값을 캐시하므로 경우에 따라 AUTO_INCREMENT 보다 빠를 수 있다고 한다.
MariaDB의 Sequence 기능은 10.3버전 부터 지원됩니다.
// SELECT VERSION(); 버전확인
// ------------------------------
// 시퀀스생성
// ------------------------------
MariaDB [DSDBDO0]> CREATE SEQUENCE DSDBDO0.SQ_TBDBDW001_01
-> start with 1
-> increment by 1
-> minvalue 1
-> maxvalue 999999
-> cycle;
Query OK, 0 rows affected (0.030 sec)
MariaDB [DSDBDO0]>
// ------------------------------
// 시퀀스조회
// ------------------------------
MariaDB [DSDBDO0]> SELECT * FROM SQ_TBDBDW001_01;
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
| 1 | 1 | 999999 | 1 | 1 | 1000 | 1 | 0 |
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
1 row in set (0.001 sec)
// 마지막 시퀀스 조회
MariaDB [DSDBDO0]> SELECT LASTVAL(SQ_TBDBDW001_01);
+--------------------------+
| LASTVAL(SQ_TBDBDW001_01) |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.001 sec)
// 시퀀스 증가
MariaDB [DSDBDO0]> SELECT NEXTVAL(SQ_TBDBDW001_01);
+--------------------------+
| NEXTVAL(SQ_TBDBDW001_01) |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.001 sec)
// 시퀀스 증가 후 다시확인해보면 마지막값이 증가되어짐
MariaDB [DSDBDO0]> SELECT LASTVAL(SQ_TBDBDW001_01);
+--------------------------+
| LASTVAL(SQ_TBDBDW001_01) |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.001 sec)
// 시퀀스 초기화 (그리고 NEXTVAL 해보면 다시 1부터 셋팅되어짐)
MariaDB [DSDBDO0]> ALTER SEQUENCE SQ_TBDBDW001_01 RESTART 1;
Query OK, 0 rows affected (0.001 sec)
// 실제 적용방법(업무에 맞는 형태로 프로그래밍)
MariaDB [DSDBDO0]>
MariaDB [DSDBDO0]>
MariaDB [DSDBDO0]> SELECT CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0) );+----------------------------------------------------+
| CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0) ) |
+----------------------------------------------------+
| M0000001 |
+----------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [DSDBDO0]> SELECT CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0) );+----------------------------------------------------+
| CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0) ) |
+----------------------------------------------------+
| M0000002 |
+----------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [DSDBDO0]> SELECT CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0) );+----------------------------------------------------+
| CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0) ) |
+----------------------------------------------------+
| M0000003 |
+----------------------------------------------------+
1 row in set (0.001 sec)
권한(DML) 설정
// ------------------------------
// 권한설정
// ------------------------------
MariaDB [DSDBDO0]> -- 권한설정
MariaDB [DSDBDO0]> GRANT SELECT ON TBDBDW001 TO MT01301;
Query OK, 0 rows affected (0.001 sec)
MariaDB [DSDBDO0]> GRANT INSERT ON TBDBDW001 TO MT01301;
Query OK, 0 rows affected (0.001 sec)
MariaDB [DSDBDO0]> GRANT DELETE ON TBDBDW001 TO MT01301;
Query OK, 0 rows affected (0.001 sec)
MariaDB [DSDBDO0]> GRANT UPDATE ON TBDBDW001 TO MT01301;
Query OK, 0 rows affected (0.001 sec)
MariaDB [DSDBDO0]>
데이터 삽입(insert)
// ------------------------------
// 테이블 데이터 삽입
// ------------------------------
MariaDB [DSDBDO0]> INSERT INTO TBDBDW001 VALUES (
-> (SELECT CONCAT('C', LPAD(NEXTVAL(SQ_TBDBDW001_01), 7, 0)))
-> , '최초테스트기업'
-> , '9999999999'
-> , null
-> , '인천광역시 인하로 999'
-> , null
-> , '0328635363'
-> , 'test@naver.co.kr'
-> , '20301231'
-> , '20221123'
-> , 'Y'
-> , 'N'
-> , date_format(sysdate(), '%Y/%m/%d %H:%i:%s')
-> , 'test'
-> , date_format(sysdate(6), '%Y%m%d%H%i%s%f')
-> );
Query OK, 1 row affected (0.010 sec)
Leave a comment