[Linux] CentOS Stream 9 환경 구축 3 - DNF로 MySQL 설치 / DB 데이터 저장소 변경 및 my.cnf / 계정 생성 및 데이터베이스, 테이블 생성

EUN JY·2024년 3월 25일

linux mysql

Linux

목록 보기

4/9

7. MySQL

CentOS 에서 패키지 설치는 가능한 dnf 를 사용
ProjYj 2022 에서 모든 테스트가 완료되면 Podman image 로 생성
- 우분투 22 my8 환경 만들기.hwp 참고
MySQL 공통 PW : password$$ (기본 설정 > 소문자 구성도 허용)

7-1. MySQL 설치 파일 다운로드

/home/projyj/Downloads 경로에 세팅 > 웹 콘솔에서 다운로드하거나, Windows 다운로드 후 FileZilla 이용
http://repo.mysql.com/yum/mysql-8.0-community/ > el/9/x86_64 목록
- mysql80-community-release-el9-1.noarch.rpm 다운로드
- mysql-community-server-8.0.32-1.el9.x86_64.rpm 다운로드

[root@localhost Downloads]$ ll
total 50304
-rw-r--r--. 1  root  root    10534 Mar 22 10:23 mysql80-community-release-el9-1.noarch.rpm
-rw-r--r--. 1  root  root 51495202 Mar 22 09:59 mysql-community-server-8.0.32-1.el9.x86_64.rpm

🔶 MySQL Yum Repository

https://dev.mysql.com/downloads/repo/yum/
Red Hat Ent Linux 9 버전용 정보 확인
rpm 파일명 : mysql80-community-release-el9-1.noarch.rpm

7-2. MySQL 설치

중간에 오류 발생했으므로, 하단 Error: GPG check FAILED 부분까지 확인 필요
설치 실행

[root@localhost Downloads]$ sudo rpm -Uvh mysql80-community-release-el9-1.noarch.rpm
warning: mysql80-community-release-el9-1.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql80-community-release-el9-1  ################################# [100%]

[root@localhost Downloads]$ rpm -Uvh  mysql-community-server-8.0.32-1.el9.x86_64.rpm
warning: mysql-community-server-8.0.32-1.el9.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
error: Failed dependencies:
        mysql-community-client(x86-64) >= 8.0.11 is needed by mysql-community-server-8.0.32-1.el9.x86_64
        mysql-community-common(x86-64) = 8.0.32-1.el9 is needed by mysql-community-server-8.0.32-1.el9.x86_64
        mysql-community-icu-data-files = 8.0.32-1.el9 is needed by mysql-community-server-8.0.32-1.el9.x86_64

설치 상태 확인
- mysql-community-server-8.0.32-1.el9.x86_64.rpm 가 설치 실패함
- 이후 dnf install 성공 시에도 결과가 동일해서 일단 보류

[root@localhost Downloads]$ rpm -qi  mysql-community-server-8.0.32-1.el9.x86_64.rpm
warning: mysql-community-server-8.0.32-1.el9.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Name        : mysql-community-server
Version     : 8.0.32
Release     : 1.el9
Architecture: x86_64
Install Date: (not installed)
Group       : Applications/Databases
Size        : 246795859
License     : Copyright (c) 2000, 2022, Oracle and/or its affiliates. Under GPLv2 license as shown in the Description field.
Signature   : RSA/SHA256, Sat 17 Dec 2022 09:25:25 PM KST, Key ID 467b942d3a79bd29
Source RPM  : mysql-community-8.0.32-1.el9.src.rpm
Build Date  : Sat 17 Dec 2022 01:08:25 AM KST
Build Host  : pb2-el9-01.regionaliad02.mysql2iad.oraclevcn.com
Packager    : MySQL Release Engineering <mysql-build@oss.oracle.com>
Vendor      : Oracle and/or its affiliates
URL         : http://www.mysql.com/
Summary     : A very fast and reliable SQL database server
Description :
############ (중략) ############

dnf 로 설치하기
- sudo dnf remove : 설치된 패키지 제거하기

[root@localhost Downloads]$ sudo dnf install mysql-community-server-8.0.32-1.el9.x86_64.rpm
[sudo] password for  root:
Last metadata expiration check: 2:39:58 ago on Fri 22 Mar 2024 12:28:45 PM KST.
Dependencies resolved.
=============================================================================================================================================================
 Package                                            Architecture               Version                           Repository                             Size
