Amazon Linux(centos) : MySQL 5.7.23 컴파일, 설치

출처

• Ubuntu server에 mysql 5.5.x 설치하기 -pupustory@- - Tistory
• [Linux/CentOS] Mysql을 컴파일해서 설치해보자 - AT BLOG

리눅스 정보 조회

출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키

$ cat /etc/*-release
NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/"
Amazon Linux release 2 (Karoo)

관련패키지 설치

$ sudo yum update
$ sudo yum install gcc gcc-c++ openssl curl autoconf ncurses-devel bison zlib curl libtermcap-devel bzip2-devel

MySQL 계정 만들기

$ sudo groupadd mysql 
$ sudo useradd -g mysql -s /bin/bash -m mysql

cmake 설치

$ sudo yum install cmake

MySQL 소스 다운로드, 압축해제, 컴파일, 설치

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.23.tar.gz 
$ tar xvf mysql-boost-5.7.23.tar.gz 
$ cd mysql-5.7.23 
$ cmake \ 
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 \ 
  -DMYSQL_DATADIR=/usr/local/mysql57/data \ 
  -DMYSQL_UNIX_ADDR=/usr/local/mysql57/mysql.sock \ 
  -DSYSCONFDIR=/usr/local/mysql57 \ 
  -DMYSQL_TCP_PORT=5723 \ 
  -DMYSQL_USER=mysql \ 
  -DDEFAULT_CHARSET=utf8 \ 
  -DDEFAULT_COLLATION=utf8_general_ci \ 
  -DWITH_EXTRA_CHARSETS=all \ 
  -DENABLED_LOCAL_INFILE=1 \ 
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \ 
  -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ 
  -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ 
  -DDOWNLOAD_BOOST=0 \
  -DWITH_BOOST=./boost 
$ make 
$ sudo make install

환경설정

$ sudo vi /usr/local/mysql57/my.cnf

[mysqld]
# port=3306
# basedir=/usr/local/mysql57
# datadir=/usr/local/mysql57/data
# pid-file=/usr/local/mysql57/mysqld.pid
# log_error=/usr/local/mysql57/mysql_error.log
# lc-messages-dir=/usr/local/mysql57/share

init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8
collation-server=utf8_general_ci
# table_cache=1024
max_connections=2048
max_user_connections=500
max_connect_errors=10000
wait_timeout=300
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 5M
slow_query_log
long_query_time=3
max_allowed_packet=16M
sort_buffer_size = 2M
# skip-innodb
skip-name-resolve
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

MySQL 설치 디렉토리 mysql 계정으로 권한 수정

$ sudo chown -R mysql:mysql /usr/local/mysql57

MySQL 데이터베이스 초기화 (mysql 계정으로 실행)

$ cd /usr/local/mysql57
$ bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql57/data --basedir=/usr/local/mysql57 -v
2021-05-08 08:15:24 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2021-05-08 08:15:24 [NOTE]    Creating data directory /usr/local/mysql57/data
2021-05-08 08:15:24 [NOTE]    Generating random password to /root/.mysql_secret...done.
2021-05-08 08:15:24 [NOTE]    Setting file ownership to mysql
2021-05-08 08:15:24 [NOTE]    Executing /usr/local/mysql57/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql57/data --lc-messages-dir=/usr/local/mysql57/share --lc-messages=en_US --basedir=/usr/local/mysql57
2021-05-08 08:15:24 [NOTE]    Creating system tables...done.
2021-05-08 08:15:24 [NOTE]    Filling system tables with data...done.
2021-05-08 08:15:25 [NOTE]    Filling help table with data...done.
2021-05-08 08:15:25 [NOTE]    Creating user for internal session service...done.
2021-05-08 08:15:25 [NOTE]    Creating default user root@localhost
2021-05-08 08:15:25 [NOTE]    Creating default proxy root@localhost
2021-05-08 08:15:25 [NOTE]    Creating sys schema
2021-05-08 08:15:25 [NOTE]    done.
2021-05-08 08:15:26 [WARNING] The bootstrap log isn't empty:
2021-05-08 08:15:26 [WARNING] 2021-05-08T08:15:24.294574Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2021-05-08T08:15:24.299965Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2021-05-08T08:15:24.299974Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
 
2021-05-08 08:15:26 [NOTE]    Generating SSL Certificates

MySQL 서비스 등록

서비스 mysqld 파일 복사

$ sudo cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld57

mysqld 수정

$ sudo vi /etc/init.d/mysqld57

mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.

basedir=/usr/local/mysql57
datadir=/usr/local/mysql57/data

mysqld 서비스 등록

$ sudo chkconfig --add mysqld57
$ sudo chmod +x /etc/init.d/mysqld57
$ chkconfig --list
 
Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.
 
mysqld57        0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off
tomcat          0:off   1:off   2:off   3:on    4:on    5:on    6:off
$ sudo chkconfig --level 345 mysqld57 on

mysqld 서비스 실행

$ sudo service mysqld57 start
Starting MySQL. SUCCESS!

root 암호 초기화

mysqld 실행

$ sudo bin/mysqld_safe --skip-grant-tables &
[1] 11505
2021-05-08T08:56:04.765371Z mysqld_safe Logging to '/var/log/mysqld.log'.
2021-05-08T08:56:04.792894Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

root 암호 설정

$ bin/mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-log Source distribution
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> update user set authentication_string = password('manager') where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye

ERROR 1820 (HY000) 오류 해결

mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('soY&Be9p/4ed!!!'); 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> 

사용자 추가

mysql> create user 'user1'@'%' identified by 'userpw';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant all privileges on *.* to 'user1'@'%' with grant option; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

사용자 추가 - 특정 DB에 권한 부여

mysql> create user 'terecal'@'%' identified by '****'; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant all privileges ON terecal_db.* TO 'terecal'@'%'; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)