[CentOS] MariaDB 설치 및 세팅

 

#1. MariaDB 설치 및 설정 변경하기

 

1) MariaDB 설치하기

 

YUM Repolist 저장소에 MairaDB  추가하기

 

$ sudo vim /etc/yum.repos.d/MariaDB.repo
[mariadb]
name=MariaDB
baseurl=http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

 

MariaDB-server와 MariaDB-client를 설치한다.

 

$ sudo yum install -y MariaDB-server
$ sudo yum install -y MariaDB-client

 

설치작업이 완료되면 아래와같이 MariaDB의 설치가 잘 진행되었는지 확인해 보자.

$ rpm -qa | grep MariaDB
MariaDB-compat-10.4.28-1.el7.centos.x86_64
MariaDB-common-10.4.28-1.el7.centos.x86_64
MariaDB-client-10.4.28-1.el7.centos.x86_64
MariaDB-server-10.4.28-1.el7.centos.x86_64

 

2) MariaDB 시간( KST ) 및 문자셋( UTF-8 ) 변경

▶ 한국 KST 시간 설정

[mysqld]
default-time-zone='+9:00'

 

▶ 문자셋 UTF-8 변경

[mysqld]
init_connect=SET collation_connection=utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8 
collation-server=utf8_general_ci

      ~~ 이 하 생 략 ~~
      
[client]
default-character-set=utf8

[mysqldump] 
default-character-set=utf8 

[mysql] 
default-character-set=utf8

 

 

※ 전체 코드

 

$ sudo vim /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 only for the mysqld standalone daemon
[mysqld]

# KST 한국 표준시 설정
default-time-zone='+9:00'

# UTF-8 문자셋 변경
init_connect=SET collation_connection=utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8 
collation-server=utf8_general_ci

      ~~ 이 하 생 략 ~~

# server.cnf 가장 하단에 적어준다
[client]
default-character-set=utf8

[mysqldump] 
default-character-set=utf8 

[mysql] 
default-character-set=utf8

※ 문제가 되진 않지만 한글로 적은 주석은 삭제한다.

 

 

3) 관리자 계정 비밀 번호 변경 및 관리자 계정 접속

 

MairaDB를 실행하기전 접속할 관리자 계정( root ) 비밀번호를 변경하여 준다.

$ sudo /usr/bin/mysqladmin -u root password '관리자_비밀번호'

 

 

관리자 비밀번호를 변경했다면 MariaDB를 실행한다.

$ sudo systemctl start mariadb
$ netstat -anp | grep 3306
(No info could be read for "-p": geteuid()=1000 but you should be root.)
tcp6       0      0 :::3306                 :::*                    LISTEN      -

 

 

포트는 3306, 데몬이름은 mysqld 이다.

 

관리자 계정으로 MariaDB에 접속한다.

 

$ sudo mysql -u root -p
Enter password: 관리자_비밀번호
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.4.28-MariaDB 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의 시간 설정과 문자셋을 변경하였다.

 

해당 내용이 잘 적용 되었는지 한번 확인해 보자.

 

한국 KST 시간 변경 확인

 

MariaDB> SHOW VARIABLES WHERE Variable_name LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | KST    |
| time_zone        | +09:00 |
+------------------+--------+
2 rows in set (0.016 sec)

 

 

UTF-8 문자셋 변경 확인

 

MariaDB> show variables like 'c%';
+----------------------------------+----------------------------+
| Variable_name                    | Value                      |
+----------------------------------+----------------------------+
| character_set_client             | utf8                       |
| character_set_connection         | utf8                       |
| character_set_database           | latin1                     |
| character_set_filesystem         | binary                     |
| character_set_results            | utf8                       |
| character_set_server             | latin1                     |
| character_set_system             | utf8                       |
| character_sets_dir               | /usr/share/mysql/charsets/ |
| check_constraint_checks          | ON                         |
| collation_connection             | utf8_general_ci            |
| collation_database               | latin1_swedish_ci          |
| collation_server                 | latin1_swedish_ci          |
| column_compression_threshold     | 100                        |
| column_compression_zlib_level    | 6                          |
| column_compression_zlib_strategy | DEFAULT_STRATEGY           |
| column_compression_zlib_wrap     | OFF                        |
| completion_type                  | NO_CHAIN                   |
| concurrent_insert                | AUTO                       |
| connect_timeout                  | 10                         |
| core_file                        | OFF                        |
+----------------------------------+----------------------------+
20 rows in set (0.000 sec)

 

