Setting up mariadb with non standard directories

Today I was configuring a mariadb 10.0.16 server with non default directories. There is a lot of things to overcome in the process.

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
[client]
socket=/database/mysql/mysql.sock

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_logs 

We 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

Peter