MySQL – 지엘디지털

MariaDB Binary Log 삭제하기

MySQLBy 관리자 2019년 11월 19일

 root[mysql]> <strong>show binary logs;
</strong>+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000035 |        401 |
| mysql-bin.000036 |        362 |
| mysql-bin.000037 | 1073742328 |
| mysql-bin.000038 |   77130306 |
+------------------+------------+
4 rows in set (0.05 sec)

root[mysql]> <strong>purge binary logs to 'mysql-bin.000038';
</strong>Query OK, 0 rows affected (0.10 sec)
 
root[mysql]> <strong>show binary logs;</strong>
0 row in set (0.14 sec)
root[mysql]>

root[mysql]> show binary logs;

+------------------+------------+

| Log_name | File_size |

+------------------+------------+

| mysql-bin.000035 | 401 |

| mysql-bin.000036 | 362 |

| mysql-bin.000037 | 1073742328 |

| mysql-bin.000038 | 77130306 |

+------------------+------------+

4 rows in set (0.05 sec)

root[mysql]> purge binary logs to 'mysql-bin.000038';

Query OK, 0 rows affected (0.10 sec)

root[mysql]> show binary logs;

0 row in set (0.14 sec)

root[mysql]>

DB에 레코드가 많이 쌓여 테이블 파일의 용량이 증가하는 것도 문제지만, binary log를 초기 세팅대로 방치하면 금방 HDD가 꽉 차게 된다. data 디렉토리(configure에 따라 다름) 내의 파일들을 보면 binary log가 많이 생성된 것을 알 수 있다. 파일을 rm 명령어로 직접 지우지 말고 mysql 콘솔에서 지우는 것이 바람직하다.

root[mysql]> <strong>purge master logs to 'mysql-bin.000186';</strong>
Query OK, 0 rows affected (0.91 sec)
 root[mysql]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000186 | 1073742098 |
| mysql-bin.000187 | 1073742018 |
| mysql-bin.000188 | 1073742083 |
| mysql-bin.000189 |  349742255 |
+------------------+------------+
4 rows in set (0.00 sec)

root[mysql]> purge master logs to 'mysql-bin.000186';

Query OK, 0 rows affected (0.91 sec)

root[mysql]> show binary logs;

+------------------+------------+

| Log_name | File_size |

+------------------+------------+

| mysql-bin.000186 | 1073742098 |

| mysql-bin.000187 | 1073742018 |

| mysql-bin.000188 | 1073742083 |

| mysql-bin.000189 | 349742255 |

+------------------+------------+

4 rows in set (0.00 sec)

위 명령어에서 binary 로그 이름을 지정하면 해당…

MySQL Data directory 변경하기

MySQLBy 관리자 2019년 10월 31일

Mysql datadir 경로 확인 및 Mysql service 중지 mysql 접속 하여 아래와 같은 명령어로 datadir 경로를 확인 하고 서비스를 중단 한다.

[root@mail ~]# mysql mysql
mysql> select @@datadir;
 +-----------------+
 | @@datadir       |
 +-----------------+
 | /var/lib/mysql/ |
 +-----------------+
 1 row in set (0.00 sec)
 \q

[root@mail ~]# systemctl stop mysql

[root@mail ~]# mysql mysql

mysql> select @@datadir;

+-----------------+

| @@datadir |

+-----------------+

| /var/lib/mysql/ |

+-----------------+

1 row in set (0.00 sec)

[root@mail ~]# systemctl stop mysql

새로운 Mysql datadir 생성 및 경로 복사 하기새로운 Mysql datadir 생성 후 Mysql datadir 경로 복사 한다. 권한 또한 부여 한다.

[root@mail ~] mkdir /data/
[root@mail ~] rsync -av /var/lib/mysql /data/
[root@mail ~] chown -R mysql:mysql /data/mysql

[root@mail ~] mkdir /data/

