created by Aha00aAha00a at 2015-11-21
last modified by Aha00aAha00a at 2025-05-16
revision: 5

MySQL Config

sudo vi /etc/mysql/my.cnf

1. bind

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address        = 0.0.0.0

2. InnoDB

[mysqld]
default-storage-engine=innodb
innodb_file_per_table=1

3. charset

[mysqld]
character-set-server=utf8
#collation-server=utf8_general_ci
collation-server=utf8_bin
SHOW FULL COLUMNS FROM table_name;

4. skip_name_resolve

http://serverfault.com/questions/15073/remote-connection-to-mysql-server-takes-very-long

[mysqld]
skip_name_resolve

5. Logging

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

5.1. Slow Query Log

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html --2025-04-17

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
mysqldumpslow -s t /var/log/mysql/mysql-slow.log # sort by query time
mysqldumpslow -s c /var/log/mysql/mysql-slow.log # sort by count

5.2. binlog

expire_logs_days = 7  # 7일 지나면 자동 삭제 (옛날 방식)

--2025-05-16

6. See Also

6.2. Similar Pages

Similar pages by cosine similarity. Words after page name are term frequency.

  • 35.29% Percona log(18:2), mysql(14:2), query(10:3), slow(10:1), name(6:1), var(4:1), lock(3:1), from(2:2), sudo(2:2), and(2:1)
  • 31.35% MySQL mysql(14:11), my(4:18), sql(3:9), time(8:1), server(7:2), name(6:1), en(1:4), dev(1:3), doc(1:3), config(2:1)
  • 30.47% AndroidLog log(18:8), time(8:1), verbose(3:3), debug(3:2), html(1:1)

6.3. Adjacent Pages

≤ 32
all
1.0x
1.0x
80
-120
ON
0/0
0/0
0.00
0
0 / 0
1.00x