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) | 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
이와 달리, 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 .. | 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) | 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 | 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 | cs |
# 참고자료
https://www.slideshare.net/yoku0825/mysql-57-53449734
댓글 없음:
댓글 쓰기