Firstly make sure your directories are owned by mysql where you want data logs and other elements created.
The following had to be set in server.cnf and set in separate sections in some cases
socket in client section
The rest could be safely accommodated in the mysqld section due to running only a single flavour of mysql/mariadb on the server using this cnf file.
[mysqld] #PID file pid-file=/var/run/mysqld/mysqld.pid datadir=/database/mysql/dbfile socket=/database/mysql/mysql.sock
log_error=/database/mysql/serverlog/mysql_error.log slow_query_log_file=/database/mysql/serverlog/mysql_slow.log general_log_file=/database/mysql/serverlog/mysql_general.log general_log_file=/database/mysql/serverlog/mysql_general.log #INNODB logs on separate location innodb_log_group_home_dir=/database/mysql/innodb_logsWe are running multiple log files to allow better log handling.
Hopefully these examples will be of some use to someone contemplating the change to not using the default of /var/lib/mysql and not using /var/log for the logging and not using /var/lib/mysql as a place of other logs such as slow query log
We hit another random error related to MySQL bug #45379. We had added user= and group= to the [mysqld] section and had the server randomly setting the group_concat_max_len to 4 breaking the use of group_concat. Moving those settings to mysqld_safe resolved that. Thanks Pythian for writing about this.
[mysqld] [mysqld_safe] user=mysql group=mysql
Now looking forward to getting the new servers commissioned and into production.
If all the above is not done then the database will not create files in all the expected locations but use defaults and try using /var/lib/mysql, one day this will hopefully gone, instead replaced by an intelligent installer. which will ask questions and build a config file and create directories.
One final note make sure you run mysql_install_db as the mysql user or you will have issues with starting your database once again due to permissions of files created when running the install to create the database. If you have all your config correct with directories in teh config matching actual directories then when you run mysql_install_db you wont require any parameters with it. Simple.
See ya Round