=============================================================================================================================================================
Installing:
 mysql-community-server                             x86_64                     8.0.32-1.el9                      @commandline                           49 M
     replacing  mariadb-connector-c-config.noarch 3.2.6-1.el9
Installing dependencies:
 mysql-community-client                             x86_64                     8.0.36-1.el9                      mysql80-community                     3.4 M
 mysql-community-client-plugins                     x86_64                     8.0.36-1.el9                      mysql80-community                     1.4 M
 mysql-community-common                             x86_64                     8.0.32-1.el9                      mysql80-community                     544 k
 mysql-community-icu-data-files                     x86_64                     8.0.32-1.el9                      mysql80-community                     2.2 M
 mysql-community-libs                               x86_64                     8.0.36-1.el9                      mysql80-community                     1.4 M

Transaction Summary
=============================================================================================================================================================
Install  6 Packages

Total size: 58 M
Total download size: 8.9 M
Is this ok [y/N]: y
Downloading Packages:
(1/5): mysql-community-common-8.0.32-1.el9.x86_64.rpm                                                                        1.8 MB/s | 544 kB     00:00
(2/5): mysql-community-client-plugins-8.0.36-1.el9.x86_64.rpm                                                                2.4 MB/s | 1.4 MB     00:00
(3/5): mysql-community-client-8.0.36-1.el9.x86_64.rpm                                                                        3.5 MB/s | 3.4 MB     00:00
(4/5): mysql-community-libs-8.0.36-1.el9.x86_64.rpm                                                                          3.4 MB/s | 1.4 MB     00:00
(5/5): mysql-community-icu-data-files-8.0.32-1.el9.x86_64.rpm                                                                2.9 MB/s | 2.2 MB     00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        8.4 MB/s | 8.9 MB     00:01
MySQL 8.0 Community Server                                                                                                   3.0 MB/s | 3.1 kB     00:00
Importing GPG key 0x3A79BD29:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: 859B E8D7 C586 F538 430B 19C2 467B 942D 3A79 BD29
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Is this ok [y/N]: y
Key imported successfully
Import of key(s) didn't help, wrong key(s)?
Public key for mysql-community-client-8.0.36-1.el9.x86_64.rpm is not installed. Failing package is: mysql-community-client-8.0.36-1.el9.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Public key for mysql-community-client-plugins-8.0.36-1.el9.x86_64.rpm is not installed. Failing package is: mysql-community-client-plugins-8.0.36-1.el9.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Public key for mysql-community-libs-8.0.36-1.el9.x86_64.rpm is not installed. Failing package is: mysql-community-libs-8.0.36-1.el9.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
The downloaded packages were saved in cache until the next successful transaction.
You can remove cached packages by executing 'dnf clean packages'.
Error: GPG check FAILED

🔶 Error: GPG check FAILED

dnf install 진행 시 오류 발생
원인 : GPG-KEY 인증이 되고 있지 않았기 때문
인증을 위해서 rpm 에 key 를 import 시킴

[root@localhost rpm-gpg]$ sudo rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
[sudo] password for  root:
[root@localhost Downloads]$ sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

# 시도 했다가 다시 2022 로 돌렸음 (혹시 영향이 있을까봐 기재)
[root@localhost Downloads]$ sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

위의 내용을 수행하고 다시 dnf install 진행해도 오류 발생
dnf check-update 수행

[root@localhost Downloads]$ dnf check-update
CentOS Stream 9 - BaseOS                                                                                                     1.1 MB/s | 8.0 MB     00:07
CentOS Stream 9 - AppStream                                                                                                  2.6 MB/s |  19 MB     00:07
CentOS Stream 9 - Extras packages                                                                                            2.3 kB/s |  16 kB     00:06
Extra Packages for Enterprise Linux 9 - x86_64                                                                               4.2 MB/s |  21 MB     00:04
Extra Packages for Enterprise Linux 9 openh264 (From Cisco) - x86_64                                                         463  B/s | 2.5 kB     00:05
Extra Packages for Enterprise Linux 9 - Next - x86_64                                                                        453 kB/s | 1.4 MB     00:03
MySQL 8.0 Community Server                                                                                                   3.2 MB/s | 1.4 MB     00:00
MySQL Connectors Community                                                                                                   227 kB/s |  45 kB     00:00
MySQL Tools Community                                                                                                        1.6 MB/s | 536 kB     00:00

