information technology: 6월 2021

MySQL 5.7을 사용할 때, 기억해두면 좋은 내용들을 정리해보도록 하겠습니다.

1. show_compatibility_56

global_variables, session_variables, global_status, session_status 의 결과 값을 information_schema 에서 출력할 지, performance_schema 에서 출력할 지 지정하는 변수입니다.

MySQL 5.7 에서 생긴 변수이며,
MySQL 5.6 과 동일하게 information_schema 에서 출력하려면 ON
performance_schema에서 출력하려면 OFF

MySQL 5.7.9 부터는 performance_schema에 SELECT grant없이도 검색할 수 있게 되었습니다.

2. default_password_lifetime

MySQL 5.7.10 까지의 기본 값은 360 입니다.
그로 인해, password_last_changed 부터 360일 이상 경과한 유저는 로그인 할 수 없게 되는 상황이 발생하게 되었습니다.

하지만, MySQL 5.7.11 부터는 기본 값이 0으로 되어 lifetime이 무기한으로 변경되었습니다.

3. 16 byte 해시 패스워드 폐지

old_passwords, skip_secure_auth 옵션은 16 byte 해시 패스워드와 관련있는 변수입니다.
- old_passwords: 비밀번호를 저장할 때 사용하는 암호화를 예전 버전의 알고리즘을 채용
- skip_secure_auth: 예전 버전의 암호화로 저장되어 있는 유저가 로그인 하기 위해 구사

MySQL 5.7부터는 아예 사용할 수 없게 되었습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.00 sec)
 
mysql> SELECT user, host, password FROM user WHERE user <> 'root';
+--------------+-----------+-------------------------------------------+
| user         | host      | password                                  |
+--------------+-----------+-------------------------------------------+
| old_password | localhost | 5d2e19393cc5ef67                          |
| new_password | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | # 새로운 암호화는 *로 시작
+--------------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
Colored by Color Scripter
cs

4. sql_mode

- MySQL 5.6
NO_ENGINE_SUBSTITUTION: 존재하지 않는 스토리지 엔진을 지정하여 테이블을 작성하면 에러

- MySQL 5.7
ONLY_FULL_GROUP_BY: GROUP BY 절에 지정한 컬럼 이외에는 검색할 수 없게 되었습니다(집계함수 제외)

STRICT_TRANS_TABLES: MySQL이 멋대로 행을 자른다던가, 문자를 숫자로 캐스팅 할 때 warning을 출력하면서 실행되었지만. 해당 설정으로 인해 에러를 반환하도록 합니다.

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
mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
 
mysql> DESC jg;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| text_col | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
 
mysql> insert into jg (text_col) values (REPEAT('Aa', 11));
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'text_col' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.01 sec)
 
mysql> select * from jg;
+----------------------+
| text_col             |
+----------------------+
| AaAaAaAaAaAaAaAaAaAa |
+----------------------+
1 row in set (0.00 sec)
 
mysql>  set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into jg (text_col) values (REPEAT('Aa', 11));
ERROR 1406 (22001): Data too long for column 'text_col' at row 1
cs

NO_ZERO_IN_DATE: date, datetime 타입의 month, day에 0을 넣으면 warning을 출력하면서 0000-00-00 으로 저장됩니다.
(STRICT_TRANS_TABLES 랑 같이 사용하면 에러를 반환하도록 합니다)

NO_ZERO_DATE: date, datetime 타입에 0000-00-00을 넣으면 warning이 출력됩니다.(NO_ZERO_IN_DATE와 동일하게 warning 이므로 STRICT_TRANS_TABLES 랑 같이 사용하면 에러를 반환하도록 합니다)

ERROR_FOR_DIVISION_BY_ZERO: x를 0으로 나누면 warning을 출력 후 NULL을 반환합니다.(MySQL 5.6에서는 warning이 없었음)
(STRICT_TRANS_TABLES 랑 같이 사용하면 에러를 반환하도록 합니다)

NO_AUTO_CREATE_USER: 패스워드 없는 유저를 CREATE USER가 아닌 GRANT로 생성 시 에러를 반환합니다.

5. log_error_verbosity

MySQL 5.7.2 부터 추가된 로그 레벨 제어 옵션입니다.
특징
- log_warnings + 1 의 값이 log_error_verbosity 로 지정됩니다
- log_warnings, log_error_verbosity 가 혼재되어 있을 경우 높은 쪽으로 설정됩니다

