실습> 아래 조건에 해당하는 권한을 부여하시오.
1. 접속 정보
Host User Passw0rd
localhost user1 1111112. GRANT 권한
naver_db.member 테이블에 no, id, name, age 를 볼 수 있는 권한을 부여한다.
naver_db.member 테이블에 INSERT 권한을 부여한다.
naver_db.member 테이블에 DELETE 권한을 부여한다.
3. DATA 확인
user1@localhost 사용자로 접속해서
INSERT INTO member(id, name, age, sex) VALUES('user1', '유저1', 30, 'M');
SELECT no, name, age FROM naver_db.member;
DELETE FROM member WHERE id='user1';
SELECT no, name, age FROM naver_db.member;4. REVOKE 권한
naver_db.member 테이블에 INSERT, DELETE 권한을 제거한다.
-- 답안 --
CREATE USER user1@localhost IDENTIFIED BY '111111';
GRANT SELECT(no,id,name,age),INSERT,DELETE ON naver_db.member TO user1@localhost;
SHOW GRANTS FOR user1@localhost\G
exit
# mysql -u user1 -p111111 naver_db
SELECT user();
INSERT INTO member(id, name, age, sex) VALUES('user1', '유저1', 30, 'M');
SELECT no, name, age FROM naver_db.member;
DELETE FROM member WHERE id='user1';
SELECT no, name, age FROM naver_db.member;
exit
# mysql
REVOKE INSERT,DELETE ON naver_db.member FROM user1@localhost;
SHOW GRANTS FOR user1@localhost\G
MariaDB [naver_db]> USE information_schema
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
MariaDB [information_schema]> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| CLIENT_STATISTICS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS | <-- 컬럼에 대한 정보가 여기에 저장되어 있다.
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| INDEX_STATISTICS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES | <-- 테이블에 대한 정보가 여기에 저장되어 있다.
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TABLE_STATISTICS |
| TRIGGERS |
| USER_PRIVILEGES |
| USER_STATISTICS |
| VIEWS |
| INNODB_CMPMEM_RESET |
| INNODB_RSEG |
| INNODB_UNDO_LOGS |
| INNODB_CMPMEM |
| INNODB_SYS_TABLESTATS |
| INNODB_LOCK_WAITS |
| INNODB_INDEX_STATS |
| INNODB_CMP |
| INNODB_CMP_RESET |
| INNODB_CHANGED_PAGES |
| INNODB_BUFFER_POOL_PAGES |
| INNODB_TRX |
| INNODB_BUFFER_POOL_PAGES_INDEX |
| INNODB_LOCKS |
| INNODB_BUFFER_POOL_PAGES_BLOB |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_STATS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_INDEXES |
| XTRADB_ADMIN_COMMAND |
| INNODB_TABLE_STATS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_BUFFER_PAGE |
+---------------------------------------+
62 rows in set (0.00 sec)
MariaDB [information_schema]> DESC COLUMNS;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
MariaDB [information_schema]> DESC TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
SYSTEM VIEW: 시스템에서 만들어진 뷰(테이블)
BASE TABLE : 사용자가 생성한 테이블
MariaDB [information_schema]> SELECT TABLE_NAME, TABLE_TYPE FROM TABLES;
+----------------------------------------------+-------------+
| TABLE_NAME | TABLE_TYPE |
+----------------------------------------------+-------------+
| CHARACTER_SETS | SYSTEM VIEW |
| CLIENT_STATISTICS | SYSTEM VIEW |
| COLLATIONS | SYSTEM VIEW |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW |
| COLUMNS | SYSTEM VIEW |
| COLUMN_PRIVILEGES | SYSTEM VIEW |
| ENGINES | SYSTEM VIEW |
| EVENTS | SYSTEM VIEW |
| FILES | SYSTEM VIEW |
| GLOBAL_STATUS | SYSTEM VIEW |
| GLOBAL_VARIABLES | SYSTEM VIEW |
| INDEX_STATISTICS | SYSTEM VIEW |
| KEY_CACHES | SYSTEM VIEW |
| KEY_COLUMN_USAGE | SYSTEM VIEW |
| PARAMETERS | SYSTEM VIEW |
| PARTITIONS | SYSTEM VIEW |
| PLUGINS | SYSTEM VIEW |
| PROCESSLIST | SYSTEM VIEW |
| PROFILING | SYSTEM VIEW |
| REFERENTIAL_CONSTRAINTS | SYSTEM VIEW |
| ROUTINES | SYSTEM VIEW |
| SCHEMATA | SYSTEM VIEW |
| SCHEMA_PRIVILEGES | SYSTEM VIEW |
| SESSION_STATUS | SYSTEM VIEW |
| SESSION_VARIABLES | SYSTEM VIEW |
| STATISTICS | SYSTEM VIEW |
| TABLES | SYSTEM VIEW |
| TABLESPACES | SYSTEM VIEW |
| TABLE_CONSTRAINTS | SYSTEM VIEW |
| TABLE_PRIVILEGES | SYSTEM VIEW |
| TABLE_STATISTICS | SYSTEM VIEW |
| TRIGGERS | SYSTEM VIEW |
| USER_PRIVILEGES | SYSTEM VIEW |
| USER_STATISTICS | SYSTEM VIEW |
| VIEWS | SYSTEM VIEW |
| INNODB_CMPMEM_RESET | SYSTEM VIEW |
| INNODB_RSEG | SYSTEM VIEW |
| INNODB_UNDO_LOGS | SYSTEM VIEW |
| INNODB_CMPMEM | SYSTEM VIEW |
| INNODB_SYS_TABLESTATS | SYSTEM VIEW |
| INNODB_LOCK_WAITS | SYSTEM VIEW |
| INNODB_INDEX_STATS | SYSTEM VIEW |
| INNODB_CMP | SYSTEM VIEW |
| INNODB_CMP_RESET | SYSTEM VIEW |
| INNODB_CHANGED_PAGES | SYSTEM VIEW |
| INNODB_BUFFER_POOL_PAGES | SYSTEM VIEW |
| INNODB_TRX | SYSTEM VIEW |
| INNODB_BUFFER_POOL_PAGES_INDEX | SYSTEM VIEW |
| INNODB_LOCKS | SYSTEM VIEW |
| INNODB_BUFFER_POOL_PAGES_BLOB | SYSTEM VIEW |
| INNODB_SYS_TABLES | SYSTEM VIEW |
| INNODB_SYS_FIELDS | SYSTEM VIEW |
| INNODB_SYS_COLUMNS | SYSTEM VIEW |
| INNODB_SYS_STATS | SYSTEM VIEW |
| INNODB_SYS_FOREIGN | SYSTEM VIEW |
| INNODB_SYS_INDEXES | SYSTEM VIEW |
| XTRADB_ADMIN_COMMAND | SYSTEM VIEW |
| INNODB_TABLE_STATS | SYSTEM VIEW |
| INNODB_SYS_FOREIGN_COLS | SYSTEM VIEW |
| INNODB_BUFFER_PAGE_LRU | SYSTEM VIEW |
| INNODB_BUFFER_POOL_STATS | SYSTEM VIEW |
| INNODB_BUFFER_PAGE | SYSTEM VIEW |
| test | BASE TABLE |
| freeboard | BASE TABLE |
| kh_mem | BASE TABLE |
| columns_priv | BASE TABLE |
| db | BASE TABLE |
| event | BASE TABLE |
| func | BASE TABLE |
| general_log | BASE TABLE |
| help_category | BASE TABLE |
| help_keyword | BASE TABLE |
| help_relation | BASE TABLE |
| help_topic | BASE TABLE |
| host | BASE TABLE |
| ndb_binlog_index | BASE TABLE |
| plugin | BASE TABLE |
| proc | BASE TABLE |
| procs_priv | BASE TABLE |
| proxies_priv | BASE TABLE |
| servers | BASE TABLE |
| slow_log | BASE TABLE |
| tables_priv | BASE TABLE |
| time_zone | BASE TABLE |
| time_zone_leap_second | BASE TABLE |
| time_zone_name | BASE TABLE |
| time_zone_transition | BASE TABLE |
| time_zone_transition_type | BASE TABLE |
| user | BASE TABLE | <-- mysql.user (MariaDB가 설치되면서 생성된 테이블)
| member | BASE TABLE | <-- 사용자가 생성한 테이블
| cond_instances | BASE TABLE |
| events_waits_current | BASE TABLE |
| events_waits_history | BASE TABLE |
| events_waits_history_long | BASE TABLE |
| events_waits_summary_by_instance | BASE TABLE |
| events_waits_summary_by_thread_by_event_name | BASE TABLE |
| events_waits_summary_global_by_event_name | BASE TABLE |
| file_instances | BASE TABLE |
| file_summary_by_event_name | BASE TABLE |
| file_summary_by_instance | BASE TABLE |
| mutex_instances | BASE TABLE |
| performance_timers | BASE TABLE |
| rwlock_instances | BASE TABLE |
| setup_consumers | BASE TABLE |
| setup_instruments | BASE TABLE |
| setup_timers | BASE TABLE |
| threads | BASE TABLE |
| zfTable | BASE TABLE |
| zipcode | BASE TABLE | <-- 사용자가 생성한 테이블
+----------------------------------------------+-------------+
109 rows in set (0.02 sec)
TABLE_SCHEMA: DB(DataBase)명
TABLE_NAME: 테이블명
COLUMN_NAME: 컬럼명
MariaDB [information_schema]> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM COLUMNS;
+--------------------+----------------------------------------------+----------------------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+--------------------+----------------------------------------------+----------------------------------+
| information_schema | CHARACTER_SETS | CHARACTER_SET_NAME |
| information_schema | CHARACTER_SETS | DEFAULT_COLLATE_NAME |
| information_schema | CHARACTER_SETS | DESCRIPTION |
| information_schema | CHARACTER_SETS | MAXLEN |
| information_schema | CLIENT_STATISTICS | CLIENT |
| information_schema | CLIENT_STATISTICS | TOTAL_CONNECTIONS |
| mysql | user | Host |
| mysql | user | User |
| mysql | user | Password |
| naver_db | member | no |
| naver_db | member | id |
| naver_db | member | name |
| naver_db | member | sex |
| naver_db | member | post_num |
| naver_db | member | address |
| naver_db | member | tel |
| naver_db | member | age |
| test | zipcode | san |
| test | zipcode | zibun1 |
| test | zipcode | zibunSerial |
| test | zipcode | zibun2 |
| test | zipcode | oldzipCode |
| test | zipcode | zipCodeSerial |
+--------------------+----------------------------------------------+----------------------------------+
1038 rows in set (0.01 sec)
MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.68-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8 <--
Db characterset: utf8 <--
Client characterset: utf8 <--
Conn. characterset: utf8 <--
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 days 13 hours 23 min 18 sec
Threads: 1 Questions: 119 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.000
--------------
MariaDB [(none)]> SHOW VARIABLES LIKE 'c%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
| completion_type | NO_CHAIN |
| concurrent_insert | AUTO |
| connect_timeout | 10 |
+--------------------------+----------------------------+
14 rows in set (0.00 sec)
DB와 테이블의 인코딩 형식을 확인하기 위해서는 SHOW를 이용해서 확인한다.
MariaDB [(none)]> SHOW CREATE DATABASE khacademy;
MariaDB [(none)]> SHOW CREATE TABLE naver_db.member;
MariaDB [(none)]> SHOW CREATE TABLE mysql.user;
MariaDB [(none)]> SELECT TABLE_NAME, TABLE_COLLATION, TABLE_TYPE FROM information_schema.TABLES;
MariaDB [(none)]> SELECT TABLE_NAME, TABLE_COLLATION, TABLE_TYPE FROM information_schema.TABLES;
+----------------------------------------------+-----------------+-------------+
| TABLE_NAME | TABLE_COLLATION | TABLE_TYPE |
+----------------------------------------------+-----------------+-------------+
:
:(생략)
| user | utf8_bin | BASE TABLE |
| member | utf8_general_ci | BASE TABLE |
| zipcode | utf8_general_ci | BASE TABLE |
+----------------------------------------------+-----------------+-------------+
109 rows in set (0.00 sec)
MySQL 문자셋과 콜레이션이란 무엇인가 ?
문자셋(Character set) :
각 문자가 컴퓨터에 저장될 때 어떠한 '코드'로 저장될지에 대한 규칙의 집합을 의미한다.
콜레이션(Collation):
특정 문자 셋에 의해 데이터베이스에 저장된 값들을 비교 검색하거나 정렬 등의 작업을 위해
문자들을 서로 '비교' 할때 사용하는 규칙들의 집합을 의미한다.
같은 문자셋이라도 콜레이션에 따라 영어의 경우 대소문자의 구분 비교 여부,
일본어의 경우 히라가나와 카타카나의 구분 방법 등이 달라진다.
UTF8 문자셋을 사용하는 경우 utf8-general-ci 또는 utf8-unicode-ci 둘중 하나를 collation으로
지정하는 경우가 많은데 utf8-general-ci는 utf8-unicode-ci 를 사용해서 정렬할 때 보다
다소 정확도가 떨어지는 경향이 있으나 속도는 빠르다.
MySQL OR MariaDB의 경우 latin1 이 기본 인코딩이고 설정에 의해서 utf8-general-ci 로 Default Collation을 변경할 수 있다.
실습> MariaDB의 기본 인코딩을 UTF8로 설정하기
# vi /etc/my.cnf
-- /etc/my.cnf --
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
collation-server=utf8_general_ci <--
character-set-server=utf8 <--
skip-character-set-client-handshake <--
#bind-address=127.0.0.1
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
-- /etc/my.cnf --
# systemctl restart mariadb
# mysql
MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.68-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 5 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.800
--------------
실습> UTF8 인코딩 확인하기
예전: euc-kr, euckr (실무에서는 이전 데이터를 저장한 것들은 euckr 형태로 사용중인 경우도 있다.)
요즘: utf8, utf-8, UTF8, UTF-8 (새롭게 만들면 utf8로 사용한다.)
MariaDB [(none)]> \s
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
MariaDB [(none)]> CREATE DATABASE entest;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW CREATE DATABASE entest;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| entest | CREATE DATABASE `entest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> USE entest
Database changed
MariaDB [entest]> CREATE TABLE entest_tb(no int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
MariaDB [entest]> SHOW CREATE TABLE entest_tb\G
*************************** 1. row ***************************
Table: entest_tb
Create Table: CREATE TABLE `entest_tb` (
`no` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [entest]> exit
물리적인 저장 디렉터리에 db.opt 파일이 존재하며 여기에 default-character-set 이 저장되어 있다.
# cat /var/lib/mysql/entest/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
DB를 생성할 때 언어셋을 변경할 수 있다.
DEFAULT CHARACTER SET을 생략하면 DBMS가 동작되는 DEFAULT CHARACTER SET 으로 생성된다.
MariaDB [(none)]> \s
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
형식: CREATE DATABASE <DB명> DEFAULT CHARACTER SET <언어셋>;
MariaDB [(none)]> CREATE DATABASE entest2 DEFAULT CHARACTER SET ASCII;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW CREATE DATABASE entest2;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| entest2 | CREATE DATABASE `entest2` /*!40100 DEFAULT CHARACTER SET ascii */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [entest2]> CREATE TABLE entest2_tbl(no int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
MariaDB [entest2]> SHOW CREATE TABLE entest2_tbl\G
*************************** 1. row ***************************
Table: entest2_tbl
Create Table: CREATE TABLE `entest2_tbl` (
`no` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ascii
1 row in set (0.00 sec)
MariaDB [entest2]> exit
# cat /var/lib/mysql/entest2/db.opt
default-character-set=ascii
default-collation=ascii_general_ci
o DEFAULT CHARACTER SET 변경하기
형식: ALTER DATABASE entest2 DEFAULT CHARACTER SET <문자인코딩>;
ascii -> utf8로 변경한다.
MariaDB [(none)]> ALTER DATABASE entest2 DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW CREATE DATABASE entest2;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| entest2 | CREATE DATABASE `entest2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
# cat /var/lib/mysql/entest2/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
# mysql
entest2 DB가 DEFAULT CHARACTER SET utf8 이므로 그 DB 안에서 테이블을 생성하면 기본 인코딩은 utf8로 설정된다.
MariaDB [(none)]> USE entest2
Database changed
MariaDB [entest2]> SHOW CREATE DATABASE entest2;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| entest2 | CREATE DATABASE `entest2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [entest2]> CREATE TABLE entest2_tb3(no int);
Query OK, 0 rows affected (0.00 sec)
DEFAULT CHARSET을 설정하지 않았기 때문에 utf8로 설정된다.
MariaDB [entest2]> SHOW CREATE TABLE entest2_tb3\G
*************************** 1. row ***************************
Table: entest2_tb3
Create Table: CREATE TABLE `entest2_tb3` (
`no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 <-- <-- entest2_tb3 DB의 CHARSET을 그대로 물려 받았다.
1 row in set (0.00 sec)
생성된 entest2_tb3 테이블을 삭제한다.
MariaDB [entest2]> DROP TABLE entest2_tb3;
Query OK, 0 rows affected (0.00 sec)
DEFAULT CHARSET을 euckr로 변경하고 테이블을 생성한다.
MariaDB [entest2]> CREATE TABLE entest2_tb3(no int) DEFAULT CHARACTER SET euckr;
Query OK, 0 rows affected (0.00 sec)
MariaDB [entest2]> SHOW CREATE TABLE entest2_tb3\G
*************************** 1. row ***************************
Table: entest2_tb3
Create Table: CREATE TABLE `entest2_tb3` (
`no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=euckr <-- DEFAULT CHARSET이 utf8이 아닌 euckr로 변경된다.
1 row in set (0.00 sec)
실습> Heidi SQL로 원격 접속하기
MariaDB [mysql]> SELECT host,user,password FROm user;
+---------------+--------+-------------------------------------------+
| host | user | password |
+---------------+--------+-------------------------------------------+
| localhost | root | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| 200.200.200.1 | root | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| localhost | kuser1 | |
| localhost | kuser2 | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| 200.200.200.% | root | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| localhost | user1 | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+---------------+--------+-------------------------------------------+
6 rows in set (0.00 sec)MariaDB [mysql]> GRANT ALL PRIVILEGES ON . TO root@'200.200.200.1' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS FOR root@200.200.200.1\G
*************************** 1. row ***************************
Grants for root@200.200.200.1: GRANT ALL PRIVILEGES ON *.* TO 'root'@'200.200.200.1' IDENTIFIED BY PASSWORD '*8232A1298A49F710DBEE0B330C42EEC825D4190A' WITH GRANT OPTION
1 row in set (0.00 sec)스토리지 엔진
MariaDB [entest2]> SHOW CREATE TABLE mysql.user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) NOT NULL DEFAULT '0',
`plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
`authentication_string` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)MariaDB [entest2]>
MariaDB [entest2]> SHOW CREATE TABLE naver_db.member\G
*************************** 1. row ***************************
Table: member
Create Table: CREATE TABLE `member` (
`no` int(11) NOT NULL AUTO_INCREMENT COMMENT '번호',
`id` varchar(10) NOT NULL COMMENT '아이디',
`name` varchar(20) NOT NULL COMMENT '이름',
`sex` char(1) DEFAULT NULL COMMENT '성별',
`post_num` char(8) DEFAULT NULL COMMENT '우편번호',
`address` varchar(80) DEFAULT NULL COMMENT '주소',
`tel` varchar(15) DEFAULT NULL COMMENT '전화번호',
`age` int(11) DEFAULT NULL COMMENT '나이',
PRIMARY KEY (`no`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)##################
📌 스토리지 엔진 📌
##################
MySQL 5.5 부터 MyISAM -> InnoDB로 변경되었다.
MariaDB를 설치하면 특별히 설정하지 않는다면 기본 스토리지 엔진이 InnoDB로 설정된다.
실습> DB안에 테이블이 스토리지 엔진 확인하기
MariaDB [entest2]> SHOW TABLE STATUS\G
MariaDB [entest2]> exit
실습> 테이블의 스토리지 엔진 확인하기
information_schema: 가상 테이블로 데이터 사전으로 많은 정보가 저장되어 있다.
웹해킹을 할 때 이쪽을 공략을 한다. (?)
# mysql information_schema
MariaDB [information_schema]> DESC TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | | <-- DB명
| TABLE_NAME | varchar(64) | NO | | | | <-- 테이블명
| TABLE_TYPE | varchar(64) | NO | | | | <-- 시스템 테이블, 사용자 테이블인지 확인
| ENGINE | varchar(64) | YES | | NULL | | <-- 스토리지 엔진
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES WHERE TABLE_SCHEMA='naver_db';
MariaDB [information_schema]> SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_SCHEMA FROM TABLES WHERE TABLE_SCHEMA='naver_db';
+--------------+------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+--------------+------------+------------+--------+
| naver_db | member | BASE TABLE | InnoDB |
+--------------+------------+------------+--------+
1 row in set (0.00 sec)
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES WHERE TABLE_SCHEMA='mysql';
MariaDB [information_schema]> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES WHERE TABLE_SCHEMA='mysql';
+--------------+---------------------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+--------------+---------------------------+------------+--------+
| mysql | columns_priv | BASE TABLE | MyISAM |
| mysql | db | BASE TABLE | MyISAM |
| mysql | event | BASE TABLE | MyISAM |
| mysql | func | BASE TABLE | MyISAM |
| mysql | general_log | BASE TABLE | CSV |
| mysql | help_category | BASE TABLE | MyISAM |
| mysql | help_keyword | BASE TABLE | MyISAM |
| mysql | help_relation | BASE TABLE | MyISAM |
| mysql | help_topic | BASE TABLE | MyISAM |
| mysql | host | BASE TABLE | MyISAM |
| mysql | ndb_binlog_index | BASE TABLE | MyISAM |
| mysql | plugin | BASE TABLE | MyISAM |
| mysql | proc | BASE TABLE | MyISAM |
| mysql | procs_priv | BASE TABLE | MyISAM |
| mysql | proxies_priv | BASE TABLE | MyISAM |
| mysql | servers | BASE TABLE | MyISAM |
| mysql | slow_log | BASE TABLE | CSV |
| mysql | tables_priv | BASE TABLE | MyISAM |
| mysql | time_zone | BASE TABLE | MyISAM |
| mysql | time_zone_leap_second | BASE TABLE | MyISAM |
| mysql | time_zone_name | BASE TABLE | MyISAM |
| mysql | time_zone_transition | BASE TABLE | MyISAM |
| mysql | time_zone_transition_type | BASE TABLE | MyISAM |
| mysql | user | BASE TABLE | MyISAM |
+--------------+---------------------------+------------+--------+
24 rows in set (0.00 sec)
DBMS(MariaDB)에서 지원하는 스토리지 엔진의 종류를 확인한다.
MariaDB [information_schema]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)실습> 스토리지 엔진 변경하기
1. 스토리지 엔진 확인하기
현재 설정된 스토리지 엔진을 확인한다.
MariaDB [information_schema]> SHOW VARIABLES LIKE '%ENGINE%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | OFF |
| storage_engine | InnoDB |
+---------------------------+--------+
3 rows in set (0.00 sec)2. 임시적 설정으로 스토리지 엔진 변경하기
임시적 설정을 하면 DBMS를 재시작하면 다시 원래값으로 돌아간다.
!!! 실무에 나가서 DBMS를 사용하게 되면 반드시 스토리지 엔진과 문자 인코딩을 먼저 확인하고 사용하자 !!!
스토리지 엔진을 InnoDB -> MyISAM으로 변경한다.
MariaDB [information_schema]> SET GLOBAL storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
DBMS를 접속을 종료하기 다시 접속한다.
MariaDB [information_schema]> exit
# mysql
MariaDB [(none)]> SHOW VARIABLES LIKE '%ENGINE%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | MyISAM | <-- 변경된 스토리지 엔진
| engine_condition_pushdown | OFF |
| storage_engine | MyISAM | <-- 변경된 스토리지 엔진
+---------------------------+--------+
3 rows in set (0.00 sec)
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
MariaDB [(none)]> exit
임시적 설정은 DBMS를 재시작하면 InnoDB로 다시 변경된다.
# systemctl restart mariadb
# mysql
MariaDB [(none)]> SHOW VARIABLES LIKE '%ENGINE%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | OFF |
| storage_engine | InnoDB |
+---------------------------+--------+
3 rows in set (0.01 sec)
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
MariaDB [(none)]> exit
3. 영구적 설정으로 스토리지 엔진 변경하기
서버가 리부팅하거나 MariaDB 서버를 재시작해도 영구적으로 적용된다.
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
collation-server=utf8_general_ci
character-set-server=utf8
skip-character-set-client-handshake
#bind-address=127.0.0.1
default_storage_engine=MyISAM
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
DBMS를 재시작하고 스토리지 엔진을 확인한다.
# systemctl restart mariadb
# mysql
MariaDB [(none)]> SHOW VARIABLES LIKE '%ENGINE%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | MyISAM |
| engine_condition_pushdown | OFF |
| storage_engine | MyISAM |
+---------------------------+--------+
3 rows in set (0.00 sec)
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
MariaDB [(none)]> USE naver_db
Database changed
MariaDB [naver_db]> CREATE TABLE t1(no int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [naver_db]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`no` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [naver_db]> exitMyISAM 테이블
- .frm, .MYI, .MYD 이렇게 3개의 파일로 구성되어 있다.
.frm: 테이블의 구조가 저장되어 있다.
.MYI: Index 정보가 저장되어 있다.
.MYD: 실제 데이터가 저장되어 있다.
# ll /var/lib/mysql/naver_db/
합계 32
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm
-rw-rw----. 1 mysql mysql 0 2월 7 12:12 t1.MYD
-rw-rw----. 1 mysql mysql 1024 2월 7 12:12 t1.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:12 t1.frm
# ll /var/lib/mysql/naver_db/
합계 32
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm
-rw-rw----. 1 mysql mysql 0 2월 7 12:12 t1.MYD <-- 실제 DATA가 하나도 없기 때문에 0byte의 크기를 갖는다.
-rw-rw----. 1 mysql mysql 1024 2월 7 12:12 t1.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:12 t1.frm
# mysql -e "INSERT INTO naver_db.t1 VALUES(1)"
# mysql -e "SELECT * FROM naver_db.t1"
+------+
| no |
+------+
| 1 |
+------+
# ll /var/lib/mysql/naver_db/
합계 36
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm
-rw-rw----. 1 mysql mysql 7 2월 7 12:25 t1.MYD <-- 실제 DATA가 저장되었기 때문에 크기가 7로 변경되었다.
-rw-rw----. 1 mysql mysql 1024 2월 7 12:25 t1.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:12 t1.frm
# file /var/lib/mysql/naver_db/t1.MYD
/var/lib/mysql/naver_db/t1.MYD: data
실습> 테이블의 엔진 변경하기
형식: ALTER TABLE <테이블명> ENGINE=엔진명;
1. 테이블 생성
# mysql naver_db
MariaDB [naver_db]> SHOW VARIABLES LIKE '%ENGINE%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | MyISAM |
| engine_condition_pushdown | OFF |
| storage_engine | MyISAM |
+---------------------------+--------+
3 rows in set (0.00 sec)default_storage_engine이 MyISAM일 때 InnoDB 테이블 형식으로 생성하고자 한다면 아래처럼 ENGINE=InnoDB를 사용해야 한다.
MariaDB [naver_db]> CREATE TABLE t2(no int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)2. 스토리지 엔진 확인
t2 테이블에서 확인한다.
MariaDB [naver_db]> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)t1 테이블에서 확인한다.
MariaDB [naver_db]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`no` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)information_schema DB(데이터사전) 에서 naver_db 에 있는 모든 테이블들을 확인한다.
naver_db에 있는 테이블의 스토리지 엔진을 확인한다.
MariaDB [naver_db]> SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA='naver_db';
+------------+------------+--------+--------------+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_SCHEMA |
+------------+------------+--------+--------------+
| member | BASE TABLE | InnoDB | naver_db |
| t1 | BASE TABLE | MyISAM | naver_db | <-- MyISAM -> InnoDB로 변경할 예정임.
| t2 | BASE TABLE | InnoDB | naver_db |
+------------+------------+--------+--------------+
3 rows in set (0.01 sec)3. 스토리지 엔진 변경
t1 테이블의 스토리지 엔진을 MyISAM -> InnoDB로 변경한다.
MariaDB [naver_db]> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
information_schema DB(데이터사전) 에서 naver_db 에 있는 모든 테이블들을 확인하면 변경된 내용을 볼 수 있다.
MariaDB [naver_db]> SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA='naver_db';
+------------+------------+--------+--------------+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_SCHEMA |
+------------+------------+--------+--------------+
| member | BASE TABLE | InnoDB | naver_db |
| t1 | BASE TABLE | InnoDB | naver_db |
| t2 | BASE TABLE | InnoDB | naver_db |
+------------+------------+--------+--------------+
3 rows in set (0.00 sec)
MariaDB [naver_db]> exit4. 파일시스템에서 확인
MyISAM에서 InnoDB로 변경이 되었기 때문에 파일이 하나로 변경되었다.
InnoDB는 파일이 하나로 되어 있다.
# ll /var/lib/mysql/naver_db/
합계 40
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm
-rw-rw----. 1 mysql mysql 8556 2월 7 12:38 t1.frm
-rw-rw----. 1 mysql mysql 8556 2월 7 12:30 t2.frm
# ll /var/lib/mysql/ibdata1
-rw-rw----. 1 mysql mysql 18874368 2월 7 12:38 /var/lib/mysql/ibdata1실습> 테이블의 스토리지 엔진 변경하기
# cat .my.cnf
[client]
host = localhost
user = root
password = P@ssw0rd
# mysql -e "SHOW TABLES FROM naver_db"
+--------------------+
| Tables_in_naver_db |
+--------------------+
| member |
| t1 |
| t2 |
+--------------------+
# ll /var/lib/mysql/naver_db/
합계 40
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm <-- InnoDB
-rw-rw----. 1 mysql mysql 8556 2월 7 12:38 t1.frm <-- InnoDB
-rw-rw----. 1 mysql mysql 8556 2월 7 12:30 t2.frm <-- InnoDB
naver_db.t1 InnoDB -> MyISAM 테이블로 변경하고 MariaDB의 data 디렉터리를 확인한다.
# mysql -e "ALTER TABLE naver_db.t1 ENGINE=MyISAM"
# ll /var/lib/mysql/naver_db/
합계 48
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm <-- InnoDB
-rw-rw----. 1 mysql mysql 7 2월 7 12:48 t1.MYD <-- MyISAM
-rw-rw----. 1 mysql mysql 1024 2월 7 12:48 t1.MYI <-- MyISAM
-rw-rw----. 1 mysql mysql 8556 2월 7 12:48 t1.frm <-- MyISAM
-rw-rw----. 1 mysql mysql 8556 2월 7 12:30 t2.frm <-- InnoDB
naver_db.t2 InnoDB -> MyISAM 테이블로 변경하고 MariaDB의 data 디렉터리를 확인한다.
# mysql -e "ALTER TABLE naver_db.t2 ENGINE=MyISAM"
# ll /var/lib/mysql/naver_db/
합계 52
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm <-- InnoDB
-rw-rw----. 1 mysql mysql 7 2월 7 12:48 t1.MYD <-- MyISAM
-rw-rw----. 1 mysql mysql 1024 2월 7 12:48 t1.MYI <-- MyISAM
-rw-rw----. 1 mysql mysql 8556 2월 7 12:48 t1.frm <-- MyISAM
-rw-rw----. 1 mysql mysql 0 2월 7 12:49 t2.MYD <-- MyISAM
-rw-rw----. 1 mysql mysql 1024 2월 7 12:49 t2.MYI <-- MyISAM
-rw-rw----. 1 mysql mysql 8556 2월 7 12:49 t2.frm <-- MyISAM
실습> InnoDB 테이블
# head /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql <-- <MariaDB의 데이터 디렉터리>MyISAM 테이블
-.frm, .MYI, .MYD 이렇게 3개의 파일로 구성되어 있다.
3개 파일 모두 파일의 위치: <MariaDB의 데이터 디렉터리>/DB명/ (e.g. /var/lib/mysql/DB명/)
.frm: 테이블의 구조가 저장되어 있다.
.MYI: Index 정보가 저장되어 있다.
.MYD: 실제 데이터가 저장되어 있다.
**InnoDB 테이블
-.frm, ibdata1 이렇게 2개의 파일로 구성되어 있다.
.frm: 테이블의 구조가 저장되어 있다.
-파일의 위치: <MariaDB의 데이터 디렉터리>/DB명/ (e.g. /var/lib/mysql/DB명/)
ibdata1: Index 정보, 실제 데이터가 저장되어 있다.
-파일의 위치: <MariaDB의 데이터 디렉터리>/ (e.g. /var/lib/mysql/)**
1. InnoDB 테이블 확인
# cd /var/lib/mysql/ibdata1: InnoDB 스토리지 엔진의 테이블에 대한 데이터가 저장되는 파일 (테이블스페이스)
# ll
합계 28700
-rw-rw----. 1 mysql mysql 16384 2월 7 14:06 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 2월 7 14:06 aria_log_control
drwx------. 2 mysql mysql 69 2월 7 10:29 entest
drwx------. 2 mysql mysql 66 2월 7 11:10 entest2
-rw-rw----. 1 mysql mysql 5242880 2월 7 14:06 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 2월 2 11:37 ib_logfile1
-rw-rw----. 1 mysql mysql 18874368 2월 7 14:06 ibdata1 <-- InnoDB 스토리지 엔진의 테이블에 대한 데이터가 저장되는 파일
drwx------. 2 mysql mysql 36 2월 3 12:11 kh_db
drwx------. 2 mysql mysql 59 2월 3 14:25 khacademy
drwx------. 2 mysql mysql 4096 2월 2 11:37 mysql
drwx------. 2 mysql mysql 122 2월 7 12:49 naver_db
drwx------. 2 mysql mysql 4096 2월 2 11:37 performance_schema
drwx------. 2 mysql mysql 96 2월 4 04:19 test
# mysql -e "SELECT * FROM naver_db.member"
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| no | id | name | sex | post_num | address | tel | age |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| 1 | yjhwang | 황영주 | M | 100-011 | 서울시 중구 충무로1가 | 234-8879 | 35 |
| 2 | khshul | 설기형 | M | 607-010 | 부산시 동래구 명륜동 | 764-3784 | 33 |
| 3 | chpark | 박철호 | M | 503-200 | 광주시 남구 지석동 | 298-9730 | 34 |
| 4 | shlee | 이상훈 | M | 503-200 | 광주시 남구 도금동 | 838-4347 | 32 |
| 5 | jyjang | 장영숙 | W | 503-201 | 부산시 영도구 봉래동5가 | 399-9809 | 24 |
| 6 | yjbae | 배용진 | M | 606-065 | 서울시 은평구 응암4동 | 857-5683 | 30 |
| 7 | hbpark | 박혜빈 | W | 122-014 | 경기도 과천시 중앙동 | 234-7677 | 22 |
| 8 | mskim | 김문수 | M | 427-760 | 경기도 시흥시 신천동 | 370-6003 | 63 |
| 9 | bkcha | 차범길 | M | 429-020 | 대전시 서구 둔산1동 | 432-9877 | 49 |
| 10 | kskim | 김길수 | M | 302-121 | 경기도 수원시 장안구 파장동 | 324-5875 | 54 |
| 11 | srkim | 김수련 | M | 440-747 | 대구시 달서구 신당동 | 987-3688 | 23 |
| 12 | srlee | 이성현 | M | 704-701 | 경기도 수원시 권선구 매산로1가 | 243-6844 | 36 |
| 13 | hnjang | 정한나 | W | 441-081 | 광주시 서구 화정4동 | 845-4547 | 58 |
| 14 | mylee | 이명연 | W | 502-791 | 광주시 서구 쌍촌동 | 837-9432 | 33 |
| 15 | yskim | 김영숙 | W | 429-010 | 경기도 시흥시 대야동 | 374-8438 | 53 |
| 16 | khuser | 케이 | M | 111-222 | 서울시 강남구 역삼동 | 02-1111-2222 | 17 |
| 17 | kuser1 | 케이유저 | W | NULL | NULL | NULL | 20 |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
2. InnoDB 테이블스페이스 이동
DBMS를 중지하고 InnoDB 테이블스페이스 파일과 로그파일을 홈디렉터리 /root로 이동시킨다.
# systemctl stop mariadb
# mv ibdata1 ~
# mv ib_logfile* ~
# ll
합계 28
-rw-rw----. 1 mysql mysql 16384 2월 7 14:06 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 2월 7 14:06 aria_log_control
drwx------. 2 mysql mysql 69 2월 7 10:29 entest
drwx------. 2 mysql mysql 66 2월 7 11:10 entest2
drwx------. 2 mysql mysql 36 2월 3 12:11 kh_db
drwx------. 2 mysql mysql 59 2월 3 14:25 khacademy
drwx------. 2 mysql mysql 4096 2월 2 11:37 mysql
drwx------. 2 mysql mysql 122 2월 7 12:49 naver_db
drwx------. 2 mysql mysql 4096 2월 2 11:37 performance_schema
drwx------. 2 mysql mysql 96 2월 4 04:19 test
DBMS를 시작한다.
# systemctl start mariadb
# ll
합계 37852
-rw-rw----. 1 mysql mysql 16384 2월 7 14:06 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 2월 7 14:06 aria_log_control
drwx------. 2 mysql mysql 69 2월 7 10:29 entest
drwx------. 2 mysql mysql 66 2월 7 11:10 entest2
-rw-rw----. 1 mysql mysql 5242880 2월 7 14:11 ib_logfile0 <-- 새롭게 생성되었다.
-rw-rw----. 1 mysql mysql 5242880 2월 7 14:11 ib_logfile1 <-- 새롭게 생성되었다.
-rw-rw----. 1 mysql mysql 18874368 2월 7 14:11 ibdata1 <-- 새롭게 생성되었다.
drwx------. 2 mysql mysql 36 2월 3 12:11 kh_db
drwx------. 2 mysql mysql 59 2월 3 14:25 khacademy
drwx------. 2 mysql mysql 4096 2월 2 11:37 mysql
srwxrwxrwx. 1 mysql mysql 0 2월 7 14:11 mysql.sock
drwx------. 2 mysql mysql 122 2월 7 12:49 naver_db
drwx------. 2 mysql mysql 4096 2월 2 11:37 performance_schema
drwx------. 2 mysql mysql 96 2월 4 04:19 test3. DATA 확인
ibdata1: /var/lib/mysql 에 저장되어 있다.
데이터가 저장된 ibdata1 파일이 /root로 이동되었고 DBMS를 새롭게 시작했기 때문에 ibdata1에는 데이터가 존재하지 않기 때문에
아래처럼 에러가 발생된다.
# mysql -e "SELECT * FROM naver_db.member"
ERROR 1146 (42S02) at line 1: Table 'naver_db.member' doesn't exist
# mysql naver_db
MariaDB [naver_db]> SHOW TABLES;
+--------------------+
| Tables_in_naver_db |
+--------------------+
| member | <--
| t1 |
| t2 |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [naver_db]> exit
# ll /var/lib/mysql/naver_db/
합계 52
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 3 16:26 member.frm <-- InnoDB 테이블 구조만 저장되어 있다.
-rw-rw----. 1 mysql mysql 7 2월 7 12:48 t1.MYD
-rw-rw----. 1 mysql mysql 1024 2월 7 12:48 t1.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:48 t1.frm
-rw-rw----. 1 mysql mysql 0 2월 7 12:49 t2.MYD
-rw-rw----. 1 mysql mysql 1024 2월 7 12:49 t2.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:49 t2.frm
DATA가 저장된 ibdata1 파일이 새롭게 만들어졌기 때문에 member 테이블의 데이터가 없기 때문에 아래처럼 에러가 발생된다.
# mysql naver_db
MariaDB [naver_db]> SELECT * FROM member;
ERROR 1146 (42S02): Table 'naver_db.member' doesn't exist
MariaDB [naver_db]> exit
4. 복구
DBMS를 중지하고 테스트하기 위해서 이동시켰던 파일들을 모두 다시 옯긴다.
# pwd
/var/lib/mysql
# systemctl stop mariadb
# mv ~/ib_logfile* ~/ib
ib_logfile0 ib_logfile1 ibdata1
# mv ~/ib_logfile* ~/ibdata1 .
mv: overwrite `./ib_logfile0'? y
mv: overwrite `./ib_logfile1'? y
mv: overwrite `./ibdata1'? y파일 이동이 완료되면 DBMS를 다시 시작한다.
# systemctl start mariadbSELECT 쿼리를 이용하면 데이터가 출력된다.
# mysql -e "SELECT * FROM naver_db.member"
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| no | id | name | sex | post_num | address | tel | age |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| 1 | yjhwang | 황영주 | M | 100-011 | 서울시 중구 충무로1가 | 234-8879 | 35 |
| 2 | khshul | 설기형 | M | 607-010 | 부산시 동래구 명륜동 | 764-3784 | 33 |
| 3 | chpark | 박철호 | M | 503-200 | 광주시 남구 지석동 | 298-9730 | 34 |
| 4 | shlee | 이상훈 | M | 503-200 | 광주시 남구 도금동 | 838-4347 | 32 |
| 5 | jyjang | 장영숙 | W | 503-201 | 부산시 영도구 봉래동5가 | 399-9809 | 24 |
| 6 | yjbae | 배용진 | M | 606-065 | 서울시 은평구 응암4동 | 857-5683 | 30 |
| 7 | hbpark | 박혜빈 | W | 122-014 | 경기도 과천시 중앙동 | 234-7677 | 22 |
| 8 | mskim | 김문수 | M | 427-760 | 경기도 시흥시 신천동 | 370-6003 | 63 |
| 9 | bkcha | 차범길 | M | 429-020 | 대전시 서구 둔산1동 | 432-9877 | 49 |
| 10 | kskim | 김길수 | M | 302-121 | 경기도 수원시 장안구 파장동 | 324-5875 | 54 |
| 11 | srkim | 김수련 | M | 440-747 | 대구시 달서구 신당동 | 987-3688 | 23 |
| 12 | srlee | 이성현 | M | 704-701 | 경기도 수원시 권선구 매산로1가 | 243-6844 | 36 |
| 13 | hnjang | 정한나 | W | 441-081 | 광주시 서구 화정4동 | 845-4547 | 58 |
| 14 | mylee | 이명연 | W | 502-791 | 광주시 서구 쌍촌동 | 837-9432 | 33 |
| 15 | yskim | 김영숙 | W | 429-010 | 경기도 시흥시 대야동 | 374-8438 | 53 |
| 16 | khuser | 케이 | M | 111-222 | 서울시 강남구 역삼동 | 02-1111-2222 | 17 |
| 17 | kuser1 | 케이유저 | W | NULL | NULL | NULL | 20 |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+실습> InnoDB 테이블 테스트 2
mysqldump: 백업 명령어
백업 형식:
mysqldump DB명 TB명 > 백업파일.sql (e.g. mysqldump naver_db member > member.sql)
복구 형식:
mysql DB명 < 백업파일.sql
# head /etc/my.cnf
[mysqld]
datadir=/var/lib/mysqlinnodb_file_per_table=OFF (Default)
-datadir변수/ibdata1 시스템 파일에 데이터가 저장된다.
innodb_file_per_table=ON
-datadir변수/DB명/테이블명.ibd 테이블 파일에 데이터가 저장된다.
innodb_file_per_table 변수를 설정하는 형식 :
-임시적 형식: SET GLOBAL innodb_file_per_table=ON
-영구적 형식: /etc/my.cnf 에 innodb_file_per_table=ON 으로 설정한다.
1. member 테이블 백업
member 테이블을 mysqldump 명령어로 $HOME/member.sql 파일로 백업하고 naver_db.member 테이블을 삭제한다.
# mysql naver_dbinformation_schema DB(데이터사전) 에서 naver_db 에 있는 모든 테이블들을 확인한다.
naver_db에 있는 테이블의 스토리지 엔진을 확인한다.
MariaDB [naver_db]> SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA='naver_db';
+------------+------------+--------+--------------+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_SCHEMA |
+------------+------------+--------+--------------+
| member | BASE TABLE | InnoDB | naver_db | <-- 스토리지 엔진은 InnoDB로 되어있다.
| t1 | BASE TABLE | MyISAM | naver_db |
| t2 | BASE TABLE | InnoDB | naver_db |
+------------+------------+--------+--------------+
3 rows in set (0.01 sec)
MariaDB [naver_db]> exit# cd
# mysqldump naver_db member > member.sql
# cat member.sql
-- MySQL dump 10.14 Distrib 5.5.68-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: naver_db
-- ------------------------------------------------------
-- Server version 5.5.68-MariaDB
/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 `member`
--
DROP TABLE IF EXISTS `member`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `member` (
`no` int(11) NOT NULL AUTO_INCREMENT COMMENT '번호',
`id` varchar(10) NOT NULL COMMENT '아이디',
`name` varchar(20) NOT NULL COMMENT '이름',
`sex` char(1) DEFAULT NULL COMMENT '성별',
`post_num` char(8) DEFAULT NULL COMMENT '우편번호',
`address` varchar(80) DEFAULT NULL COMMENT '주소',
`tel` varchar(15) DEFAULT NULL COMMENT '전화번호',
`age` int(11) DEFAULT NULL COMMENT '나이',
PRIMARY KEY (`no`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `member`
--
LOCK TABLES `member` WRITE;
/*!40000 ALTER TABLE `member` DISABLE KEYS */;
INSERT INTO `member` VALUES (1,'yjhwang','황영주','M','100-011','서울시 중구 충무로1가','234-8879',35),(2,'khshul','설기형','M','607-010','부산시 동래구 명륜동','764-3784',33),(3,'chpark','박철호','M','503-200','광주시 남구 지석동','298-9730',34),(4,'shlee','이상훈','M','503-200','광주시 남구 도금동','838-4347',32),(5,'jyjang','장영숙','W','503-201','부산시 영도구 봉래동5가','399-9809',24),(6,'yjbae','배용진','M','606-065','서울시 은평구 응암4동','857-5683',30),(7,'hbpark','박혜빈','W','122-014','경기도 과천시 중앙동','234-7677',22),(8,'mskim','김문수','M','427-760','경기도 시흥시 신천동','370-6003',63),(9,'bkcha','차범길','M','429-020','대전시 서구 둔산1동','432-9877',49),(10,'kskim','김길수','M','302-121','경기도 수원시 장안구 파장동','324-5875',54),(11,'srkim','김수련','M','440-747','대구시 달서구 신당동','987-3688',23),(12,'srlee','이성현','M','704-701','경기도 수원시 권선구 매산로1가','243-6844',36),(13,'hnjang','정한나','W','441-081','광주시 서구 화정4동','845-4547',58),(14,'mylee','이명연','W','502-791','광주시 서구 쌍촌동','837-9432',33),(15,'yskim','김영숙','W','429-010','경기도 시흥시 대야동','374-8438',53),(16,'khuser','케이','M','111-222','서울시 강남구 역삼동','02-1111-2222',17),(17,'kuser1','케이유저','W',NULL,NULL,NULL,20);
/*!40000 ALTER TABLE `member` 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 2023-02-07 14:35:00
백업을 받은 후 naver_db.member 테이블을 삭제한다.
# mysql -e "DROP TABLE naver_db.member"
# mysql -e "SHOW TABLES FROM naver_db"
+--------------------+
| Tables_in_naver_db |
+--------------------+
| t1 |
| t2 |
| test1 |
+--------------------+
# mysql naver_db
MariaDB [naver_db]> SHOW TABLES;
+--------------------+
| Tables_in_naver_db |
+--------------------+
| t1 |
| t2 |
| test1 |
+--------------------+
3 rows in set (0.01 sec)
MariaDB [naver_db]> SHOW VARIABLES LIKE 'innodb%'
+-------------------------------------------+------------------------+
| Variable_name | Value |
+-------------------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_method | estimate |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_partitions | 1 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_blocking_buffer_pool_restore | OFF |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_restore_at_startup | 0 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_checkpoint_age_target | 0 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_corrupt_table_action | assert |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_dict_size_limit | 0 |
| innodb_doublewrite | ON |
| innodb_doublewrite_file | |
| innodb_fake_changes | OFF |
| innodb_fast_checksum | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbor_pages | area |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_ibuf_active_contract | 1 |
| innodb_ibuf_max_size | 67092480 |
| innodb_import_table_from_xtrabackup | 0 |
| innodb_io_capacity | 200 |
| innodb_kill_idle_transaction | 0 |
| innodb_large_prefix | OFF |
| innodb_lazy_drop_table | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | ON |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_bitmap_file_size | 104857600 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_merge_sort_block_size | 1048576 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead | linear |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_recovery_stats | OFF |
| innodb_recovery_update_relay_log | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_simulate_comp_failures | 0 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_update | 1 |
| innodb_stats_method | nulls_equal |
| innodb_stats_modified_counter | 0 |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_stats_traditional | ON |
| innodb_stats_update_need_lock | 1 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_thread_sleep_delay | 10000 |
| innodb_track_changed_pages | OFF |
| innodb_use_atomic_writes | OFF |
| innodb_use_fallocate | OFF |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_stacktrace | OFF |
| innodb_use_sys_malloc | ON |
| innodb_use_sys_stats_table | OFF |
| innodb_version | 5.5.61-MariaDB-38.13 |
| innodb_write_io_threads | 4 |
+-------------------------------------------+------------------------+
103 rows in set (0.00 sec)
MariaDB [naver_db]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)2. 임시적 설정
데이터가 저장되는 공간을 각 테이블명의 이름으로 생성하기 위해서 innodb_file_per_table=ON 으로 설정한다.
MariaDB [naver_db]> SET GLOBAL innodb_file_per_table=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [naver_db]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
MariaDB [naver_db]> exit백업받은 member.sql 파일을 naver_db 에 복구한다.
# mysql naver_db < member.sql
# mysql -e "SHOW TABLES FROM naver_db"
+--------------------+
| Tables_in_naver_db |
+--------------------+
| member |
| t1 |
| t2 |
| test1 |
+--------------------+
# mysql -e "SELECT * FROM naver_db.member"
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| no | id | name | sex | post_num | address | tel | age |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| 1 | yjhwang | 황영주 | M | 100-011 | 서울시 중구 충무로1가 | 234-8879 | 35 |
| 2 | khshul | 설기형 | M | 607-010 | 부산시 동래구 명륜동 | 764-3784 | 33 |
| 3 | chpark | 박철호 | M | 503-200 | 광주시 남구 지석동 | 298-9730 | 34 |
| 4 | shlee | 이상훈 | M | 503-200 | 광주시 남구 도금동 | 838-4347 | 32 |
| 5 | jyjang | 장영숙 | W | 503-201 | 부산시 영도구 봉래동5가 | 399-9809 | 24 |
| 6 | yjbae | 배용진 | M | 606-065 | 서울시 은평구 응암4동 | 857-5683 | 30 |
| 7 | hbpark | 박혜빈 | W | 122-014 | 경기도 과천시 중앙동 | 234-7677 | 22 |
| 8 | mskim | 김문수 | M | 427-760 | 경기도 시흥시 신천동 | 370-6003 | 63 |
| 9 | bkcha | 차범길 | M | 429-020 | 대전시 서구 둔산1동 | 432-9877 | 49 |
| 10 | kskim | 김길수 | M | 302-121 | 경기도 수원시 장안구 파장동 | 324-5875 | 54 |
| 11 | srkim | 김수련 | M | 440-747 | 대구시 달서구 신당동 | 987-3688 | 23 |
| 12 | srlee | 이성현 | M | 704-701 | 경기도 수원시 권선구 매산로1가 | 243-6844 | 36 |
| 13 | hnjang | 정한나 | W | 441-081 | 광주시 서구 화정4동 | 845-4547 | 58 |
| 14 | mylee | 이명연 | W | 502-791 | 광주시 서구 쌍촌동 | 837-9432 | 33 |
| 15 | yskim | 김영숙 | W | 429-010 | 경기도 시흥시 대야동 | 374-8438 | 53 |
| 16 | khuser | 케이 | M | 111-222 | 서울시 강남구 역삼동 | 02-1111-2222 | 17 |
| 17 | kuser1 | 케이유저 | W | NULL | NULL | NULL | 20 |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+innodb_file_per_table=ON 에 의해서 테이블마다 DATA 파일이 저장되는 테이블스페이스가 새롭게 생기고 DATA가 그 안에 저장된다.
# ll /var/lib/mysql/naver_db/
합계 244
-rw-rw----. 1 mysql mysql 61 2월 3 16:26 db.opt
-rw-rw----. 1 mysql mysql 8833 2월 7 14:43 member.frm
-rw-rw----. 1 mysql mysql 114688 2월 7 14:43 member.ibd <-- innodb_file_per_table=ON 에 의해서 생성된 DATA 파일
-rw-rw----. 1 mysql mysql 7 2월 7 12:48 t1.MYD
-rw-rw----. 1 mysql mysql 1024 2월 7 12:48 t1.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:48 t1.frm
-rw-rw----. 1 mysql mysql 0 2월 7 12:49 t2.MYD
-rw-rw----. 1 mysql mysql 1024 2월 7 12:49 t2.MYI
-rw-rw----. 1 mysql mysql 8556 2월 7 12:49 t2.frm
-rw-r--r--. 1 root root 0 2월 7 14:20 test1.frm3. 영구적 설정
영구적으로 설정하기 위해서는 /etc/my.cnf 설정파일에 innodb_file_per_table=ON 을 [mysqld] 섹션 밑에 추가한다.
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
collation-server=utf8_general_ci
character-set-server=utf8
skip-character-set-client-handshake
#bind-address=127.0.0.1
default_storage_engine=InnoDB
innodb_file_per_table=ON
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
DBMS 서버를 재시작하고 확인한다.
# systemctl restart mariadb
# mysql -e "SHOW VARIABLES LIKE 'innodb_file_per_table'"
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
# mysql -e "SHOW VARIABLES LIKE 'default_storage_engine'"
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
[Tech Report] MySQL Type의 데이터 파일 구조 분석과 삭제된 레코드의 복구 방안
https://www.cyber.pe.kr/2016/11/mysql-innodb-type.html?m=1
############
📌 트랜젝션 📌
############
COMMIT : 보류중인 모든 데이터의 변경사항을 영구적으로 적용하고 현재 트랜잭션을 종료한다.
ROLLBACK : 보류중인 모든 데이터의 변경사항을 폐기하고 현재 트랜잭션을 종료한다. 이전의 COMMIT 직후의 단계로 되돌아 간다.
ROLLBACK to SAVEPOINT : SAVEPOINT로 다시 되돌아 간다. SAVEPOINT 이후의 작은 모두 취소된다.
!!! 한마디로 말하면 !!!
COMMIT : 작업 완료
ROLLBACK : 작업 되돌리기 (Undo 기능)
-- 트랜젝션 commit 확인
-- autocommit 설정 값 확인
-- autocommit 값 1 : 자동으로 commit
-- autocommit 값 2 : 수동으로 commit
SELECT @@autocommit;
SHOW VARIABLES LIKE 'autocommit';
autocommit 설정 또는 해제
임시적으로 autocommit을 설정하는 방법
MariaDB 서버가 재시작하면 원래 설정대로 설정된다. (기본값은 On)
set autocommit = 1; # autocommit 설정
set autocommit = On; # autocommit 설정
set autocommit = 0; # autocommit 해제
set autocommit = Off; # autocommit 해제
영구적으로 autocommit을 설정하는 방법
/etc/my.cnf에 [mysqld] 섹션에 넣고 mariadb 데몬을 재시작한다.
[mysqld]
autocommit = 1 # autocommit 설정, 기본값으로 설정이 없으면 default로 설정
autocommit = 0 # autocommit 해제실습> autocommit 확인하기
# myql
MariaDB [(none)]> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)실습> autocommit 설정 및 해제
1. 임시적 설정
변수에 값을 직접 넣어서 설정하는 방법이다.
set autocommit = 1; # autocommit 설정
set autocommit = On; # autocommit 설정
set autocommit = 0; # autocommit 해제
set autocommit = Off; # autocommit 해제
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET autocommit = 1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET autocommit = Off;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET autocommit = On;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
2. 영구적 설정
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
collation-server=utf8_general_ci
character-set-server=utf8
skip-character-set-client-handshake
#bind-address=127.0.0.1
default_storage_engine=InnoDB
innodb_file_per_table=ON
autocommit=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
# systemctl restart mariadb
# mysql -e "SHOW VARIABLES LIKE 'autocommit'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
# mysql -e "SELECT @@autocommit"
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+실습> mysql -e "명령어" 스크립트 작성하기
# cd
# mkdir bin
# install /dev/null bin/mysqle.sh# vi bin/mysqle.sh#!/bin/sh
# 파일명: mysqle.sh
# 프로그램 설명: mysql -e "SQL문"
# 작성자: 리눅스마스터넷
# 인수의 개수
# argc: argument count
argc=$#
# 인수가 없다면
if [ "$argc" -eq 0 ]
then
echo "SQL문이 없습니다."
echo "Usage: $0 \"SQL문\""
echo
exit 1
fi
# 첫 번째 인수에 들어온 값을 실행한다.
mysql -e "$1"# mysqle.sh "SHOW DATABASES"
+--------------------+
| Database |
+--------------------+
| information_schema |
| entest |
| entest2 |
| kh_db |
| khacademy |
| mysql |
| naver_db |
| performance_schema |
| test |
+--------------------+
실습> 트랙젝션 테스트 1
터미널 2개가 필요하다.
/dev/pts/0: 첫 번째 터미널
/dev/pts/1: 두 번째 터미널
1. autocommit 확인
/dev/pts/0# grep autocommit /etc/my.cnf
autocommit=0
2. DBMS 접속
/dev/pts/0# mysql test
/dev/pts/1# mysql test
3. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
MariaDB [test]> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)table1이 InnoDB로 설정되어 있어야 한다.
MariaDB [test]> CREATE TABLE table1(no int);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> SHOW CREATE TABLE table1\G
*************************** 1. row ***************************
Table: table1
Create Table: CREATE TABLE `table1` (
`no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO table1 VALUES(1);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FROM table1;
+------+
| no |
+------+
| 1 |
+------+
1 row in set (0.00 sec)4. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
MariaDB [test]> DESC table1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| no | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)table1을 확인하면 아직 COMMIT 이 실행되지 않았기 때문에 DATA가 저장되지 않았다.
MariaDB [test]> SELECT * FROM table1;
Empty set (0.00 sec)
5. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
MariaDB [test]> INSERT INTO table1 VALUES(2);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FROM table1;
+------+
| no |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)6. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
table1을 확인하면 아직 COMMIT 이 실행되지 않았기 때문에 DATA가 보이지 않는다.
MariaDB [test]> SELECT * FROM table1;
Empty set (0.00 sec)
7. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
COMMIT; 명령어를 실행함으로써 트랜젝션이 모두 적용되어 보류된 1,2가 table1에 반영된다.
MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM table1;
+------+
| no |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)8. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
현재 연결된 세션을 종료하고 다시 접속한 후 SELECT로 확인한다.
MariaDB [test]> exit
# mysql test
MariaDB [test]> SELECT * FROM table1;
+------+
| no |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)실습> 트랙젝션 테스트 2
터미널 2개가 필요하다.
/dev/pts/0: 첫 번째 터미널
/dev/pts/1: 두 번째 터미널
1. autocommit 확인
/dev/pts/0# grep autocommit /etc/my.cnf
autocommit=0
2. DBMS 접속
/dev/pts/0# mysql test
/dev/pts/1# mysql test
3. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
MariaDB [test]> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)MariaDB [test]> CREATE TABLE table2(no int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> DESC table2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| no | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)MariaDB [test]> INSERT INTO table2 VALUES(1);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
+------+
1 row in set (0.00 sec)4. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
/dev/pts/0에서 autocommit=1로 설정되어 있고 START TRANSACTION이 실행되지 않았기 때문에 INSERT를 실행하면
자동으로 COMMIT이 되므로 1이 table2에 저장되어 있다.
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
+------+
1 row in set (0.00 sec)5. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
트랜젝션을 시작하고 데이터 2를 저장한다.
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO table2 VALUES(2);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FrOM table2;
+------+
| no |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)6. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
table2를 확인하면 아직 첫 번째 터미널에서 COMMIT 이 실행되지 않았기 때문에 DATA가 보이지 않는다.
MariaDB [test]> exit
'# mysql test
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
+------+
1 row in set (0.00 sec)MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)MariaDB [test]> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)MariaDB [test]> INSERT INTO table2 VALUES(3);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)7. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
table2를 확인하면 아직 두 번째 터미널에서 COMMIT 이 실행되지 않았기 때문에 3이 보이지 않는다.
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)8. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
COMMIT; 명령어를 실행함으로써 트랜젝션이 모두 적용된다.
MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
9. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
COMMIT; 명령어를 실행함으로써 트랜젝션이 모두 적용된다.
MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
10. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
COMMIT; 이후에 적용된 값이 모두 출력된다.
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)11. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
COMMIT; 이후에 적용된 값이 모두 출력된다.
MariaDB [test]> SELECT * FROM table2;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)실습> 트랜젝션 3
SAVEPOINT: 책갈피라고 생각하자.
SAVEPOINT 를 이용해서 중간 중간에 책갈피를 끼울 수 있다.
COMMIT, ROLLBACK이 실행되면 완전히 TRANSACTION이 종료된다.
터미널 2개가 필요하다.
/dev/pts/0: 첫 번째 터미널
/dev/pts/1: 두 번째 터미널
1. autocommit 확인
/dev/pts/0# grep autocommit /etc/my.cnf
autocommit=0
2. DBMS 접속
/dev/pts/0# mysql test
/dev/pts/1# mysql test
3. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
MariaDB [test]> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)4. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
MariaDB [test]> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)5. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
table3을 생성하고 DATA 4개를 저장한다.
MariaDB [test]> CREATE TABLE table3(no int);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO table3 VALUES(1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)6. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
첫 번째 터미널 /dev/pts/0 에서 autocommit=1로 설정되어 있기 때문에 INSERT문이 실행되고 자동 COMMIT이 실행된 것이다.
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)7. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
트랜젝션을 새롭게 시작한다.
트랜젝션이 시작되면 아래와 같다.
+-- START TRANSACTION
|
----[1,2,3,4]----|
startMariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
no의 값이 1인 데이터를 삭제한다.
1을 삭제하면 아래와 같다.
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]----|
start 현재MariaDB [test]> DELETE FROM table3 WHERE no=1;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 2 |
| 3 |
| 4 |
+------+
3 rows in set (0.00 sec)8. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
table3을 확인하면 첫 번째 터미널에서 COMMIT 이 실행되지 않았기 때문에 1이 보인다.
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)9. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
no의 값이 4인 데이터를 삭제한다.
4를 삭제하면 아래와 같다.
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]----[4삭제]----|
start 현재
MariaDB [test]> DELETE FROM table3 WHERE no=4;
Query OK, 1 row affected (0.00 sec)MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 2 |
| 3 |
+------+
2 rows in set (0.00 sec)10. /dev/pts/1에서 실행
두 번째 터미널 /dev/pts/1 에서 실행한다.
table3을 확인하면 첫 번째 터미널에서 COMMIT 이 실행되지 않았기 때문에 1과, 4가 보인다.
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)11. /dev/pts/0에서 실행
첫 번째 터미널 /dev/pts/0 에서 실행한다.
SAVEPOINT: 책갈피라고 생각하자
SAVEPOINT sp1을 생성한다.
sp1을 생성하면 아래와 같다.
+-- START TRANSACTION
| 현재
----[1,2,3,4]----|----[1삭제]--[4삭제]----|
start sp1 MariaDB [test]> SAVEPOINT sp1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO table3 VALUES(5);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 2 |
| 3 |
| 5 |
+------+
3 rows in set (0.00 sec)SAVEPOINT sp2를 생성한다.
sp2를 생성하면 아래와 같다.
+-- START TRANSACTION
| 현재
----[1,2,3,4]----|----[1삭제]--[4삭제]----|----[5삽입]----|
start sp1 sp2MariaDB [test]> SAVEPOINT sp2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO table3 VALUES(6);
Query OK, 1 row affected (0.00 sec)
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]--[4삭제]----|----[5삽입]----|----[6삽입]----|
start sp1 sp2 현재
--------------------------------------------------------------------------> COMMIT
<---------------- ROLLBACK to sp2 (sp2 이후는 모두 취소됨)
<-------------------------------- ROLLBACK to sp1 (sp1 이후는 모두 취소됨)
<--------------------------------------------------------- ROLLBACK (start 이후는 모두 취소됨)MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 2 |
| 3 |
| 5 |
| 6 |
+------+
4 rows in set (0.00 sec)sp2로 ROLLBACK 한다. sp2로 ROLLBACK을 하면 6은 복구할 수 없다.
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]--[4삭제]----|----[5삽입]----|----[6삽입]----|
start sp1 sp2 현재
<--------------- ROLLBACK to sp2ROLLBACK to sp2 이후의 상태는 아래와 같다.
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]--[4삭제]----|----[5삽입]----|
start sp1 현재MariaDB [test]> ROLLBACK to sp2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 2 |
| 3 |
| 5 |
+------+
3 rows in set (0.00 sec)sp1 으로 ROLLBACK 한다. sp1으로 ROLLBACK을 하면 5는 복구할 수 없다.
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]--[4삭제]----|----[5삽입]----|
start sp1 현재
<---------------- ROLLBACK to sp1ROLLBACK to sp1 이후의 상태는 아래와 같다.
+-- START TRANSACTION
|
----[1,2,3,4]----|----[1삭제]--[4삭제]----|
start 현재MariaDB [test]> ROLLBACK to sp1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 2 |
| 3 |
+------+
2 rows in set (0.00 sec)ROLLBACK을 한다.
----[1,2,3,4]----|----[1삭제]--[4삭제]----|
start 현재
<------------------------- ROLLBACKROLLBACK이후의 상태는 아래와 같고 ROLLBACK을 하면 TRANSACTION은 종료된다.
----[1,2,3,4]----|
현재MariaDB [test]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM table3;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
정보보안 전문가