mysql80-community-release.noarch                                                 el9-5                                                      mysql80-community
xrdp.x86_64                                                                      1:0.9.25-2.el9                                             epel
xrdp-selinux.x86_64                                                              1:0.9.25-2.el9                                             epel
Obsoleting Packages
mysql-community-server.x86_64                                                    8.0.30-1.el9                                               mysql80-community
############ (중략) ############

🔶 dnf install 재실행

[root@localhost Downloads]$ sudo dnf install mysql-community-server-8.0.32-1.el9.x86_64.rpm
Last metadata expiration check: 3:13:05 ago on Fri 22 Mar 2024 12:28:45 PM KST.
Dependencies resolved.
=============================================================================================================================================================
 Package                                            Architecture               Version                           Repository                             Size
=============================================================================================================================================================
Installing:
 mysql-community-server                             x86_64                     8.0.32-1.el9                      @commandline                           49 M
     replacing  mariadb-connector-c-config.noarch 3.2.6-1.el9
Installing dependencies:
 mysql-community-client                             x86_64                     8.0.36-1.el9                      mysql80-community                     3.4 M
 mysql-community-client-plugins                     x86_64                     8.0.36-1.el9                      mysql80-community                     1.4 M
 mysql-community-common                             x86_64                     8.0.32-1.el9                      mysql80-community                     544 k
 mysql-community-icu-data-files                     x86_64                     8.0.32-1.el9                      mysql80-community                     2.2 M
 mysql-community-libs                               x86_64                     8.0.36-1.el9                      mysql80-community                     1.4 M

Transaction Summary
=============================================================================================================================================================
Install  6 Packages

Total size: 58 M
Is this ok [y/N]: y
Downloading Packages:
[SKIPPED] mysql-community-client-8.0.36-1.el9.x86_64.rpm: Already downloaded
[SKIPPED] mysql-community-client-plugins-8.0.36-1.el9.x86_64.rpm: Already downloaded
[SKIPPED] mysql-community-common-8.0.32-1.el9.x86_64.rpm: Already downloaded
[SKIPPED] mysql-community-icu-data-files-8.0.32-1.el9.x86_64.rpm: Already downloaded
[SKIPPED] mysql-community-libs-8.0.36-1.el9.x86_64.rpm: Already downloaded
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                     1/1
  Installing       : mysql-community-common-8.0.32-1.el9.x86_64                                                                                          1/7
  Installing       : mysql-community-client-plugins-8.0.36-1.el9.x86_64                                                                                  2/7
  Installing       : mysql-community-libs-8.0.36-1.el9.x86_64                                                                                            3/7
  Running scriptlet: mysql-community-libs-8.0.36-1.el9.x86_64                                                                                            3/7
  Installing       : mysql-community-client-8.0.36-1.el9.x86_64                                                                                          4/7
  Installing       : mysql-community-icu-data-files-8.0.32-1.el9.x86_64                                                                                  5/7
  Running scriptlet: mysql-community-server-8.0.32-1.el9.x86_64                                                                                          6/7
  Installing       : mysql-community-server-8.0.32-1.el9.x86_64                                                                                          6/7
  Running scriptlet: mysql-community-server-8.0.32-1.el9.x86_64                                                                                          6/7
  Obsoleting       : mariadb-connector-c-config-3.2.6-1.el9.noarch                                                                                       7/7
  Running scriptlet: mariadb-connector-c-config-3.2.6-1.el9.noarch                                                                                       7/7
  Verifying        : mysql-community-client-8.0.36-1.el9.x86_64                                                                                          1/7
  Verifying        : mysql-community-client-plugins-8.0.36-1.el9.x86_64                                                                                  2/7
  Verifying        : mysql-community-common-8.0.32-1.el9.x86_64                                                                                          3/7
  Verifying        : mysql-community-icu-data-files-8.0.32-1.el9.x86_64                                                                                  4/7
  Verifying        : mysql-community-libs-8.0.36-1.el9.x86_64                                                                                            5/7
  Verifying        : mysql-community-server-8.0.32-1.el9.x86_64                                                                                          6/7
  Verifying        : mariadb-connector-c-config-3.2.6-1.el9.noarch                                                                                       7/7

