1. 파일 다운로드
$ vi copySqlite.sh
#!/bin/bash
for i in `seq 1 31`; do
no=`printf "%02d" $i`
scp irteam@ssingssing2Server:/home1/irteam/backup/backup_202001${no}/backup.sqlite ./vst202001${no}.sqlite
done
$ chmod 755 ./copySqlite.sh
$ ./copySqlite.sh
backup.sqlite 100% 3369KB 1.7MB/s 00:02
backup.sqlite 100% 3671KB 3.6MB/s 00:01
backup.sqlite 100% 3590KB 3.5MB/s 00:01
backup.sqlite 100% 4113KB 4.0MB/s 00:01
backup.sqlite 100% 3665KB 3.6MB/s 00:01
backup.sqlite 100% 3615KB 3.5MB/s 00:01
backup.sqlite 100% 3609KB 1.8MB/s 00:02
backup.sqlite 100% 3781KB 3.7MB/s 00:01
backup.sqlite 100% 3414KB 3.3MB/s 00:01
backup.sqlite 100% 3325KB 3.3MB/s 00:01
backup.sqlite 100% 3406KB 3.3MB/s 00:01
backup.sqlite 100% 3306KB 3.2MB/s 00:01
backup.sqlite 100% 3295KB 3.2MB/s 00:00
backup.sqlite 100% 3464KB 3.4MB/s 00:01
backup.sqlite 100% 3535KB 3.5MB/s 00:01
backup.sqlite 100% 3220KB 3.1MB/s 00:01
backup.sqlite 100% 3112KB 3.0MB/s 00:01
backup.sqlite 100% 2987KB 2.9MB/s 00:01
backup.sqlite 100% 3133KB 3.1MB/s 00:01
backup.sqlite 100% 3100KB 3.0MB/s 00:01
backup.sqlite 100% 3312KB 3.2MB/s 00:01
backup.sqlite 100% 3431KB 3.4MB/s 00:01
backup.sqlite 100% 3190KB 1.6MB/s 00:02
backup.sqlite 100% 3171KB 1.6MB/s 00:02
backup.sqlite 100% 4996KB 4.9MB/s 00:01
backup.sqlite 100% 3916KB 3.8MB/s 00:01
backup.sqlite 100% 3732KB 3.6MB/s 00:01
backup.sqlite 100% 3859KB 3.8MB/s 00:01
backup.sqlite 100% 3760KB 3.7MB/s 00:01
backup.sqlite 100% 3396KB 3.3MB/s 00:01
backup.sqlite 100% 3116KB 3.0MB/s 00:00
2. MySQL 로컬 설치
2-1. Homebrew 확인
$ brew search mysql
==> Formulae
automysqlbackup mysql-connector-c++ mysql@5.6
mysql mysql-connector-c++@1.1 mysql@5.7
mysql++ mysql-sandbox mysqltuner
mysql-client mysql-search-replace
mysql-connector-c mysql-utilities
==> Casks
homebrew/cask/mysql-connector-python homebrew/cask/navicat-for-mysql
homebrew/cask/mysql-shell homebrew/cask/sqlpro-for-mysql
homebrew/cask/mysql-utilities
2-2. MySQL 설치 (8버전이 설치 됨)
$ brew install mysql
$ mysql --version
mysql Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)
2-3. MySQL 구동
$ mysql.server start
Starting MySQL
. SUCCESS!
2-4. MySQL 설정
$ mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough.
# "Would you like to setup VALIDATE PASSWORD component?
# Press y|Y for Yes, any other key for No"
# 비밀번호 가이드 설정에 대한 질문입니다.
# Yes 또는 No를 입력해주세요.
# Yes - 복잡한 비밀번호 설정
# (ex. "q1w2e3r4"와 같은 조합형(?) 비밀번호를 설정하여야 합니다.)
# No - 쉬운 비밀번호 설정
# (ex. "1234"처럼 쉬운 비밀번호를 설정할 수 있습니다.)
# *저는 "No"로 설정하였습니다.
Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: No
Please set the password for root here.
# 앞으로 사용할 새로운 비밀번호를 입력해주세요.
# (비밀번호를 입력할 때 창에 아무것도 입력되지 않으므로 그냥 입력하시면 됩니다.)
New password:
Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
# "Remove anonymous users? (Press y|Y for Yes. any other key for No)"
# 사용자 설정을 묻는 질문입니다.
# Yes 또는 No를 입력해주세요.
# Yes - 접속하는 경우 "mysql -uroot"처럼 -u 옵션 필요
# No - 접속하는 경우 "mysql"처럼 -u 옵션 불필요
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Yes
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
# "Disallow root login remotely? (Press y|Y for Yes, any other key for No)"
# 다른 IP에서 root 아이디로 원격접속을 설정하는 질문입니다.
# Yes - 원격접속 불가능
# No - 원격접속 가능
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Yes
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
# "Remove test database and access to it? (Press y|Y for Yes, any other key for No)"
# Test 데이터베이스를 설정하는 질문입니다.
# Yes - Test 데이터베이스 제거
# No - Test 데이터베이스 유지
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Yes
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
# "Reload privilege tables now? (Press y|Y for Yes, any other key for No)"
# 변경된 권한을 테이블에 적용하는 설정에 대한 질문입니다.
# Yes - 적용시킨다.
# No - 적용시키지 않는다.
# *저는 "Yes"로 설정하였습니다.
# (해당 질문은 무조건 "Yes" 하시기 바랍니다.)
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Yes
Success.
All done!
2-5. MySQL 접속
$ mysql -u'root' -p'1234'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: Unknown OS character set ''.
mysql: Switching to the default character set 'utf8mb4'.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.19 Homebrew
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> status
--------------
mysql Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)
Connection id: 12
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.19 Homebrew
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 9 min 38 sec
Threads: 2 Questions: 15 Slow queries: 0 Opens: 130 Flush tables: 3 Open tables: 48 Queries per second avg: 0.025
———————
mysql>
2-6. 유저 생성 및 권한 부여
mysql> create user 'ssing'@'%' identified by '1234';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'ssing'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
2-7. testdb 데이터베이스 생성
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb;
Database changed
2-8. avt_brnd_vst_hist 테이블 생성
mysql> CREATE TABLE avt_brnd_vst_hist ( vst_seq BIGINT(19) NOT NULL, brnd_avt_no BIGINT(20) NOT NULL, guest_avt_no BIGINT(20) DEFAULT NULL, vst_ymdt varchar(20) NOT NULL );
Query OK, 0 rows affected, 3 warnings (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_testdb |
+-------------------+
| avt_brnd_vst_hist |
+-------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
3. 소스 작업
3-1. 프로젝트 생성
3-2. 소스 코드 적용
1) Dependency
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.30.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
2) Application
@SpringBootApplication
public class Sqlite2mysqlApplication {
public static void main(String[] args) {
Sqlite2mysqlApplication app = new Sqlite2mysqlApplication();
final int maxDay = 31;
for (int i = 1; i < maxDay; i++) {
if (i < 10) {
app.insertMysql(app.getSqliteData("0" + i));
} else {
app.insertMysql(app.getSqliteData(Integer.toString(i)));
}
}
}
private Connection connectSqlite(String date) {
//todo sqlite 파일이 있는 위치 정보
String url = "jdbc:sqlite:/Applications/ssingssing2/sqlite/vst202001" + date + ".sqlite";
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private List<InsertData> getSqliteData(String date) {
String sql = "SELECT vst_seq, brnd_avt_no, guest_avt_no, vst_ymdt FROM avt_brnd_vst_hist";
try (Connection conn = this.connectSqlite(date);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
List<InsertData> list = new ArrayList<>();
while (rs.next()) {
list.add(new InsertData(rs.getInt("vst_seq"), rs.getLong("brnd_avt_no"), rs.getLong("guest_avt_no"), changeDateFormat(rs.getString("vst_ymdt"))));
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
private String changeDateFormat(String vst_ymdt) {
return vst_ymdt.substring(0, 4) + vst_ymdt.substring(5, 7) + vst_ymdt.substring(8, 10);
}
private void insertMysql(List<InsertData> list) {
try {
//todo 백업 DB정보 -- 개인테스트db
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb?autoReconnect=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC", "ssing", "1234");
String query = " insert into avt_brnd_vst_hist values (?, ?, ?, ?)";
PreparedStatement preparedStmt;
for (InsertData insertData : list) {
preparedStmt = conn.prepareStatement(query);
preparedStmt.setInt(1, insertData.vst_seq);
preparedStmt.setLong(2, insertData.brnd_avt_no);
preparedStmt.setLong(3, insertData.guest_avt_no);
preparedStmt.setString(4, insertData.vst_ymdt);
preparedStmt.execute();
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
static class InsertData {
int vst_seq;
long brnd_avt_no;
long guest_avt_no;
String vst_ymdt;
InsertData(int vst_seq, long brnd_avt_no, long guest_avt_no, String vst_ymdt) {
this.vst_seq = vst_seq;
this.brnd_avt_no = brnd_avt_no;
this.guest_avt_no = guest_avt_no;
this.vst_ymdt = vst_ymdt;
}
}
}
3-3. 데이터 확인
SELECT
vst_ymdt,
count(1)
FROM
testdb.avt_brnd_vst_hist
GROUP BY
vst_ymdt
ORDER BY
vst_ymdt ASC
;
******************** 1. row *********************
vst_ymdt: 20191229
count(1): 54295
******************** 2. row *********************
vst_ymdt: 20191230
count(1): 59087
******************** 3. row *********************
...
******************** 29. row *********************
vst_ymdt: 20200126
count(1): 60491
******************** 30. row *********************
vst_ymdt: 20200127
count(1): 54729
30 rows in set