my.cnf 설정과 서버 용량 문제(+ 스토리지 용량 추가로 해결)

virtual box로 가상머신을 생성한 후에 mariaDB를 다운 받았다.

 

압출을 해제한 후에 해당 디렉토리에 진입했다. 그리고 아래의 명령어를 입력했다.

 

./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/personal-db

스크립트 디렉토리 안의 mysql_install_db.sh을 실행하라는 의미이다.

이 때 조건을 설정해 주었다.

 

./scripts/mysql_install_db : mysql_install_db.sh 실행 (설치 스크립트이다. 이 때 아래의 조건을 추가)

 

--user=mysql : 사용자명은 mysql로 설정

--basedir=/usr/local/mysql : mariadb 관련 파일이 위치한 폴더(나의 경우 /usr/local/mysql)로 설정

--datadir=/data/personal-db : mariaDB에서 생성하는 데이터를 저장하는 폴더는 ( /data/personal-db)로 설정

 

 

[client]
port=3306
default-character-set = utf8
socket= /tmp/mysql.sock

[mysqld]

user=mysql
#mariadb를 사용하는 유저 이름

basedir=/usr/local/mysql
#basedir는 압축해제된 mariadb 폴더가 위치하는 폴더이다.
#나는 ln -s mariadb디렉토리 mysql 로 해주었다.

datadir=/data/personal-db
#datadir는 db에서 만들어지는 데이터가 저장되는 디렉토리이다. 나는 /data/personal-db를
#먼저 만들어 준 후에 여기에 설정해 주었다.

port=3306
innodb-page-size=65536

back_log = 50
bind-address = 0.0.0.0

default-storage-engine = InnoDB

### Character Set ###
init_connect = SET collation_connection = utf8_general_ci
init_connect = SET NAME utf8
character-set-server = utf8
collation-server = utf8_general_ci
character-set-client-handshake = false

### Connections ###
skip-name-resolve
max_connections = 600
#max_connect_error = 2000
wait_timeout = 300
interactive_timeout = 300

### Table Cache ###
table_open_cache = 3000

### Temporary Tables ###
max_heap_table_size = 1G
tmp_table_size = 1G

### Query Cache ###
query_cache_size = 0
query_cache_type = 0

### Thread ###
thread_cache_size = 32
thread_concurrency = 16

### Query_log ###
slow_query_log
long_query_time = 3

### Binary Log ###
log-bin
expire_logs_days = 7
binlog_cache_size = 8M

skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 32M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 64M

### INNO DB SETTING ###
innodb_purge_threads = 1
innodb_file_format = Barracuda
#innodb_support_xa = 0
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_buffer_pool_size = 3G

innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend
#여기를 눈여겨 보라

innodb_write_io_thread = 32
innodb_read_io_thread = 32
innodb_thread_concurrency = 0
innodb_log_file_size = 1000M
innodb_log_files_in_group = 2
innodb_lock_wait_timeout = 120
innodb_open_files = 300
innodb_max_dirty_pages_pct = 90
#innodb_additional_mem_pool_size = 16M
innodb_io_capacity = 300

[mysqldump]
default-character-set = utf8
quick
max_allowed_packet = 16M

[mysql]
default-character-set = utf8
no-auto-rehash

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

[mysqlhotcopy]
interactive-timeout

 

 

오류 발생:

 

[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/personal-db/
Installing MariaDB/MySQL system tables in '/data/personal-db/' ...

2022-01-19 14:19:52 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2022-01-19 14:19:52 0 [Warning] No argument was provided to --log-bin and neither --log-basename or --log-bin-index where used;  This may cause repliction to break when this server acts as a master and has its hostname changed! Please use '--log-basename=localhost.localdomain' or '--log-bin=localhost-bin' to avoid this problem.
2022-01-19 14:19:53 0 [Warning] The parameter innodb_file_format is deprecated and has no effect. It may be removed in future releases. See https://mariadb.com/kb/en/library/xtradbinnodb-file-format/
2022-01-19 14:19:53 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata2' is of a different size 0 pages than specified in the .cnf file: initial 128000 pages, max 0 (relevant if non-zero) pages!
2022-01-19 14:19:53 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-01-19 14:19:54 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-01-19 14:19:54 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-01-19 14:19:54 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-01-19 14:19:54 0 [ERROR] Aborting


Installation of system tables failed!  Examine the logs in
/data/personal-db/ for more information.

The problem could be conflicting information in an external
my.cnf files. You can ignore these by doing:

    shell> ./scripts/mysql_install_db --defaults-file=~/.my.cnf

You can also try to start the mysqld daemon with:

    shell> /usr/local/mysql//bin/mysqld --skip-grant-tables --general-log &

and use the command line tool /usr/local/mysql//bin/mysql
to connect to the mysql database and look at the grant tables:

    shell> /usr/local/mysql//bin/mysql -u root mysql
    mysql> show tables;

Try 'mysqld --help' if you have problems with paths.  Using
--general-log gives you a log in /data/personal-db/ that may be helpful.

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
You can find the latest source at https://downloads.mariadb.org and
the maria-discuss email list at https://launchpad.net/~maria-discuss

Please check all of the above before submitting a bug report
at http://mariadb.org/jira

[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]#

 

저장 용량이 문제였다. 

 

innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend

 

확보 공간이 500MB 필요했는데, 디비 저장 공간이 400메가밖에 남지 않아서 설치가 되지 않았다. (ibdata1 또는 ibdata2 문제는 대부분 용량 크기 부족에서 기인하는 듯하다.)

 

따라서 저장공간을 늘려 주는 방법을 떠올렸다.

 

버추얼 박스 gui를 사용하여 공간을 추가해 주었는데, 이게 가상 저장공간으로 잡히고 물리 저장공간으로 잡히지 않아서 계속해서 같은 에러가 발생하였다.

 

이것을 모르고 다른 데서 원인을 찾다가 회사 동료의 의견에 따라 결국 가상머신을 새로 설치했다. 그의 말에 따르면 가상머신 스토리지는 50GB가 국룰이라는데, 과연 50GB로 늘려 설정하고 가상머신을 구축하고 mariaDB를 설치하자 증상은 사라졌다.

 

사실 과거에 콘솔 명령어를 사용하여 물리 저장공간을 늘려준 경험이 있었지만, 별 내요이 없는테스트용 서버이기도 하고, 무엇보다도 하루 종일 고생한 탓에 머리가 아파 쉬운 방법을 사용하였다...

 

 

 

 

 

 

 

 

동료는 아래의 my.cnf를 사용한다. 추후 내가 사용하는 것과 설정을 비교하기 위해 여기에 남긴다.

 

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
#user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
#
# * Fine Tuning
#
max_connections		= 100
connect_timeout		= 5
wait_timeout		= 600
max_allowed_packet	= 16M
thread_cache_size       = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size		= 128M
#open-files-limit	= 2000
table_open_cache	= 400
myisam_sort_buffer_size	= 512M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit		= 128K
query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
#log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file	= /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit	= 1000
#log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
#log_bin			= /var/log/mysql/mariadb-bin
#log_bin_index		= /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
expire_logs_days	= 10
max_binlog_size         = 100M
# slaves
#relay_log		= /var/log/mysql/relay-bin
#relay_log_index	= /var/log/mysql/relay-bin.index
#relay_log_info_file	= /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine	= InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size	= 50M
innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/