7. MySQL
- CentOS 에서 패키지 설치는 가능한 dnf 를 사용
- ProjYj 2022 에서 모든 테스트가 완료되면 Podman image 로 생성
- 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
7-2. MySQL 설치
[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...
Preparing...
Updating / installing...
1:mysql80-community-release-el9-1
[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
[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
[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=/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 작업 시 기존 스키마들을 모두 복사하여 가져옴 (현재 환경에는 생성된 스키마가 없지만 우선 따라함)
[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
[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.
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=/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
[root@localhost etc]
-rwxr-xr-x. 1 root root 1326 Mar 25 10:37 my.cnf
🔶 3306 Port 상시 허용
- 방화벽에 3306 Port를 상시 허용으로 등록 및 재실행
[root@localhost ~]
success
[root@localhost ~]
success
[root@localhost ~]
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@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]
- 위와 같이 소켓 파일 위치를 직접 지정해줄 수 있음
- 로그 확인 :
tail -f /var/log/mysqld.log
[root@localhost /]
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)
[root@localhost mysql]
Enter password: [password$$]
8-3. 계정 추가 및 권한 설정
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. 데이터베이스 생성
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]
[root@localhost home]
ALTER_TABLE_COLUMN.sql ALTER_TABLE.sql EZMIG_init.sql projyj
EZMIG_init.sql
실행 > 57개 테이블이 생성되면 성공
[root@localhost home]
Enter password:
ALTER_TABLE_COLUMN.sql
실행 > 테이블 및 컬럼 코멘트가 생성되면 성공
[root@localhost home]
Enter password:
[root@localhost home]
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$$