How to turn MariaDB?

[Evgeny1]

I want to turn my MariaDB server, but all of my string in /etc/my.cnf ignored.

my.cnf below:
------------------
[mysqld]
bind-address = ::
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[innodb]
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=780M
innodb_additional_mem_pool_size=20M
innodb_read_io_threads=8
innodb_lock_wait_timeout=50
innodb_log_file_size=100M
innodb_log_buffer_size=16M

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

table_cache=4096
tmp_table_size=32m
join_buffer_size = 2M

key_buffer_size=16M
sort_buffer=8M
read_buffer_size=16M

query_cache_size=64M
query_cache_type=1
--------------

For example, if I look through mysql>show variable; I see innodb_buffer_pool_size=128M

 

[Evgeny1]

Centos7, Plesk 12.5

 

[urki]

Hello,

it looks like you do not really know what you are doing, and by changing variables without looking into what they do, you could do more harm than good.

Anyway, the problem lies in [sections].
You should write your lines under "[mysqld]" section in order for them to take effect. What I do is I make the "[mysqld]" section below the "!includedir /etc/my.cnf.d" command, making sure the new values will not get overwritten, and then make the modifications.

So you should:

• remove "[innodb]" section, as it does not exist
• create another "[mysqld]" section below "!includedir /etc/my.cnf.d"
• move and add all your new variables under the newly created "[mysqld]" section

Also, you could try some scripts like http://mysqltuner.com/ to assist you in tuning.
This could also help: https://tools.percona.com/.

 

[Evgeny1]

Thx. But I dont understand, how can I change innodb settings?

 

[urki]

Well, in your case try this:

[mysqld]
bind-address = ::
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=780M
innodb_additional_mem_pool_size=20M
innodb_read_io_threads=8
innodb_lock_wait_timeout=50
innodb_log_file_size=100M
innodb_log_buffer_size=16M

table_cache=4096
tmp_table_size=32m
join_buffer_size = 2M

key_buffer_size=16M
sort_buffer=8M
read_buffer_size=16M

query_cache_size=64M
query_cache_type=1

and restart the mysql server afterwards.

 

[Evgeny1]

I see errors after restart ...

160419 09:21:04 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
160419 09:21:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160419  9:21:05 [Note] /usr/libexec/mysqld (mysqld 5.5.44-MariaDB) starting as process 25028 ...
160419  9:21:05 [Warning] Changed limits: max_open_files: 1024  max_connections: 151  table_cache: 431
160419  9:21:05 InnoDB: The InnoDB memory heap is disabled
160419  9:21:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160419  9:21:05 InnoDB: Compressed tables use zlib 1.2.7
160419  9:21:05 InnoDB: Using Linux native AIO
160419  9:21:05 InnoDB: Initializing buffer pool, size = 256.0M
160419  9:21:05 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
InnoDB: Possible causes for this error:
(a) Incorrect log file is used or log file size is changed
(b) In case default size is used this log file is from 10.0
(c) Log file is corrupted or there was not enough disk space
In case (b) you need to set innodb_log_file_size = 48M
160419  9:21:05 [ERROR] Plugin 'InnoDB' init function returned error.
160419  9:21:05 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160419  9:21:05 [Note] Plugin 'FEEDBACK' is disabled.
160419  9:21:05 [ERROR] Unknown/unsupported storage engine: InnoDB
160419  9:21:05 [ERROR] Aborting

 

[IgorG]

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 104857600 bytes! InnoDB: Possible causes for this error: (a) Incorrect log file is used or log file size is changed (b) In case default size is used this log file is from 10.0 (c) Log file is corrupted or there was not enough disk space In case (b) you need to set innodb_log_file_size = 48M 160419 9:21:05 [ERROR] Plugin 'InnoDB' init function returned error. 160419 9:21:05 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 160419 9:21:05 [Note] Plugin 'FEEDBACK' is disabled. 160419 9:21:05 [ERROR] Unknown/unsupported storage engine: InnoDB 160419 9:21:05 [ERROR] Aborting

Try to fix it with this KB article https://kb.plesk.com/en/124230

 

[urki]

Problem most likely lies in one of your variables, as you probably did not check what they actually do. I would recommend you do to that for every single variable you are about to change.
innodb_log_file_size requires you to do some additional steps, check out this http://www.techoism.com/how-to-resize-mysql-innodb-logs-file-in-centosrhel/