4) 부팅시 MariaDB 자동 재시작 설정

 

 

MariaDB 설치가 완료되었다면 CentOS7 재부팅 시 자동 실행되게 설정을 적용한다.

 

$ sudo systemctl enable mariadb
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mariadb.service.
Created symlink from /etc/systemd/system/mysqld.service to /usr/lib/systemd/system/mariadb.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

 

 

부팅시 자동 시작 확인

$ sudo systemctl is-enabled mariadb
enabled

enabled 라고 나타나면 CentOS7 재부팅시 MariaDB가 자동 시작된다.

 

---

 

 

#2. 마리아 DB 원격 접속 설정

 

참고 : https://wickedmagica.tistory.com/104

[MariaDB] root 계정의 비밀번호 및 보안 설정 - Ubuntu

참고:https://saakmiso.tistory.com/81

 

 

$ sudo 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
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): Enter
OK, successfully used password, moving on...

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] Y
Enabled 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] Y
 ... 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] Y
 ... 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] Y
 - 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] Y
 ... 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!

 

---

 

 

#3. 사용자 계정 추가

 

 

관리자 계정으로 MariaDB에 접속한다.

 

$ sudo mysql -u root -p
Enter password: 관리자_비밀번호

 

접속하면 아래 작업을 순서대로 하나씩 진행해 나간다.

 

1) DataBase 생성

MariaDB> CREATE DATABASE 데이터_베이스;
Query OK, 1 row affected (0.000 sec)

 

 

2) DataBase 생성 확인

MariaDB> SHOW Databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| 데이터_베이스      |
+--------------------+

 

 

3) MariaDB 사용자 계정 생성

MariaDB> CREATE USER '사용자_계정'@'%' IDENTIFIED BY '사용자_비밀번호';
Query OK, 0 rows affected (0.002 sec)

MariaDB> CREATE USER '사용자_계정'@'localhost' IDENTIFIED BY '사용자_비밀번호';
Query OK, 0 rows affected (0.002 sec)

4) 사용자 계정 권한 설정

 

생성한 DataBase에 대한 사용자 계정의 접근을 포함한 CRUD등의 모든 권한 부여 한다.

 

 

※ 참고

DataBase의 사용자 계정은 CRUD 및 기타 권한을 가진다.
평소 개발을 진행할때는 "ALL PRIVILEGES" 모든 권한을 전부 준 계정을 사용하는 편이지만
상황에 맞추어 권한을 변경하게 된다.

예로 실운영될 완성된 프로젝트에서는 DataBase의 SELECT, INSET, UPDATE, DELETE 권한만을 부여하고
접근 가능한 HOST 설정은 "%"가 아닌 "127.0.0.1( localhost )"를 사용하여
보안성을 강화하는 형태로 사용할 수 있다.

예) 보안 등급 지정 참고 예시
① DBA : ALL PRIVILEGES
② 설계자 : CREATE, DROP, ALTER, INDEX, INSERT, SELECT, INSERT, UPDATE, DELETE
③ 개발자, 실운영 : SELECT, INSERT, UPDATE, DELETE

 

 

① DBA

MariaDB> GRANT ALL PRIVILEGES ON 데이터_베이스.* TO '사용자_계정'@'%' WITH GRANT OPTION;

 

② 설계자

MariaDB> GRANT CREATE, DROP, ALTER, INDEX, SELECT, INSERT, UPDATE, DELETE ON 데이터_베이스.* TO '사용자_계정'@'%' WITH GRANT OPTION;

 

 

③ 개발자, 실운영

MariaDB> GRANT SELECT, INSERT, UPDATE, DELETE ON 데이터_베이스.* TO '사용자_계정'@'%' WITH GRANT OPTION;

 

 

5) 사용자 계정 생성 및 설정 변경사항 저장

MariaDB> FLUSH PRIVILEGES;

변경사항 적용 유무 확인

MariaDB> SELECT Host, User, Password, Super_priv FROM mysql.user;
+-----------+-------------+-------------------------------------------+------------+
| Host      | User        | Password                                  | Super_priv |
+-----------+-------------+-------------------------------------------+------------+
| localhost | mariadb.sys |                                           | N          |
| localhost | root        | *712B69033BB2C08B8E1243774687F3DE968DBBF4 | Y          |
| localhost | mysql       | invalid                                   | Y          |
| %         | 사용자_계정 | *712B69033BB2C08B8E1243774687F3DE968DBBF4 | N          |
| localhost | 사용자_계정 | *712B69033BB2C08B8E1243774687F3DE968DBBF4 | N          |
+-----------+-------------+-------------------------------------------+------------+

 