Installed:
  mysql-community-client-8.0.36-1.el9.x86_64            mysql-community-client-plugins-8.0.36-1.el9.x86_64    mysql-community-common-8.0.32-1.el9.x86_64
  mysql-community-icu-data-files-8.0.32-1.el9.x86_64    mysql-community-libs-8.0.36-1.el9.x86_64              mysql-community-server-8.0.32-1.el9.x86_64

Complete!

활성화 및 비활성화된 MySQL 리포지토리를 모두 나열

[root@localhost Downloads]$ dnf repolist all | grep mysql
mysql-cluster-8.0-community           MySQL Cluster 8.0 Community       disabled
mysql-cluster-8.0-community-debuginfo MySQL Cluster 8.0 Community - Deb disabled
mysql-cluster-8.0-community-source    MySQL Cluster 8.0 Community - Sou disabled
mysql-connectors-community            MySQL Connectors Community        enabled
mysql-connectors-community-debuginfo  MySQL Connectors Community - Debu disabled
mysql-connectors-community-source     MySQL Connectors Community - Sour disabled
mysql-tools-community                 MySQL Tools Community             enabled
mysql-tools-community-debuginfo       MySQL Tools Community - Debuginfo disabled
mysql-tools-community-source          MySQL Tools Community - Source    disabled
mysql80-community                     MySQL 8.0 Community Server        enabled
mysql80-community-debuginfo           MySQL 8.0 Community Server - Debu disabled
mysql80-community-source              MySQL 8.0 Community Server - Sour disabled

설치된 mysql * 패키지 목록

[root@localhost mysql]$ sudo dnf list installed | grep mysql
mysql-community-client.x86_64                    8.0.36-1.el9                       @mysql80-community
mysql-community-client-plugins.x86_64            8.0.36-1.el9                       @mysql80-community
mysql-community-common.x86_64                    8.0.32-1.el9                       @mysql80-community
mysql-community-icu-data-files.x86_64            8.0.32-1.el9                       @mysql80-community
mysql-community-libs.x86_64                      8.0.36-1.el9                       @mysql80-community
mysql-community-server.x86_64                    8.0.32-1.el9                       @@commandline
mysql-ref-manual-8.0-en-html-chapter.noarch      1-20220914                         @mysql80-community
mysql80-community-release.noarch                 el9-1                              @System
pcp-pmda-mysql.x86_64                            6.2.0-1.el9                        @AppStream

🔶 패키지 추가 설치

repo 에서 추가 설치 가능한 패키지 확인하기

[root@localhost mysql]$ dnf --disablerepo=\* --enablerepo='mysql*-community*' list available
No read/execute access in current directory, moving to /
MySQL Cluster 8.0 Community                                                                                                  2.4 MB/s | 1.5 MB     00:00
MySQL 8.0 Community Server - Debuginfo                                                                                       386 kB/s | 136 kB     00:00
MySQL Connectors Community - Debuginfo                                                                                        15 kB/s | 7.6 kB     00:00
MySQL Tools Community - Debuginfo                                                                                             65 kB/s |  25 kB     00:00
MySQL Cluster 8.0 Community - Debuginfo                                                                                      315 kB/s | 155 kB     00:00
MySQL 8.0 Community Server - Source                                                                                          8.3 kB/s | 3.2 kB     00:00
MySQL Connectors Community - Source                                                                                          8.9 kB/s | 5.1 kB     00:00
MySQL Tools Community - Source                                                                                               6.6 kB/s | 2.8 kB     00:00
MySQL Cluster 8.0 Community - Source                                                                                         5.9 kB/s | 2.9 kB     00:00
Available Packages
mysql-cluster-community.src                                                       8.0.36-1.el9                          mysql-cluster-8.0-community-source
mysql-cluster-community-client.x86_64                                             8.0.36-1.el9                          mysql-cluster-8.0-community
mysql-cluster-community-client-debuginfo.x86_64                                   8.0.36-1.el9                          mysql-cluster-8.0-community-debuginfo
mysql-cluster-community-client-plugins.x86_64                                     8.0.36-1.el9                          mysql-cluster-8.0-community
mysql-cluster-community-client-plugins-debuginfo.x86_64                           8.0.36-1.el9                          mysql-cluster-8.0-community-debuginfo
mysql-cluster-community-common.x86_64                                             8.0.36-1.el9                          mysql-cluster-8.0-community
############ (중략) ############

mysql-ref-manual-8.0-en-html-chapter.noarch 패키지 추가 설치

