Question MySQL 5.7 my.cnf problem (default plesk)

[Servus]

Hello!
I have big problem. If this is wrong place in forum, please tell me, I will delete thread and copy content to somewhere else, think it is also related general to plesk onyx.
To this year a friend with database experience was optimising my mysql database for many years. Now he is ill and can not do anything at all.
My problem with plesk database 5.7.19 on ubuntu 16 is the my.cnf is total different from mysql 5.6 and 5.5. I was able by myself to replace/upgrade plesk database mysql 5.5 to mysql 5.6 on ubuntu 14 early this year. (for this the old optimised values of my.cnf didn't change and were applied to new version).
Now with 5.7 and plesk onyx 17.5.3 #21 there are no options like normal. The whole content is like in a sandbox modus. I searched for days in many support articles in plesk, ubuntu, mysql. I think it's like running in a different modus (safe mode) by defauld or innodb modul is not activated.
It must be a only a small little setting/option to open my.cnf for normal editing.
Logs are ok (see last box).
I already made two changes weeks ago in [mysqld] it seems ok, but as you see there are missing too much settings in actual 5.7 msql.cnf.
Please, help would be so nice. Sorry for this simple question.

Error.log + Tweaked (optimised) my.cnf from 5.4.2017 (before or after upgrading mysql from 5.5 to 5.6) in attachment


Actual .mycnf 5.7.19 with plesk 17.5.3 Ubuntu 16.04 (Today)

#
# The MySQL database server configuration file.

# You can copy this 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

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
bind-address=127.0.0.1
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#bind-address = ::ffff:127.0.0.1
local-infile=0
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G

Tweaked and optimised for forum and blog software .mycnf 5.5 with plesk 17.0 Ubuntu 14.04 (16.12.2016)

#
# The MySQL database server configuration file.
#
# You can copy this 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]
bind-address=127.0.0.1
#bind-address = ::
skip_name_resolve
local-infile=0
#
# * 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
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.
#
# * Fine Tuning
#
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_cache_size        = 16M
#
# * 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 log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# 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
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * 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 = 3072M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit  = 2                                       
innodb_autoextend_increment     = 512M
# * 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

[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

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

[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.
#
!includedir /etc/mysql/conf.d/

Kindest regards

 

[UFHH01]

Hi Servus,

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Where is the rest here, pls. ? As you can see, there are additional folders included, where additional configuation files exist. It is essential, that you include ALL of your configuration files, if you desire possible suggestions.


Second, I can't see your issue/error/problem here. Could you pls. specify your request, or what you would like to discuss? If you desire additional "optimized" settings at you MySQL - configuration, pls. try at least to insert them, according to the official documentation:

=> MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.19 (2017-07-17, General Availability)​

... and IF you experience issues/errors/problems, you should include them with your next post for further investigations.

 

[Servus]

No, I just stopped mysql after backup whole /var/lib/mysql and .mycnf and compared again actual .mycnf with the older ones.
tweaking is second. Problem is mainly that I can't see all configuration options, in my actual is no section safe mode, nothing, except my two tweaks in the bottom, made by myself.
There is basically missing something.
I found out that the #bind-address = :: is totally different.
Tweaking I want also do, but after I have normal access (not the right word). I think like you said there is missing general options.
When I open psa database in Plesk I see so much greyed-out. Everything is not like usual. Sorry, the most will laugh, perhaps it's obvious for most.
Please, could you tell if I should replace the new mycnf with the old if it's not dangerous or doing sth else.
Thanks for the link, but I don't like the manual of mysql very much, it's overwhelming with informations. Too much, too much which are not needed and hide sometimes a little problem you have. Sometimes other sides refer directly to the problem and don't show a universe of possible options.
Thanks and greets

 

[Servus]

Hi @UFHH01

Two files in /etc/mysql/conf.d :

/etc/mysql/conf.d/mysql.cnf is totally empty:

[mysql]

/etc/mysql/conf.d/mysqldump.cnf

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

Two files in /etc/mysql/mysql.conf.d/

/etc/mysql/mysql.conf.d/mysqld.cnf

#
# The MySQL database server configuration file.
#
# You can copy this 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.

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

[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
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
#
key_buffer_size  = 16M
max_allowed_packet = 16M
thread_stack  = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size        = 16M
#
# * 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 log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# 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
#log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size   = 100M
#binlog_do_db  = include_database_name
#binlog_ignore_db = include_database_name
#
# * 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!
#
# * 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

/etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf

[mysqld_safe]
syslog

Thanks for help...I see now were the main configuration is, but how to fix it to normal (if it's needed). that options are in normal /etc/mysql/.mycnf.
And then, if you want, could you please tell me where to make the changes/tweaks, I mean in which file?

Note for me:

innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_autoextend_increment=512M

2nd try:
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_autoextend_increment=512M

Greets

 

[UFHH01]

Hi Servus,

could you please tell me where to make the changes/tweaks, I mean in which file?

Pls. have a CLOSER look at your old "my.cnf". As you can see, the "InnoDB" settings have been done at

[mysqld]

... so pls. add your modification as well there and report back, if you experience issues/errors/problems.


In addition, pls. note, that it would not harm to TEST ( possible ) modifications/new settings first with a MySQL - clone ( second instance on a different port and within a different "/var/lib/mysqlXXX" - folder, with the configuration example name "my-clone.cnf" ! ), before you implement it to your first MySQL - instance on your server.

 

[Servus]

Thanks very much @UFHH01. Really a good advice with the clone. But for this solution I have to change paths in cnf file(s), cloning the whole database /var/lib/mysql(orig/neu/ganzneu/alt) thats what I did until today morning ~10.00am over 30 hours the whole night.
I made the bold printed recommendations from support of my blog software manufactor the first two I already had implemented since few months under
[mysqld] in /etc/mysql/.mycnf
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_autoextend_increment=512M
innodb_file_per_table=1
innodb_flush_method=O_DIRECT

First time I deleted also ibdata by accident and because of not clear formulated tutorial, first recovery of whole ~mysql.original.
Second time it worked fine, but I had error with

vhost webmail.conf

(first one, then all vhost webmail.configs, then also Zend PHP error by login in Plesk panel. Total mysql/server breakdown and successful recovery after 5 minutes by upload the saved log file 0 and1 and or taking one backup, delete the logfiles, edit mycnf, and start. 5 or 6 times last night/morning
I removed innodb_file_per_table=1 and innodb_flush_method=O_DIRECT of the new tweaks in my.cnf
(every time same procedure: copy the original /mysql/ directory in /var/lib/, mysql stop, editing my.cnf, deleting both log files ib_logfile0 and *1. and start mysql.
Then I had an error with innodb_autoextend_increment=512:

[Warning] option 'innodb_autoextend_increment': unsigned value 1048576 (note by me: *another high value in real) adjusted to 1000.

It seems an old bug from 2009 but I didn't know if I should take MB or nothing after 512 (autoincrement value).
In the end I decided to take my two old simple values

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G

and wait until someone perhaps give me detailed advice like replacing my.cnf with an optimised like the old (16.12.2016) of my helping hand, perhaps with also the ones I made first (see above in this posting) or a similar new suggesting.
Perhaps I should do this optimisation: innodb_file_per_table = 1

In the moment I have one running /mysql/ and 3 backups in /var/lib/.
I want never play and testing so much, better like my friend did before her illness, tweaking the basics with values which work perfectly together and in the right syntax (M or Mb or nothing, for example.
I'm too sad that incredible girl is away who teached me how and why in few words, and disappointed in the moment (took my 2 hours to write this answer), by editing mysql you see clearly the real experts and masters of administration, because this is the heart and soul of every server.

Thanks my friend. I will do it for sure but I need more information what and how.
Best regards