MySQL을 설치해보자! (Ubuntu 18.04) + 샘플 데이터 불러오기!

MySQL 설치

$ sudo apt-get update
$ sudo apt-get intsall mysql-server

설치 후 usr/bin 경로 아래 mysql 관련 파일들이 생긴 것을 확인할 수 있다.

$ ls /usr/bin | grep mysql
mysql
mysql_config_editor
mysql_embedded
mysql_install_db
mysql_plugin
mysql_secure_installation
mysql_ssl_rsa_setup
mysql_tzinfo_to_sql
mysql_upgrade
mysqladmin
mysqlanalyze
mysqlbinlog
mysqlcheck
mysqld_multi
mysqld_safe
mysqldump
mysqldumpslow
mysqlimport
mysqloptimize
mysqlpump
mysqlrepair
mysqlreport
mysqlshow
mysqlslap

 

MySQL 설정

//방화벽 설정
$ sudo ufw allow mysql 
Rules updated 
Rules updated (v6) 

$ cat /etc/services | grep mysql
mysql           3306/tcp
mysql           3306/udp
mysql-proxy     6446/tcp                        # MySQL Proxy
mysql-proxy     6446/udp

//systemctl 등록(부팅 시 자동 실행 기능. 생략가능)
$ sudo systemctl start mysql 
$ sudo systemctl enable mysql 
Synchronizing state of mysql.service with SysV service script with /lib/syste md/systemd-sysv-install. 
Executing: /lib/systemd/systemd-sysv-install enable mysql 

//환경 변수 추가
$ cat ~/.bashrc | grep mysql
#mysql
export MYSQL_HOME=/usr/bin/mysql

 

MySQL 접속 및 사용자 계정 생성

$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, 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

//계정 생성
//사용자 계정과 호스트네임 또는 ip 입력
mysql> create user 'user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

//계정 비밀번호 생성(mysql 5.7 버전 이하는 authentication_string이 아닌 password)
mysql> update user set authentication_string=password('password입력') where user='user입력';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

//생성된 계정 확인. 테이블 형태로 결과 출력됨
mysql> select user, host, authentication_string from user;

//권한 부여. 모든 권한을 부여
mysql> grant all privileges on *.* to 'user입력'@'호스트네임';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//권한 확인
mysql> show grants for 'user'@'hostname';
+--------------------------------------------------+
| Grants for user@hostname                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user'@'hostname' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

 

MySQL 호스트네임 또는 외부 접속을 위한 설정

/etc/mysql/mysql.conf.d/mysqld.cnf

위 파일을 열어 bind-address = 127.0.0.1 을 주석처리

$ sudo cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address
# bind-address          = 127.0.0.1

//설정 적용을 위해 mysql 재실행
$ service mysql restart

 

사용자 계정 접속방법

$ mysql -u 계정 -h 호스트 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, 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>

 

SSL 설정

$ mkdir mysqlcerts

$ cd mysqlcerts

$ openssl genrsa -out key.pem 2048
Generating RSA private key, 2048 bit long modulus (2 primes)
....................................+++++
......................................................+++++
e is 65537 (0x010001)
hadoopuser@songhee1:~/mysqlcerts$ openssl req -new -x509 -key key.pem -out cert.pem -days 0
req: Non-positive number "0" for -days
req: Use -help for summary.

$ openssl req -new -x509 -key key.pem -out cert.pem -days 100

$ ls
cert.pem  key.pem

//위에 cert.pem과 key.pem를 저장한 위치를 적절하게 입력한다.
$ vi /etc/mysql/my.cnf
[mysqld]
ssl-ca=/mysqlcerts/cert.pem
ssl-cert=/home/mysqlcerts/cert.pem
ssl-key=/home/mysqlcerts/key.pem

//mysql 재실행
$ sudo service mysql restart 

 

Sample 데이터 불러오기

아래 깃헙 레파지토리의 샘플 데이터를 불러온다.

github.com/datacharmer/test_db

datacharmer/test_db

//sample DB를 저장할 디렉토리를 생성하고, 해당 디렉토리에 clone.
/sample_data$ git clone https://github.com/datacharmer/test_db.git
Cloning into 'test_db'...
remote: Enumerating objects: 11, done.
remote: Counting objects: 100% (11/11), done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 116 (delta 5), reused 8 (delta 4), pack-reused 105
Receiving objects: 100% (116/116), 74.27 MiB | 13.58 MiB/s, done.
Resolving deltas: 100% (59/59), done.


~/sample_data$ ls
test_db

~/sample_data$ cd test_db/
~/sample_data/test_db$ ls
Changelog                      load_dept_emp.dump      objects.sql
README.md                      load_dept_manager.dump  sakila
employees.sql                  load_employees.dump     show_elapsed.sql
employees_partitioned.sql      load_salaries1.dump     sql_test.sh
employees_partitioned_5.1.sql  load_salaries2.dump     test_employees_md5.sql
images                         load_salaries3.dump     test_employees_sha.sql
load_departments.dump          load_titles.dump        test_versions.sh

//샘플DB(employees.sql) 불러오기
$ mysql -u song -h songhee1 -p < ~/sample_data/test_db/employees.sql
Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:22

//mysql 접속
$ mysql -u user입력 -h hostname입력 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, 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.

//DB확인. employees DB가 저장된 것을 볼 수 있다.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use employees;
Database changed

mysql> select * from employees;
//무지 많은 데이터가 나올거임..