[root@localhost mysql]$ sudo dnf install mysql-ref-manual-8.0-en-html-chapter.noarch
Last metadata expiration check: 0:56:36 ago on Fri 22 Mar 2024 03:42:06 PM KST.
Dependencies resolved.
=============================================================================================================================================================
 Package                                                 Architecture              Version                        Repository                            Size
=============================================================================================================================================================
Installing:
 mysql-ref-manual-8.0-en-html-chapter                    noarch                    1-20220914                     mysql80-community                    7.9 M

Transaction Summary
=============================================================================================================================================================
Install  1 Package

Total download size: 7.9 M
Installed size: 41 M
Is this ok [y/N]: y
Downloading Packages:
mysql-ref-manual-8.0-en-html-chapter-1-20220914.noarch.rpm                                                                   8.1 MB/s | 7.9 MB     00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        8.1 MB/s | 7.9 MB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                     1/1
  Installing       : mysql-ref-manual-8.0-en-html-chapter-1-20220914.noarch                                                                              1/1
  Verifying        : mysql-ref-manual-8.0-en-html-chapter-1-20220914.noarch                                                                              1/1

Installed:
  mysql-ref-manual-8.0-en-html-chapter-1-20220914.noarch

Complete!

7-3. MySQL DB 데이터 저장소 변경

조건 : systemctl status mysqld 결과 inactive (dead) 상태여야 함
DB 데이터 저장소로 지정될 경로 : /cargo1
my.cnf 파일 > datadir, socket 경로 수정
- 테이블명 조회 시 대소문자 구분 없이 사용하도록 하는 설정도 함께 추가

[root@localhost mysql]$ sudo vi /etc/my.cnf

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/cargo1/mysql
socket=/cargo1/mysql/mysql.sock
lower_case_table_names=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

rsync 작업 시 기존 스키마들을 모두 복사하여 가져옴 (현재 환경에는 생성된 스키마가 없지만 우선 따라함)

# mysql 복사
[root@localhost mysql]$ sudo rsync -av /var/lib/mysql /cargo1
sending incremental file list
mysql/

sent 64 bytes  received 20 bytes  168.00 bytes/sec
total size is 0  speedup is 0.00

# mysql 권한 부여
[root@localhost mysql]$ sudo chown -R mysql:mysql /cargo1/mysql

SElinux 보안 추가
- 프로세스에 새로 권한을 추가해주었기 때문에 해당 경로의 SElinux 보안 컨텍스트를 추가해야 함
- 추가하지 않으면 MySQL 실행시 permission 에러 발생 (에러 내용은 /var/log/audit/audit.log 에서 확인 가능)
- 안된다고 SElinux 를 해제하는것은 매우 위험하므로 비추

[root@localhost mysql]$ sudo semanage fcontext -a -t mysqld_db_t "/cargo1/mysql(/.*)?"
[root@localhost mysql]$ sudo restorecon -R /cargo1/mysql

# 설정 완료되었는지 확인
[root@localhost mysql]$ sudo semanage fcontext -l | grep mysql
/cargo1/mysql(/.*)?                                all files          system_u:object_r:mysqld_db_t:s0

7-4. MySQL 실행

systemctl status mysqld : 상태 확인
- systemctl start mysqld : MySQL 실행
- systemctl stop mysqld : MySQL 종료

[root@localhost mysql]$ systemctl status mysqld
○ mysqld.service - MySQL Server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
     Active: inactive (dead)
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
[root@localhost mysql]$ systemctl start mysqld
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to start 'mysqld.service'.
Authenticating as: YNJCH (root)
Password:
==== AUTHENTICATION COMPLETE ====
[root@localhost mysql]$ systemctl status mysqld
● mysqld.service - MySQL Server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
     Active: active (running) since Fri 2024-03-22 16:34:12 KST; 13s ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 417304 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
   Main PID: 417378 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 202223)
     Memory: 451.2M
        CPU: 6.081s
     CGroup: /system.slice/mysqld.service
             └─417378 /usr/sbin/mysqld

Mar 22 16:33:25 localhost.localdomain systemd[1]: Starting MySQL Server...
Mar 22 16:34:12 localhost.localdomain systemd[1]: Started MySQL Server.

/cargo1/mysql 경로에 파일들이 생긴 것 확인 가능

