Nginx에 MySQL 설치하기 for Centos7

 

Centos 환경에 PHP를 설치한 후 MySQL을 연동하는 방법을 알아보자.

 

---

 

1. MySQL 설치하기

$ yum install mysql-server

먼저 MySQL 서버를 설치해준다.

 

 

2. mysqld 데몬을 실행 및 자동 재시작 설정하기

$ systemctl start mysqld

$ systemctl enable mysqld

$ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2020-09-17 17:10:38 KST; 3 days ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 5068 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 5048 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5072 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─5072 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Sep 17 17:10:27 lifestyleredesign systemd[1]: Stopped MySQL Server.
Sep 17 17:10:27 lifestyleredesign systemd[1]: Starting MySQL Server...
Sep 17 17:10:38 lifestyleredesign systemd[1]: Started MySQL Server.

데몬 실행 및 자동 재시작 설정 후 실행 상태 확인하기

 

 

3. mysql root 계정 설정하기

$ mysql_secure_installation

Enter current password for root(enter for none):  (enter)

Set root password? [Y/n] y

New password: password

Re-enter new password: re enter password

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] n

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

Enter current password for root(enter for none): 이 부분에서 초기 root 계정은 비밀번호가 없으므로

그냥 엔터를 치고 다음으로 넘어간다.

 

 

4. mysql conf 파일 세팅하기

$ vi /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

 

5. mysqld 재시작하기

$ systemctl restart mysqld

 

 

6. mysql 접속하기

$ mysql -u root -p
Enter password: password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 541
Server version: 5.7.31 MySQL Community Server (GPL)

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>

Welcome ~ 안내 문구가 나오고 프롬프트 창이 mysql> 로 바뀌면 접속에 성공한 것이다.

 

 

7. 데이터베이스 생성 및 생성 데이터베이스 확인하기

mysql> CREATE DATABASE databasename;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

 

 

8. 데이터베이스로 접속하기

mysql> USE databasename;
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

databasename에 만들고자 하는 데이터베이스 이름을 대신 입력하면 된다.

 

 

9. 테이블 생성하기

mysql> CREATE TABLE users (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	userID VARCHAR(30) NOT NULL,
	password VARCHAR(30) NOT NULL,
	username VARCHAR(30) NOT NULL
)

 

 

10. 해당 데이터베이스에 접근 가능한 계정 만들기

mysql> CREATE USER 'userID'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

 

 

11. 해당 계정에 접근 권한 부여하기

mysql> GRANT ALL ON databasename.* TO userID'%' IDENTIFIED BY 'password';
Query OK, 0 row affected (0.00 sec)

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

 

 

(추가사항) 12. 서버 방화벽 해제하기

$ firewall-cmd --zone=public --add-service=mysql --permanent

$ firewall-cmd --reload

로컬 서버가 아닌 클라우드 서버 사용의 경우 방화벽 설정을 추가로 해줘야 할 수 도 있다.