OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Mysql 8.0
방법 : mysql 8.0 mysqldump 사용법
본문에서는 mysqldump 명령어 사용법을 설명하고
실제 sample_db라는 db를 생성하고 sample_user를 생성 후 이 유저에 테이블, 뷰, 프로시저, 트리거, 이벤트 등을 생성하고 mysqldump를 이용해 이관해봄
mysqldump 백업 명령어
전체 데이터베이스 백업
예시 : mysqldump -u [사용자] -p --all-databases > all_backup.sql
|
1
|
# mysqldump -u root -p --all-databases > all_backup.sql
|
특정 데이터베이스 백업
|
1
2
|
예시 : mysqldump -u [사용자] -p [데이터베이스명] > db_backup.sql
# mysqldump -u root -p sample_db > sample_db.sql
|
특정 테이블만 백업
예시 : mysqldump -u [사용자] -p [DB명] [테이블1] [테이블2] > table_backup.sql
|
1
|
# mysqldump -u root -p sample_db logs > logs_users.sql
|
구조만 백업 (데이터 제외)
예시 : mysqldump -u [사용자] -p --no-data [DB명] > sample_schema_only.sql
|
1
|
# mysqldump -u root -p --no-data sample_db > sample_schema_only.sql
|
dump 파일 확인
|
1
2
3
4
5
|
# ls -al *.sql
-rw-r--r--. 1 root root 6558125 Jun 8 15:26 all_backup.sql
-rw-r--r--. 1 root root 5272285 Jun 8 15:30 logs_users.sql
-rw-r--r--. 1 root root 5272285 Jun 8 15:27 sample_db.sql
-rw-r--r--. 1 root root 1833 Jun 8 15:30 sample_schema_only.sql
|
정상적으로 생성됨
테스트
MySQL 접속
|
1
|
# mysql -u root -p
|
샘플 데이터베이스 생성
|
1
2
|
mysql> CREATE DATABASE IF NOT EXISTS sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)
|
사용자 생성 (이미 있으면 생략)
|
1
2
|
mysql> CREATE USER IF NOT EXISTS 'sample_user'@'localhost' IDENTIFIED BY 'Sample_pass123!';
Query OK, 0 rows affected (0.01 sec)
|
권한 부여
|
1
2
3
4
|
mysql>
GRANT ALL PRIVILEGES ON sample_db.* TO 'sample_user'@'localhost';
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
|
샘플 db로 이동(database 변경)
|
1
2
|
mysql> USE sample_db;
Database changed
|
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
|
mysql>
CREATE TABLE IF NOT EXISTS logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50),
action VARCHAR(100),
created_at DATETIME
);
Query OK, 0 rows affected (0.01 sec)
|
대량 데이터를 삽입할 샘플 프로시저 작성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql>
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_logs_bulk$$
CREATE PROCEDURE insert_logs_bulk(IN max_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION;
WHILE i <= max_count DO
INSERT INTO logs (user_name, action, created_at)
VALUES (
CONCAT('user_', FLOOR(1 + RAND() * 1000)),
CONCAT('action_', FLOOR(1 + RAND() * 100)),
NOW() - INTERVAL FLOOR(RAND() * 365) DAY
);
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
|
*참고. DELIMITER 란?
MySQL에서 사용하는 DELIMITER 명령은 프로시저, 트리거, 이벤트 등에서 사용되는 구문 블록(BEGIN ... END)을 제대로 인식시키기 위해 사용하는 구문
프로시저나 트리거를 작성할 때는 내부에 세미콜론이 여러 번 사용됨
이렇게 되면 MySQL은 첫 번째 세미콜론(;)에서 명령이 끝났다고 착각하고 구문 오류를 냄
그래서 MySQL에게 "지금부터 명령어의 끝은 ;가 아니라 $$ 또는 다른 문자야"라고 알려줘야 함
그게 바로 DELIMITER 명령
10만 건 삽입
|
1
2
|
mysql> CALL insert_logs_bulk(100000);
Query OK, 0 rows affected (6.34 sec)
|
건수 확인
|
1
2
3
4
5
6
7
|
mysql> SELECT COUNT(*) FROM logs;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.02 sec)
|
10만건이 삽입됨
샘플 테이블 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> SELECT * FROM logs LIMIT 10;
+----+-----------+-----------+---------------------+
| id | user_name | action | created_at |
+----+-----------+-----------+---------------------+
| 1 | user_959 | action_19 | 2025-05-25 15:05:10 |
| 2 | user_652 | action_14 | 2024-09-17 15:05:10 |
| 3 | user_212 | action_89 | 2024-08-18 15:05:10 |
| 4 | user_366 | action_41 | 2024-06-26 15:05:10 |
| 5 | user_532 | action_81 | 2025-01-08 15:05:10 |
| 6 | user_669 | action_10 | 2024-12-15 15:05:10 |
| 7 | user_110 | action_11 | 2025-03-25 15:05:10 |
| 8 | user_715 | action_96 | 2024-10-29 15:05:10 |
| 9 | user_192 | action_14 | 2025-05-09 15:05:10 |
| 10 | user_24 | action_87 | 2025-03-07 15:05:10 |
+----+-----------+-----------+---------------------+
10 rows in set (0.00 sec)
|
기타 오브젝트 생성
샘플 뷰 생성
|
1
2
|
mysql> CREATE VIEW logs_v1 AS SELECT id, user_name FROM logs;
Query OK, 0 rows affected (0.01 sec)
|
샘플 트리거 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql>
CREATE TABLE log_table (
log_id INT AUTO_INCREMENT PRIMARY KEY,
log_time DATETIME,
log_message VARCHAR(100)
);
DELIMITER $$
CREATE TRIGGER trg_logs_insert AFTER INSERT ON logs
FOR EACH ROW
BEGIN
INSERT INTO log_table (log_time, log_message)
VALUES (NOW(), CONCAT('Inserted ID: ', NEW.id));
END$$
DELIMITER ;
|
샘플 이벤트 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql>
SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE EVENT e1
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
INSERT INTO log_table (log_time, log_message)
VALUES (NOW(), 'Periodic event ran');
END$$
DELIMITER ;
|
오브젝트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
mysql>
SELECT table_name AS object_name, 'TABLE' AS object_type
FROM information_schema.tables
WHERE table_schema = 'sample_db' AND table_type = 'BASE TABLE'
UNION ALL
SELECT table_name AS object_name, 'VIEW' AS object_type
FROM information_schema.views
WHERE table_schema = 'sample_db'
UNION ALL
SELECT routine_name AS object_name, routine_type AS object_type
FROM information_schema.routines
WHERE routine_schema = 'sample_db'
UNION ALL
SELECT trigger_name AS object_name, 'TRIGGER' AS object_type
FROM information_schema.triggers
WHERE trigger_schema = 'sample_db'
UNION ALL
SELECT event_name AS object_name, 'EVENT' AS object_type
FROM information_schema.events
WHERE event_schema = 'sample_db';
+------------------+-------------+
| object_name | object_type |
+------------------+-------------+
| log_table | TABLE |
| logs | TABLE |
| logs_v1 | VIEW |
| insert_logs_bulk | PROCEDURE |
| trg_logs_insert | TRIGGER |
| e1 | EVENT |
+------------------+-------------+
6 rows in set (0.01 sec)
|
유저 목록 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| sample_user | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
|
권한 확인
|
1
2
3
4
5
6
7
8
|
mysql> SHOW GRANTS FOR 'sample_user'@'localhost';
+--------------------------------------------------------------------+
| Grants for sample_user@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sample_user`@`localhost` |
| GRANT ALL PRIVILEGES ON `sample_db`.* TO `sample_user`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
|
이관대상 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql>
SELECT
table_schema AS database_name,
ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE 1=1
#AND table_schema = 'sample_db'
GROUP BY table_schema
ORDER BY total_mb DESC;
+--------------------+---------+----------+----------+
| database_name | data_mb | index_mb | total_mb |
+--------------------+---------+----------+----------+
| sample_db | 5.53 | 0.00 | 5.53 |
| mysql | 2.28 | 0.33 | 2.61 |
| sys | 0.02 | 0.00 | 0.02 |
| information_schema | 0.00 | 0.00 | 0.00 |
| performance_schema | 0.00 | 0.00 | 0.00 |
+--------------------+---------+----------+----------+
5 rows in set (0.01 sec)
|
특정 db만 보고싶은 경우 where 절 table_schema 부분 주석 해제후 사용
특정 db 내 테이블별 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql>
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_mb,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'sample_db'
ORDER BY total_mb DESC;
+------------+---------+----------+----------+
| TABLE_NAME | data_mb | index_mb | total_mb |
+------------+---------+----------+----------+
| logs | 5.52 | 0.00 | 5.52 |
| log_table | 0.02 | 0.00 | 0.02 |
| logs_v1 | NULL | NULL | NULL |
+------------+---------+----------+----------+
3 rows in set (0.00 sec)
|
db 케릭터셋 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql>
SELECT schema_name , default_character_set_name, default_collation_name
FROM information_schema.schemata;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| mysql | utf8mb4 | utf8mb4_0900_ai_ci |
| information_schema | utf8mb3 | utf8mb3_general_ci |
| performance_schema | utf8mb4 | utf8mb4_0900_ai_ci |
| sys | utf8mb4 | utf8mb4_0900_ai_ci |
| sample_db | utf8mb4 | utf8mb4_general_ci |
+--------------------+----------------------------+------------------------+
5 rows in set (0.00 sec)
|
실제 이관
시스템 DB(mysql)의 사용자 및 권한 관련 테이블 데이터 백업
|
1
|
# mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > mysql_user_grants.sql
|
명령어 설명 :
mysqldump : MySQL 백업용 명령어 도구임
-u root : MySQL 접속에 사용할 사용자 이름을 지정함 (여기서는 root 계정임)
-p : 비밀번호 입력을 요구함. 입력 시 프롬프트에서 묻고 직접 입력하게 됨
mysql : 백업할 데이터베이스 이름임 (여기서는 시스템 DB인 mysql임)
user db tables_priv columns_priv procs_priv : 백업 대상 테이블들을 지정함. 사용자 계정과 권한 관련된 테이블임
> mysql_user_grants.sql : 실행 결과(SQL 문장)를 이 파일로 리디렉션하여 저장함
목적 : 시스템 DB(mysql)의 사용자 및 권한 관련 테이블 데이터를 백업함
대상 DB : mysql (시스템 DB) 중에서 일부 권한 관련 테이블만 백업함
백업되는 내용 : user 계정 정보, DB별 권한, 테이블/컬럼 권한, 프로시저 권한 등이 포함됨
사용 시기 : 사용자/권한만 복구하거나 다른 서버에 계정만 이전하고 싶을 때 사용함
주의 : MySQL 버전에 따라 mysql.user 테이블 구조가 달라 복구 시 오류가 발생할 수 있음
GRANT 또는 CREATE USER 같은 SQL 구문은 포함되지 않으며, 테이블 구조가 달라질 경우 복원에 실패할 수 있음
장점 : 시스템 DB 전체가 아닌 필요한 권한 테이블만 백업하므로 백업 용량이 작고 목적이 명확함
특정 사용자 데이터베이스 전체 백업
|
1
|
# mysqldump -u root -p --single-transaction --routines --triggers --events sample_db > sample_db_full.sql
|
명령어 설명:
mysqldump : MySQL 백업용 명령어 도구임
-u root : MySQL 접속에 사용할 사용자 이름을 지정함
-p : 비밀번호 입력을 요구함
--single-transaction : 백업 중 트랜잭션을 사용해 데이터 일관성을 보장하고, InnoDB 테이블에 대해 락 없이 백업할 수 있게 함
--routines : 저장 프로시저와 함수(routine)를 함께 백업함
--triggers : 테이블에 설정된 트리거도 함께 백업함
--events : 스케줄러 기반 이벤트도 백업에 포함함
sample_db : 백업 대상이 되는 데이터베이스 이름임
> sample_db_full.sql : 백업 결과를 sample_db_full.sql 파일로 저장함
목적: 특정 사용자 데이터베이스 전체를 백업함
대상 DB: sample_db (일반 사용자 데이터베이스 전체)
백업되는 내용: 모든 테이블, 데이터, 트리거, 프로시저, 함수, 이벤트 등이 포함됨
사용 시기: 특정 DB를 다른 서버로 이전하거나 백업용으로 저장할 때 사용함
특징: --single-transaction 옵션을 사용해 InnoDB 테이블 기준으로 테이블 락 없이 백업이 가능함
주의: 사용자 정보나 권한 정보는 포함되지 않으며, 오직 데이터베이스 안의 오브젝트만 백업됨
dump된 파일 확인
|
1
2
3
|
# ls -al *.sql
-rw-r--r--. 1 root root 15530 Jun 8 16:20 mysql_user_grants.sql
-rw-r--r--. 1 root root 5278274 Jun 8 16:20 sample_db_full.sql
|
참고1. mysql_user_grants.sql 파일 내용 확인(더보기)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
|
# cat mysql_user_grants.sql
-- MySQL dump 10.13 Distrib 8.0.42, for Linux (x86_64)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 8.0.42
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int unsigned NOT NULL DEFAULT '0',
`max_updates` int unsigned NOT NULL DEFAULT '0',
`max_connections` int unsigned NOT NULL DEFAULT '0',
`max_user_connections` int unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT 'caching_sha2_password',
`authentication_string` text COLLATE utf8mb3_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_role_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Drop_role_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Password_reuse_history` smallint unsigned DEFAULT NULL,
`Password_reuse_time` smallint unsigned DEFAULT NULL,
`Password_require_current` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`User_attributes` json DEFAULT NULL,
PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Users and global privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('localhost','mysql.infoschema','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'caching_sha2_password','$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED','N','2025-06-08 05:53:04',NULL,'Y','N','N',NULL,NULL,NULL,NULL),('localhost','mysql.session','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'caching_sha2_password','$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED','N','2025-06-08 05:53:03',NULL,'Y','N','N',NULL,NULL,NULL,NULL),('localhost','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'caching_sha2_password','$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED','N','2025-06-08 05:53:04',NULL,'Y','N','N',NULL,NULL,NULL,NULL),('localhost','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'caching_sha2_password','$A$005$jWD\Z
Jdg%CWozMESXkOl89TgSDHEw5yHJpyuBE0mf2NX.WeWdxMccM5','N','2025-06-08 05:53:43',NULL,'N','Y','Y',NULL,NULL,NULL,NULL),('localhost','sample_user','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'caching_sha2_password','$A$005$heQg 9N!vp!0vCa.X/.bLYV0MX/eQZ7rRFhnjlZUbDX8CyB2bcCc1nn6H4cIt2','N','2025-06-08 06:03:03',NULL,'N','N','N',NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `db`
--
DROP TABLE IF EXISTS `db`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `db` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`,`Db`),
KEY `User` (`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Database privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `db`
--
LOCK TABLES `db` WRITE;
/*!40000 ALTER TABLE `db` DISABLE KEYS */;
INSERT INTO `db` VALUES ('localhost','performance_schema','mysql.session','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'),('localhost','sys','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y'),('localhost','sample_db','sample_user','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
/*!40000 ALTER TABLE `db` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tables_priv`
--
DROP TABLE IF EXISTS `tables_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tables_priv` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Grantor` varchar(288) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`User`,`Db`,`Table_name`),
KEY `Grantor` (`Grantor`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Table privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tables_priv`
--
LOCK TABLES `tables_priv` WRITE;
/*!40000 ALTER TABLE `tables_priv` DISABLE KEYS */;
INSERT INTO `tables_priv` VALUES ('localhost','mysql','mysql.session','user','boot@','2025-06-08 05:53:04','Select',''),('localhost','sys','mysql.sys','sys_config','root@localhost','2025-06-08 05:53:04','Select','');
/*!40000 ALTER TABLE `tables_priv` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `columns_priv` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Column_name` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`User`,`Db`,`Table_name`,`Column_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `columns_priv`
--
LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `procs_priv`
--
DROP TABLE IF EXISTS `procs_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `procs_priv` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Routine_name` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8mb3_bin NOT NULL,
`Grantor` varchar(288) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Host`,`User`,`Db`,`Routine_name`,`Routine_type`),
KEY `Grantor` (`Grantor`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Procedure privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `procs_priv`
--
LOCK TABLES `procs_priv` WRITE;
/*!40000 ALTER TABLE `procs_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `procs_priv` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2025-06-08 16:20:33
|
참고2. sample_db_full.sql 파일 내용 확인(더보기)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
# cat sample_db_full.sql
-- MySQL dump 10.13 Distrib 8.0.42, for Linux (x86_64)
--
-- Host: localhost Database: sample_db
-- ------------------------------------------------------
-- Server version 8.0.42
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `log_table`
--
DROP TABLE IF EXISTS `log_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `log_table` (
`log_id` int NOT NULL AUTO_INCREMENT,
`log_time` datetime DEFAULT NULL,
`log_message` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `log_table`
--
LOCK TABLES `log_table` WRITE;
/*!40000 ALTER TABLE `log_table` DISABLE KEYS */;
INSERT INTO `log_table` VALUES (1,'2025-06-08 16:19:21','Periodic event ran'),(2,'2025-06-08 16:20:21','Periodic event ran');
/*!40000 ALTER TABLE `log_table` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `logs`
--
DROP TABLE IF EXISTS `logs`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `logs` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`action` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `logs`
--
LOCK TABLES `logs` WRITE;
/*!40000 ALTER TABLE `logs` DISABLE KEYS */;
LOCK TABLES `logs` WRITE;
/*!40000 ALTER TABLE `logs` DISABLE KEYS */;
INSERT INTO `logs` VALUES (1,'user_959','action_19','2025-05-25 15:05:10'),(2,'user_652','action_14','2024-09-17 15:05:10')
,(3,'user_212','action_89','2024-08-18 15:05:10'),(4,'user_366','action_41','2024-06-26 15:05:10'),(5,'user_532','action_81'
,'2025-01-08 15:05:10'),(6,'user_669','action_10','2024-12-15 15:05:10'),(7,'user_110','action_11','2025-03-25 15:05:10'),
(8,'user_715','action_96','2024-10-29 15:05:10'),(9,'user_192','action_14','2025-05-09 15:05:10'),(10,'user_24','action_87','2025-03-07 15:05:10'),
(11,'user_690','action_68','2025-02-16 15:05:10'),(12,'user_512','action_64','2024-10-14 15:05:10'),(13,'user_345','action_77','2024-08-18 15:05:10'),
(14,'user_734','action_25','2025-05-31 15:05:10'),(15,'user_382','action_84','2025-05-21 15:05:10'),(16,'user_731','action_51','2025-02-05 15:05:10'),
...
(수많은 insert 구문)
,(100000,'user_148','action_71','2025-05-12 15:05:16');
/*!40000 ALTER TABLE `logs` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_logs_insert` AFTER INSERT ON `logs` FOR EACH ROW BEGIN
INSERT INTO log_table (log_time, log_message)
VALUES (NOW(), CONCAT('Inserted ID: ', NEW.id));
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Temporary view structure for view `logs_v1`
--
DROP TABLE IF EXISTS `logs_v1`;
/*!50001 DROP VIEW IF EXISTS `logs_v1`*/;
SET @saved_cs_client = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `logs_v1` AS SELECT
1 AS `id`,
1 AS `user_name`*/;
SET character_set_client = @saved_cs_client;
--
-- Dumping events for database 'sample_db'
--
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
/*!50106 DROP EVENT IF EXISTS `e1` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `e1` ON SCHEDULE EVERY 1 MINUTE STARTS '2025-06-08 16:18:21' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
INSERT INTO log_table (log_time, log_message)
VALUES (NOW(), 'Periodic event ran');
END */ ;;
/*!50003 SET time_zone = @saved_time_zone */ ;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;;
/*!50003 SET character_set_client = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ;
--
-- Dumping routines for database 'sample_db'
--
/*!50003 DROP PROCEDURE IF EXISTS `insert_logs_bulk` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_logs_bulk`(IN max_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION;
WHILE i <= max_count DO
INSERT INTO logs (user_name, action, created_at)
VALUES (
CONCAT('user_', FLOOR(1 + RAND() * 1000)),
CONCAT('action_', FLOOR(1 + RAND() * 100)),
NOW() - INTERVAL FLOOR(RAND() * 365) DAY
);
SET i = i + 1;
END WHILE;
COMMIT;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Final view structure for view `logs_v1`
--
/*!50001 DROP VIEW IF EXISTS `logs_v1`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8mb4 */;
/*!50001 SET character_set_results = utf8mb4 */;
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `logs_v1` AS select `logs`.`id` AS `id`,`logs`.`user_name` AS `user_name` from `logs` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2025-06-08 16:20:41
|
scp로 tobe 서버로 복사
|
1
2
3
4
5
6
7
8
9
|
# scp ./*.sql 192.168.137.51:/root/
The authenticity of host '192.168.137.51 (192.168.137.51)' can't be established.
ECDSA key fingerprint is SHA256:vgMXqL8w8gj1KVuz8Xa8Lp6au+LAao288G/XgDZRUJE.
ECDSA key fingerprint is MD5:a6:7b:73:a5:41:ff:be:4b:18:f4:8e:da:77:8c:2a:67.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.137.51' (ECDSA) to the list of known hosts.
root@192.168.137.51's password:
mysql_user_grants.sql 100% 15KB 11.0MB/s 00:00
sample_db_full.sql 100% 5155KB 92.1MB/s 00:00
|
tobe 서버에 mysql 설치
참고 : Oracle Linux 7.6에 Mysql 8.0 설치 가이드 ( https://positivemh.tistory.com/1239 )
tobe 서버에서 sample_user 생성
|
1
2
3
|
# mysql -u root -p
mysql> CREATE USER IF NOT EXISTS 'sample_user'@'localhost' IDENTIFIED BY 'Sample_pass123!';
Query OK, 0 rows affected (0.01 sec)
|
tobe 서버에서 tobe db 백업
|
1
|
# mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > mysql_user_before_restore.sql
|
시스템 DB(mysql)의 사용자 및 권한 관련 테이블 데이터 복원
|
1
|
# mysql -u root -p mysql < mysql_user_grants.sql
|
권한 적용
|
1
2
3
|
# mysql -u root -p
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
|
대상 db 생성
|
1
2
|
mysql> CREATE DATABASE IF NOT EXISTS sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
|
특정 사용자 데이터베이스 전체 복원
|
1
|
# mysql -u root -p sample_db < sample_db_full.sql
|
이관후 점검
유저 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| sample_user | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
|
권한 확인
|
1
2
3
4
5
6
7
8
|
mysql> SHOW GRANTS FOR 'sample_user'@'localhost';
+--------------------------------------------------------------------+
| Grants for sample_user@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sample_user`@`localhost` |
| GRANT ALL PRIVILEGES ON `sample_db`.* TO `sample_user`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
|
오브젝트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
mysql>
SELECT table_name AS object_name, 'TABLE' AS object_type
FROM information_schema.tables
WHERE table_schema = 'sample_db' AND table_type = 'BASE TABLE'
UNION ALL
SELECT table_name AS object_name, 'VIEW' AS object_type
FROM information_schema.views
WHERE table_schema = 'sample_db'
UNION ALL
SELECT routine_name AS object_name, routine_type AS object_type
FROM information_schema.routines
WHERE routine_schema = 'sample_db'
UNION ALL
SELECT trigger_name AS object_name, 'TRIGGER' AS object_type
FROM information_schema.triggers
WHERE trigger_schema = 'sample_db'
UNION ALL
SELECT event_name AS object_name, 'EVENT' AS object_type
FROM information_schema.events
WHERE event_schema = 'sample_db';
+------------------+-------------+
| object_name | object_type |
+------------------+-------------+
| log_table | TABLE |
| logs | TABLE |
| logs_v1 | VIEW |
| insert_logs_bulk | PROCEDURE |
| trg_logs_insert | TRIGGER |
| e1 | EVENT |
+------------------+-------------+
6 rows in set (0.01 sec)
|
테이블 건수 확인
|
1
2
3
4
5
6
7
8
|
mysql> USE sample_db;
mysql> SELECT COUNT(*) FROM logs;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.02 sec)
|
정상적으로 이관됨
이관 후 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql>
SELECT
table_schema AS database_name,
ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE 1=1
#AND table_schema = 'sample_db'
GROUP BY table_schema
ORDER BY total_mb DESC;
+--------------------+---------+----------+----------+
| database_name | data_mb | index_mb | total_mb |
+--------------------+---------+----------+----------+
| sample_db | 5.53 | 0.00 | 5.53 |
| mysql | 2.28 | 0.33 | 2.61 |
| sys | 0.02 | 0.00 | 0.02 |
| information_schema | 0.00 | 0.00 | 0.00 |
| performance_schema | 0.00 | 0.00 | 0.00 |
+--------------------+---------+----------+----------+
5 rows in set (0.01 sec)
|
이관 후 db 케릭터셋 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql>
SELECT schema_name , default_character_set_name, default_collation_name
FROM information_schema.schemata;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| mysql | utf8mb4 | utf8mb4_0900_ai_ci |
| information_schema | utf8mb3 | utf8mb3_general_ci |
| performance_schema | utf8mb4 | utf8mb4_0900_ai_ci |
| sys | utf8mb4 | utf8mb4_0900_ai_ci |
| sample_db | utf8mb4 | utf8mb4_general_ci |
+--------------------+----------------------------+------------------------+
5 rows in set (0.00 sec)
|
결론 : 대상db의 용량이 큰 경우에는 mysqldump 보다 다른 방식이 낫다고 하지만 용량이 적당한 경우 mysqldump를 사용하면 간편하게 이관 가능함