---

 

 

#4. DB관리 Tool을 이용한 원격지에서 MariaDB 접속

https://dbeaver.io/download/

Download | DBeaver Community

 

 

---

 

 

#5. Cron을 사용한 MariaDB 데이터 베이스 자동 백업 프로세스

 

1) MariaDB 덤프 파일 Backup 디렉토리 생성

 

DataBase 백업 쉘 스크립트를 실행할 유틸티티( utility ) 디렉토리를 생성한다.

 

$ sudo mkdir /home/utility
$ ls /home -al
drwxr-xr-x.  5 root     root      49  7월 26 18:21 .
dr-xr-xr-x. 17 root     root     224  7월 26 16:23 ..
drwxr-xr-x.  3 root     root      60  7월 26 17:55 utility

 

 

utility 디렉토리아래 database_backup 디렉토리를 추가한다.

 

$ sudo mkdir /home/utility/database_backup
$ ls /home/utility -al
drwxr-xr-x. 3 root root   60  7월 26 17:55 .
drwxr-xr-x. 5 root root   49  7월 26 18:21 ..
drwxr-xr-x. 2 root root 4096  7월 26 17:55 mariadb_backup

2) 백업 Shell Script 작성 및 실행

 

먼저 MariaDB 데이터 베이스를 백업하는 쉘 스크립트를 작성한다.

 

$ sudo vim /home/utility/database_backup_script.sh

 

 

『 database_backup_script.sh 』 라는 이름의 쉘 스크립트를 생성한다.

#!/bin/bash

# 날짜 포맷
NowTime=$(date +%Y%m%d%H%M)

# home 디렉토리 경로
HomeDirPath="/home"

# DataBase 백업 디렉토리 경로
BackDirPath="/home/utility/database_backup"

# mysqldump 덤프 파일(*.sql) 생성
# mysqldump -u사용자_계정 -p사용자_비밀번호 데이터베이스_이름 > 백업_디렉토리_경로/"데이터베이스_이름_"날짜_포맷.sql
mysqldump -usaakmiso -p1q2w3e example_database > ${BackDirPath}/"example_database_"${NowTime}.sql
mysqldump -uwicked -p1q2w3e magicworkshop_database > ${BackDirPath}/"magicworkshop_database_"${NowTime}.sql

# DB 백업 파일 -15일 경과 후 삭제 
find ${BackDirPath} -type f -name "*.sql" -mtime +15 -exec rm -f {} \;

 

위 『 database_backup_script.sh 』 쉘 스크립트는

지정한 2개의 데이터 베이스에 대하여 mysqldump 명령을 사용하여

*.sql 확장자를 사용하는 덤프 파일을 만들고 /home/utiltiy/database_backup 디렉토리에 저장한다.

 

mysqldump 명령

 

 

 

 

이후 15일이 경과되면 /home/utiltiy/database_backup 경로에 존재하는 15일 이전에 생성된 *.sql 파일을 삭제한다.

 

 

이제 작성한 『 database_backup_script.sh 』 파일을 실행한다.

 

$ sudo bash /home/utility/database_backup_script.sh

 

쉘 스크립트를 실행 하였다면 데이터베이스의 덤프(*.sql)파일이 잘 생성 되었는지 확인해보자.

 

 

$ ls /home/utility/database_backup/ -al
drwxr-xr-x. 2 root root     238  7월 26 18:57 .
drwxr-xr-x. 4 root root      84  7월 26 18:57 ..
-rw-r--r--. 1 root root    4501  7월 26 18:57 첫번째_데이터베이스_YYYYMMDDHHMM.sql
-rw-r--r--. 1 root root    3439  7월 26 18:57 두번째_데이터베이스_YYYYMMDDHHMM.sql

 

위와 같이 데이터베이스_YYYYMMDDHHMM.sql 파일이 생성된 것을 확인 할 수 있다.

 

 

 

3) Cron을 사용하여 반복 수행 설정

 

먼저 크론 탭 입력창을 오픈한다.

 

$ sudo crontab -e

매일 자정에 데이터베이스 백업 작업을 반복 수행한다.

 

# 매일 자정에 DataBase 백업 Shell Script 실행
0 0 * * * /bin/bash /home/utility/database_backup_script.sh