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

Mariadb 10 Slave wont set up after upgrading from mariadb 5.5

We are in teh process of upgrading mariadb 5.5 databases to maridb 10. The actual upgrade process of the database has been flawless for the stand alone databases. I have started on our first replication set a pre-production system and after upgrading a slave to 10.1.14  have hit a wall

The maria10 database slave not starting up as a slave after upgrading from 5.5 The master is still running as 5.5

This is the error log

141111 10:57:10 [Note] Event Scheduler: Purging the queue. 0 events
141111 10:57:10 [Note] InnoDB: FTS optimize thread exiting.
141111 10:57:10 [Note] InnoDB: Starting shutdown...
141111 10:57:12 [Note] InnoDB: Shutdown completed; log sequence number 36086013855
141111 10:57:12 [Note] /usr/sbin/mysqld: Shutdown complete

141111 10:57:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld-nxq.pid ended
141111 10:57:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/nxq
141111 10:57:13 [Note] InnoDB: Using mutexes to ref count buffer pool pages
141111 10:57:13 [Note] InnoDB: The InnoDB memory heap is disabled
141111 10:57:13 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
141111 10:57:13 [Note] InnoDB: Memory barrier is not used
141111 10:57:13 [Note] InnoDB: Compressed tables use zlib 1.2.3
141111 10:57:13 [Note] InnoDB: Using Linux native AIO
141111 10:57:13 [Note] InnoDB: Using CPU crc32 instructions
141111 10:57:13 [Note] InnoDB: Initializing buffer pool, size = 128.0M
141111 10:57:13 [Note] InnoDB: Completed initialization of buffer pool
141111 10:57:14 [Note] InnoDB: Highest supported file format is Barracuda.
141111 10:57:14 [Note] InnoDB: 128 rollback segment(s) are active.
141111 10:57:14 [Note] InnoDB: Waiting for purge to start
141111 10:57:14 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.20-68.0 started; log sequence number 36086013855
141111 10:57:14 [Note] Plugin 'FEEDBACK' is disabled.
141111 10:57:14 [ERROR] Failed to create a socket for IPv6 '::': errno: 97.
141111 10:57:14 [Note] Server socket created on IP: '0.0.0.0'.
141111 10:57:14 [Note] Event Scheduler: Loaded 0 events
141111 10:57:14 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-log=mysqld-nxq-relay-bin' to avoid this problem.
141111 10:57:14 [ERROR] Server id not set, will not start slave
141111 10:57:14 [ERROR] Failed to create slave threads
141111 10:57:14 [ERROR] Failed to allocate memory for the Master Info structure
141111 10:57:14 [Note] /usr/sbin/mysqld: ready for connections.




This is the server.cnf it isn't as clean and tidy as it should be but it is what it is



[server]

# this is only for the mysqld standalone daemon
[mysqld]
datadir=/var/lib/mysql/nxq
log_error=/var/log/mysqld-nxq.log
socket=/var/lib/mysql/nxq/mysql.sock
pid_file=/var/run/mysqld/mysqld-nxq.pid

user=mysql
group=mysql
port=3306
binlog_format = mixed


# MASTER config
# log-bin=mysql-bin.log

expire_logs_days=7
# Autostart the event scheduler
event_scheduler=on

# SLAVE config
relay_log=mysqld-nxq-relay-bin
log_bin=mysql-bin.log
#expire_logs_days=7
log_slave_updates=1
read_only=1
#skip_slave_start=1

# Performance Tuning

innodb_buffer_pool_size=8G
innodb_file_per_table = 1
innodb_log_file_size=256M
innodb_flush_method = O_DSYNC

max_allowed_packet=700M
#table_cache=2M
#table_definition_cache=2M
#read_buffer_size=1M
#myisam_sort_buffer_size=64M
#thread_cache_size=8
query_cache_size=64M
key_buffer_size=42M
#connect_timeout=2
max_connections=300
#open_files_limit=16384
performance_schema=1

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

server_id=201

I moved the server_ID value to the [mariadb-10.0] section from the [mysqld] section to try and find a solution


Update: Turns out the reason for this behaviour was due to a typo in the init.d startup script in this line defaultfile="/etc/my.cnf" It had to be changed from another location and it had an error. Funnily enough though the strace output was not helpful as it showed what appeared to be it reading the files in the /etc/my.cnf.d directory.

How did I find it? By using the startup process of executing mysqld --help. It displays the default location parameters and i could see the server-id set correctly

See ya round

Peter