[FEAT] MySQL DB 서버 구축 (175.45.200.149) 및 테이블 생성

[2018007956]

Background

• DB 연결
  • Hostname: 175.45.200.149 (제 ncp 서버 공인IP)
  • Port: 3306 (default)
  • Username: root
  • DB name: STOCK_DB
• DB 구조

To do

• [x] DB 서버 구축
  • MySQL 설치 (Reference Link)
  • mysql-server 설치
    • sudo apt-get update
    • sudo apt-get install mysql-server
  • MySQl 기본 설정
    • sudo ufw allow mysql
    • sudo systemctl start mysql
    • sudo systemctl enable mysql
  • MySQL 접속
    • mysql -u root -p
  • 모든 ip에서 접근 할 수 있도록 LISTEN IP 대역을 로컬호스트로 제한하고 있는 설정 주석 처리 (Reference Link)
  • vi /etc/mysql/mysql.conf.d/mysqld.cnf

  • bind-address = 127.0.0.1

  • mysql 계정 생성 및 권한 부여 (Reference Link) mysql> create user 계정ID@'%' identified by '비밀번호'; mysql> grant all privileges on DB이름.* to 계정ID@'%'; mysql> flush privileges;
  • dbdiagram.io 에서 설계된 스키마에 따라 테이블 생성
  • Workbench로 접속 확인

[2018007956]

• DB 구조 변경
  • 이전 DB 구조
  • 변경 사항 : KRX.close의 경우 날짜 별로 history가 기록 되어야 하기 떄문에 date attribute 추가 필요
  • 문제 사항 : close를 KRX 테이블에 기록해 놓으면 code, name 속성 값들이 쓸데없이 중복되어 기록되며 테이블 용량 또한 증가
  • 개선 : KRX 테이블에서 date, close를 제외하여 원래대로 값이 갱신되는 테이블로 두고, 모델 예측 테이블에 close 속성 추가
  • 개선된 DB 구조

[2018007956]

DB 업데이트

• 테이블명 변경
  • Users -> UserInfo
  • favorite -> FavoriteStocks
• BertPredHistory 테이블 속성값 type 변경
  • positive/neutral/negative 각각의 뉴스 개수이므로 float -> int
• CandlePredHIstory 테이블 candle_name type 수정: float -> str
• TimeSeriesPredHistory 테이블 model 속성값 primary key 지정
• UserInfo 테이블 nickname 속성 추가

[2018007956]

<데이터베이스 테이블 대소문자 구분 없애는 설정 및 트러블슈팅 과정 기록>

• 문제 상황 : DB 스키마 구조에 따라 쿼리문을 날려서 테이블 생성했을 때는 대소문자 구분이 됐는데, FastAPI 실행 시 같은 테이블명이 소문자로 처리되어 중복 생성되는 문제 발생

• 해결 : 테이블명의 대소문자 구분을 없애기 mysql> show variables like 'lower%'; lower_case_table_names = 0 이면 구분 O , 1이면 구분 X Base setting 인 0을 1로 바꾸는 설정은 서버가 시작될 때 읽히므로, MySQL 서버를 중지 후 변경 필요

  1. MySQL 서버 중지 : sudo systemctl stop mysql
  2. my.cnf 파일 편집 : vi /etc/mysql/mysql.conf.d/mysqld.cnf 이 파일의 위치는 설치 방법과 사용 중인 MySQL 버전에 따라 다를 수 있음 파일을 찾은 후, [mysqld] 섹션에 lower_case_table_names=1 추가
     • 파일 찾는 방법
       1. MySQL 설정 파일 들어가보기 : /etc/mysql/my.cnf
       2. !includedir 지시어를 사용하여 다른 디렉토리를 포함하고 있다면, 해당 경로에서 mysqld.cnf 또는 유사한 이름을 가진 파일 찾기
  3. MySQL 서버 시작: sudo systemctl start mysql ERROR) Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. 가 떴고, sudo cat /var/log/mysql/error.log로 MySQL 오류 로그를 확인해보니, MySQL 서버의 lower_case_table_names 설정값과 데이터 디렉토리에 저장된 데이터 사전의 설정값이 서로 다르다는 에러(Different lower_case_table_names settings for server ('1') and data dictionary ('0').)가 뜸 SOLVE) lower_case_table_names 값을 변경하려면 아래와 같은 데이터베이스 초기화 과정 필요
     1. 데이터베이스 백업: 모든 데이터 백업
     2. 서버 정지 : sudo systemctl stop mysql
     3. 데이터 디렉토리 초기화 : sudo mv /var/lib/mysql /var/lib/mysql_backup
     4. 데이터 디렉토리 재생성 및 권한 부여 : sudo mkdir /var/lib/mysql, sudo chown mysql:mysql /var/lib/mysql
     5. 서버 초기화 (이 과정에서 설정값 지정 가능) : sudo mysqld --initialize --lower_case_table_names=1
     6. 서버 재시작 : sudo systemctl start mysql
     7. 데이터 복원 : 내보낸 데이터베이스 가져오기

  ERROR) 위 과정을 거치고 서버를 재시작 했더니 MySQL 접속 과정에서 password 오류 발생 SOLVE) safe mode로 접속하여 비밀번호 재설정

  1. 필요한 디렉토리 생성 (UNIX 소켓 파일을 위한 /var/run/mysqld 디렉토리)
     • sudo mkdir -p /var/run/mysqld
     • sudo chown mysql:mysql /var/run/mysqld
  2. MySQL을 안전 모드로 재시작
     • sudo systemctl start mysql
     • sudo mysqld_safe --skip-grant-tables &
  3. 비밀번호 없이 접속 : mysql -u root
  4. root 비밀번호 재설정

      FLUSH PRIVILEGES;
      ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
      FLUSH PRIVILEGES;

  5. 인증 플러그인 확인 및 변경
     • MySQL 8.0 이상을 사용하는 경우, root 사용자의 인증 플러그인이 mysql_native_password로 설정되어 있는지 확인하고, 필요한 경우 변경

        SELECT user, plugin FROM mysql.user WHERE user = 'root';
        ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
        FLUSH PRIVILEGES;

     • 플러그인이 caching_sha2_password 인 경우 추가적인 ALTER USER 명령 실행할 필요 없음
  6. mysqld_safe 모두 종료 및 MySQL 서비스 재시작
     • sudo mysqladmin -u root -p shutdown
     • sudo systemctl start mysql
  7. 새 비밀번호로 접속
     • mysql -u root -p
       9. 설정 확인 : show variables like 'lower_case_table_names'; 또는 show variables like 'lower%';

<트러블슈팅>

• ERROR) inotify watch limit reached
  • inotify는 파일 시스템 이벤트를 모니터링하기 위해 리눅스 커널에서 제공하는 기능
  • 시스템에서 너무 많은 파일이나 디렉토리를 모니터링 하려고 할 때 위와 같은 에러 발생
• SOLVE) inotify의 watch 제한치 증가
  • /etc/sysctl.conf 파일 편집 : echo fs.inotify.max_user_watches=524288 | sudo tee -a /etc/sysctl.conf
  • 변경사항 적용 : sudo sysctl -p