value	notes	warnings	errors
log-warnings = 0 log-error-verbosity = 1	No	No	Yes
log-warnings = 1 log-error-verbosity = 2	No	Yes	Yes
log-warnings >= 2 log-error-verbosity >= 3	Yes	Yes	Yes

6. Temporary table

CREATE TEMPORARY TABLE 은 default_tmp_storage_engine 변수로 제어합니다(5.6 ~)

MySQL 5.7 부터 internal_tmp_disk_storage_engine 변수가 추가되었습니다.
내부 임시 테이블(Using temporary)는 해당 변수로 제어되며, 기본 값은 InnoDB 입니다.

임시 테이블을 이용하는 경우는 redo log, x lock 이 필요 없으므로 전용 ibtmp1 파일을 작성합니다. 관련 변수의 기본 값은 innodb_temp_data_file_path = ibtmp1:12M:autoextend

주의 점은 한번 확보한 용량은 mysqld를 재시작하기 전까지 줄어들지 않습니다.
이와 달리, MyISAM 임시 테이블은 쿼리가 끝나면 용량이 바로 회수됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from (select name from test_table group by name order by age, id) as a, (select age from test_table group by age order by created_at, updated_at) as b ORDER BY a.name, b.age ASC;
 
[root@08d751da1200 tmp]# ls -al
total 50524
-rw-rw---- 1 mysql mysql 24379392 Jun  3 22:03 #sql_cfe_2.MYD
-rw-rw---- 1 mysql mysql 27345920 Jun  3 22:03 #sql_cfe_2.MYI
drwxr-xr-x 2 mysql mysql     4096 Jun  3 22:03 .
drwxr-xr-x 1 root  root      4096 Jun  3 18:24 ..
 
# Query end
518983 rows in set (4.31 sec)
 
[root@08d751da1200 tmp]# ls -al
total 8
drwxr-xr-x 2 mysql mysql 4096 Jun  3 22:03 .
drwxr-xr-x 1 root  root  4096 Jun  3 18:24 ..
Colored by Color Scripter
cs

7. mysql.user.password column 폐지

MySQL 5.7 부터 패스워드 해시는 mysql.user.authentication_string 컬럼에 격납되게 되었습니다.
MySQL 5.6 이전은 mysql.user.password 이었습니다.

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
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.00 sec)
 
mysql> SELECT user, host, password, authentication_string FROM user WHERE user <> 'root';
+--------------+-----------+-------------------------------------------+------------------------
---------------+
| user         | host      | password                                  | authentication_string |
+--------------+-----------+-------------------------------------------+------------------------
---------------+
| old_password | localhost | 5d2e19393cc5ef67                          |                       |
               |
| new_password | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |                       |
               |
+--------------+-----------+-------------------------------------------+------------------------
---------------+
2 rows in set (0.00 sec)
 
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.7.34    |
+-----------+
1 row in set (0.00 sec)
 
mysql> SELECT user, host, password, authentication_string FROM user WHERE user <> 'root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
 
mysql> SELECT user, host, authentication_string FROM user WHERE user <> 'root';
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
Colored by Color Scripter
cs

8. log_timestamps

slow query, error, general log Default timestamp 가 UTC 로 되었습니다.
UTC 또는 SYSTEM 로 설정하며, 온라인으로 변경 가능합니다.

binary log 는 해당 변수와 상관 없습니다.

9. innodb_buffer_pool_dump_pct

MySQL 5.7 부터 innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup Default 값이 ON 으로 변경되었습니다.

새로 추가된 innodb_buffer_pool_dump_pct 변수는 mysqld 를 정상종료 할 때 버퍼 풀 전체의 25% 페이지를 텍스트 파일에 남기도록 하는 설정입니다.
(MySQL 5.6 이전은 100% 다 남김)

개인적으로는 innodb_buffer_pool_dump_at_shutdown = ON, innodb_buffer_pool_load_at_startup = OFF 가 좋다고 생각합니다.

10. binlog_format

Default 가 ROW 로 변경되었습니다.

관련된 변수들을 나열해보면
- binlog_row_image: format 이 row 일 때, row image를 최소 컬럼셋으로 로깅하도록 설정
- binlog_rows_query_log_events: format 이 row 일 때, Row query log event 같은 정보성 로그 이벤트들이 기록됩니다(original query)

mysqlbinlog 를 사용할 땐, --vv 또는 --verbose --verbose 옵션 사용을 추천합니다.

11. secure_file_priv

