Ubuntu 리눅스 서버에 MySQL 5.7.x 설치

Ubuntu 리눅스 서버에 MySQL 5.7.x 설치

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

리눅스 정보 조회

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

$ cat /etc/*-release | uniq
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.3 LTS"
NAME="Ubuntu"
VERSION="14.04.3 LTS, Trusty Tahr"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 14.04.3 LTS"
VERSION_ID="14.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

리눅스 비트 확인

출처 : 리눅스 32비트 64비트 확인 - 제타위키

$ getconf LONG_BIT
64

관련패키지 설치

$ sudo apt-get update
$ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

MySQL 계정 만들기

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

cmake 설치

$ sudo apt-get install cmake

cmake 수동 설치(option)

$ wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
$ tar xvf cmake-2.8.4.tar.gz 
$ cd cmake-2.8.4
$ ./bootstrap
$ make all
$ sudo make install

MySQL 소스 다운로드

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20.tar.gz

MySQL 소스 압축풀기

$ tar xvf mysql-5.7.20.tar.gz

MySQL 컴파일 및 설치

$ cd mysql-5.7.20
$ sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=3306 \
-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=1 -DWITH_BOOST=/usr/local/mysql/boost
$ sudo make
$ sudo make install

MySQL 데이터베이스 초기화

$ cd /usr/local/mysql
$ echo "./bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data -v"
$ sudo ./bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql -v
2017-09-20 20:17:58 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2017-09-20 20:17:58 [NOTE]    Using existing directory /usr/local/mysql/data
2017-09-20 20:17:58 [NOTE]    Generating random password to /root/.mysql_secret...done.
2017-09-20 20:17:58 [NOTE]    Setting file ownership to mysql
2017-09-20 20:17:58 [NOTE]    Executing /usr/local/mysql/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql
2017-09-20 20:18:00 [NOTE]    Creating system tables...done.
2017-09-20 20:18:00 [NOTE]    Filling system tables with data...done.
2017-09-20 20:18:01 [NOTE]    Filling help table with data...done.
2017-09-20 20:18:01 [NOTE]    Creating user for internal session service...done.
2017-09-20 20:18:01 [NOTE]    Creating default user root@localhost
2017-09-20 20:18:01 [NOTE]    Creating default proxy root@localhost
2017-09-20 20:18:01 [NOTE]    Creating sys schema
2017-09-20 20:18:02 [NOTE]    done.
2017-09-20 20:18:03 [WARNING] The bootstrap log isn't empty:
2017-09-20 20:18:03 [WARNING] 2017-09-20T11:17:58.125569Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2017-09-20T11:17:58.126616Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-09-20T11:17:58.126627Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2017-09-20 20:18:03 [NOTE]    Generating SSL Certificates

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

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

환경설정

$ vi /usr/local/mysql/my.cnf

[mysqld]
# port=3306
# basedir=/usr/local/mysql
# datadir=/usr/local/mysql/data
# pid-file=/usr/local/mysql/mysqld.pid
# log_error=/usr/local/mysql/mysql_error.log
# lc-messages-dir=/usr/local/mysql/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 서비스 등록

서비스 mysqld 파일 복사

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

mysqld 수정

$ sudo vi /etc/init.d/mysqld

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

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

mysqld 서비스 등록

$ sudo update-rc.d mysqld defaults
 Adding system startup for /etc/init.d/mysqld ...
   /etc/rc0.d/K20mysqld -> ../init.d/mysqld
   /etc/rc1.d/K20mysqld -> ../init.d/mysqld
   /etc/rc6.d/K20mysqld -> ../init.d/mysqld
   /etc/rc2.d/S20mysqld -> ../init.d/mysqld
   /etc/rc3.d/S20mysqld -> ../init.d/mysqld
   /etc/rc4.d/S20mysqld -> ../init.d/mysqld
   /etc/rc5.d/S20mysqld -> ../init.d/mysqld

mysqld 서비스 실행

$ sudo service mysqld start
Starting MySQL
. * 
$ sudo service mysqld status
 * MySQL running (29565)
$ sudo service mysqld stop
Shutting down MySQL
. * 

root 암호 초기화

$ sudo ./bin/mysqld_safe --skip-grant-tables &
[1] 31008
mysql@localhost:/usr/local/mysql$ 2017-04-18T09:21:34.035860Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.err'.
2017-04-18T09:21:34.050764Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

mysql@localhost:/usr/local/mysql$ ./bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, 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('sql') where user = 'root';

Query OK, 1 row affected, 1 warning (0.02 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> select 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('sql');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql$gt; select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

사용자 추가

mysql> create user 'user1'@'%' identified by 'userpw';
mysql> grant all privileges on *.* to 'user1'@'%' with grant option;
mysql> flush privileges;

사용자 추가

create user 'terecal'@'%' identified by '****';
GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%';
flush privileges;