1. MySQL Connector install
$ wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.40.tar.gz
$ tar -xvzf mysql-connector-java-5.1.40.tar.gz
$ cd mysql-connector-java-5.1.40
## connector 파일 이동
$ cp mysql-connector-java-5.1.40-bin.jar /usr/local/tomcat8/lib2. MySQL Test DB create
$ cd /usr/local/mysql/bin
$ ./mysql -u root -p
password: root
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
# 사용할 DB 선택
mysql> use test;
Database changed
# test DB에 users 테이블 생성
mysql> CREATE TABLE test.users (
-> _id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> age INT UNSIGNED NOT NULL,
-> PRIMARY KEY(_id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users;
Empty set (0.00 sec)
mysql> INSERT INTO users (name, age) VALUES ("엄신영", 28);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users (name, age) VALUES ("신영이", 28);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM users;
+-----+------------+-----+
| _id | name | age |
+-----+------------+-----+
| 1 | 엄신영 | 28 |
| 2 | 신영이 | 28 |
+-----+------------+-----+
2 rows in set (0.00 sec)
mysql>3. test.jsp
웹 페이지에 띄워볼 jsp 파일을 만들어보자.
$ vi /usr/local/tomcat8/webapps/ROOT/test.jsp
<%@page import="java.sql.*" contentType="text/html;charset=utf-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>DB Connection Test</title>
</head>
<body>
<%
String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false";
String DB_USER = "root";
String DB_PASSWORD= "root";
Connection conn;
Statement stmt;
PreparedStatement ps;
ResultSet rs;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
stmt = conn.createStatement();
ps = conn.prepareStatement("SELECT*FROM users");
rs = ps.executeQuery();
%>
<table border="1"><thead><th>Name</th><th>Age</th></thead><tbody>
<%
while (rs.next()) {
out.print("<tr><td>" + rs.getString("name") + "</td>");
out.print("<td>" + rs.getString("age") + "</td></tr>");
}
%>
</tbody>
</table>
<%
out.println("MySQL Connection Success!");
conn.close();
} catch(Exception e){
out.println(e);
}
%>
</body>
</html>
4. Error
Error (1)
"Host '127.0.0.1' is not allowed to connect to this MySQL server"
# Error
java.sql.SQLException: null, message from server: "Host '127.0.0.1' is not allowed to connect to this MySQL server"
# Solution
mysql> select host, user from mysql.user;
mysql> INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher,
x509_issuer, x509_subject) VALUES ('127.0.0.1','root',password('test'),'','','');
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';
mysql> flush privileges;Error (2)
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 2 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
# Error
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications
link failure The last packet successfully received from the server was
2 milliseconds ago. The last packet sent successfully to the server was
1 milliseconds ago.
# Solution
# 로그파일 열어서 직접 까보자
$ cd /usr/local/tomcat8/logs
# 오늘 날짜로된 로그 파일 열어보기
$ cat catalina.out
22-May-2021 22:21:23.837 정보 [main] org.apache.coyote.AbstractProtocol.start
프로토콜 핸들러 ["http-nio-8080"]을(를) 시작합니다.
22-May-2021 22:21:23.848 정보 [main] org.apache.coyote.AbstractProtocol.start
프로토콜 핸들러 ["ajp-nio-0.0.0.0-8009"]을(를) 시작합니다.
22-May-2021 22:21:23.850 정보 [main] org.apache.catalina.startup.Catalina.start
Server startup in 1289 ms
Sat May 22 22:21:47 KST 2021 WARN: Establishing SSL connection without
server's identity verification is not recommended. According to MySQL
5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be
established by default if explicit option isn't set. For compliance
with existing applications not using SSL the verifyServerCertificate
property is set to 'false'. You need either to explicitly disable SSL
by setting useSSL=false, or set useSSL=true and provide truststore
for server certificate verification.
# 위의 로그를 읽어보면 SSL 옵션을 명시하라는 경고
# useSSL 옵션이란? db에 접속할 때, SSL방식으로 접속할건지 안할건지 명시하는 것.
# 허용한다면 true, SSL을 지원하지 않는다면(아니라면) false 기입
# 다시돌아와서, db.jsp 파일을 열어서 수정하자
$ cd /usr/local/tomcat8/webapps/ROOT/
$ vi db.jsp
String url = "jdbc:mysql://localhost:3306/new_schema?useSSL=false";