File_priv grant 가 부여되어 있는 유저의 SELECT ... INTO OUTFILE 또는 LOAD DATA INFILE statement에 관련된 설정입니다.

secure_file_priv 가 지정되어 있지 않은 경우, mysqld 실효한 유저 권한으로 모든 파일에 접근할 수 있습니다.

온라인 변경 불가능

MySQL 5.7 부터의 변경내용
- rpm, deb 등의 패키지의 경우, Default 값이 /var/lib/mysql-files 로 설정됩니다.
- secure_file_priv = NULL 로 설정하는 경우, File 권한을 이용하는 모든 동작이 불가능 해졌습니다.
- secure_file_priv 가 설정되어 있지 않은 경우, warning 을 출력하도록 되었습니다.

* 기존 유저에 새롭게 FILE grant 을 부여했는 데도 불구하고, 동작하지 않는 경우는 세션 재접속 후 시도해보시길 바랍니다.

12. generated column

실제 column 값을 계산한 결과를 격납하는 전용 column 을 작성할 수 있습니다.

- STORED 타입은 ALTER TABLE 실행 시 값이 계산되어 디스크에 고정화 됩니다.
- VIRTUAL 타입은 SELECT 실행 시 계산됩니다(Default)
- 어느 타입이라도 secondary index 를 작성할 수 있으며, 디스크에 고정화 됩니다.
- 생성된 열에 NOT NULL, UNIQUE 제약을 걸 수 있습니다.
- column 으로 정의되기 때문에, SELECT * 이랑 INSERT INTO ... VALUES 에 주의가 필요합니다.

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
# Covering index
mysql> ALTER TABLE t2 ADD v_lang varchar(16) AS (CONCAT('OK-', lang)), ADD KEY(v_lang);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> EXPLAIN SELECT v_lang, COUNT(*) FROM t2 GROUP BY v_lang;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | v_lang        | v_lang | 19      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
 
# 범위 검색으로 ORDER BY 까지 KEY가 사용되지 않을 때, 활용법
mysql> SHOW INDEX FROM t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          0 | PRIMARY  |            1 | id          | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| t2    |          1 | age      |            1 | age         | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | age      |            2 | created_at  | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
 
mysql> EXPLAIN SELECT * FROM t2 WHERE age < 20 ORDER BY created_at;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | age           | age  | 2       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
 
mysql> ALTER TABLE t2 ADD is_adult tinyint AS (IF(age < 20, 1, 0)), ADD KEY (is_adult, created_at);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> EXPLAIN SELECT * FROM t2 WHERE is_adult = 1 ORDER BY created_at DESC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | is_adult      | is_adult | 2       | const |    2 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
 
# CHECK 제약으로 응용
mysql> ALTER TABLE t2 ADD age_check tinyint AS (IF(age > 100 , null, 1)) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t2 (lang, age) VALUES ('test', 101);
ERROR 1048 (23000): Column 'age_check' cannot be null
 
 
Colored by Color Scripter
cs

generated column 을 CHECK 제약으로 사용할 때의 주의사항

- VIRTUAL 타입이고 secondary index 가 없는 경우
기존의 데이터가 CHECK 제약을 위반해도 ALTER TABLE 은 성공합니다. 그 이후에 발생하는 갱신에 관해서는 에러를 반환하게 됩니다.
- STORED 타입 또는 secondary index 가 있는 경우
기존의 데이터가 CHECK 제약을 위반하면 ALTER TABLE 가 에러납니다.

1
2
3
4
5
6
7
mysql> ALTER TABLE t2 ADD age_check tinyint AS (IF(age > 100 , null, 1)) STORED NOT NULL;
ERROR 1048 (23000): Column 'age_check' cannot be null
mysql> ALTER TABLE t2 ADD age_check tinyint AS (IF(age > 100 , null, 1)) NOT NULL, ADD KEY (age_check);
ERROR 1048 (23000): Column 'age_check' cannot be null
mysql> ALTER TABLE t2 ADD age_check tinyint AS (IF(age > 100 , null, 1)) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
Colored by Color Scripter
cs

# 참고자료
https://www.slideshare.net/yoku0825/mysql-57-53449734

information technology

2021년 6월 10일 목요일

결혼이민비자 신청방법(F-6-1 국민의 배우자)

2021년 6월 3일 목요일

MySQL 5.7 기억해두면 좋은 것들

결혼이민비자 신청방법(F-6-1 국민의 배우자)

이 블로그 검색