[root@localhost mysql]$ pwd
/cargo1/mysql
[root@localhost mysql]$ sudo ls
 auto.cnf        ca-key.pem        client-key.pem       ib_buffer_pool  '#innodb_redo'   mysql.ibd         performance_schema   server-cert.pem   undo_001
 binlog.000001   ca.pem           '#ib_16384_0.dblwr'   ibdata1         '#innodb_temp'   mysql.sock        private_key.pem      server-key.pem    undo_002
 binlog.index    client-cert.pem  '#ib_16384_1.dblwr'   ibtmp1           mysql           mysql.sock.lock   public_key.pem       sys

🔶 systemctl start mysqld 실패

아래와 같이 오류 발생과 함께 start 되지 않음

[root@localhost mysql]$ systemctl start mysqld
############ (중략) ############
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xeu mysqld.service" for details.

/var/log/mysqld.log 에서 아래 문구 확인 (https://dev-taerin.tistory.com/14)

2024-03-22T07:47:45.105142Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2024-03-22T07:47:45.105570Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

lower_case_table_names=1 값의 위치, #datadir 등의 주석 처리가 제대로 되었는지 확인
- systemctl stop mysqld 후 파일 수정을 해야 함
이후에도 계속 문제 발생 시, my.cnf 가 초기화 시에 수정이 이루어진 것인지 체크
- MySQL 초기화(제거 및 재설치) 후 my.cnf 부터 수정하도록 위의 절차 다시 진행

[root@localhost mysql]$ sudo vi /etc/my.cnf

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/cargo1/mysql
socket=/cargo1/mysql/mysql.sock
lower_case_table_names=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

chmod 755 my.cnf 명령어 수행

[root@localhost etc]# chmod 755 my.cnf

-rwxr-xr-x.   1 root root      1326 Mar 25 10:37 my.cnf

🔶 3306 Port 상시 허용

방화벽에 3306 Port를 상시 허용으로 등록 및 재실행

[root@localhost ~]# sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
success
[root@localhost ~]# sudo firewall-cmd --reload
success
[root@localhost ~]# sudo firewall-cmd --list-port
3306/tcp 3389/tcp

🔶 status 확인

mysql> status
--------------
mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          13
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.32 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /cargo1/mysql/mysql.sock
Binary data as:         Hexadecimal

🔶 Character Set 확인

Character Set : utf8 / 4byte (변경할 사항 없음)

mysql> show variables like "ch%";
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| check_proxy_users        | OFF                            |
+--------------------------+--------------------------------+
9 rows in set (0.01 sec)

mysql> show variables like "col%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

8. MySQL 계정

8-1. root 접속

임시 패스워드 확인 & root 패스워드 변경

[root@localhost mysql]$ sudo grep 'temporary password' /var/log/mysqld.log
2024-03-22T07:33:42.873919Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: duy4Xcq-aH%L

root 접속

[root@localhost mysql]$ mysql -uroot -p
Enter password: [duy4Xcq-aH%L]

🔶 Can't connect to local MySQL server through socket

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
MySQL의 소켓 파일인 mysql.sock 파일이 없거나 정확하지 않아서 발생하는 에러
/cargo1/mysql 로 디렉토리 변경하면서 발생됨
- (24.04.01 삭제 후 재설치하니 socket 위치 기재하지 않아도 접속됨)

[root@localhost mysql]# mysql -uroot -p -S '/cargo1/mysql/mysql.sock'

위와 같이 소켓 파일 위치를 직접 지정해줄 수 있음
로그 확인 : tail -f /var/log/mysqld.log

mysql.sock 파일 위치 찾기

[root@localhost /]# find / -name mysql.sock
find: ‘/run/user/1000/gvfs’: Permission denied
/cargo1/mysql/mysql.sock

8-2. root 패스워드 변경

임시 패스워드에서 초기화해야 함
대문자를 포함해야 해서 소문자만으로도 사용할 수 있도록 패스워드 구성 방법 변경 필요

mysql> alter user 'root'@'localhost' identified by 'password$$';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> alter user 'root'@'localhost' identified by 'password$$';
Query OK, 0 rows affected (0.06 sec)

mysql> show variables like 'val%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> SET GLOBAL validate_password.mixed_case_count = 0;
Query OK, 0 rows affected (0.00 sec)

# 다시 'password$$' 으로 PW 변경

앞으로 로그인 시

[root@localhost mysql]# mysql -uroot -p -S '/cargo1/mysql/mysql.sock'
Enter password: [password$$]

8-3. 계정 추가 및 권한 설정

'root'@'%' 설정

mysql> create user 'root'@'%' identified by 'password$$';
Query OK, 0 rows affected (0.02 sec)

mysql> create user 'projyj'@localhost identified by 'password$$';
Query OK, 0 rows affected (0.02 sec)

mysql> create user 'projyj'@'%' identified by 'password$$';
Query OK, 0 rows affected (0.01 sec) 

mysql> grant all privileges on *.* to 'projyj'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

select host, user, authentication_string from USER; 로 확인 가능

🔶 암호화 방식 변경

8.0.32 버전 암호화 방식 확인 : caching_sha2_password

mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| projyj            | %         | caching_sha2_password |
| root             | %         | caching_sha2_password |
| projyj            | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

ProjYj 사용 계정은 예전 방식의 암호체계 mysql_native_password 로 변경해야 함

mysql> ALTER USER 'projyj'@'%' IDENTIFIED WITH mysql_native_password BY 'password$$';
mysql> ALTER USER 'projyj'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password$$';

🔶 MySQL 사용자 계정 비밀번호 정책

MySQL 사용자 계정 비밀번호 정책 변경
validate_password_policy 를 LOW 로 설정

mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)