[root@mail ~] rsync -av /var/lib/mysql /data/

[root@mail ~] chown -R mysql:mysql /data/mysql

my.cnf 파일 수정/etc/my.cnf 수정 한다.

[root@mail ~]# vi /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
[client]
socket=/data/mysql/mysql.sock
#symbolic-links=0
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[root@mail ~]# vi /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

[client]

socket=/data/mysql/mysql.sock

#symbolic-links=0

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

SELinux 보안 context에 추가 및…

서버를 운영하다 보면 binary 로그와 slow-query.log 가 계속해서 쌓이게 됩니다. /etc/my.cnf 파일에 아래 부분을 적용하면 로그 조절이 가능합니다. [mysqld] log-bin=mysql-bin expire_logs_days=7 long_query_time=3 slow_query_log=1 slow_query_log_file=slow-query.log log-bin=mysql-bin 바이너리 로그 파일명을 지정할 수 있습니다. expire_logs-days=7 최근 일주일 분량만 남기는 옵션입니다. long_query_time=3 쿼리타임이 3초를 넘어가면 로그를 남깁니다. slow_query_log=1 슬로우쿼리 로그를 작성하는다는 옵션입니다. slow_query_log_file=slow-query.log 슬로우쿼리 로그 파일명을 지정할 수 있습니다.…

MySQL 서버설정 참고

MySQLBy 관리자 2019년 10월 23일

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[client]
# hostname                                      = panel.uhlhosting.ch
#default-character-set                          = utf8mb4

[mysqld]
max_allowed_packet=268435456
max_connect_errors                              = 10
max_connections                                 = 50
#safe-show-database
#skip-innodb
#skip-locking
skip-networking


open_files_limit                                = 25000
local-infile                                    = 0
performance_schema                              = OFF

# Enable logs
general-log                                     = ON

explicit_defaults_for_timestamp = true

# Character collation
#character_set_server                           = utf8mb4
#collation_server                               = utf8mb4_unicode_ci

bulk_insert_buffer_size                         = 8M
connect_timeout                                 = 10
interactive_timeout                             = 50


query_cache_type                                = 1
query_cache_limit                               = M
query_cache_min_res_unit                        = 2k
query_cache_size                                = 0

table_open_cache                                = 2048

#ft_min_word_len                                = 2

# Skip reverse DNS lookup of clients
# skip-name-resolve

thread_stack                                    = 256K

transaction_isolation                           = REPEATABLE-READ

read_buffer_size                                = 2M
read_rnd_buffer_size                            = 16M
sort_buffer_size                                = 8M
table_cache                                     = 4096
join_buffer_size                                = 2M
key_buffer_size                                 = 256M

max_allowed_packet                              = 16M
read_buffer_size                                = 2M

bulk_insert_buffer_size                         = 64M
default-storage-engine                          = MYISAM
myisam_recover_options                          = BACKUP,FORCE
myisam_sort_buffer_size                         = 128M
myisam_max_sort_file_size                       = 5G
myisam_repair_threads                           = 4
myisam-recover-options

thread_cache_size                               = 1024
thread_concurrency                              = 8

max_heap_table_size                             = 64M
tmp_table_size                                  = 64M
wait_timeout                                    = 200

innodb_large_prefix                             = true
innodb_file_format                              = barracuda
innodb_file_format_max                          = barracuda
innodb_file_per_table                           = true
innodb_file_per_table                           = 1
innodb_buffer_pool_size                         = 3G
innodb_buffer_pool_instances                    = 8
innodb_flush_method                             = O_DIRECT
innodb_io_capacity                              = 100
innodb_read_io_threads                          = 8
innodb_thread_concurrency                       = 16
innodb_write_io_threads                         = 8
innodb_log_file_size                            = 256M
innodb_fast_shutdown                            = 1
innodb_additional_mem_pool_size                 = 16M
innodb_data_file_path                           = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit                  = 1
innodb_log_buffer_size                          = 8M
innodb_log_files_in_group                       = 3
innodb_max_dirty_pages_pct                      = 90
innodb_lock_wait_timeout                        = 120

