Ubuntu에 MySql 설치 & Database 및 계정 생성

 

MySQL 설치

$ sudo apt-get update
$ sudo apt-get install -y mysql-server

MySQL 데몬 설정 (서버 재시작시 자동으로 실행되도록)

$ sudo systemctl enable mysql

MySQL 프로세스 시작

$ sudo systemctl start mysql

MySQL 접속

$ sudo /usr/bin/mysql -u root -p

패스워드 설정

> ALTER USER 'root'@'localhost' IDENTIFIED BY '설정할비밀번호';
Query OK, 0 rows affected (0.00 sec)

// 5.7.6 이전 버전
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('설정할비밀번호');

버전 확인 방법

> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.31                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2   |
| version                 | 5.7.31-0ubuntu0.18.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+
8 rows in set (0.00 sec)

Database 생성 및 확인

> CREATE DATABASE DB명;
Query OK, 1 row affected (0.00 sec)

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DB명                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

계정 생성 및 확인

> SELECT User, Host, authentication_string FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | authentication_string                     |
+------------------+-----------+-------------------------------------------+
| root             | localhost |                                           |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | *A7AE3D271FAC9FFBD36FA40374E88D1CAB52E134 |
+------------------+-----------+-------------------------------------------+

> CREATE USER '계정명'@'%' IDENTIFIED BY '비밀번호';

// 반영
> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
> SELECT User, Host, authentication_string FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | authentication_string                     |
+------------------+-----------+-------------------------------------------+
| root             | localhost |                                           |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | *A7AE3D271FAC9FFBD36FA40374E88D1CAB52E134 |
| 계정명             | %         | *암호화된암호가들어갑니다.                       |
+------------------+-----------+-------------------------------------------+

계정 권한 부여 및 확인

> SHOW GRANTS FOR '계정명'@'%';
+------------------------------------+
| Grants for 계정명@%                  |
+------------------------------------+
| GRANT USAGE ON *.* TO '계정명'@'%'   |
+------------------------------------+

> GRANT ALL PRIVILEGES ON DB명.* to '계정명'@'%';
Query OK, 0 rows affected (0.00 sec)

> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

> SHOW GRANTS FOR '계정명'@'%';
+--------------------------------------------------+
| Grants for 계정명@%                                |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO '계정명'@'%'                 |
| GRANT ALL PRIVILEGES ON `DB명`.* TO '계정명'@'%'   |
+--------------------------------------------------+
2 rows in set (0.00 sec)

외부 접속 허용

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
(기존) bind-address            = 127.0.0.1 // 내부에서만 3306 호출하도록 설정, 아래는 외부에서 인입 가능하도록 설정
(변경) bind-address            = 0.0.0.0
$ sudo systemctl restart mysql

서버 시간 수정 (한국 시간으로)

$ sudo ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime
$ sudo systemctl restart mysql