9. MySQL 테이블 생성

9-1. 데이터베이스 생성

projyj 데이터베이스 생성

mysql> create database projyj;
Query OK, 1 row affected (0.15 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| projyj              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

9-2. 테이블 생성

FileZilla > /home > EZMIG_init.sql, ALTER_TABLE.sql, ALTER_TABLE_COLUMN.sql 파일을 업로드

[root@localhost home]# cd /home
[root@localhost home]# ls
ALTER_TABLE_COLUMN.sql  ALTER_TABLE.sql  EZMIG_init.sql  projyj

EZMIG_init.sql 실행 > 57개 테이블이 생성되면 성공

[root@localhost home]#  mysql -uroot -p projyj -S '/cargo1/mysql/mysql.sock' < EZMIG_init.sql
Enter password:

ALTER_TABLE_COLUMN.sql 실행 > 테이블 및 컬럼 코멘트가 생성되면 성공

[root@localhost home]#  mysql -uroot -p projyj -S '/cargo1/mysql/mysql.sock' < ALTER_TABLE.sql
Enter password:
[root@localhost home]#  mysql -uroot -p projyj -S '/cargo1/mysql/mysql.sock' < ALTER_TABLE_COLUMN.sql
Enter password:

🔶 DBeaver 에서 확인

Public Key Retrieval is not allowed 오류 발생 시, URL 에 ?useSSL=false&allowPublicKeyRetrieval=true 추가
URL : jdbc:mysql://192.168.1.111:3306/projyj?useSSL=false&allowPublicKeyRetrieval=true
ID : projyj / PW : password$$

EUN JY

개린이

이전 포스트

[Linux] CentOS Stream 9 환경 구축 2 - 웹 콘솔로 접근 / Podman 환경 구성 / Java 설치 및 환경변수 설정

다음 포스트

[Linux] CentOS Stream 9 환경 구축 3 - DNF로 MySQL 설치 / DB 데이터 저장소 변경 및 my.cnf / 계정 생성 및 데이터베이스, 테이블 생성

Linux

7. MySQL

7-1. MySQL 설치 파일 다운로드

🔶 MySQL Yum Repository

7-2. MySQL 설치

🔶 Error: GPG check FAILED

🔶 dnf install 재실행

🔶 패키지 추가 설치

7-3. MySQL DB 데이터 저장소 변경

7-4. MySQL 실행

🔶 systemctl start mysqld 실패

🔶 3306 Port 상시 허용

🔶 status 확인

🔶 Character Set 확인

8. MySQL 계정

8-1. root 접속

🔶 Can't connect to local MySQL server through socket

8-2. root 패스워드 변경

8-3. 계정 추가 및 권한 설정

🔶 암호화 방식 변경

🔶 MySQL 사용자 계정 비밀번호 정책

9. MySQL 테이블 생성

9-1. 데이터베이스 생성

9-2. 테이블 생성

🔶 DBeaver 에서 확인

[Linux] CentOS Stream 9 환경 구축 2 - 웹 콘솔로 접근 / Podman 환경 구성 / Java 설치 및 환경변수 설정

[Linux] 파일 및 디렉토리 관련 명령어 (find, grep, vi, tail, chmod, rm) / Tomcat 명령어

0개의 댓글