log-queries-not-using-indexes   = 1

# Error log
#err-log                                        = /var/log/mysql/error.log
binlog_cache_size                               = 1M

# Slow Query Log
slow_query_log_file                             = /var/log/mysql/slow.log
long_query_time                                 = 5


# General Log
general_log_file                                = /var/log/mysql/general.log

[mysqld_safe]
nice                                            = -5
open_files_limit                                = 8192

[myisamchk]
key_buffer_size                                 = 256M
sort_buffer_size                                = 256M
read_buffer                                     = 16M
write_buffer                                    = 16M

[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

# This group is read both both by the client and the server

# use it for options that affect everything

[client-server]

# include all files from the config directory

!includedir /etc/my.cnf.d

[client]

# hostname = panel.uhlhosting.ch

#default-character-set = utf8mb4

[mysqld]

max_allowed_packet=268435456

max_connect_errors = 10

max_connections = 50

#safe-show-database

#skip-innodb

#skip-locking

skip-networking

open_files_limit = 25000

local-infile = 0

performance_schema = OFF

# Enable logs

general-log = ON

explicit_defaults_for_timestamp = true

# Character collation

#character_set_server = utf8mb4

#collation_server = utf8mb4_unicode_ci

bulk_insert_buffer_size = 8M

connect_timeout = 10

interactive_timeout = 50

query_cache_type = 1

query_cache_limit = M

query_cache_min_res_unit = 2k

query_cache_size = 0

table_open_cache = 2048

#ft_min_word_len = 2

# Skip reverse DNS lookup of clients

# skip-name-resolve

thread_stack = 256K

transaction_isolation = REPEATABLE-READ

read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

table_cache = 4096

join_buffer_size = 2M

key_buffer_size = 256M

max_allowed_packet = 16M

read_buffer_size = 2M

bulk_insert_buffer_size = 64M

default-storage-engine = MYISAM

myisam_recover_options = BACKUP,FORCE

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 5G

myisam_repair_threads = 4

myisam-recover-options

thread_cache_size = 1024

thread_concurrency = 8

max_heap_table_size = 64M

tmp_table_size = 64M

wait_timeout = 200

innodb_large_prefix = true

innodb_file_format = barracuda

innodb_file_format_max = barracuda

innodb_file_per_table = true

innodb_file_per_table = 1

innodb_buffer_pool_size = 3G

innodb_buffer_pool_instances = 8

innodb_flush_method = O_DIRECT

innodb_io_capacity = 100

innodb_read_io_threads = 8

innodb_thread_concurrency = 16

innodb_write_io_threads = 8

innodb_log_file_size = 256M

innodb_fast_shutdown = 1

innodb_additional_mem_pool_size = 16M

innodb_data_file_path = ibdata1:10M:autoextend

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

log-queries-not-using-indexes = 1

# Error log

#err-log = /var/log/mysql/error.log

binlog_cache_size = 1M

# Slow Query Log

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 5

# General Log

general_log_file = /var/log/mysql/general.log

[mysqld_safe]

nice = -5

open_files_limit = 8192

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 16M

write_buffer = 16M

[mysqldump]

# Do not buffer the whole result set in memory before writing it to

# file. Required for dumping very large tables

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.

#safe-updates

MySQL over 8G Ram

MySQLBy 관리자 2016년 9월 21일 Leave a comment

8기가 램에 설정파일 스크랩 합니다. MySQL config 5.0.x, 5.1.x, 5.5.x Connections : 1000

[mysqld_safe]
nice 				= -15

[client]
socket 				= /var/lib/mysql/mysql.sock
default-character-set 		= utf8

[mysqld]
## Charset and Collation
character-set-server 		= utf8
collation-server 		= utf8_general_ci

## Files
back_log 			= 300
open-files-limit 		= 8192
open-files 			= 1024	
port 				= 3306
socket 				= /var/lib/mysql/mysql.sock
pid-file 			= /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve

## Logging
datadir				= /var/lib/mysql
relay_log			= mysql-relay-bin
relay_log_index			= mysql-relay-index
#log				= mysql-gen.log	
log_error			= mysql-error.err
log_warnings
log_bin				= mysql-bin
log_slow_queries		= mysql-slow.log
#log_queries_not_using_indexes
long_query_time 		= 10	#default: 10
max_binlog_size 		= 256M	#max size for binlog before rolling
expire_logs_days 		= 4	#binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack 			= 256K	  #default: 32bit: 192K, 64bit: 256K
sort_buffer_size 		= 512K	  #default: 2M, larger may cause perf issues
read_buffer_size 		= 512K	  #default: 128K, change in increments of 4K
read_rnd_buffer_size 		= 512K	  #default: 256K				
join_buffer_size 		= 512K	  #default: 128K
binlog_cache_size 		= 64K	  #default: 32K, size of buffer to hold TX queries
## total per-thread buffer memory usage: 4736000K = 4.625GB

## Query Cache
query_cache_size 		= 32M	#global buffer
query_cache_limit 		= 512K	#max query result size to put in cache

## Connections
max_connections 		= 2000	#multiplier for memory usage via per-thread buffers
max_connect_errors 		= 100	#default: 10
concurrent_insert		= 2	#default: 1, 2: enable insert for all instances
connect_timeout			= 30	#default -5.1.22: 5, +5.1.22: 10
max_allowed_packet 		= 32M	#max size of incoming data to allow

## Default Table Settings
sql_mode 			= NO_AUTO_CREATE_USER

## Table and TMP settings
max_heap_table_size 		= 1G	#recommend same size as tmp_table_size
bulk_insert_buffer_size 	= 1G	#recommend same size as tmp_table_size
tmp_table_size                  = 1G    #recommend 1G min
#tmpdir                         = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir

## Table cache settings
#table_cache 			= 512	#5.0.x
#table_open_cache		= 512	#5.1.x, 5.5.x

## Thread settings
thread_concurrency		= 16  #recommend 2x CPU cores
thread_cache_size		= 100 #recommend 5% of max_connections

## Replication
#read_only
#skip-slave-start		
#slave-skip-errors		= <default: none, recommend:1062>
#slave-net-timeout		= <default: 3600>
#slave-load-tmpdir		= <location of slave tmpdir>
#slave_transaction_retries	= <default: 10>
#server-id                      =
#replicate-same-server-id       =  
#auto-increment-increment       =
#auto-increment-offset          =
#master-connect-retry           =
#log-slave-updates		= <default: 0 disable>
#report-host			= <master_server_ip>
#report-user			= <replication_user>
#report-password                =
#report-port			= <default: 3306>
#replicate-do-db            	=
#replicate-ignore-db		=
#replicate-do-table		= 
#relicate-ignore-table		=
#replicate-rewrite-db		=
#replicate-wild-do-table	=
#replicate-wild-ignore-table	=

## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability 
#rpl_semi_sync_master_enabled	= 1 #enable = 1, disable = 0
#rpl_semi_sync_master_timeout	= 1000 #in milliseconds , master only setting

## 5.1.x and 5.5.x replication related setting. 
#binlog_format			= MIXED

## MyISAM Engine
key_buffer			= 512K	#global buffer
myisam_sort_buffer_size		= 128M	#index buffer size for creating/altering indexes
myisam_max_sort_file_size	= 256M	#max file size for tmp table when creating/alering indexes
myisam_repair_threads		= 4	#thread quantity when running repairs
myisam_recover			= BACKUP	#repair mode, recommend BACKUP 

## InnoDB Plugin Dependent Settings
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so

## InnoDB IO Capacity - 5.1.x plugin, 5.5.x
#innodb_io_capacity		= 200

## InnoDB IO settings -  5.1.x only
#innodb_file_io_threads		= 16

## InnoDB IO settings -  5.5.x and greater
#innodb_write_io_threads 	= 16
#innodb_read_io_threads		= 16

## InnoDB Plugin Independent Settings
innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path		= ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size		= 256M	#64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 
innodb_log_files_in_group	= 4	#combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
innodb_buffer_pool_size		= 4G 	#global buffer
innodb_additional_mem_pool_size	= 4M	#global buffer
innodb_status_file		  	#extra reporting
innodb_file_per_table			#enable always
innodb_flush_log_at_trx_commit	= 2	#2/0 = perf, 1 = ACID
innodb_table_locks		= 0	#preserve table locks
innodb_log_buffer_size 		= 128M 	#global buffer
innodb_lock_wait_timeout 	= 60	
innodb_thread_concurrency	= 16	#recommend 2x core quantity
innodb_commit_concurrency	= 16	#recommend 4x num disks
#innodb_flush_method		= O_DIRECT	   #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa		= 0		   #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite

## Binlog sync settings
## XA transactions = 1, otherwise set to 0 for best performance
sync_binlog			= 0

## TX Isolation
transaction-isolation		= REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

## Per-Thread Buffer memory utilization equation:
#(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections

## Global Buffer memory utilization equation:
# innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size

[mysqldump]
quick
quote-names
max_allowed_packet 		= 128M

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

[mysqld_safe]

nice = -15

[client]

socket = /var/lib/mysql/mysql.sock

default-character-set = utf8

[mysqld]

## Charset and Collation

character-set-server = utf8

collation-server = utf8_general_ci

## Files

back_log = 300

open-files-limit = 8192

open-files = 1024

port = 3306

socket = /var/lib/mysql/mysql.sock

pid-file = /var/lib/mysql/mysql.pid

skip-external-locking

skip-name-resolve

## Logging

datadir = /var/lib/mysql

relay_log = mysql-relay-bin

relay_log_index = mysql-relay-index

#log = mysql-gen.log

log_error = mysql-error.err

log_warnings

log_bin = mysql-bin

log_slow_queries = mysql-slow.log

#log_queries_not_using_indexes

long_query_time = 10 #default: 10

max_binlog_size = 256M #max size for binlog before rolling

expire_logs_days = 4 #binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage

thread_stack = 256K #default: 32bit: 192K, 64bit: 256K

sort_buffer_size = 512K #default: 2M, larger may cause perf issues

read_buffer_size = 512K #default: 128K, change in increments of 4K

read_rnd_buffer_size = 512K #default: 256K

join_buffer_size = 512K #default: 128K

binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries

## total per-thread buffer memory usage: 4736000K = 4.625GB

## Query Cache

query_cache_size = 32M #global buffer

query_cache_limit = 512K #max query result size to put in cache

## Connections

max_connections = 2000 #multiplier for memory usage via per-thread buffers

max_connect_errors = 100 #default: 10

concurrent_insert = 2 #default: 1, 2: enable insert for all instances

connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10

max_allowed_packet = 32M #max size of incoming data to allow

## Default Table Settings

sql_mode = NO_AUTO_CREATE_USER

## Table and TMP settings

max_heap_table_size = 1G #recommend same size as tmp_table_size

bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size

tmp_table_size = 1G #recommend 1G min

#tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir

## Table cache settings

#table_cache = 512 #5.0.x

#table_open_cache = 512 #5.1.x, 5.5.x

## Thread settings

thread_concurrency = 16 #recommend 2x CPU cores

thread_cache_size = 100 #recommend 5% of max_connections

## Replication

#read_only

#skip-slave-start

#slave-skip-errors = <default: none, recommend:1062>

#slave-net-timeout = <default: 3600>

#slave-load-tmpdir = <location of slave tmpdir>

#slave_transaction_retries = <default: 10>

#server-id =

#replicate-same-server-id =

#auto-increment-increment =

#auto-increment-offset =

#master-connect-retry =

#log-slave-updates = <default: 0 disable>

#report-host = <master_server_ip>

#report-user = <replication_user>

#report-password =

#report-port = <default: 3306>

#replicate-do-db =

#replicate-ignore-db =

#replicate-do-table =

#relicate-ignore-table =

#replicate-rewrite-db =

#replicate-wild-do-table =

#replicate-wild-ignore-table =

## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability

#rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0

#rpl_semi_sync_master_timeout = 1000 #in milliseconds , master only setting

## 5.1.x and 5.5.x replication related setting.

#binlog_format = MIXED

## MyISAM Engine

key_buffer = 512K #global buffer

myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes

myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes

myisam_repair_threads = 4 #thread quantity when running repairs

myisam_recover = BACKUP #repair mode, recommend BACKUP

## InnoDB Plugin Dependent Settings

#ignore-builtin-innodb

#plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so

## InnoDB IO Capacity - 5.1.x plugin, 5.5.x

#innodb_io_capacity = 200

## InnoDB IO settings - 5.1.x only

#innodb_file_io_threads = 16

## InnoDB IO settings - 5.5.x and greater

#innodb_write_io_threads = 16

#innodb_read_io_threads = 16

## InnoDB Plugin Independent Settings

innodb_data_home_dir = /var/lib/mysql

innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend

innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128

innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4

innodb_buffer_pool_size = 4G #global buffer

innodb_additional_mem_pool_size = 4M #global buffer

innodb_status_file #extra reporting

innodb_file_per_table #enable always

innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID

innodb_table_locks = 0 #preserve table locks

innodb_log_buffer_size = 128M #global buffer

innodb_lock_wait_timeout = 60

innodb_thread_concurrency = 16 #recommend 2x core quantity

innodb_commit_concurrency = 16 #recommend 4x num disks

#innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI

innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush

skip-innodb-doublewrite

## Binlog sync settings

## XA transactions = 1, otherwise set to 0 for best performance

sync_binlog = 0

## TX Isolation

transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

## Per-Thread Buffer memory utilization equation:

#(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections

## Global Buffer memory utilization equation:

# innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size

[mysqldump]

quick

quote-names

max_allowed_packet = 128M

DBMS 언어셋에 관해서

MySQLBy 관리자 2016년 5월 31일 Leave a comment

charset 은 문자 집합, collation 은 정렬을 뜻한다. 자료형이 왜 필요하나? 먼저 자료형이 왜 필요할지부터 생각해보자. 컴퓨터 프로그램은 프로그래머가 의도한 대로 동작한다. 이 때 같은 목적의 프로그램이라고 할지라도 효율적으로 동작하는 것이 더 좋은 프로그램이 된다. “컴퓨터 알고리즘(Computer Algorithm)” 학문에서는 이를 평가(Performance Analysis)하기 위해서, “시간복잡도(time complexity)“와 “공간복잡도(space complexity)“의 개념을 사용한다. 같은 이유로 데이터베이스 학문도 “더 작은…

ALTER문 정리

MySQLBy 관리자 2015년 4월 22일 Leave a comment

이제서야 이걸 정리하네요. ALTER TABLE 명령어 alter table 명령어는 모르시는 분이 없듯이 테이블의 스키마를 변경 할 수 있게 끔 해주는 아주 유용한 명령어입니다. alter table 명령어가 없으면 아주 끔찍한 일이 벌어질 수도 있습니다. create table…과 drop table의 남발!! 정말 끔찍하지 않습니까? 그런데 테이블의 스키마? = 테이블의 구조 관계형 데이타베이스를 이용할려면 이놈들을 속성(attribute)들로 뭉쳐진 의미있는 정보의 단위로 이끌어…

MySQL버전업 에러 error 1364 hy000 field ssl_cipher doesn t have a default value

MySQLBy 관리자 2015년 3월 23일 Leave a comment

Mysql 버전이 높아지면서 보안관련 인한 오류입니다. User 생성시 Host, User ,Password, ssl_cipher, x509_issuer, x509_subject 를 입력 해 주셔야 합니다. ssl_cipher, x509_issuer, x509_subject 값은 ” 빈값을 입력하세요.

mysql >INSERT INTO user (host,user,password,ssl_cipher,x509_issuer,x509_subject)
-> VALUES('localhost','user',password('pass'),'','','');

1 2	mysql >INSERT INTO user (host,user,password,ssl_cipher,x509_issuer,x509_subject) -> VALUES('localhost','user',password('pass'),'','','');

아니면 왠지 모를 찝찝한 GRANT 처리

mysql >GRANT  SELECT ,  INSERT ,  UPDATE ,  DELETE ,  DROP ,  CREATE 
->  ON  database .*   // for  all tables in  DATABASE  database 
->  TO  'user' @ 'localhost' 
-> IDENTIFIED BY  'password' ;

mysql >GRANT SELECT , INSERT , UPDATE , DELETE , DROP , CREATE

-> ON database .* // for all tables in DATABASE database

-> TO 'user' @ 'localhost'

-> IDENTIFIED BY 'password' ;

요래 하든지

MySQL 캐릭터셋 변경

MySQLBy 관리자 2015년 3월 23일 Leave a comment

MySQL 접속테스트

[@localhost~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 
mysql >

[@localhost~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.1.73 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql >

이제는 MySQL의 인코딩 타입을 확인해보도록 합니다.

mysql >show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | latin1_swedish_ci          |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
| completion_type          | 0                          |
| concurrent_insert        | 1                          |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+
14 rows in set (0.00 sec)
 
mysql >

mysql >show variables like 'c%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

| collation_connection | latin1_swedish_ci |

| collation_database | latin1_swedish_ci |

| collation_server | latin1_swedish_ci |

| completion_type | 0 |

| concurrent_insert | 1 |

| connect_timeout | 10 |

+--------------------------+----------------------------+

14 rows in set (0.00 sec)

mysql >

인코딩 확인 결과 대부분의 character_set이 latin1로 설정이 되어있습니다. 이럴경우 한글깨짐 현상이 발생할 확률이 크므로 latin1인코딩 타입을 utf8 타입으로 변경해주도록 하겠습니다. 리눅스 환경일 경우 my.cnf 파일을 수정해주시면 되겠고 윈도우 환경은 my.ini 파일을 수정해주시면 되겠습니다. 저는 리눅스 환경이므로 my.cnf 파일을 수정해보도록 하겠습니다.

[@localhost ~]#vi /etc/my.cnf
[client]
#추가
default-character-set = utf8
[mysqld]
#추가
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
[mysqldump]
#추가
default-character-set = utf8
[mysql]
#추가
default-character-set = utf8

[@localhost ~]#vi /etc/my.cnf

[client]

#추가

default-character-set = utf8

[mysqld]

#추가

init_connect="SET collation_connection = utf8_general_ci"

init_connect="SET NAMES utf8"

default-character-set = utf8

character-set-server = utf8

collation-server = utf8_general_ci

[mysqldump]

#추가

default-character-set = utf8

[mysql]

#추가

default-character-set = utf8

위 인코딩 설정을 모두 잡아주었다면 데몬을…

맨날 까묵어사서… 정리 한번 하고 갈께요 !!

<?
$today = date("F j, Y, g:i a");                 // March 10, 2001, 5:16 pm
$today = date("m.d.y");                         // 03.10.01
$today = date("j, n, Y");                       // 10, 3, 2001
$today = date("Ymd");                           // 20010310
$today = date('h-i-s, j-m-y, it is w Day z');   // 05-16-17, 10-03-01, 1631 1618 6 Fripm01
$today = date('it is the jS day.');             // It is the 10th day.
$today = date("D M j G:i:s T Y");               // Sat Mar 10 15:16:08 MST 2001
$today = date('H:m:s m is month');              // 17:03:17 m is month
$today = date("H:i:s");                         // 17:16:17
?>

$today = date("F j, Y, g:i a"); // March 10, 2001, 5:16 pm

$today = date("m.d.y"); // 03.10.01

$today = date("j, n, Y"); // 10, 3, 2001

$today = date("Ymd"); // 20010310

$today = date('h-i-s, j-m-y, it is w Day z'); // 05-16-17, 10-03-01, 1631 1618 6 Fripm01

$today = date('it is the jS day.'); // It is the 10th day.

$today = date("D M j G:i:s T Y"); // Sat Mar 10 15:16:08 MST 2001

$today = date('H:m:s m is month'); // 17:03:17 m is month

$today = date("H:i:s"); // 17:16:17

PHP 날짜관련함수 – date()

<?php
// 오늘로부터 5일 후
echo date(“Y-m-d”, strtotime("+5 day"));

// 다음은 오늘로부터 3일 전이 되겠습니다.
echo date(“Y-m-d”, strtotime("-3 day"));

// 오늘로부터 6주 전
$calyoil = 5 – date("w");           //현재 요일에서 금요일을 구하기 위한 가감
$needday = strtotime("-6 week");    // 6주전
$needday = date("Y-m-d" , strtotime("$calyoil days", $needday));
echo $needday;
?>

<?php

// 오늘로부터 5일 후

echo date(“Y-m-d”, strtotime("+5 day"));

// 다음은 오늘로부터 3일 전이 되겠습니다.

echo date(“Y-m-d”, strtotime("-3 day"));

// 오늘로부터 6주 전

$calyoil = 5 – date("w"); //현재 요일에서 금요일을 구하기 위한 가감

$needday = strtotime("-6 week"); // 6주전

$needday = date("Y-m-d" , strtotime("$calyoil days", $needday));

echo $needday;

<?php
// 윤년
if(29==date('t', mktime(0, 0, 0, 2, 1, $year)) {
    echo "윤년";
} else {
    echo "윤년아님";
}

// –mktime(시,분,초,월,일,년도)
// 그 달의 마지막 날짜 가지고 오기
$lastday = date('t', mktime(0, 0, 1, $month, 1, $year));
// 금주의 시작일 구하기
$now=date("w");
$m=$now-1;
$start=date("Y-n-d", strtotime("-$m day"));
?>

<?php

// 윤년

if(29==date('t', mktime(0, 0, 0, 2, 1, $year)) {

echo "윤년";

} else {

echo "윤년아님";

}

// –mktime(시,분,초,월,일,년도)

// 그 달의 마지막 날짜 가지고 오기

$lastday = date('t', mktime(0, 0, 1, $month, 1, $year));

// 금주의 시작일 구하기

$now=date("w");

$m=$now-1;

$start=date("Y-n-d", strtotime("-$m day"));

JS 날짜 정리 .. Month ( 0 ~ 11까지 정의 된다 ) 현재월을 표시하려면 month -1 하여라 .. 날짜 객체 생성 var today = new Date( 년 , 월 , 일 ) ; 날짜 년 가지고 오기 today.getYear(); 날짜 월…

MariaDB Binary Log 삭제하기

MySQL Data directory 변경하기

MySQL 서버 로그관리삭제

MySQL 서버설정 참고

MySQL over 8G Ram

DBMS 언어셋에 관해서

ALTER문 정리

MySQL버전업 에러 error 1364 hy000 field ssl_cipher doesn t have a default value

MySQL 캐릭터셋 변경

날짜관련 함수정리