구축 방법

설치

# rpm Repo 다운로드
$ wget http://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm

# Repo 설치
$ sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm

# Mysql 설치
$ sudo yum -y install mysql-community-server

# datapath 변경방법
1. 최초 설치 후 my.cnf default 상태에서 mysqld 실행/종료
$ sudo systemctl start mysqld
$ sudo systemctl stop mysqld

2. 새로운 디렉터리 생성
$ sudo mkdir /data
$ rsync -av /var/lib/mysql /data/
$ chown -R mysql:mysql /data/mysql

3. my.cnf 수정
$ sudo vi /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/data/mysql
socket=/data/mysql/mysql.sock

3. mysqld 실행
$ sudo systemctl start mysqld
$ netstat -nltp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      -
tcp6       0      0 :::22                   :::*                    LISTEN      -
tcp6       0      0 :::33060                :::*                    LISTEN      -
tcp6       0      0 :::3306                 :::*                    LISTEN      -
tcp6       0      0 :::111                  :::*                    LISTEN      -

4. mysql 비밀번호 확인 및 접속
$ sudo cat /var/log/mysqld.log | grep -i "password"
[MY-010454] [Server] A temporary password is generated for root@localhost: B(,2Gmszgktg
$ mysql -u root -p
Enter:
error!! sock error 발생!!

5. mysql.sock error 조치방법
## mysql.sock 파일을 찾을 수 없어서 원본 mysql.sock을 심볼릭 링크를 걸어줘야함
$ sudo ln -s /var/lib/mysql/mysql.sock /data/mysql/mysql.sock

6. mysqld 실행 및 접속
$ sudo systemctl start mysqld
$ mysql -u root -p
Enter:
mysql>

설정

# mysql 8.0 사용 시 비밀번호 정책 변경
mysql> set global validate_password.policy=low;
mysql> set global validate_password.length=4;
mysql> set global validate_password.mixed_case_count=0;
mysql> set global validate_password.number_count=0;
mysql> set global validate_password.special_char_count=0;

# Mysql 유저생성 및 권한부여
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '1234';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';
mysql> flush privileges;

# Mysql 유저삭제 방법
mysql> drop user '사용자'@'localhost';

# innotop 설치
$ wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/innotop/innotop-1.9.1-1.fc19.noarch.rpm
$ sudo yum -y install innotop-1.9.1-1.fc19.noarch.rpm

# innotop DBD-connector 업데이트
$ wget https://lefred.be/wp-content/uploads/2019/06/perl-DBD-MySQL-4.044-1.el7_.x86_64.zip
$ unzip perl-DBD-MySQL-4.044-1.el7_.x86_64.zip
$ sudo yum -y install perl-DBD-MySQL-4.044-1.el7.x86_64.rpm
### DBD-connector 관련자료 : https://lefred.be/content/perl-mysql-8-0/

# innotop 실행
$ innotop -h 127.0.0.1 -u root -p 1234

docker-compose 설치방법

# AWS EC2 DATE 설정
$ sudo rm -f /etc/localtime
$ sudo ln -s /usr/share/zoneinfo/Asia/Seoul /etc/localtime

# docker-compose.yml 작성
$ sudo vi docker-compose.yml
mysql-server:
  image: mysql:8.0.17
  container_name: mysql-server
  hostname: mysql-server
  restart: always
  labels:
    - "name=mysql"
    - "mode=standalone"
  network_mode: host
  ports:
    - 3306:3306
  environment:
    - MYSQL_ROOT_PASSWORD=1234
    - MYSQL_USER=test
    - MYSQL_PASSWORD=1234
  volumes:
    - /usr/local/docker/mysql/initdb:/docker-entrypoint-initdb.d/:ro
  # mysql /datadir 생성 후 PATH 추가
    - /datadir:/var/lib/mysql:rw
    - /usr/local/docker/mysql/config/conf.d:/etc/mysql/conf.d
    - /etc/localtime:/etc/localtime:ro

# docker-compose.yml dir에서 커맨드 실행
$ docker-compose up -d

# ps 확인
$ docker-compose ps
Name                   Command               State                 Ports
------------------------------------------------------------------------------------------
mysql   docker-entrypoint.sh --cha ...   Up      0.0.0.0:3306->3306/tcp, 33060/tcp

docker-compose MySQL 설정

# Mysql 접속
$ docker exec -it 컨테이너id /bin/bash
$ mysql컨테이너] mysql -u root -p
enter: "password 입력"

# Mysql 유저생성 및 권한부여
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '1234';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';
mysql> flush privileges;

# Mysql 유저삭제
mysql> drop user '사용자'@'localhost';