All pages
Powered by GitBook
1 of 15

Starting & Stopping

Learn how to start and stop MariaDB Server. This section provides essential commands and procedures for controlling your database instance's lifecycle, ensuring proper operation.

Starting and Stopping Overview

There are several different methods to start or stop the MariaDB Server process. There are two primary categories that most of these methods fall into: starting the process with the help of a service manager, and starting the process manually.

Service Managers

sysVinit and systemd are the most common Linux service managers. launchd is used in MacOS X. Upstart is a less common service manager.

Systemd

RHEL/CentOS 7 and above, Debian 8 Jessie and above, and Ubuntu 15.04 and above use systemd by default.

For information on how to start and stop MariaDB with this service manager, see systemd: Interacting with the MariaDB Server Process.

SysVinit

RHEL/CentOS 6 and below, and Debian 7 Wheezy and below use sysVinit by default.

For information on how to start and stop MariaDB with this service manager, see sysVinit: Interacting with the MariaDB Server Process.

launchd

launchd is used in MacOS X.

Upstart

Ubuntu 14.10 and below use Upstart by default.

Starting the Server Process Manually

mariadbd

mariadbd is the actual MariaDB Server binary. It can be started manually on its own.

mariadbd-safe

mariadbd-safe is a wrapper that can be used to start the mariadbd server process. The script has some built-in safeguards, such as automatically restarting the server process if it dies. See mariadbd-safe for more information.

mariadbd-multi

mariadbd-multi is a wrapper that can be used to start the mariadbd server process if you plan to run multiple server processes on the same host. See mariadbd-multi for more information.

mysql.server

mysql.server is a wrapper that works as a standard sysVinit script. However, it can be used independently of sysVinit as a regular sh script. The script starts the mariadbd server process by first changing its current working directory to the MariaDB install directory and then starting mariadbd-safe. The script requires the standard sysVinit arguments, such as start, stop, and status. See mysql.server for more information.

This page is licensed: CC BY-SA / Gnu FDL

launchd

In MacOS, create a file called /Library/LaunchDaemons/com.mariadb.server.plist with the following contents (edit to suit):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key> <string>com.mariadb.server</string>
  <key>KeepAlive</key><true/>
  <key>RunAtLoad</key><true/>
  <key>LaunchOnlyOnce</key><false/>
  <key>ExitTimeOut</key><integer>600</integer>
  <key>WorkingDirectory</key><string>/usr/local/var</string>
  <key>Program</key><string>/usr/local/bin/mysqld</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/bin/mysqld</string>
    <string>--user=_mysql</string>
    <string>--basedir=/usr/local/opt/mariadb</string>
    <string>--plugin-dir=/usr/local/opt/mariadb/lib/plugin</string>
    <string>--datadir=/usr/local/var/mysql</string>
    <string>--log-error=/usr/local/var/mysql/Data-Server.local.err</string>
    <string>--pid-file=/usr/local/var/mysql/Data-Server.local.pid</string>
    <string>--sql-mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</string>
  </array>
</dict>
</plist>

Then from a shell, run launchctl load /Library/LaunchDaemons/com.mariadb.server.plist and MariaDB will run immediately, and also upon reboot.

See Also

  • Creating Launch Daemons and Agents

  • A launchd Tutorial

This page is licensed: CC BY-SA / Gnu FDL

systemd

systemd is a sysVinit replacement that is the default service manager on the following Linux distributions:

  • RHEL 7 and above

  • CentOS 7 and above

  • Fedora 15 and above

  • Debian 8 and above

  • Ubuntu 15.04 and above

  • SLES 12 and above

  • OpenSUSE 12.2 and above

MariaDB's systemd unit file is included in the server packages for RPMs and DEBs. It is also included in certain binary tarballs.

The service name is mariadb.service.

Installing & Starting MariaDB

When installing MariaDB server rpm / dep package, it will automatically run the mariadb-install-db script, that creates the initial databases and users.

When MariaDB is started with the systemd unit file, it directly starts the mariadbd process as the mysql user. Unlike with sysVinit, the mariadbd process is not started with mariadbd-safe. As a consequence, options will not be read from the [mariadbd-safe] option group from option files.

Contents of the MariaDB Service's Unit File

The contents of the mariadb.service file can be examined with systemctl show mariadb.service.

Interacting with the MariaDB Server Process

The service can be interacted with by using the systemctl command.

Starting the MariaDB Server Process on Boot

MariaDB's systemd service can be configured to start at boot by executing the following:

sudo systemctl enable mariadb.service

Starting the MariaDB Server Process

MariaDB's systemd service can be started by executing the following:

sudo systemctl start mariadb.service

MariaDB's systemd unit file has a default startup timeout of about 90 seconds on most systems. If certain startup tasks, such as crash recovery, take longer than this default startup timeout, then systemd will assume that mariadbd has failed to startup, which causes systemd to kill the mariadbd process. To work around this, you can reconfigure the MariaDB systemd unit to have an infinite timeout.

Note that systemd 236 added the EXTEND_TIMEOUT_USEC environment variable that allows services to extend the startup timeout during long-running processes. Starting with MariaDB 10.1.33, MariaDB 10.2.15, and MariaDB 10.3.6, on systems with systemd versions that support it, MariaDB uses this feature to extend the startup timeout during certain startup processes that can run long. Therefore, if you are using systemd 236 or later, then you should not need to manually override TimeoutStartSec, even if your startup tasks, such as crash recovery, run for longer than the configured value. See MDEV-14705 for more information.

Stopping the MariaDB Server Process

MariaDB's systemd service can be stopped by executing the following:

sudo systemctl stop mariadb.service

Restarting the MariaDB Server Process

MariaDB's systemd service can be restarted by executing the following:

sudo systemctl restart mariadb.service

Checking the Status of the MariaDB Server Process

The status of MariaDB's systemd service can be obtained by executing the following:

sudo systemctl status mariadb.service

Interacting with Multiple MariaDB Server Processes

A systemd template unit file with the name mariadb@.service is installed in INSTALL_SYSTEMD_UNITDIR on some systems. See Locating the MariaDB Service's Unit File to see what directory that refers to on each distribution.

This template unit file allows you to interact with multiple MariaDB instances on the same system using the same template unit file. When you interact with a MariaDB instance using this template unit file, you have to provide an instance name as a suffix. For example, the following command tries to start a MariaDB instance with the name node1:

sudo systemctl start mariadb@node1.service

MariaDB's build system cannot include the mariadb@.service template unit file in RPM packages on platforms that have cmake versions older than 3.3.0, because these cmake versions have a bug that causes it to encounter errors when packaging a file in RPMs if the file name contains the @ character. MariaDB's RHEL 7 and CentOS 7 RPM build hosts only got a new enough cmake version starting with MariaDB 10.1.39, MariaDB 10.2.23, and MariaDB 10.3.14. To use this functionality on a MariaDB version that does not have the file, you can copy the file from a package that does have the file.

Default configuration of Multiple Instances in 10.4 and Later

systemd will also look for an option file for a specific MariaDB instance based on the instance name.

It will use the .%I as the custom option group suffix that is appended to any server option group, in any configuration file included by default.

In all distributions, the %I is the MariaDB instance name. In the above node1 case, it would use the option file at the path/etc/mynode1.cnf.

When using multiple instances, each instance will of course also need their own datadir, socket and , port (unless [skip_networking](../../../server-usage/replication-cluster-multi-master/optimization-and-tuning/system-variables/server-system-variables.md#skip_networking) is specified). As [mariadb-install-db#option-groups](../../../clients-and-utilities/mariadb-install-db.md#option-groups) reads the same sections as the server, andExecStartPre=run [mariadb-install-db](../../../clients-and-utilities/mariadb-install-db.md) within the service, the instances are autocreated if there is sufficient priviledges.

To use a 10.3 configuration in 10.4 or later and the following customisation in the editor after running sudo systemctl edit mariadb@.service:

[Unit]
ConditionPathExists=

[Service]
Environment='MYSQLD_MULTI_INSTANCE=--defaults-file=/etc/my%I.cnf'

Custom configuration of Multiple Instances in 10.4 and Later

Because users may want to do many various things with their multiple instances, we've provided a way to let the user define how they wish their multiple instances to run. The systemd environment variable MYSQLD_MULTI_INSTANCE can be set to anything that mariadbd and mariadb-install-db will recognise.

A hosting environment where each user has their own instance may look like (with sudo systemctl edit mariadb@.service):

[Service]
ProtectHome=false
Environment='MYSQLD_MULTI_INSTANCE=--defaults-file=/home/%I/my.cnf \
                        --user=%I \
                        --socket=/home/%I.sock \ 
                        --datadir=/home/%I/mariadb_data \
                        --skip-networking'

Here the instance name is the unix user of the service.

Configuring Multiple Instances in 10.3 and Earlier

systemd will also look for an option file for a specific MariaDB instance based on the instance name. By default, it will look for the option file in a directory defined at build time by the INSTALL_SYSCONF2DIR option provided to cmake.

For example, on RHEL, CentOS, Fedora, and other similar Linux distributions, INSTALL_SYSCONF2DIR is defined as /etc/my.cnf.d/, so it will look for an option file that matches the format:

  • /etc/my.cnf.d/my%I.cnf

And on Debian, Ubuntu, and other similar Linux distributions, INSTALL_SYSCONF2DIR is defined as /etc/mysql/conf.d//, so it will look for an option file that matches the format:

  • /etc/mysql/conf.d/my%I.cnf

In all distributions, the %I is the MariaDB instance name. In the above node1 case, it would use the option file at the path/etc/my.cnf.d/mynode1.cnf for RHEL-like distributions and /etc/mysql/conf.d/mynode1.cnf for Debian-like distributions.

When using multiple instances, each instance will of course also need their own datadir. See mariadb-install-db for information on how to initialize the datadir for additional MariaDB instances.

Systemd and Galera Cluster

Bootstrapping a New Cluster

When using Galera Cluster with systemd, the first node in a cluster has to be started with galera_new_cluster. See Getting Started with MariaDB Galera Cluster: Bootstrapping a New Cluster for more information.

Recovering a Node's Cluster Position

When using Galera Cluster with systemd, a node's position in the cluster can be recovered with galera_recovery. See Getting Started with MariaDB Galera Cluster: Determining the Most Advanced Node for more information.

SSTs and Systemd

MariaDB's systemd unit file has a default startup timeout of about 90 seconds on most systems. If an SST takes longer than this default startup timeout on a joiner node, then systemd will assume that mariadbd has failed to startup, which causes systemd to kill the mariadbd process on the joiner node. To work around this, you can reconfigure the MariaDB systemd unit to have an infinite timeout. See Introduction to State Snapshot Transfers (SSTs): SSTs and Systemd for more information.

Note that systemd 236 added the EXTEND_TIMEOUT_USEC environment variable that allows services to extend the startup timeout during long-running processes. Starting with MariaDB 10.1.35, MariaDB 10.2.17, and MariaDB 10.3.8, on systems with systemd versions that support it, MariaDB uses this feature to extend the startup timeout during long SSTs. Therefore, if you are using systemd 236 or later, then you should not need to manually override TimeoutStartSec, even if your SSTs run for longer than the configured value. See MDEV-15607 for more information.

Configuring the Systemd Service

You can configure MariaDB's systemd service by creating a "drop-in" configuration file for the systemd service. On most systems, the systemd service's directory for "drop-in" configuration files is /etc/systemd/system/mariadb.service.d/. You can confirm the directory and see what "drop-in" configuration files are currently loaded by executing:

$ sudo systemctl status mariadb.service
● mariadb.service - MariaDB 10.1.37 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf, timeoutstartsec.conf
...

If you want to configure the systemd service, then you can create a file with the .conf extension in that directory. The configuration option(s) that you would like to change would need to be placed in an appropriate section within the file, usually [Service]. If a systemd option is a list, then you may need to set the option to empty before you set the replacement values. For example:

[Service]

ExecStart=
ExecStart=/usr/bin/numactl --interleave=all  /usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

After any configuration change, you will need to execute the following for the change to go into effect:

sudo systemctl daemon-reload

Useful Systemd Options

Useful systemd options are listed below. If an option is equivalent to a common mariadbd-safe option, then that is also listed. Use systemctl edit mariadb.service to create the systemd option under a [Service] section header.

mariadbd-safe option
systemd option
Comments

no option

ProtectHome=false

If any MariaDB files are in /home/

no option

PrivateDevices=false

If any MariaDB storage references raw block devices

no option

ProtectSystem=

If any MariaDB write any files to anywhere under /boot, /usr or /etc

no option

TimeoutStartSec={time}

Service startup timeout. See Configuring the Systemd Service Timeout.

no option (see MDEV-9264)

OOMScoreAdjust={priority}

e.g. -600 to lower priority of OOM killer for mariadbd

open-files-limit

LimitNOFILE={limit}

Limit on number of open files. See Configuring the Open Files Limit.

core-file-size

LimitCORE={size}

Limit on core file size. Useful when enabling core dumps. See Configuring the Core File Size.

LimitMEMLOCK={size} or infinity

Limit on how much can be locked in memory. Useful when large-pages or memlock is used

nice

Nice={nice value}

syslog

StandardOutput=syslog

See Configuring MariaDB to Write the Error Log to Syslog.

StandardError=syslog

SyslogFacility=daemon

SyslogLevel=err

syslog-tag

SyslogIdentifier

flush-caches

ExecStartPre=/usr/bin/sync

ExecStartPre=/usr/sbin/sysctl -q -w vm.drop_caches=3

malloc-lib

Environment=LD_PRELOAD=/path/to/library

numa-interleave

NUMAPolicy=interleave

from systemd v243 onwards

or: ExecStart=/usr/bin/numactl --interleave=all /usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

prepending ExecStart=/usr/bin/numactl --interleave=all to existing ExecStart setting

no-auto-restart

Restart={exit-status}

Note: the systemd manual contains the official meanings for these options. The manual also lists considerably more options than the ones listed above.

There are other options and the mariadb-service-convert script will attempt to convert these as accurately as possible.

Configuring the Systemd Service Timeout

MariaDB's systemd unit file has a default startup timeout of about 90 seconds on most systems. If a service startup takes longer than this default startup timeout, then systemd will assume that mariadbd has failed to startup, which causes systemd to kill the mariadbd process. To work around this, it can be changed by configuring the TimeoutStartSec option for the systemd service.

A similar problem can happen when stopping the MariaDB service. Therefore, it may also be a good idea to set TimeoutStopSec.

For example, you can reconfigure the MariaDB systemd service to have an infinite timeout by executing one of the following commands:

If you are using systemd 228 or older, then you can execute the following to set an infinite timeout:

sudo systemctl edit mariadb.service

[Service]

TimeoutStartSec=0
TimeoutStopSec=0

Systemd 229 added the infinity option, so if you are using systemd 229 or later, then you can execute the following to set an infinite timeout:

sudo systemctl edit mariadb.service

[Service]

TimeoutStartSec=infinity
TimeoutStopSec=infinity

Note that systemd 236 added the EXTEND_TIMEOUT_USEC environment variable that allows services to extend the startup timeout during long-running processes. On systems with systemd versions that support it, MariaDB uses this feature to extend the startup timeout during certain startup processes that can run long.

Configuring the Open Files Limit

When using systemd, rather than setting the open files limit by setting the open-files-limit option for mariadbd-safe or the open_files_limit system variable, the limit can be changed by configuring the LimitNOFILE option for the MariaDB systemd service. The default is set to LimitNOFILE=16364 in mariadb.service.

For example, you can reconfigure the MariaDB systemd service to have a larger limit for open files by executing the following commands:

sudo systemctl edit mariadb.service

[Service]

LimitNOFILE=infinity

An important note is that setting LimitNOFILE=infinity doesn't actually set the open file limit to infinite.

In systemd 234 and later, setting LimitNOFILE=infinity actually sets the open file limit to the value of the kernel's fs.nr_open parameter. Therefore, in these systemd versions, you may have to change this parameter's value.

The value of the fs.nr_open parameter can be changed permanently by setting the value in /etc/sysctl.conf and restarting the server.

The value of the fs.nr_open parameter can be changed temporarily by executing the sysctl utility. For example:

sudo sysctl -w fs.nr_open=1048576‬

In systemd 233 and before, setting LimitNOFILE=infinity actually sets the open file limit to 65536. See systemd issue #6559 for more information. Therefore, in these systemd versions, it is not generally recommended to set LimitNOFILE=infinity. Instead, it is generally better to set LimitNOFILE to a very large integer. For example:

sudo systemctl edit mariadb.service

[Service]
LimitNOFILE=1048576

Configuring the Core File Size

When using systemd, if you would like to enable core dumps, rather than setting the core file size by setting the core-file-size option for mariadbd-safe, the limit can be changed by configuring the LimitCORE option for the MariaDB systemd service. For example, you can reconfigure the MariaDB systemd service to have an infinite size for core files by executing the following commands:

sudo systemctl edit mariadb.service

[Service]
LimitCORE=infinity

Configuring MariaDB to Write the Error Log to Syslog

When using systemd, if you would like to redirect the error log to the syslog, then that can easily be done by doing the following:

  • Ensure that log_error system variable is not set.

  • Set StandardOutput=syslog.

  • Set StandardError=syslog.

  • Set SyslogFacility=daemon.

  • Set SysLogLevel=err.

For example:

sudo systemctl edit mariadb.service

[Service]

StandardOutput=syslog
StandardError=syslog
SyslogFacility=daemon
SysLogLevel=err

If you have multiple instances of MariaDB, then you may also want to set SyslogIdentifier with a different tag for each instance.

Configuring LimitMEMLOCK

If using --memlock, or the io_uring asyncronious IO in InnoDB in MariaDB 10.6 or above, with a Linux Kernel version < 5.12, you will need to raise the LimitMEMLOCK limit.

sudo systemctl edit mariadb.service

[Service]

LimitMEMLOCK=2M

Note: Prior to MariaDB 10.1.10, the --memlock option could not be used with the MariaDB systemd service.

Configuring Access to Home Directories

MariaDB's systemd unit file restricts access to /home, /root, and /run/user by default. This restriction can be overridden by setting the ProtectHome option to false for the MariaDB systemd service. This is done by creating a "drop-in" directory /etc/systemd/system/mariadb.service.d/ and in it a file with a .conf suffix that contains the ProtectHome=false directive.

You can reconfigure the MariaDB systemd service to allow access to /home by executing the following commands:

sudo systemctl edit mariadb.service

[Service]

ProtectHome=false

Configuring the umask

When using systemd, the default file permissions of mariadbd can be set by setting the UMASK and UMASK_DIR environment variables for the systemd service. For example, you can configure the MariaDB systemd service's umask by executing the following commands:

sudo systemctl edit mariadb.service

[Service]

Environment="UMASK=0750"
Environment="UMASK_DIR=0750"

These environment variables do not set the umask. They set the default file system permissions. See MDEV-23058 for more information.

Keep in mind that configuring the umask this way will only affect the permissions of files created by the mariadbd process that is managed by systemd. The permissions of files created by components that are not managed by systemd, such as mariadb-install-db, will not be affected.

See Specifying Permissions for Schema (Data) Directories and Tables for more information.

Configuring the data directory

When doing a standard binary tarball install the datadir will be under /usr/local/data. The default systemd service file makes the whole /usr directory tree write protected however.

So when just copying the distributed service file a tarball install will not start up, complaining e.g. about

[Warning] Can't create test file /usr/local/.../data/ubuntu-focal.lower-test
[ERROR] mariadbd: File '/usr/local/.../data/aria_log_control' not found (Errcode: 30 "Read-only file system")
[ERROR] mariadbd: Got error 'Can't open file' when trying to use aria control file '/usr/local/.../data/aria_log_control'

So when using a data directory under /usr/local that specific directory needs to be made writable for the service using the ReadWritePaths setting:

sudo systemctl edit mariadb.service

[Service]
ReadWritePaths=/usr/local/mysql/data

Systemd Socket Activation

MariaDB starting with 10.6.0

MariaDB can use systemd's socket activation.

This is an on-demand service for MariaDB that will activate when required.

Systemd socket activation uses a mariadb.socket definition file to define a set of UNIX and TCP sockets. Systemd will listen on these sockets, and when they are connected to, systemd will start the mariadb.service and hand over the socket file descriptors for MariaDB to process the connection.

MariaDB remains running at this point and will have all sockets available and process connections exactly like it did before 10.6.

When MariaDB is shut down, the systemd mariadb.socket remains active, and a new connection will restart the mariadb.service.

Using Systemd Socket Activation

To use MariaDB systemd socket activation, instead of enabling/starting mariadb.service, mariadb.socket is used instead.

So the following commands work exactly like the mariadb.service equivalents.

systemctl start mariadb.socket
systemctl enable mariadb.socket

These files alone only contain the UNIX and TCP sockets and basic network connection information to which will be listening for connections. @mariadb is a UNIX abstract socket, which means it doesn't appear on the filesystem. Connectors based on MariaDB Connector/C will be able to connect with these by using the socket name directly, provided the higher level implementation doesn't try to test for the file's existence first. Some connectors like PHP use mysqlnd that is a pure PHP implementation and as such will only be able to connect to on filesystem UNIX sockets.

With systemd activated sockets there is only a file descriptor limit on the number of listening sockets that can be created.

When to Use Systemd Socket Activation

A common use case for systemd socket activated MariaDB is when there needs to be a quick boot up time. MariaDB needs to be ready to run, but it doesn't need to be running.

The ideal use case for systemd socket activation for MariaDB is for infrastructure providers running many multiple instances of MariaDB, where each instance is dedicated for a user.

Downsides to Using Systemd Socket Activiation

From the time the connection occurs, the client is going to be waiting until MariaDB has fully initialized before MariaDB can process the awaiting connection. If MariaDB was previously hard shutdown and needs to perform an extensive InnoDB rollback, then the activation time may be larger than the desired wait time of the client connection.

Configuring Systemd Socket Activation

When MariaDB is run under systemd socket activation, the usual socket , port, and backlog system variables are ignored, as these settings are contained within the systemd socket definition file.

There is no configuration required in MariaDB to use MariaDB under socket activation.

The systemd options available are from the systemd documentation, however ListenStream and BackLog would be the most common configuration options.

As MariaDB isn't creating these sockets, the sockets don't need to be created with a mysql user. The sockets MariaDB may end up listening to under systemd socket activation, it may have not had the privileges to create itself.

Changes to the default mariadb.socket can be made in the same way as services, systemctl edit mariadb.socket, or using /etc/systemd/system/mariadb.socket.d/someconfig.conf files.

Extra Port

A systemd socket can be configured as an extra_port, by using the[FileDescriptorName=extra](https://www.freedesktop.org/software/systemd/man/systemd.socket.html#FileDescriptorName=)in the.socketfile.

The mariadb-extra.socket is already packaged and ready for use.

Multi-instance socket activation

mariadb@.socket is MariaDB's packaged multi-instance defination. It creates multiple UNIX sockets based on the socket file started.

Starting mariadb@bob.socket will use the mariadb@.socket defination with %I within the defination replaced with "bob".

When something connects to a socket defined there, the mariadb@bob.service will be started.

Systemd Socket Activation for Hosting Service Providers

A systemd socket activation service with multi-instance can provide an on-demand per user access to a hosting service provider's dedicated database.

"User", in this case, refers to the customer of the hosting service provider.

End User Benefits

This provides the following benefits for the user:

  • Each user has their own dedicated instance with the following benefits:

    • The instance is free from the database contention of neighbors on MariaDB shared resources (table cache, connections, etc)

    • The user is free to change their own configuration of MariaDB, within the limits and permissions of the service provider.

    • Database service level backups, like mariadb-backup, are now directly available.

    • A user can install their own plugins.

    • The user can run a different database version to their neighbors.

    • If a user's neighbor triggers a fault in the server, the uder's instance isn't affected.

  • The database runs as their unix user in the server facilitating:

    • User can directly migrate their MariaDB data directory to a different provider.

    • The user's data is protected from other users on a kernel level.

Hosting Service Provider Benefits

In addition to providing user benefits as a sales item, the following are additional benefits for the hosting service provider compared to a monolith service:

  • Without passwords for the database, while still having security, support may be easier.

  • When a user's database isn't active, there is no resource usage, only listening file descriptors by systemd.

  • The socket activation transparently, with a minor startup time, starts the service as required.

  • When the user's database hasn't had any activity after a time, it will deactivate (MDEV-25282).

  • Planned enhancements in InnoDB provide:

    • an on-demand consumption of memory (MDEV-25340 .

    • a proactive reduction in memory (MDEV-25341).

    • a memory resource pressure reduction in memory use (MDEV-24670).

  • The service provider can still cap the user's database memory usage in a ulimit way that a user cannot override in settings.

  • The service provider may choose a CPU/memory/IO based billing to the user on Linux cgroup accounting rather than the available comprared to the rather limited options in CREATE USER.

  • Because a user's database will shutdown when inactive, a database upgrade on the server will not take effect for the user until it passively shuts down, restarts, and then gets reactivated hence reducing user downtime..

Downsides to the Hosting Service Provider

The extra memory used by more instances. This is mitigated by the on-demand activation. The deactivation when idle, and improved InnoDB memory management.

With plenty of medium size database servers running, the Linux OOM kill has the opportunity to kill off only a small number of database servers running rather than everyones.

Example on configuration Items for a per user, systemd socket activitated multi-instance service

From a server pespective the operation would be as follows;

To make the socket ready to connect and systemd will be listening to the socket:

# systemctl start mariadb@username.socket
# systemctl start mariadb-extra@username.socket

To enable this on reboot (the same way as a systemd service):

# systemctl enable mariadb@username.socket
# systemctl enable mariadb-extra@username.socket

A MariaDB Template File

A global template file. Once installed as a user's $HOME/.my.cnf file, it will becomes the default for many applications, and the MariaDB server itself.

# cat /etc/my.cnf.templ
[client]
socket=/home/USER/mariadb.sock

[client-server]
user=USER

[mariadbd]
datadir=/home/USER/mariadb-datadir

Custom Configuration for the Multi-instance Service

This extends/modifies the MariaDB multi-instance service.

The feature of this extension are:

  • that it will autocreate configuration file for user applications

  • It will install the database on first service start

  • auth-root-* in mariadb-install-db means that the user is their own privileged user with unix socket authentication active. This means non-that user cannot access another users service, even with access to the unix socket(s). For more information see unix socket authentication security.

  • If the MariaDB version was upgrade, the upgrade changes are made automatically

  • LimitData places a hard upper limit so the user doesn't exceed a portion of the server resources

# cat /etc/systemd/system/mariadb@.service.d/user.conf
[Service]
User=%I
ProtectHome=false

Environment=MYSQLD_MULTI_INSTANCE="--defaults-file=/home/%I/.my.cnf"

ExecStartPre=
ExecStartPre=/bin/sh -c "[ -f /home/%I/.my.cnf ] || sed -e \"s/USER/%I/g\" /etc/my.cnf.templ > /home/%I/.my.cnf"
ExecStartPre=mkdir -p /home/%I/mariadb-datadir
ExecStartPre=/usr/bin/mariadb-install-db $MYSQLD_MULTI_INSTANCE --rpm \
   --auth-root-authentication-method=socket --auth-root-socket-user=%I
ExecStartPost=/usr/bin/mariadb-upgrade $MYSQLD_MULTI_INSTANCE

# To limit user based tuning
LimitData=768M
# For io_uring use by innodb on < 5.12 kernels
LimitMEMLOCK=1M

Custom Configuration for the Multi-instance Socket

This extends/modifies the MariaDB socket defination to be per user.

Create sockets based on the user of the istance (%I). Permissions are only necessary in the sense that the user can connect to them. It won't matter to the server. Access control is enforced within the server, however if the user web services are run as the user, Mode=777 can be reduced. @mariadb-%I is a abstract unix socket not on the filesystem. It may help if a user is in a chroot. Not all applications can connect to abstract sockets.

# cat /etc/systemd/system/mariadb@.socket.d/user.conf
[Socket]
SocketUser=%I
SocketMode=777
ListenSteam=
ListenStream=@mariadb-%I
ListenStream=/home/%I/mariadb.sock

The extra socket provides the user the ability to access the server when all max-connections are used:

# cat /etc/systemd/system/mariadb-extra@.socket.d/user.conf
[Socket]
SocketUser=%I
SocketMode=777
ListenSteam=
ListenStream=@mariadb-extra-%I
ListenStream=/home/%I/mariadb-extra.sock

Systemd Journal

systemd has its own logging system called the systemd journal. The systemd journal contains information about the service startup process. It is a good place to look when a failure has occurred.

The MariaDB systemd service's journal can be queried by using the journalctl command. For example:

$ sudo journalctl n 20 -u mariadb.service
-- Logs begin at Fri 2019-01-25 13:49:04 EST, end at Fri 2019-01-25 18:07:02 EST. --
Jan 25 13:49:15 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Starting MariaDB 10.1.37 database server...
Jan 25 13:49:16 ip-172-30-0-249.us-west-2.compute.internal mysqld[2364]: 2019-01-25 13:49:16 140547528317120 [Note] /usr/sbin/mysqld (mysqld 10.1.37-MariaDB) starting as process 2364 ...
Jan 25 13:49:17 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Started MariaDB 10.1.37 database server.
Jan 25 18:06:42 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Stopping MariaDB 10.1.37 database server...
Jan 25 18:06:44 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Stopped MariaDB 10.1.37 database server.
Jan 25 18:06:57 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Starting MariaDB 10.1.37 database server...
Jan 25 18:08:32 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: mariadb.service start-pre operation timed out. Terminating.
Jan 25 18:08:32 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Failed to start MariaDB 10.1.37 database server.
Jan 25 18:08:32 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: Unit mariadb.service entered failed state.
Jan 25 18:08:32 ip-172-30-0-249.us-west-2.compute.internal systemd[1]: mariadb.service failed.

Converting mariadbd-safe Options to Systemd Options

mariadb-service-convert is a script included in many MariaDB packages that is used by the package manager to convert mariadbd-safe options to systemd options. It reads any explicit settings in the [mariadbd-safe] option group from option files, and its output is directed to /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf. This helps to keep the configuration the same when upgrading from a version of MariaDB that does not use systemd to one that does.

Implicitly high defaults of open-files-limit may be missed by the conversion script and require explicit configuration. See Configuring the Open Files Limit.

This page is licensed: CC BY-SA / Gnu FDL

sysVinit

sysVinit is one of the most common service managers. On systems that use sysVinit, the mysql.server script is normally installed to /etc/init.d/mysql.

Interacting with the MariaDB Server Process

The service can be interacted with by using the service command.

Starting the MariaDB Server Process on Boot

On RHEL/CentOS and other similar distributions, the chkconfig command can be used to enable the MariaDB Server process at boot:

chkconfig --add mysql
chkconfig --level 345 mysql on

On Debian and Ubuntu and other similar distributions, the update-rc.d command can be used:

update-rc.d mysql defaults

Starting the MariaDB Server Process

service mysql start

Stopping the MariaDB Server Process

service mysql stop

Restarting the MariaDB Server Process

service mysql restart

Checking the Status of the MariaDB Server Process

service mysql status

Manually Installing mysql.server with SysVinit

If you install MariaDB from source or from a binary tarball that does not install mysql.server automatically, and if you are on a system that uses sysVinit, then you can manually install mysql.server with sysVinit. See mysql.server: Manually Installing with SysVinit for more information.

SysVinit and Galera Cluster

Bootstrapping a New Cluster

When using Galera Cluster with sysVinit, the first node in a cluster has to be started with service mysql bootstrap. See Getting Started with MariaDB Galera Cluster: Bootstrapping a New Cluster for more information.

This page is licensed: CC BY-SA / Gnu FDL

mariadbd-safe

The mariadbd-safe startup script is in MariaDB distributions on Linux and Unix. It is a wrapper that starts mariadbd with some extra safety features. For example, if mariadbd-safe notices that mariadbd has crashed, then mariadbd-safe will automatically restart mariadbd.

mariadbd-safe is the recommended way to start mariadbd on Linux and Unix distributions that do not support systemd. Additionally, the mysql.server init script used by sysVinit starts mariadbd with mariadbd-safe by default.

Prior to MariaDB 10.5, the client used to be called mysqld_safe, and can still be accessed under this name, via a symlink in Linux, or an alternate binary in Windows.

Using mariadbd-safe

The command to use mariadbd-safe and the general syntax is:

mariadbd-safe [ --no-defaults | --defaults-file | --defaults-extra-file | --defaults-group-suffix | --print-defaults ] <options> <mariadbd_options>

Options

Many of the options supported by mariadbd-safe are identical to options supported by mariadbd. If an unknown option is provided to mariadbd-safe on the command-line, then it is passed to mariadbd.

mariadbd-safe supports the following options:

Option
Description

--help

Display a help message and exit.

--autoclose

(NetWare only) On NetWare, mariadbd-safe provides a screen presence. When you unload (shut down) the mariadbd-safe NLM, the screen does not by default go away. Instead, it prompts for user input: NLM has terminated; Press any key to close the screen. If you want NetWare to close the screen automatically instead, use the --autoclose option to mariadbd-safe.

--basedir=path

The path to the MariaDB installation directory.

--core-file-size=size

The size of the core file that mariadbd should be able to create. The option value is passed to ulimit -c.

--crash-script=file

Script to call in the event of mariadbd crashing.

--datadir=path

The path to the data directory.

--defaults-extra-file=path

The name of an option file to be read in addition to the usual option files. This must be the first option on the command line if it is used. If the file does not exist or is otherwise inaccessible, the server will exit with an error.

--defaults-file=file_name

The name of an option file to be read instead of the usual option files. This must be the first option on the command line if it is used.

--defaults-group-suffix=#

In addition to the default option groups, also read option groups with this suffix.

--flush-caches

Flush and purge buffers/caches before starting the server.

--ledir=path

If mariadbd-safe cannot find the server, use this option to indicate the path name to the directory where the server is located.

--log-error=file_name

Write the error log to the given file.

--malloc-lib=lib

Preload shared library lib if available. See debugging MariaDB for an example.

--mysqld=prog_nam

The name of the server program (in the ledir directory) that you want to start. This option is needed if you use the MariaDB binary distribution but have the data directory outside of the binary distribution. If mariadbd-safe cannot find the server, use the --ledir option to indicate the path name to the directory where the server is located.

--mysqld-version=suffix

This option is similar to the --mysqld option, but you specify only the suffix for the server program name. The basename is assumed to be mysqld. For example, if you use--mysqld-version=debug, mariadbd-safe starts the mariadbd-debug program in the ledir directory. If the argument to --mysqld-version is empty, mariadbd-safe uses mysqld in the ledir directory.

--nice=priority

Use the nice program to set the server´s scheduling priority to the given value.

--no-defaults

Do not read any option files. This must be the first option on the command line if it is used.

--no-watch, --nowatch, --no-auto-restart

Exit after starting mariadbd.

--numa-interleave

Run mariadbd with its memory interleaved on all NUMA nodes.

--open-files-limit=count

The number of files that mariadbd should be able to open. The option value is passed to ulimit -n. Note that you need to start mariadbd-safe as root for this to work properly.

--pid-file=file_name

The path name of the process ID file.

--plugin-dir=dir_name

Directory for client-side plugins.

--port=port_num

The port number that the server should use when listening for TCP/IP connections. The port number must be 1024 or higher unless the server is started by the root system user.

--print-defaults

Print the program argument list and exit.

--skip-kill-mysqld

Do not try to kill stray mariadbd processes at startup. This option works only on Linux.

--socket=path

The Unix socket file that the server should use when listening for local connections.

--syslog, --skip-syslog

--syslog causes error messages to be sent to syslog on systems that support the logger program. --skip-syslog suppresses the use of syslog; messages are written to an error log file.

--syslog-tag=tag

For logging to syslog, messages from mariadbd-safe and mariadbd are written with a tag of mariadbd-safe and mariadbd, respectively. To specify a suffix for the tag, use --syslog-tag=tag, which modifies the tags to be mariadbd-safe-tag and mariadbd-tag.

--timezone=timezone

Set the TZ time zone environment variable to the given option value. Consult your operating system documentation for legal time zone specification formats. Also see Time Zones.

--user={user_name or user_id}

Run the mariadbd server as the user having the name user_name or the numeric user ID user_id. (“User” in this context refers to a system login account, not a MariaDB user listed in the grant tables.)

Option Files

In addition to reading options from the command-line, mariadbd-safe can also read options from option files. If an unknown option is provided to mariadbd-safe in an option file, then it is ignored.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

Option
Description

--print-defaults

Print the program argument list and exit.

--no-defaults

Don't read default options from any option file.

--defaults-file=#

Only read default options from the given file #.

--defaults-extra-file=#

Read this file after the global files are read.

--defaults-group-suffix=#

In addition to the default option groups, also read option groups with this suffix.

Option Groups

mariadbd-safe reads options from the following option groups from option files:

Group
Description

[mysqld_safe]

Options read by mysqld_safe, which includes both MariaDB Server and MySQL Server.

[safe_mysqld]

Options read by mysqld_safe, which includes both MariaDB Server and MySQL Server.

[mariadbd-safe]

Options read by mariadbd_safe_safe from MariaDB Server. Available starting with MariaDB 10.4.6.

[mariadb-safe]

Options read by mysqld_safe from MariaDB Server. Deprecated, please avoid using this.

The [safe_mariadbd] option group is primarily supported for backward compatibility. You should rename such option groups to [mariadbd-safe] in MariaDB installations to prevent breakage in the future if this compatibility is removed.

mariadbd-safe also reads options from the following server option groups from option files:

Group
Description

[mysqld]

Options read by mysqld, which includes both MariaDB Server and MySQL Server.

[server]

Options read by MariaDB Server.

[mysqld-X.Y]

Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example, [mysqld-5.5].

[mariadb]

Options read by MariaDB Server.

[mariadb-X.Y]

Options read by a specific version of MariaDB Server.

[client-server]

Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients.

[galera]

Options read by a galera-capable MariaDB Server. Available on systems compiled with Galera support.

For example, if you specify the log_error option in a server option group in an option file, like this:

[mariadb]
log_error=error.log

Then mariadbd-safe will also use this value for its own --log-error option:

Configuring the Open Files Limit

When using mariadbd-safe, the system's open files limit can be changed by providing the --open-files-limit option either on the command-line or in an option file. For example:

[mariadbd-safe]
open_files_limit=4294967295

The option value is passed to ulimit -n. Note that you need to start mariadbd-safe as root for this to work properly. However, you can't currently set this to unlimited. See MDEV-18410 about that.

When mariadbd-safe starts mariadbd, it also uses this option to set the value of the open_files_limit system variable for mariadbd.

Configuring the Core File Size

When using mariadbd-safe, if you would like to enable core dumps, the system's core file size limit can be changed by providing the --core-file-size option either on the command-line or in an option file. For example:

[mariadbd-safe]
core_file_size=unlimited

The option value is passed to ulimit -c. Note that you need to start mariadbd-safe as root for this to work properly.

Configuring MariaDB to Write the Error Log to Syslog

When using mariadbd-safe, if you would like to redirect the error log to the syslog, then that can easily be done by using the --syslog option. mariadbd-safe redirects two types of log messages to the syslog--its own log messages, and log messages for mariadbd.

  • mariadbd-safe configures its own log messages to go to the daemon syslog facility. The log level for these messages is either notice or error, depending on the specific type of log message. The default tag is mariadbd-safe.

  • mariadbd-safe also configures the log messages for mariadbd to go to the daemon syslog facility. The log level for these messages is error. The default tag is mariadbd.

Sometimes it can be helpful to add a suffix to the syslog tag, such as if you are running multiple instances of MariaDB on the same host. To add a suffix to each syslog tag, use the --syslog-tag option.

Specifying mariadbd

By default, mariadbd-safe tries to start an executable named mariadbd.

You can also specify another executable for mariadbd-safe to start instead of mariadbd by providing the --mariadbd or --mariadbd-version options either on the command-line or in an option file.

By default, it will look for mariadbd in the following locations in the following order:

  • $BASEDIR/libexec/mysqld

  • $BASEDIR/sbin/mysqld

  • $BASEDIR/bin/mysqld

  • $PWD/bin/mysqld

  • $PWD/libexec/mysqld

  • $PWD/sbin/mysqld

  • @libexecdir@/mysql

Where $BASEDIR is set by the --basedir option, $PWD is the current working directory where mariadbd-safe was invoked, and @libexecdir@ is set at compile-time by the INSTALL_BINDIR option for cmake.

You can also specify where the executable is located by providing the --ledir option either on the command-line or in an option file.

Specifying datadir

By default, mariadbd-safe will look for the datadir in the following locations in the following order:

  • $BASEDIR/data/mysql

  • $BASEDIR/data

  • $BASEDIR/var/mysql

  • $BASEDIR/var

  • @localstatedir@

Where $BASEDIR is set by the --basedir option, and @localstatedir@ is set at compile-time by the INSTALL_MYSQLDATADIR option for cmake.

You can also specify where the datadir is located by providing the --datadir option either on the command-line or in an option file.

Logging

When you use mariadbd-safe to start mariadbd, mariadbd-safe logs to the same destination as mariadbd.

mariadbd-safe has several log-related options:

  • --syslog: Write error messages to syslog on systems that support the logger program.

  • --skip-syslog: Do not write error messages to syslog. Messages are written to the default error log file (host_name.err in the data directory), or to a named file if the --log-error option is given.

  • --log-error=file_name: Write error messages to the named error file.

If none of these options is provided, then the default is --skip-syslog.

If --syslog and --log-error are both provided, then a warning is issued and --log-error takes precedence.

mariadbd-safe also writes notices to stdout and errors to stderr.

Editing mariadbd-safe

mariadbd-safe is a sh script, so if you need to change its behavior, then it can easily be edited. However, you should not normally edit the script. A lot of behavior can be changed by providing options either on the command-line or in an option file.

If you do edit mariadbd-safe, then you should be aware of the fact that a package upgrade can overwrite your changes. If you would like to preserve your changes, be sure to have a backup.

NetWare

On NetWare, mariadbd-safe is a NetWare Loadable Module (NLM) that is ported from the original Unix shell script. It starts the server as follows:

  1. Runs a number of system and option checks.

  2. Runs a check on MyISAM tables.

  3. Provides a screen presence for the MariaDB server.

  4. Starts mariadbd, monitors it, and restarts it if it terminates in error.

  5. Sends error messages from mariadbd to the host_name.err file in the data directory.

  6. Sends mariadbd-safe screen output to the host_name.safe file in the data directory.

See Also

  • How to increase max number of open files on Linux. This can be used to solve issues like this warning from mariadbd: Changed limits: max_open_files: 1024 (requested 5000)"

  • mariadbd Options

  • systemd

This page is licensed: CC BY-SA / Gnu FDL

mariadbd-multi

Before using mariadbd-multi be sure that you understand the meanings of the options that are passed to the mariadbd servers and why you would want to have separate mariadbd processes. Beware of the dangers of using multiple mariadbd servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system.

The mariadbd-multi startup script is in MariaDB distributions on Linux and Unix. It is a wrapper that is designed to manage several mariadbd processes running on the same host.

Prior to MariaDB 10.5, the client was called mysqld_multi. It can still be accessed under this name, via a symlink in Linux, or an alternate binary in Windows.

In order for multiple mariadbd processes to work on the same host, these processes must:

  • Use different Unix socket files for local connections.

  • Use different TCP/IP ports for network connections.

  • Use different data directories.

  • Use different process ID files (specified by the --pid-file option) if using mariadbd-safe to start mariadbd.

mariadbd-multi can start or stop servers, or report their current status.

Using mariadbd-multi

The command to use mariadbd-multi and the general syntax is:

mariadbd-multi [options] {start|stop|report} [GNR[,GNR] ...]

start, stop, and report indicate which operation to perform.

You can specify which servers to perform the operation on by providing one or more GNR values. GNR refers to an option group number, and it is explained more in the option groups section below. If there is no GNR list, then mariadbd-multi performs the operation for all GNR values found in its option files.

Multiple GNR values can be specified as a comma-separated list. GNR values can also be specified as a range by separating the numbers by a dash. There must not be any whitespace characters in the GNR list.

For example:

This command starts a single server using option group [mariadbd17]:

mariadbd-multi start 17

This command stops several servers, using option groups [mariadbd8] and [mariadbd10] through [mariadbd13]:

mariadbd-multi stop 8,10-13

Options

mariadbd-multi supports the following options:

Option
Description

--example

Give an example of a config file with extra information.

--help

Display help and exit.

--log=filename

Specify the path and name of the log file. If the file exists, log output is appended to it.

--mysqladmin=prog_name

The mariadb-admin binary to be used to stop servers. Can be given within groups [mariadbd#].

--mariadbd=prog_name

The mariadbd binary to be used. Note that you can also specify mariadbd-safe as the value for this option. If you use mariadbd-safe to start the server, you can include the mariadbd or ledir options in the corresponding [mariadbdN] option group. These options indicate the name of the server that mariadbd-safe should start and the path name of the directory where the server is located. Example:[mariadbd38]mariadbd = mariadbd-debugledir = /opt/local/mysql/libexec.

--no-log

Print to stdout instead of the log file. By default the log file is turned on.

--password=password

The password of the MariaDB account to use when invoking mariadb-admin. Note that the password value is not optional for this option, unlike for other MariaDB programs.

--silent

Silent mode; disable warnings.

--tcp-ip

Connect to the MariaDB server(s) via the TCP/IP port instead of the UNIX socket. This affects stopping and reporting. If a socket file is missing, the server may still be running, but can be accessed only via the TCP/IP port. By default connecting is done via the UNIX socket. This option affects stop and report operations.

--user=username

The user name of the MariaDB account to use when invoking mariadb-admin.

--verbose

Be more verbose.

--version

Display version information and exit.

--wsrep-new-cluster

Bootstrap a cluster. Added in MariaDB 10.1.15.

Option Files

In addition to reading options from the command-line, mariadbd-multi can also read options from option files. If an unknown option is provided to mariadbd-multi in an option file, then it is ignored.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

Option
Description

--print-defaults

Print the program argument list and exit.

--no-defaults

Don't read default options from any option file.

--defaults-file=#

Only read default options from the given file #.

--defaults-extra-file=#

Read this file after the global files are read.

--defaults-group-suffix=#

In addition to the default option groups, also read option groups with this suffix.

Option Groups

mariadbd-multi reads options from the following option groups from option files:

Group
Description

[mysqld_multi]

Options read by mysqld_multi, which includes both MariaDB Server and MySQL Server.

mariadbd-multi also searches option files for option groups with names like [mariadbdN], where N can be any positive integer. This number is referred to in the following discussion as the option group number, or GNR:

Group
Description

[mysqldN]

Options read by a mysqld instance managed by mysqld_multi, which includes both MariaDB Server and MySQL Server. The N refers to the instance's GNR.

GNR values distinguish option groups from one another and are used as arguments to mariadbd-multi to specify which servers you want to start, stop, or obtain a status report for. The GNR value should be the number at the end of the option group name in the option file. For example, the GNR for an option group named [mariadbd17] is 17.

Options listed in these option groups are the same that you would use in the regular server option groups used for configuring mariadbd. However, when using multiple servers, it is necessary that each one use its own value for options such as the Unix socket file and TCP/IP port number.

The [mariadbd-multi] option group can be used for options that are needed for mariadbd-multi itself. [mariadbdN] option groups can be used for options passed to specific mariadbd instances.

The regular server option groups can also be used for common options that are read by all instances:

Group
Description

[mysqld]

Options read by mysqld, which includes both MariaDB Server and MySQL Server.

[server]

Options read by MariaDB Server.

[mysqld-X.Y]

Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example, [mysqld-5.5].

[mariadb]

Options read by MariaDB Server.

[mariadb-X.Y]

Options read by a specific version of MariaDB Server.

[client-server]

Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients.

[galera]

Options read by a galera-capable MariaDB Server. Available on systems compiled with Galera support.

For an example of how you might set up an option file, use this command:

mariadbd-multi --example

Authentication and Privileges

Make sure that the MariaDB account used for stopping the mariadbd processes (with the mariadb-admin utility) has the same user name and password for each server. Also, make sure that the account has the SHUTDOWN privilege. If the servers that you want to manage have different user names or passwords for the administrative accounts, you might want to create an account on each server that has the same user name and password. For example, you might set up a common multi_admin account by executing the following commands for each server:

shell> mysql -u root -S /tmp/mysql.sock -p
Enter password:
mysql> GRANT SHUTDOWN ON *.*
 -> TO ´multi_admin´@´localhost´ IDENTIFIED BY ´multipass´;

Change the connection parameters appropriately when connecting to each one. Note that the host name part of the account name must allow you to connect as multi_admin from the host where you want to run mariadbd-multi.

User Account

Make sure that the data directory for each server is fully accessible to the Unix account that the specific mariadbd process is started as. If you run the mariadbd-multi script as the Unix root account, and if you want the mariadbd process to be started with another Unix account, then you can use the --user option with mariadbd. If you specify the --user option in an option file, and if you did not run the mariadbd-multi script as the Unix root account, then it will just log a warning and the mariadbd processes are started under the original Unix account.

Do not run the mariadbd process as the Unix root account, unless you know what you are doing.

Example

The following example shows how you might set up an option file for use with mariadbd-multi. The order in which the mariadbd programs are started or stopped depends on the order in which they appear in the option file. Group numbers need not form an unbroken sequence. The first and fifth [mariadbdN] groups were intentionally omitted from the example to illustrate that you can have “gaps” in the option file. This gives you more flexibility.

# This file should probably be in your home dir (~/.my.cnf)
           # or /etc/my.cnf
           # Version 2.1 by Jani Tolonen
           [mariadbd-multi]
           mariadbd     = /usr/local/bin/mariadbd-safe
           mysqladmin = /usr/local/bin/mysqladmin
           user       = multi_admin
           password   = multipass
           [mariadbd2]
           socket     = /tmp/mysql.sock2
           port       = 3307
           pid-file   = /usr/local/mysql/var2/hostname.pid2
           datadir    = /usr/local/mysql/var2
           language   = /usr/local/share/mysql/english
           user       = john
           [mariadbd3]
           socket     = /tmp/mysql.sock3
           port       = 3308
           pid-file   = /usr/local/mysql/var3/hostname.pid3
           datadir    = /usr/local/mysql/var3
           language   = /usr/local/share/mysql/swedish
           user       = monty
           [mariadbd4]
           socket     = /tmp/mysql.sock4
           port       = 3309
           pid-file   = /usr/local/mysql/var4/hostname.pid4
           datadir    = /usr/local/mysql/var4
           language   = /usr/local/share/mysql/estonia
           user       = tonu
           [mariadbd6]
           socket     = /tmp/mysql.sock6
           port       = 3311
           pid-file   = /usr/local/mysql/var6/hostname.pid6
           datadir    = /usr/local/mysql/var6
           language   = /usr/local/share/mysql/japanese
           user       = jani

This page is licensed: CC BY-SA / Gnu FDL

mariadbd Option

This page lists all of the options for mariadbd (called mysqld before MariaDB 10.5), ordered by topic. For a full alphabetical list of all mariadbd options, as well as server and status variables, see Full list of MariaDB options, system and status variables.

In many cases, the entry here is a summary, and links to the full description.

By convention, server variables have usually been specified with an underscore in the configuration files, and a dash on the command line. You can however specify underscores as dashes - they are interchangeable.

See Configuring MariaDB with Option Files for which files and groups mariadbd reads for it's default options.

Prior to MariaDB 10.5, the client used to be called mysqld, and can still be accessed under this name, via a symlink in Linux, or an alternate binary in Windows.

Option Prefixes

--autoset-*

  • Description: Sets the option value automatically. Only supported for certain options.

--disable-*

  • Description: For all boolean options, disables the setting (equivalent to setting it to 0). Same as --skip.

--enable-*

  • Description: For all boolean options, enables the setting (equivalent to setting it to 1).

--loose-*

  • Description: Don't produce an error if the option doesn't exist.

--maximum-*

  • Description: Sets the maximum value for the option.

--skip-*

  • Description: For all boolean options, disables the setting (equivalent to setting it to 0). Same as --disable.

Option File Options

--defaults-extra-file

  • Commandline: --defaults-extra-file=name

  • Description: Read this extra option file after all other option files are read.

    • See Configuring MariaDB with Option Files.

--defaults-file

  • Commandline: --defaults-file=name

  • Description: Only read options from the given option file.

    • See Configuring MariaDB with Option Files.

--defaults-group-suffix

  • Commandline: --defaults-group-suffix=name

  • Description: In addition to the default option groups, also read option groups with the given suffix.

    • See Configuring MariaDB with Option Files.

--no-defaults

  • Commandline: --no-defaults

  • Description: Don't read options from any option file.

    • See Configuring MariaDB with Option Files.

--print-defaults

  • Commandline: --print-defaults

  • Description: Read options from option files, print all option values, and then exit the program.

    • See Configuring MariaDB with Option Files.

Compatibility Options

The following options have been added to MariaDB to make it more compliant with other MariaDB and MySQL versions. Options that are also system variables are listed after:

-a, --ansi

  • Description: Use ANSI SQL syntax instead of MariaDB syntax. This mode will also set transaction isolation level serializable.

--new

  • Description: Use new functionality that will exist in next version of MariaDB. This function exists to make it easier to prepare for an upgrade.

--old-style-user-limits

  • Description: Enable old-style user limits (before MySQL 5.0.3, user resources were counted per each user+host vs. per account).

--safe-mode

  • Description: Disable some potential unsafe optimizations. For 5.2, INSERT DELAYED is disabled, myisam_recover_options is set to DEFAULT (automatically recover crashed MyISAM files) and the query cache is disabled. For Aria tables, disable bulk insert optimization to enable one to use aria_read_log to recover tables even if tables are deleted (good for testing recovery).

--skip-new

  • Description: Disables --new.

Compatibility Options and System Variables

  • --old

  • --old-alter-table

  • --old-mode

  • --old-passwords

  • --show-old-temporals

Locale Options

Options that are also system variables are listed after:

--character-set-client-handshake

  • Commandline: --character-set-client-handshake

  • Description: Don't ignore client side character set value sent during handshake. --skip-character-set-client-handshake will ignore the client value and use the default server value.

--default-character-set

  • Commandline: --default-character-set=name

  • Description: Still available as an option for setting the default character set for clients and their connections, it was deprecated and removed in MariaDB 10.2 as a server option. Use character-set-server instead.

--language

  • Description: This option can be used to set the server's language for error messages. This option can be specified either as a language name or as the path to the directory storing the language's error message file. See Server Locales for a list of supported locales and their associated languages.

    • This option is deprecated. Use the lc_messages and lc_messages_dir system variables instead.

    • See Setting the Language for Error Messages for more information.

Locale Options and System Variables

  • character-set-filesystem

  • character-set-client

  • character-set-connection

  • character-set-database

  • character-set-filesystem

  • character-set-results

  • character-set-server

  • character-set-system

  • character-sets-dir

  • collation-connection

  • collation-database

  • collation-server

  • default-week-format

  • default-time-zone

  • lc-messages

  • lc-messages-dir

  • lc-time-names

Windows Options

Options that are also system variables are listed after:

--console

  • Description: Windows-only option that keeps the console window open and for writing log messages to stderr and stdout. If specified together with --log-error, the last option will take precedence.

--install

  • Description: Windows-only option that installs the mariadbd process as a Windows service.

    • The Windows service created with this option auto-starts. If you want a service that is started on demand, then use the --install-manual option.

    • This option takes a service name as an argument. If this option is provided without a service name, then the service name defaults to "MARIADB".

    • This option is deprecated and may be removed in a future version. See MDEV-19358 for more information.

--install-manual

  • Description: Windows-only option that installs the mariadbd process as a Windows service.

    • The Windows service created with this option is started on demand. If you want a service that auto-starts, use the --install option.

    • This option takes a service name as an argument. If this option is provided without a service name, then the service name defaults to "MARIADB".

    • This option is deprecated and may be removed in a future version. See MDEV-19358 for more information.

--remove

  • Description: Windows-only option that removes the Windows service created by the --install or --install-manual options.

    • This option takes a service name as an argument. If this option is provided without a service name, then the service name defaults to "MARIADB".

    • This option is deprecated and may be removed in a future version. See MDEV-19358 for more information.

--slow-start-timeout

  • Description: Windows-only option that defines the maximum number of milliseconds that the service control manager should wait before trying to kill the Windows service during startup. Defaults to 15000.

--standalone

  • Description: Windows-only option that has no effect. Kept for compatibility reasons.

Windows Options and System Variables

The following options and system variables are related to using MariaDB on Windows:

  • --named-pipe

Replication and Binary Logging Options

The following options are related to replication and the binary log. Options that are also system variables are listed after:

--abort-slave-event-count

  • Commandline: --abort-slave-event-count=#

  • Description: Option used by mysql-test for debugging and testing of replication.

--binlog-do-db

  • Commandline: --binlog-do-db=name

  • Description: This option allows you to configure a replication master to write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.

    • This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

    • This option can not be set dynamically. Available as a system variable from MariaDB 11.2.0.

    • When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.

    • See Replication Filters for more information.

--binlog-ignore-db

  • Commandline: --binlog-ignore-db=name

  • Description: This option allows you to configure a replication master to not write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.

    • This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

    • This option can not be set dynamically. Available as a system variable from MariaDB 11.2.0.

    • When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.

    • See Replication Filters for more information.

--binlog-row-event-max-size

  • Commandline: --binlog-row-event-max-size=#

  • Description: The maximum size of a row-based binary log event in bytes. Rows will be grouped into events smaller than this size if possible. The value has to be a multiple of 256. Available as a system variable from MariaDB 11.2.0.

  • Default value 8192

--disconnect-slave-event-count

  • Commandline: --disconnect-slave-event-count=#

  • Description: Option used by mysql-test for debugging and testing of replication.

--flashback

  • Commandline: --flashback

  • Description: Setup the server to use flashback. This enables the binary log and sets binlog_format=ROW.

--init-rpl-role

  • Commandline: --init-rpl-role=name

  • Description: Set the replication role. From MariaDB 10.6.19, MariaDB 10.11.9, MariaDB 11.1.6, MariaDB 11.2.5, MariaDB 11.4.3 and MariaDB 11.5.2, changes the condition for semi-sync recovery to truncate the binlog to instead use this option, when set to SLAVE. This allows for both rpl_semi_sync_master_enabled and rpl_semi_sync_slave_enabled to be set for a primary that is restarted, and no transactions will be lost, so long as --init-rpl-role is not set to SLAVE. In earlier versions, for servers configured with both rpl_semi_sync_master_enabled=1 and rpl_semi_sync_slave_enabled=1, if a primary is just re-started (i.e. retaining its role as primary), it can truncate its binlog to drop transactions which its replica(s) have already received and executed. If this happens, when the replica reconnects, its gtid_slave_pos can be ahead of the recovered primary’s gtid_binlog_pos, resulting in an error state where the replica’s state is ahead of the primary’s. See -init-rpl-role.

  • Valid values: Empty, MASTER or SLAVE

--log-basename

  • Commandline: --log-basename=name

  • Description: Basename for all log files and the .pid file. This sets all log file names at once (in 'datadir') and is normally the only option you need for specifying log files. This is especially recommended to be set if you are using replication as it ensures that your log file names are not dependent on your host name. Sets names for the binary log, relay log, general query log, slow query log and error log. Note that if you explicity set log file names with any of these other options; log-bin-index, relay-log, relay-log-index, general-log-file, log_slow_query_file (slow_query_log_file), log_error, and pid-file, these should be placed after --log-basename in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.

--log-bin-trust-routine-creators

  • Commandline: --log-bin-trust-routine-creators

  • Description: Deprecated, use log-bin-trust-function-creators.

--master-host

  • Commandline: --master-host=name

  • Description: Primary hostname or IP address for replication. If not set, the replica thread will not be started. Note that the setting of master-host will be ignored if there exists a valid master.info file.

--master-info-file

  • Commandline: --master-info-file=name

  • Description: Name and location of the file on the replica where the MASTER_LOG_FILE and MASTER_LOG_POS options (i.e. the binary log position on the primary) and most other CHANGE MASTER options are written. The replica's I/O thread keeps this binary log position updated as it downloads events.

    • See CHANGE MASTER TO: Option Persistence for more information.

--master-password

  • Commandline: --master-password=name

  • Description: The password the replica thread will authenticate with when connecting to the primary. If not set, an empty password is assumed. The value in master.info will take precedence if it can be read.

--master-port

  • Commandline: --master-port=#

  • Description: The port the master is listening on. If not set, the compiled setting of MYSQL_PORT is assumed. If you have not tinkered with configure options, this should be 3306. The value in master.info will take precedence if it can be read.

--master-retry-count

  • Commandline: --master-retry-count=#

  • Description: Number of times a replica will attempt to connect to a primary before giving up. The retry interval is determined by the MASTER_CONNECT_RETRY option for the CHANGE MASTER statement. A value of 0 means the replica will not stop attempting to reconnect. Reconnects are triggered when a replica has timed out. See slave_net_timeout.

  • Default Value: 86400 through 10.5, 100000 as of 10.6

  • Range - 32 bit: 0 to 4294967295

  • Range - 64 bit: 0 to 18446744073709551615

--master-ssl

  • Commandline: --master-ssl

  • Description: Enable the replica to connect to the master using TLS.

--master-ssl-ca

  • Commandline: --master-ssl-ca[=name]

  • Description: Master TLS CA file. Only applies if you have enabled master-ssl.

--master-ssl-capath

  • Commandline: --master-ssl-capath[=name]

  • Description: Master TLS CA path. Only applies if you have enabled master-ssl.

--master-ssl-cert

  • Commandline: --master-ssl-cert[=name]

  • Description: Master TLS certificate file name. Only applies if you have enabled master-ssl.

--master-ssl-cipher

  • Commandline: --master-ssl-cipher[=name]

  • Description: Master TLS cipher. Only applies if you have enabled master-ssl.

--master-ssl-key

  • Commandline: --master-ssl-key[=name]

  • Description: Master TLS keyfile name. Only applies if you have enabled master-ssl.

--master-user

  • Commandline: --master-user=name

  • Description: The username the replica thread will use for authentication when connecting to the primary. The user must have FILE privilege. If the primary user is not set, user test is assumed. The value in master.info will take precedence if it can be read.

--max-binlog-dump-events

  • Commandline: --max-binlog-dump-events=#

  • Description: Option used by mysql-test for debugging and testing of replication.

--replicate-same-server-id

  • Commandline: --replicate-same-server-id

  • Description: In replication, if set to 1, do not skip events having our server id. Default value is 0 (to break infinite loops in circular replication). Can't be set to 1 if log-slave-updates is used.

--sporadic-binlog-dump-fail

  • Commandline: --sporadic-binlog-dump-fail

  • Description: Option used by mysql-test for debugging and testing of replication.

--sysdate-is-now

  • Commandline: --sysdate-is-now

  • Description: Non-default option to alias SYSDATE() to NOW() to make it safe for replication. Since 5.0, SYSDATE() has returned a `dynamic' value different for different invocations, even within the same statement.

Replication and Binary Logging Options and System Variables

The following options and system variables are related to replication and the binary log:

  • auto-increment-increment

  • auto-increment-offset

  • binlog-alter-two-phase

  • binlog-annotate-row-events

  • binlog-cache-size

  • binlog-checksum

  • binlog-commit-wait-count

  • binlog-commit-wait-usec

  • binlog-direct-non-transactional-updates|

  • binlog-expire-logs-seconds

  • binlog-file-cache-size

  • binlog-format

  • binlog-gtid-index

  • binlog-gtid-index-page-size

  • binlog-gtid-index-span-min

  • binlog-large-commit-threshold

  • binlog-legacy-event-pos

  • binlog-optimize-thread-scheduling

  • binlog-row-image

  • binlog-row-metadata

  • binlog-space-limit

  • binlog-stmt-cache-size

  • default-master-connection

  • gtid-cleanup-batch-size

  • gtid-domain-id

  • gtid-ignore-duplicates

  • gtid-strict-mode

  • init-slave

  • log-bin

  • log-bin-compress

  • log-bin-compress-min-len

  • log-bin-index

  • log-bin-trust-function-creators

  • log-slave-updates

  • master-verify-checksum

  • max-binlog-cache-size

  • max-binlog-size

  • max-binlog-stmt-cache-size

  • max-binlog-total-size

  • max-relay-log-size

  • read-binlog-speed-limit

  • relay-log

  • relay-log-index

  • relay-log-info-file

  • relay-log-purge

  • relay-log-recovery

  • relay-log-space-limit

  • replicate-annotate-row-events

  • replicate-do-db

  • replicate-do-table

  • replicate-events-marked-for-skip

  • replicate-ignore-db

  • replicate-ignore-table

  • replicate-rewrite-db

  • replicate-wild-do-table

  • replicate-wild-ignore-table

  • report-host

  • report-password

  • report-port

  • report-user

  • rpl-recovery-rank

  • server-id

  • slave-abort-blocking-timeout

  • slave-compressed-protocol

  • slave-connections-needed-for-purge

  • slave-ddl-exec-mode

  • slave-domain-parallel-threads

  • slave-exec-mode

  • slave-load-tmpdir

  • slave-max-allowed-packet

  • slave-max-statement-time

  • slave-net-timeout

  • slave-parallel-max-queued

  • slave-parallel-threads

  • slave-run-triggers-for-rbr

  • slave-skip-errors

  • slave-sql-verify-checksum

  • slave-transaction-retries

  • slave_transaction_retry_errors

  • slave_transaction_retry_interval

  • slave-type-conversions

  • sync-binlog

  • sync-master-info

  • sync-relay-log

  • sync-relay-log-info

Semisynchronous Replication Options and System Variables

The options and system variables related to Semisynchronous Replication are described here.

Optimizer Options

Options that are also system variables are listed after:

--record-buffer

  • Commandline: --record-buffer=#

  • Description: Old alias for read_buffer_size.

  • Removed: MariaDB 5.5

--table-cache

  • Commandline: --table-open-cache=#

  • Description: Removed; use --table-open-cache instead.

  • Removed: MariaDB 5.3.1

Optimizer Options and System Variables

  • alter-algorithm

  • analyze-sample-percentage

  • big-tables

  • bulk-insert-buffer-size

  • expensive-subquery-limit

  • join-buffer-size

  • join-buffer-space-limit

  • join-cache-level

  • max-heap-table-size

  • max-join-size

  • max-seeks-for-key

  • max-sort-length

  • mrr-buffer-size

  • optimizer-adjust-secondary-key-costs

  • optimizer-extra-pruning-depth

  • optimizer-join-limit-pref-ratio

  • optimizer-max-sel-arg-weight

  • optimizer-max-sel-args

  • optimizer-prune-level

  • optimizer-search-depth

  • optimizer-selectivity-sampling-limit

  • optimizer-switch

  • optimizer-trace

  • optimizer-trace-max-mem-size

  • optimizer-use-condition-selectivity

  • query-alloc-block-size

  • query-prealloc-size

  • range-alloc-block-size

  • read-buffer-size

  • rowid-merge-buff-size

  • table-definition-cache

  • table-open-cache

  • table-open-cache-instances

  • tmp-disk-table-size

  • tmp-memory-table-size

  • tmp-table-size

  • use-stat-tables

Storage Engine Options

--skip-bdb

  • Commandline: ----skip-bdb

  • Description: Deprecated option; Exists only for compatibility with very old my.cnf files.

  • Removed: MariaDB 10.5.1

--external-locking

  • Commandline: --external-locking

  • Description: Use system (external) locking (disabled by default). With this option enabled you can run myisamchk to test (not repair) tables while the server is running. Disable with --skip-external-locking. From MariaDB 10.2.40, MariaDB 10.3.31, MariaDB 10.4.21, MariaDB 10.5.12, MariaDB 10.6.4 and all later version, this effects InnoDB and can be used to prevent multiple instances running on the same data.

MyISAM Storage Engine Options

The options related to the MyISAM storage engine are described below. Options that are also system variables are listed after:

--log-isam

  • Commandline: --log-isam[=file_name]

  • Description: Enable the MyISAM log, which logs all MyISAM changes to file. If no filename is provided, the default, myisam.log is used.

MyISAM Storage Engine Options and System Variables

Some options and system variables related to the MyISAM storage engine can be found here. Direct links to many of them can be found below.

  • concurrent-insert

  • delayed-insert-limit

  • delayed-insert-timeout

  • delayed-queue-size

  • keep-files-on-create

  • key-buffer-size

  • key-cache-age-threshold

  • key-cache-block-size

  • key-cache-division-limit

  • key-cache-file-hash-size

  • key-cache-segments

  • myisam-block-size

  • myisam-data-pointer-size

  • myisam-max-sort-file-size

  • myisam-mmap-size

  • myisam-recover-options

  • myisam-repair-threads

  • myisam-sort-buffer-size

  • myisam-stats-method

  • myisam-use-mmap

InnoDB Storage Engine Options

The options related to the InnoDB storage engine are described below. Options that are also system variables are listed after:

--innodb

  • Commandline: --innodb=value, --skip-innodb

  • Description: This variable controls whether or not to load the InnoDB storage engine. Possible values are ON, OFF, FORCE or FORCE_PLUS_PERMANENT (from MariaDB 5.5). If set to OFF (the same as --skip-innodb), since InnoDB is the default storage engine, the server will not start unless another storage engine has been chosen with --default-storage-engine. FORCE means that the storage engine must be successfully loaded, or else the server won't start. FORCE_PLUS_PERMANENT enables the plugin, but if plugin cannot initialize, the server will not start. In addition, the plugin cannot be uninstalled while the server is running.

--innodb-cmp

  • Commandline: --innodb-cmp

  • Description:

  • Default: ON

--innodb-cmp-reset

  • Commandline: --innodb-cmp-reset

  • Description:

  • Default: ON

--innodb-cmpmem

  • Commandline: --innodb-cmpmem

  • Description:

  • Default: ON

--innodb-cmpmem-reset

  • Commandline: --innodb-cmpmem-reset

  • Description:

  • Default: ON

--innodb-file-io-threads

  • Commandline: --innodb-file-io-threads

  • Description:

  • Default: 4

  • Removed: MariaDB 10.3.0

--innodb-index-stats

  • Commandline: --innodb-index-stats

  • Description:

  • Default: ON

  • Removed: MariaDB 10.0.0

--innodb-lock-waits

  • Commandline: --innodb-lock-waits

  • Description:

  • Default: ON

--innodb-locks

  • Commandline: --innodb-locks

  • Description:

  • Default: ON

--innodb-rseg

  • Commandline: --innodb-rseg

  • Description:

  • Default: ON

  • Removed: MariaDB 10.0.0

--innodb-status-file

  • Commandline: --innodb-status-file

  • Description:

  • Default: FALSE

--innodb-sys-indexes

  • Commandline: --innodb-sys-indexes

  • Description:

  • Default: ON

--innodb-sys-stats

  • Commandline: --innodb-sys-stats

  • Description:

  • Default: ON

  • Removed: MariaDB 10.0.0

--innodb-sys-tables

  • Commandline: --innodb-sys-tables

  • Description:

  • Default: ON

--innodb-table-stats

  • Commandline: --innodb-table-stats

  • Description:

  • Default: ON

  • Removed: MariaDB 10.0.0

--innodb-trx

  • Commandline: --innodb-trx

  • Description:

  • Default: ON

InnoDB Storage Engine Options and System Variables

Some options and system variables related to the InnoDB storage engine can be found here. Direct links to many of them can be found below.

  • ignore-builtin-innodb

  • innodb-adaptive-checkpoint

  • innodb-adaptive-flushing

  • innodb-adaptive-flushing-lwm

  • innodb-adaptive-flushing-method

  • innodb-adaptive-hash-index

  • innodb-adaptive-hash-index-partitions

  • innodb-adaptive-hash-index-parts

  • innodb-adaptive-max-sleep-delay

  • innodb-additional-mem-pool-size

  • innodb-alter-copy-bulk

  • innodb-api-bk-commit-interval

  • innodb-api-disable-rowlock

  • innodb-api-enable-binlog

  • innodb-api-enable-mdl

  • innodb-api-trx-level

  • innodb-auto-lru-dump

  • innodb-autoextend-increment

  • innodb-autoinc-lock-mode

  • innodb-background-scrub-data-check-interval

  • innodb-background-scrub-data-compressed

  • innodb-background-scrub-data-interval

  • innodb-background-scrub-data-uncompressed

  • innodb-blocking-buffer-pool-restore

  • innodb-buf-dump-status-frequency

  • innodb-buffer-pool-chunk-size

  • innodb-buffer-pool-dump-at-shutdown

  • innodb-buffer-pool-dump-now

  • innodb-buffer-pool-dump-pct

  • innodb-buffer-pool-evict

  • innodb-buffer-pool-filename

  • innodb-buffer-pool-instances

  • innodb-buffer-pool-load-abort

  • innodb-buffer-pool-load-at-startup

  • innodb-buffer-pool-load-now

  • innodb-buffer-pool-load-pages-abort

  • innodb-buffer-pool-populate

  • innodb-buffer-pool-restore-at-startup

  • innodb-buffer-pool-shm-checksum

  • innodb-buffer-pool-shm-key

  • innodb-buffer-pool-size

  • innodb-change-buffer-max-size

  • innodb-change-buffering

  • innodb-change-buffering-debug

  • innodb-checkpoint-age-target

  • innodb-checksum-algorithm

  • innodb-checksums

  • innodb-cleaner-lsn-age-factor

  • innodb-cmp-per-index-enabled

  • innodb-commit-concurrency

  • innodb-compression-algorithm

  • innodb-compression-failure-threshold-pct

  • innodb-compression-level

  • innodb-compression-pad-pct-max

  • innodb-concurrency-tickets

  • innodb-corrupt-table-action

  • innodb-data-file-buffering

  • innodb-data-file-path

  • innodb-data-file-write-through

  • innodb-data-home-dir

  • innodb-deadlock-detect

  • innodb-deadlock-report

  • innodb-default-encryption-key-id

  • innodb-default-page-encryption-key

  • innodb-default-row-format

  • innodb-defragment

  • innodb-defragment-fill-factor

  • innodb-defragment-fill-factor-n-recs

  • innodb-defragment-frequency

  • innodb-defragment-n-pages

  • innodb-defragment-stats-accuracy

  • innodb-dict-size-limit

  • innodb_disable_sort_file_cache

  • innodb-doublewrite

  • innodb-doublewrite-file

  • innodb-empty-free-list-algorithm

  • innodb-enable-unsafe-group-commit

  • innodb-encrypt-log

  • innodb-encrypt-tables

  • innodb-encrypt-temporary-tables

  • innodb-encryption-rotate-key-age

  • innodb-encryption-rotation_iops

  • innodb-encryption-threads

  • innodb-extra-rsegments

  • innodb-extra-undoslots

  • innodb-fake-changes

  • innodb-fast-checksum

  • innodb-fast-shutdown

  • innodb-fatal-semaphore-wait-threshold

  • innodb-file-format

  • innodb-file-format-check

  • innodb-file-format-max

  • innodb-file-per-table

  • innodb-fill-factor

  • innodb-flush-log-at-trx-commit

  • innodb-flush-method

  • innodb-flush-neighbor-pages

  • innodb-flush-neighbors

  • innodb-flush-sync

  • innodb-flushing-avg-loops

  • innodb-force-load-corrupted

  • innodb-force-primary-key

  • innodb-force-recovery

  • innodb-foreground-preflush

  • innodb-ft-aux-table

  • innodb-ft-cache-size

  • innodb-ft-enable-diag-print

  • innodb-ft-enable-stopword

  • innodb-ft-max-token-size

  • innodb-ft-min-token-size

  • innodb-ft-num-word-optimize

  • innodb-ft-result-cache-limit

  • innodb-ft-server-stopword-table

  • innodb-ft-sort-pll-degree

  • innodb-ft-total-cache-size

  • innodb-ft-user-stopword-table

  • innodb-ibuf-accel-rate

  • innodb-ibuf-active-contract

  • innodb-ibuf-max-size

  • innodb-idle-flush-pct

  • innodb-immediate-scrub-data-uncompressed

  • innodb-import-table-from-xtrabackup

  • innodb-instant-alter-column-allowed

  • innodb-instrument-semaphores

  • innodb-io-capacity

  • innodb-io-capacity-max

  • innodb-large-prefix

  • innodb-lazy-drop-table

  • innodb-lock-schedule-algorithm

  • innodb-locking-fake-changes

  • innodb-locks-unsafe-for-binlog

  • innodb-log-arch-dir

  • innodb-log-arch-expire-sec

  • innodb-log-archive

  • innodb-log-block-size

  • innodb-log-buffer-size

  • innodb-log-checksum-algorithm

  • innodb-log-checksums

  • innodb-log-compressed-pages

  • innodb-log-file-buffering

  • innodb-log-file-mmap

  • innodb-log-file-size

  • innodb-log-file-write-through

  • innodb-log-files-in-group

  • innodb-log-group-home-dir

  • innodb-log-optimize-ddl

  • innodb-log-spin-wait-delay

  • innodb-log-write-ahead-size

  • innodb-lru-flush-size

  • innodb-lru-scan-depth

  • innodb-max-bitmap-file-size

  • innodb-max-changed-pages

  • innodb-max-dirty-pages-pct

  • innodb-max-dirty-pages-pct-lwm

  • innodb-max-purge-lag

  • innodb-max-purge-lag-delay

  • innodb-max-purge-lag-wait

  • innodb-max-undo-log-size

  • innodb-merge-sort-block-size

  • innodb-mirrored-log-groups

  • innodb-monitor-disable

  • innodb-monitor-enable

  • innodb-monitor-reset

  • innodb-monitor-reset-all

  • innodb-mtflush-threads

  • innodb-numa-interleave

  • innodb-old-blocks-pct

  • innodb-old-blocks-time

  • innodb-online-alter-log-max-size

  • innodb-open-files

  • innodb-optimize-fulltext-only

  • innodb-page-cleaners

  • innodb-page-size

  • innodb-pass-corrupt-table

  • innodb-prefix-index-cluster-optimization

  • innodb-print-all-deadlocks

  • innodb-purge-batch-size

  • innodb-purge-rseg-truncate-frequency

  • innodb-purge-threads

  • innodb-random-read-ahead

  • innodb-read-ahead

  • innodb-read-ahead-threshold

  • innodb-read-io-threads

  • innodb-read-only

  • innodb-recovery-update-relay-log

  • innodb-replication-delay

  • innodb-rollback-on-timeout

  • innodb-rollback-segments

  • innodb-safe-truncate

  • innodb-sched-priority-cleaner

  • innodb-scrub-log

  • innodb-scrub-log-interval

  • innodb-scrub-log-speed

  • innodb-show-locks-held

  • innodb-show-verbose-locks

  • innodb-snapshot-isolation

  • innodb-sort-buffer-size

  • innodb-spin-wait-delay

  • innodb-stats-auto-recalc

  • innodb-stats-auto-update

  • innodb-stats-include-delete-marked

  • innodb-stats-method

  • innodb-stats-modified-counter

  • innodb-stats-on-metadata

  • innodb-stats-persistent

  • innodb-stats-persistent-sample-pages

  • innodb-stats-sample-pages

  • innodb-stats-transient-sample-pages

  • innodb-stats-traditional

  • innodb-stats-update-need-lock

  • innodb-status-output

  • innodb-status-output-locks

  • innodb-strict-mode

  • innodb-support-xa

  • innodb-sync-array-size

  • innodb-sync-spin-loops

  • innodb-table-locks

  • innodb-temp-data-file-path

  • innodb-thread-concurrency

  • innodb-thread-concurrency-timer-based

  • innodb-thread-sleep-delay

  • innodb-tmpdir

  • innodb-track-changed-pages

  • innodb-track-redo-log-now

  • innodb-truncate-temporary-tablespace-now

  • innodb-undo-directory

  • innodb-undo-log-truncate

  • innodb-undo-logs

  • innodb-undo-tablespaces

  • innodb-use-atomic-writes

  • innodb-use-fallocate

  • innodb-use-global-flush-log-at-trx-commit

  • innodb-use-mtflush

  • innodb-use-native_aio

  • innodb-use-purge-thread

  • innodb-use-stacktrace

  • innodb-use-sys-malloc

  • innodb-use-sys-stats-table

  • innodb-use-trim

  • innodb-write-io-threads

  • skip-innodb

  • skip-innodb-checksums

  • skip-innodb-doublewrite

Aria Storage Engine Options

Options related to the Aria storage engine are listed below:

Aria Storage Engine Options and System Variables

Some options and system variables related to the Aria storage engine can be found here. Direct links to many of them can be found below.

  • aria-block-size

  • aria-checkpoint-interval

  • aria-checkpoint-log-activity

  • aria-encrypt-tables

  • aria-force-start-after-recovery-failures

  • aria-group-commit

  • aria-group-commit-interval

  • aria-log-dir-path

  • aria-log-file-size

  • aria-log-purge-type

  • aria-max-sort-file-size

  • aria-page-checksum

  • aria-pagecache-age-threshold

  • aria-pagecache-buffer-size

  • aria-pagecache-division-limit

  • aria-pagecache-file-hash-size

  • aria-recover

  • aria-recover-options

  • aria-repair-threads

  • aria-sort-buffer-size

  • aria-stats-method

  • aria-sync-log-dir

  • aria-used-for-temp-tables

  • deadlock-search-depth-long

  • deadlock-search-depth-short

  • deadlock-timeout-long

  • deadlock-timeout-short

MyRocks Storage Engine Options

The options and system variables related to the MyRocks storage engine can be found here.

S3 Storage Engine Options

The options and system variables related to the S3 storage engine can be found here.

CONNECT Storage Engine Options

The options related to the CONNECT storage engine are described below.

CONNECT Storage Engine Options and System Variables

Some options and system variables related to the CONNECT storage engine can be found here. Direct links to many of them can be found below.

  • connect-class-path

  • connect-cond-push

  • connect-conv-size

  • connect-default-depth

  • connect-default-prec

  • connect-enable-mongo

  • connect-exact-info

  • connect-force_bson

  • connect-indx-map

  • connect-java-wrapper

  • connect-json-all-path

  • connect-json-grp-size

  • connect-json-null

  • connect-jvm-path

  • connect-type-conv

  • connect-use-tempfile

  • connect-work-size

  • connect-xtrace

Spider Storage Engine Options

The options and system variables related to the Spider storage engine can be found here.

Mroonga Storage Engine Options

The options and system variables related to the Mroonga storage engine can be found here.

TokuDB Storage Engine Options

The options and system variables related to the TokuDB storage engine can be found here.

Vector Options

The options and system variables related to Vectors storage engine (beginning with mhnsw) can be found here.

Performance Schema Options

The options related to the Performance Schema are described below. Options that are also system variables are listed after:

--performance-schema-consumer-events-stages-current

  • Commandline: --performance-schema-consumer-events-stages-current

  • Description: Enable the events-stages-current consumer.

  • Default: OFF

--performance-schema-consumer-events-stages-history

  • Commandline: --performance-schema-consumer-events-stages-history

  • Description: Enable the events-stages-history consumer.

  • Default: OFF

--performance-schema-consumer-events-stages-history-long

  • Commandline: --performance-schema-consumer-events-stages-history-long

  • Description: Enable the events-stages-history-long consumer.

  • Default: OFF

--performance-schema-consumer-events-statements-current

  • Commandline: --performance-schema-consumer-events-statements-current

  • Description: Enable the events-statements-current consumer. Use --skip-performance-schema-consumer-events-statements-current to disable.

  • Default: ON

--performance-schema-consumer-events-statements-history

  • Commandline: --performance-schema-consumer-events-statements-history

  • Description: Enable the events-statements-history consumer.

  • Default: OFF

--performance-schema-consumer-events-statements-history-long

  • Commandline: --performance-schema-consumer-events-statements-history-long

  • Description: Enable the events-statements-history-long consumer.

  • Default: OFF

--performance-schema-consumer-events-waits-current

  • Commandline: --performance-schema-consumer-events-waits-current

  • Description: Enable the events-waits-current consumer.

  • Default: OFF

--performance-schema-consumer-events-waits-history

  • Commandline: --performance-schema-consumer-events-waits-history

  • Description: Enable the events-waits-history consumer.

  • Default: OFF

--performance-schema-consumer-events-waits-history-long

  • Commandline: --performance-schema-consumer-events-waits-history-long

  • Description: Enable the events-waits-history-long consumer.

  • Default: OFF

--performance-schema-consumer-global-instrumentation

  • Commandline: --performance-schema-consumer-global-instrumentation

  • Description: Enable the global-instrumentation consumer. Use --skip-performance-schema-consumer-global-instrumentation to disable.

  • Default: ON

--performance-schema-consumer-statements-digest

  • Commandline: --performance-schema-consumer-statements-digest

  • Description: Enable the statements-digest consumer. Use --skip-performance-schema-consumer-statements-digest to disable.

  • Default: ON

--performance-schema-consumer-thread-instrumentation

  • Commandline: --performance-schema-consumer-thread-instrumentation

  • Description: Enable the statements-thread-instrumentation. Use --skip-performance-schema-thread-instrumentation to disable.

  • Default: ON

Performance Schema Options and System Variables

Some options and system variables related to the Performance Schema can be found here. Direct links to many of them can be found below.

  • performance-schema

  • performance-schema-accounts-size

  • performance-schema-digests-size

  • performance-schema-events-stages-history-long-size

  • performance-schema-events-stages-history-size

  • performance-schema-events-statements-history-long-size

  • performance-schema-events-statements-history-size

  • performance-schema-events-waits-history-long-size

  • performance-schema-events-waits-history-size

  • performance-schema-hosts-size

  • performance-schema-max-cond-classes

  • performance-schema-max-cond-instances

  • performance-schema-max-digest-length

  • performance-schema-max-file-classes

  • performance-schema-max-file-handles

  • performance-schema-max-file-instances

  • performance-schema-max-mutex-classes

  • performance-schema-max-mutex-instances

  • performance-schema-max-rwlock-classes

  • performance-schema-max-rwlock-instances

  • performance-schema-max-socket-classes

  • performance-schema-max-socket-instances

  • performance-schema-max-stage-classes

  • performance-schema-max-statement-classes

  • performance-schema-max-table-handles

  • performance-schema-max-table-instances

  • performance-schema-max-thread-classes

  • performance-schema-max-thread-instances

  • performance-schema-session-connect-attrs-size

  • performance-schema-setup-actors-size

  • performance-schema-setup-objects-size

  • performance-schema-users-size

Galera Cluster Options

The options related to Galera Cluster are described below. Options that are also system variables are listed after:

--wsrep-new-cluster

  • Commandline: --wsrep-new-cluster

  • Description: Bootstrap a cluster. It works by overriding the current value of wsrep_cluster_address. It is recommended not to add this option to the config file as this will trigger bootstrap on every server start.

Galera Cluster Options and System Variables

Some options and system variables related to Galera Cluster can be found here. Direct links to many of them can be found below.

  • wsrep-allowlist

  • wsrep-auto-increment-control

  • wsrep-causal-reads

  • wsrep-certify-nonPK

  • wsrep-cluster-address

  • wsrep-cluster-name

  • wsrep-convert-LOCK-to-trx

  • wsrep-data-home-dir

  • wsrep-dbug-option

  • wsrep-debug

  • wsrep-desync

  • wsrep-dirty-reads

  • wsrep-drupal-282555-workaround

  • wsrep-forced-binlog-format

  • wsrep-gtid-domain-id

  • wsrep-gtid-mode

  • wsrep-ignore-apply-errors

  • wsrep-load-data-splitting

  • wsrep-log-conflicts

  • wsrep-max-ws-rows

  • wsrep-max-ws-size

  • wsrep-mode

  • wsrep-mysql-replication-bundle

  • wsrep-node-address

  • wsrep-node-incoming-address

  • wsrep-node-name

  • wsrep-notify-cmd

  • wsrep-on

  • wsrep-OSU-method

  • wsrep-provider

  • wsrep-provider-options

  • wsrep-recover

  • wsrep-reject_queries

  • wsrep-retry-autocommit

  • wsrep-slave-FK-checks

  • wsrep-slave-threads

  • wsrep-slave-UK-checks

  • wsrep-sr-store

  • wsrep-sst-auth

  • wsrep-sst-donor

  • wsrep-sst-donor-rejects-queries

  • wsrep-sst-method

  • wsrep-sst-receive-address

  • wsrep-start-position

  • wsrep-status-file

  • wsrep-strict-ddl

  • wsrep-sync-wait

  • wsrep-trx_fragment_size

  • wsrep-trx_fragment_unit

Options When Debugging mariadbd

--debug-assert-if-crashed-table

  • Description: Do an assert in handler::print_error() if we get a crashed table.

--debug-binlog-fsync-sleep

  • Description: --debug-binlog-fsync-sleep=#If not set to zero, sets the number of micro-seconds to sleep after running fsync() on the binary log to flush transactions to disk. This can thus be used to artificially increase the perceived cost of such an fsync().

--debug-crc-break

  • Description: --debug-crc-break=#Call my_debug_put_break_here() if crc matches this number (for debug).

--debug-flush

  • Description: Default debug log with flush after write.

--debug-no-sync

  • Description: debug-no-sync[=#]Disables system sync calls. Only for running tests or debugging!

--debug-sync-timeout

  • Description: debug-sync-timeout[=#]Enable the debug sync facility and optionally specify a default wait timeout in seconds. A zero value keeps the facility disabled.

--gdb

  • Description: Set up signals usable for debugging.

--silent-startup

  • Description: Don't print Notes to the error log during startup.

--sync-sys

  • Description: Enable/disable system sync calls. Syncs should only be turned off (--disable-sync-sys) when running tests or debugging! Replaced by debug-no-sync from MariaDB 5.5.

  • Removed: MariaDB 5.5

--thread-alarm

  • Description: Enable/disable system thread alarm calls. Should only be turned off (--disable-thread-alarm) when running tests or debugging!

Debugging Options and System Variables

  • core-file

  • debug

  • debug-no-thread-alarm

Other Options

Options that are also system variables are listed after:

--allow-suspicious-udfs

  • Commandline: --allow-suspicious-udfs

  • Description: Allows use of user-defined functions consisting of only one symbol x() without corresponding x_init() or x_deinit(). That also means that one can load any function from any library, for example exit() from libc.so. Not recommended unless you require old UDFs with one symbol that cannot be recompiled. From MariaDB 10.10, available as a system variable as well.

--bootstrap

  • Commandline: --bootstrap

  • Description: Used by mariadb installation scripts, such as mariadb-install-db to execute SQL scripts before any privilege or system tables exist. Do no use while an existing MariaDB instance is running.

--chroot

  • Commandline: --chroot=name

  • Description: Chroot mariadbd daemon during startup.

--des-key-file

  • Commandline: --des-key-file=name

  • Description: Load keys for des_encrypt() and des_encrypt from given file.

--exit-info

  • Commandline: --exit-info[=#]

  • Description: Used for debugging. Use at your own risk.

--getopt-prefix-matching

  • Commandline: --getopt-prefix-matching={0|1}

  • Description: Makes it possible to disable historical "unambiguous prefix" matching in the command-line option parsing.

  • Default: TRUE

  • Introduced: MariaDB 10.1.3

--help

  • Commandline: --help

  • Description: Displays help with many commandline options described, and exits. From MariaDB 11.5, includes deprecation information.

--log-ddl-recovery

  • Commandline: --log-ddl-recovery=name

  • Description: Path to file used for recovery of DDL statements after a crash.

  • Default Value: ddl-recover.log

  • Introduced: MariaDB 10.6.1

--log-short-format

  • Commandline: --log-short-format

  • Description: Don't log extra information to update and slow-query logs.

--log-slow-file

  • Commandline: --log-slow-file=name

  • Description: Log slow queries to given log file. Defaults logging to hostname-slow.log

--log-slow-time

  • Commandline: --log-slow-time=#

  • Description: Log all queries that have taken more than long-query-time seconds to execute to the slow query log, if active. The argument will be treated as a decimal value with microsecond precision.

--log-tc

  • Commandline: --log-tc=name

  • Description: Defines the path to the memory-mapped file-based transaction coordinator log, which is only used if the binary log is disabled. If you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available. See Transaction Coordinator Log for more information. Also see the log_tc_size system variable and the --tc-heuristic-recover option.

  • Default Value: tc.log

--master-connect-retry

  • Commandline: --master-connect-retry=#

  • Description: Deprecated in 5.1.17 and removed in 5.5. The number of seconds the replica thread will sleep before retrying to connect to the master, in case the master goes down or the connection is lost.

--memlock

  • Commandline: --memlock

  • Description: Lock mariadbd in memory.

--ndb-use-copying-alter-table

  • Commandline: --ndb-use-copying-alter-table

  • Description: Force ndbcluster to always copy tables at alter table (should only be used if on-line alter table fails).

--one-thread

  • Commandline: --one-thread

  • Description: (Deprecated): Only use one thread (for debugging under Linux). Use thread-handling=no-threads instead.

  • Removed: MariaDB 10.0.4

--plugin-load

  • Commandline: --plugin-load=name

  • Description: This option can be used to configure the server to load specific plugins. This option uses the following format:

    • Plugins can be specified in the format name=library, where name is the plugin name and library is the plugin library. This format installs a single plugin from the given plugin library.

    • Plugins can also be specified in the format library, where library is the plugin library. This format installs all plugins from the given plugin library.

    • Multiple plugins can be specified by separating them with semicolons.

  • Special care must be taken when specifying the --plugin-load option multiple times, or when specifying both the --plugin-load option and the --plugin-load-add option together. The --plugin-load option resets the plugin load list, and this can cause unexpected problems if you are not aware. The --plugin-load-add option does not reset the plugin load list, so it is much safer to use. See Plugin Overview: Specifying Multiple Plugin Load Options for more information.

  • See Plugin Overview: Installing a Plugin with Plugin Load Options for more information.

--plugin-load-add

  • Commandline: --plugin-load-add=name

  • Description: This option can be used to configure the server to load specific plugins. This option uses the following format:

    • Plugins can be specified in the format name=library, where name is the plugin name and library is the plugin library. This format installs a single plugin from the given plugin library.

    • Plugins can also be specified in the format library, where library is the plugin library. This format installs all plugins from the given plugin library.

    • Multiple plugins can be specified by separating them with semicolons.

  • Special care must be taken when specifying both the --plugin-load option and the --plugin-load-add option together. The --plugin-load option resets the plugin load list, and this can cause unexpected problems if you are not aware. The --plugin-load-add option does not reset the plugin load list, so it is much safer to use. See Plugin Overview: Specifying Multiple Plugin Load Options for more information.

  • See Plugin Overview: Installing a Plugin with Plugin Load Options for more information.

--port-open-timeout

  • Commandline: --port-open-timeout=#

  • Description: Maximum time in seconds to wait for the port to become free. (Default: No wait).

--safe-user-create

  • Commandline: --safe-user-create

  • Description: Don't allow new user creation by the user who has no write privileges to the mysql.user table.

--safemalloc-mem-limit

  • Commandline: --safemalloc-mem-limit=#

  • Description: Simulate memory shortage when compiled with the --with-debug=full option.

--show-slave-auth-info

  • Commandline: --show-slave-auth-info

  • Description: Show user and password in SHOW SLAVE HOSTS on this primary.

--skip-grant-tables

  • Commandline: --skip-grant-tables

  • Description: Start without grant tables. This gives all users FULL ACCESS to all tables, which is useful in case of a lost root password. Use mariadb-admin flush-privileges, mariadb-admin reload or FLUSH PRIVILEGES to resume using the grant tables. From MariaDB 10.10, available as a system variable as well.

Because the Event Scheduler also depends on the grant tables for its functionality, it is automatically disabled when running with --skip-grant-tables.

--skip-host-cache

  • Commandline: --skip-host-cache

  • Description: Don't cache host names.

--skip-partition

  • Commandline: --skip-partition, --disable-partition

  • Description: Disables user-defined partitioning. Previously partitioned tables cannot be accessed or modifed. Tables can still be seen with SHOW TABLES or by viewing the INFORMATION_SCHEMA.TABLES table. Tables can be dropped with DROP TABLE, but this only removes .frm files, not the associated .par files, which will need to be removed manually.

--skip-slave-start

  • Commandline: --skip-slave-start

  • Description: If set, replica is not autostarted. From MariaDB 12.0, server will display in the log if this option is set.

--skip-ssl

  • Commandline: --skip-ssl

  • Description: Disable TLS connections.

--skip-symlink

  • Commandline: --skip-symlink

  • Description: Don't allow symlinking of tables. Deprecated and removed in MariaDB 5.5. Use symbolic-links with the skip option prefix instead.

  • Removed: MariaDB 5.5

--skip-thread-priority

  • Commandline: --skip-thread-priority

  • Description: Don't give threads different priorities. Deprecated and removed in MariaDB 10.0.

  • Removed: MariaDB 10.0

--sql-bin-update-same

  • Commandline: --sql-bin-update-same=#

  • Description: The update log was deprecated in version 5.0 and replaced by the binary log, so this option did nothing since then. Deprecated and removed in MariaDB 5.5.

  • Removed: MariaDB 5.5

--ssl

  • Commandline: --ssl

  • Description: Enable TLS for connection (automatically enabled with other flags). Disable with '--skip-ssl'.

--stack-trace

  • Commandline: --stack-trace, --skip-stack-trace

  • Description: Print a stack trace on failure. Enabled by default, disable with -skip-stack-trace.

--symbolic-links

  • Commandline: --symbolic-links

  • Description: Enables symbolic link support. When set, the have_symlink system variable shows as YES. Silently ignored in Windows. Use --skip-symbolic-links to disable.

--tc-heuristic-recover

  • Commandline: --tc-heuristic-recover=name

  • Description: If manual heuristic recovery is needed, this option defines the decision to use in the heuristic recovery process. Manual heuristic recovery may be needed if the transaction coordination log is missing or if it doesn't contain all prepared transactions. This option can be set to OFF, COMMIT, or ROLLBACK. The default is OFF. See also the --log-tc server option and the log_tc_size system variable.

--temp-pool

  • Commandline: --temp-pool

  • Description: Using this option will cause most temporary files created to use a small set of names, rather than a unique name for each new file. This behavior works around a bug in old Linux kernels where the kernel appeared to "leak" memory. In a Docker environment it might look like an unbounded working-set memory growth. Defaults to 1 until MariaDB 10.5.6, use --skip-temp-pool to disable. Defaults to 0 from MariaDB 10.5.7, as benchmarking shows it causes a heavy mutex contention.

--test-expect-abort

  • Commandline: --test-expect-abort

  • Description: Expect that server aborts with 'abort'; Don't write out server variables on 'abort'. Useful only for test scripts.

--test-ignore-wrong-options

  • Commandline: --test-ignore-wrong-options

  • Description: Ignore wrong enums values in command line arguments. Useful only for test scripts.

--user

  • Commandline: --user=name

  • Description: Run mariadbd daemon as user.

--verbose

  • Commandline: -v, --verbose

  • Description: Used with help option for detailed help.

Other Options and System Variables

  • allow-suspicious-udfs

  • automatic-sp-privileges

  • back-log

  • basedir

  • check-constraint-checks

  • column-compression-threshold

  • column-compression-zlib-level

  • column-compression-zlib-strategy

  • column-compression-zlib-wrap

  • completion-type

  • connect-timeout

  • datadir

  • date-format

  • datetime-format

  • deadlock-search-depth-long

  • deadlock-search-depth-short

  • deadlock-timeout-long

  • deadlock-timeout-short

  • default-password-lifetime

  • default-regex-flags

  • default-storage-engine

  • default-table-type

  • delay-key-write

  • disconnect-on-expired-password

  • div-precision-increment

  • enable-named-pipe

  • encrypt-binlog

  • encrypt-tmp-disk-tables

  • encrypt-tmp-files

  • encryption-algorithm

  • engine-condition-pushdown

  • eq-range-index-dive-limit

  • event-scheduler

  • expire-logs-days

  • explicit-defaults-for-timestamp

  • extra-max-connections

  • extra-port

  • flush

  • flush-time

  • ft-boolean-syntax

  • ft-max-word-len

  • ft-min-word-len

  • ft-query-expansion-limit

  • ft-stopword-file

  • general-log

  • general-log-file

  • group-concat-max-len

  • histogram-size

  • histogram-type

  • host-cache-size

  • idle-readonly-transaction-timeout

  • idle-transaction-timeout

  • idle-write-transaction-timeout

  • ignore-db-dirs

  • in-predicate-conversion-threshold

  • init-connect

  • init-file

  • interactive-timeout

  • large-pages

  • local-infile

  • lock-wait-timeout

  • log

  • log-disabled-statements

  • log-error

  • log-output

  • log-queries-not-using-indexes

  • log-slow-admin-statements

  • log-slow-always-query-time

  • log-slow-disabled-statements

  • log-slow-filter

  • log-slow-min-examined-row-limit

  • log-slow-queries

  • log-slow-query

  • log-slow-query-file

  • log-slow-query-time

  • log-slow-rate-limit

  • log-slow-slave-statements

  • log-slow-verbosity

  • log-tc-size

  • log-warnings

  • long-query-time

  • low-priority-updates

  • lower-case-table-names

  • max-allowed-packet

  • max-connections

  • max-connect-errors

  • max-delayed-threads

  • max-digest-length

  • max-error-count

  • max-length-for-sort-data

  • max-long-data-size

  • max-password-errors

  • max-prepared-stmt-count

  • max-recursive-iterations

  • max-rowid-filter-size

  • max-session-mem-used

  • max-sp-recursion-depth

  • max-statement-time

  • max-tmp-session-space-usage

  • max-tmp-tables

  • max-tmp-total-space-usage

  • max-user-connections

  • max-write-lock-count

  • metadata-locks-cache-size

  • metadata-locks-hash-instances

  • min-examined-row-limit

  • mrr-buffer-size

  • multi-range-count

  • --mysql56-temporal-format

  • net-buffer-length

  • net-read-timeout

  • net-retry-count

  • net-write-timeout

  • open-files-limit

  • pid-file

  • plugin-dir

  • plugin-maturity

  • port

  • preload-buffer-size

  • profiling-history-size

  • progress-report-time

  • proxy-protocol-networks

  • query-cache-limit

  • query-cache-min-res-unit

  • query-cache-strip-comments

  • query-cache-wlock-invalidate

  • read-rnd-buffer-size

  • read-only

  • redirect-url

  • require-secure-transport

  • safe-show-database

  • secure-auth

  • secure-file-priv

  • secure-timestamp

  • session-track-schema

  • session-track-state-change

  • session-track-system-variables

  • session-track-transaction-info

  • skip-automatic-sp-privileges

  • skip-external-locking

  • skip-large-pages

  • skip-log-error

  • skip-name-resolve

  • skip-networking

  • skip-show-database

  • slow-launch-time

  • slow-query-log

  • slow-query-log-file

  • socket

  • sort-buffer-size

  • sql-if-exists

  • sql-mode

  • ssl-ca

  • ssl-capath

  • ssl-cert

  • ssl-cipher

  • ssl-crl

  • ssl-crlpath

  • ssl-key

  • standards_compliant_cte

  • stored-program-cache

  • strict_password_validation

  • sync-frm

  • system-versioning-alter-history

  • system-versioning-asof

  • system-versioning-innodb-algorithm-simple

  • system-versioning-insert-history

  • table-lock-wait-timeout

  • tcp-keepalive-interval

  • tcp-keepalive-probes

  • tcp-keepalive-time

  • tcp-nodelay

  • thread-cache-size

  • thread-concurrency

  • thread-handling

  • thread-pool-dedicated-listener

  • thread-pool-exact-stats

  • thread-pool-idle-timeout

  • thread-pool-max-threads

  • thread-pool-min-threads

  • thread-pool-oversubscribe

  • thread-pool-prio-kickup-timer

  • thread-pool-priority

  • thread-pool-size

  • thread-pool-stall-limit

  • thread-stack

  • timed-mutexes

  • time-format

  • tls-version

  • tmpdir

  • transaction-isolation

  • transaction-alloc-block-size

  • transaction-prealloc-size

  • transaction-read-only

  • updatable-views-with-limit

  • userstat

  • version

  • wait-timeout

Authentication Plugins - Options and System Variables

Authentication Plugin - ed25519

The options related to the ed25519 authentication plugin can be found here.

Authentication Plugin - gssapi

The system variables related to the gssapi authentication plugin can be found here.

The options related to the gssapi authentication plugin can be found here.

Authentication Plugin - named_pipe

The options related to the named_pipe authentication plugin can be found here.

Authentication Plugin - pam

The system variables related to the pam authentication plugin can be found here.

The options related to the pam authentication plugin can be found here.

Authentication Plugin - unix_socket

The options related to the unix_socket authentication plugin can be found here.

Encryption Plugins - Options and System Variables

Encryption Plugin - aws_key_management

The system variables related to the aws_key_management encryption plugin can be found here.

The options elated to the aws_key_managementencryption plugin can be found here.

Encryption Plugin - file_key_management

The system variables related to the file_key_management encryption plugin can be found here.

The options related to the file_key_management encryption plugin can be found here.

Password Validation Plugins - Options and System Variables

Password Validation Plugin - simple_password_check

The system variables related to the c password validation plugin can be found here.

The options related to the simple_password_check password validation plugin can be found here.

Password Validation Plugin - cracklib_password_check

The system variables related to the cracklib_password_check password validation plugin can be found here.

The options related to the cracklib_password_check password validation plugin can be found here.

Audit Plugins - Options and System Variables

Audit Plugin - server_audit

Options and system variables related to the server_audit audit plugin can be found here.

Audit Plugin - SQL_ERROR_LOG

The options and system variables related to the SQL_ERROR_LOG audit plugin can be found here.

Audit Plugin - QUERY_RESPONSE_TIME_AUDIT

The options related to the QUERY_RESPONSE_TIME_AUDIT audit plugin can be found here.

Daemon Plugins - Options and System Variables

Daemon Plugin - handlersocket

The options for the HandlerSocket plugin are all described on the HandlerSocket Configuration Option page.

Information Schema Plugins - Options and System Variables

Information Schema Plugin - DISKS

The options related to the DISKS information schema plugin can be found here.

Information Schema Plugin - feedback

The system variables related to the feedback plugin can be found here.

The options related to the feedback plugin can be found here.

Information Schema Plugin - LOCALES

The options related to the LOCALES information schema plugin can be found here.

Information Schema Plugin - METADATA_LOCK_INFO

The options related to the METADATA_LOCK_INFO information schema plugin can be found here.

Information Schema Plugin - QUERY_CACHE_INFO

The options related to the QUERY_CACHE_INFO information schema plugin can be found here.

Information Schema Plugin - QUERY_RESPONSE_TIME

The system variables related to the QUERY_RESPONSE_TIME information schema plugin can be found here.

The options related to the QUERY_RESPONSE_TIME information schema plugin can be found here.

Information Schema Plugin - user_variables

The options related to the user_variables information schema plugin can be found here.

Information Schema Plugin - WSREP_MEMBERSHIP

The options related to the WSREP_MEMBERSHIP information schema plugin can be found here.

Information Schema Plugin - WSREP_STATUS

The options related to the WSREP_STATUS information schema plugin can be found here.

Replication Plugins - Options and System Variables

Replication Plugin - rpl_semi_sync_master

The system variables related to the rpl_semi_sync_master replication plugin can be found here.

The options related to the rpl_semi_sync_master replication plugin can be found here.

Replication Plugin - rpl_semi_sync_slave

The system variables related to the rpl_semi_sync_slave replication plugin can be found here.

The options related to the rpl_semi_sync_slave replication plugin can be found here.

Default Values

You can verify the default values for an option by doing:

mariadbd --no-defaults --help --verbose

This page is licensed: GPLv2

mariadbd

Prior to MariaDB 10.5, the client was called mysqld. It can still be accessed under this name, via a symlink in Linux, or an alternate binary in Windows.

See mariadbd-options for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql.server

The mysql.server startup script is in MariaDB distributions on Linux and Unix. It is a wrapper that works as a standard sysVinit script. However, it can be used independently of sysVinit as a regular sh script. The script starts the mariadbd server process by first changing its current working directory to the MariaDB install directory and then starting mariadbd-safe. The script requires the standard sysVinit arguments, such as start, stop, restart, and status. For example:

mysql.server start
mysql.server restart
mysql.server stop
mysql.server status

It can be used on systems such as Linux, Solaris, and Mac OS X.

The mysql.server script starts mariadbd by first changing to the MariaDB install directory and then calling mariadbd-safe.

Using mysql.server

The command to use mysql.server and the general syntax is:

mysql.server [ start | stop | restart | status ] <options> <mariadbd_options>

Options

If an unknown option is provided to mariadbd-safe on the command-line, then it is passed to mariadbd-safe.

mysql.server supports the following options:

Option
Description

--basedir=path

The path to the MariaDB installation directory.

--datadir=path

The path to the MariaDB data directory.

--pid-file=file_name

The path name of the file in which the server should write its process ID. If not provided, the default, host_name.pid is used.

--service-startup-timeout=file_name

How long in seconds to wait for confirmation of server startup. If the server does not start within this time, mysql.server exits with an error. The default value is 900. A value of 0 means not to wait at all for startup. Negative values mean to wait forever (no timeout).

--use-mysqld_safe

Use mariadbd-safe to start the server. This is the default.

--use-manager

Use Instance Manager to start the server.

--user=user_name

The login user name to use for running mariadbd.

Option Files

In addition to reading options from the command-line, mysql.server can also read options from option files.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

Option
Description

--print-defaults

Print the program argument list and exit.

--no-defaults

Don't read default options from any option file.

--defaults-file=#

Only read default options from the given file #.

--defaults-extra-file=#

Read this file after the global files are read.

Option Groups

mysql.server reads options from the following option groups from option files:

Group
Description

[mysql.server]

Options read by mysql.server, which includes both MariaDB Server and MySQL Server.

mysql.server also reads options from the following server option groups from option files:

Group
Description

[mysqld]

Options read by mysqld, which includes both MariaDB Server and MySQL Server.

[server]

Options read by MariaDB Server.

[mysqld-X.Y]

Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example, [mysqld-5.5].

[mariadbd]

Options read by MariaDB Server.

[mariadbd-X.Y]

Options read by a specific version of MariaDB Server.

[client-server]

Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients.

[galera]

Options read by a galera-capable MariaDB Server. Available on systems compiled with Galera support.

Customizing mysql.server

If you have installed MariaDB to a non-standard location, then you may need to edit the mysql.server script to get it to work right.

If you do not want to edit the mysql.server script itself, then mysql.server also sources a few other sh scripts. These files can be used to set any variables that might be needed to make the script work in your specific environment. The files are:

  • /etc/default/mysql

  • /etc/sysconfig/mysql

  • /etc/conf.d/mysql

Installed Locations

mysql.server can be found in the support-files directory under your MariaDB installation directory or in a MariaDB source distribution.

Installed SysVinit Locations

On systems that use sysVinit, mysql.server may also be installed in other locations and with other names.

If you installed MariaDB on Linux using RPMs, then the mysql.server script will be installed into the /etc/init.d directory with the name mysql. You need not install it manually.

Manually Installing with SysVinit

If you install MariaDB from source or from a binary tarball that does not install mysql.server automatically, and if you are on a system that uses sysVinit, then you can manually install mysql.server with sysVinit. This is usually done by copying it to /etc/init.d/ and then creating specially named symlinks in the appropriate /etc/rcX.d/ directories (where 'X' is a number between 0 and 6).

In the examples below we will follow the historical convention of renaming themysql.server script to 'mysql' when we copy it to /etc/init.d/.

The first step for most Linux distributions is to copy the mysql.server script to /etc/init.d/ and make it executable:

cd /path/to/your/mariadb-version/support-files/
cp mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql

Now all that is needed is to create the specially-named symlinks. On both RPM and Debian-based Linux distributions there are tools which do this for you. Consult your distribution's documentation if neither of these work for you and follow their instructions for generating the symlinks or creating them manually.

On RPM-based distributions (like Fedora and CentOS), you use chkconfig:

chkconfig --add mysql
chkconfig --level 345 mysql on

On Debian-based distributions you use update-rc.d:

update-rc.d mysql defaults

On FreeBSD, the location for startup scripts is/usr/local/etc/rc.d/ and when you copy themysql.server script there you should rename it so that it matches the *.sh pattern, like so:

cd /path/to/your/mariadb/support-files/
cp mysql.server /usr/local/etc/rc.d/mysql.server.sh

As stated above, consult your distribution's documentation for more information on starting services like MariaDB at system startup.

See mariadbd startup options for information on configuration options for mariadbd.

This page is licensed: GPLv2

Running MariaDB from the Build Directory

You can run mariadbd directly from the build directory (without doingmake install).

Starting mariadbd After Build on Windows

On Windows, the data directory is produced during the build.

The simplest way to start database from the command line is:

  1. Go to the directory where mariadbd.exe is located (subdirectory sql\Debug or sql\Relwithdebinfo of the build directory)

  2. From here, execute, if you are using MariaDB 10.5 or newer,

mariadbd.exe --console

else

mariadbd.exe --console

As usual, you can pass other server parameters on the command line, or store them in a my.ini configuraton file and pass --defaults-file=path\to\my.ini

The default search path on Windows for the my.ini file is:

  • GetSystemWindowsDirectory()

  • GetWindowsDirectory()

  • C:\

  • Directory where the executable is located

Starting mariadbd After Build on Unix

Copy the following to your '~/.my.cnf' file.

There are two lines you have to edit: 'datadir=' and 'language='. Be sure to change them to match your environment.

# Example MariadB config file.
# You can copy this to one of:
# /etc/my.cnf to set global options,
# /mysql-data-dir/my.cnf to get server specific options or
# ~/my.cnf for user specific options.
# 
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all MariaDB clients
[client]
#password=my_password
#port=3306
#socket=/tmp/mysql.sock

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

# The mariadb server  (both [mysqld] and [mariadb] works here)
[mariadb]
#port=3306
#socket=/tmp/mysql.sock

# The following three entries caused mysqld 10.0.1-MariaDB (and possibly other versions) to abort...
# skip-locking
# set-variable  = key_buffer=16M

loose-innodb_data_file_path = ibdata1:1000M
loose-mutex-deadlock-detector
gdb

######### Fix the two following paths

# Where you want to have your database
datadir=/path/to/data/dir

# Where you have your mysql/MariaDB source + sql/share/english
language=/path/to/src/dir/sql/share/english

########## One can also have a different path for different versions, to simplify development.

[mariadb-10.1]
lc-messages-dir=/my/maria-10.1/sql/share

[mariadb-10.2]
lc-messages-dir=/my/maria-10.2/sql/share

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[myisamchk]
set-variable= key_buffer=128M

With the above file in place, go to your MariaDB source directory and execute:

./scripts/mariadb-install-db --srcdir=$PWD --datadir=/path/to/data/dir --user=$LOGNAME

Above '$PWD' is the environment variable that points to your current directory. If you added datadir to your my.cnf, you don't have to give this option above. Also above, --user=$LOGNAME is necessary when using msqyld 10.0.1-MariaDB (and possibly other versions)

Now you can start mariadbd (or mysqld if you are using a version older than MariaDB 10.5) in the debugger:

cd sql
ddd ./mariadbd &

Or start mariadbd on its own:

cd sql
./mariadbd &

After starting up mariadbd using one of the above methods (with the debugger or without), launch the client (as root if you don't have any users setup yet).

../client/mariadb

Using a Storage Engine Plugin

The simplest case is to compile the storage engine into MariaDB:

cmake -DWITH_PLUGIN_<plugin_name>=1` .

Another option is to point mariadbd to the storage engine directory:

./mariadbd --plugin-dir={build-dir-path}/storage/connect/.libs

This page is licensed: CC BY-SA / Gnu FDL

Running Multiple MariaDB Server Processes

It is possible to run multiple MariaDB Server processes on the same server, but there are certain things that need to be kept in mind. This page will go over some of those things.

Configuring Multiple MariaDB Server Processes

If multiple MariaDB Server process are running on the same server, then at minimum, you will need to ensure that the different instances do not use the same datadir, port, and socket. The following example shows these options set in an option file:

[client]
# TCP port to use to connect to mariadbd server
port=3306
# Socket to use to connect to mariadbd server
socket=/tmp/mysql.sock
[mariadb]
# TCP port to make available for clients
port=3306
#  Socket to make available for clients
socket=/tmp/mysql.sock
# Where MariaDB should store all its data
datadir=/usr/local/mysql/data

The above values are the defaults. If you would like to run multiple MariaDB Server instances on the same server, then you will need to set unique values for each instance.

There may be additional options that also need to be changed for each instance. Take a look at the full list of options for mariadbd.

To see the current values set for an instance, see Checking Program Options for how to do so.

To list the default values, check the end of:

mariadbd --help --verbose

Starting Multiple MariaDB Server Processes

There are several different methods to start or stop the MariaDB Server process. There are two primary categories that most of these methods fall into: starting the process with the help of a service manager, and starting the process manually. See Starting and Stopping MariaDB for more information.

If you want to run different MariaDB versions on the same machine, using binary tarballs, Docker or using Virtual Machines (VMs) are the recommended ways. A binary tarball uses least resources, Docker a bit more and a VM uses most resources.

Service Managers

sysVinit and systemd are the most common Linux service managers. launchd is used in MacOS X. Upstart is a less common service manager.

Systemd

RHEL/CentOS 7 and above, Debian 8 Jessie and above, and Ubuntu 15.04 and above use systemd by default.

For information on how to start and stop multiple MariaDB Server processes on the same server with this service manager, see systemd: Interacting with Multiple MariaDB Server Processes.

Starting the Server Process Manually

mariadbd

mariadbd is the actual MariaDB Server binary. It can be started manually on its own.

If you want to force each instance to read only a single option file, then you can use the --defaults-file option:

mariadbd --defaults-file=/etc/my_instance1.cnf

mariadbd-safe

mariadbd-safe is a wrapper that can be used to start the mariadbd server process. The script has some built-in safeguards, such as automatically restarting the server process if it dies. See mariadbd-safe for more information.

If you want to force each instance to read only a single option file, then you can use the --defaults-file option:

mariadbd-safe --defaults-file=/etc/my_instance1.cnf

mariadbd-multi

mariadbd-multi is a wrapper that can be used to start the mariadbd server process if you plan to run multiple server processes on the same host. See mariadbd-multi for more information.

Other Options

In some cases, there may be easier ways to run multiple MariaDB Server instances on the same server, such as:

  • Starting multiple Docker containers.

  • Using dbdeployer (no longer maintained).

This page is licensed: CC BY-SA / Gnu FDL

Specifying Permissions for Schema (Data) Directories and Tables

Default File Permissions

By default MariaDB uses the following permissions for files and directories:

Object Type
Default Mode
Default Permissions

Files

0660

-rw-rw----

Directories

0700

drwx------

Configuring File Permissions with Environment Variables

You can configure MariaDB to use different permissions for files and directories by setting the following environment variables before you start the server:

Object Type
Environment Variable

Files

UMASK

Directories

UMASK_DIR

In other words, if you would run the following in a shell:

export UMASK=0640
export UMASK_DIR=0750

These environment variables do not set the umask. They set the default file system permissions. See MDEV-23058 for more information.

Configuring File Permissions with systemd

If your server is started by systemd, then there is a specific way to configure the umask. See Systemd: Configuring the umask for more information.

This page is licensed: CC BY-SA / Gnu FDL

Switching Between Different Installed MariaDB Versions

This article is about managing many different installed MariaDB versions and running them one at a time. This is useful when doing benchmarking, testing, or for when developing different MariaDB versions.

This is most easily done using the tar files frommariadb.org/download/.

Stopping a pre-installed MySQL/MariaDB from interfering with your tests

If MySQL/MariaDB is already installed and running, you have two options:

  1. Use test MariaDB servers with a different port & socket.

  • In this case you are probably best off creating a specific section for MariaDB in your ~/.my.cnf file.

  1. Stop mysqld with /etc/rc.d/mysql stop or mariadb-admin shutdown.

Note that you don't have to uninstall or otherwise remove MySQL!

How to create a binary distribution (tar file)

Here is a short description of how to generate a tar file from a source distribution. If you havedownloaded a binary tar file, you can skip this section.

The steps to create a binary tar file are:

  • Decide where to put the source. A good place is under /usr/local/src/mariadb-5.#.

  • Get the source

  • Compile the source

  • Create the binary tar ball.

You will then be left with a tar file named something like:mariadb-11.0.1-MariaDB-linux-x86_64.tar.gz

Creating a directory structure for the different installations

Install the binary tar files under /usr/local/ with the following directory names (one for each MariaDB version you want to use), for example:

  • mariadb-10.5

  • mariadb-10.6

  • mariadb-10.11

  • mariadb-11.0

  • mariadb-11.1

The above assumes you are just testing major versions of MariaDB. If you are testing specific versions, use directory names like mariadb-11.0.1

With the directories in place, create a sym-link named mariadb which points at the mariadb-XXX directory you are currently testing. When you want to switch to testing a different version, just update the sym-link.

Example:

cd /usr/local
tar xfz /tmp/mariadb-11.0.1-linux-systemd-x86_64.tar.gz
mv -vi mariadb-11.0.1-MariaDB-systemd-linux-x86_64 mariadb-11.0
ln -vs mariadb-11.0 mariadb

Setting up the data directory

When setting up the data directory, you have the option of either using a shared database directory or creating a unique database directory for each server version. For testing, a common directory is probably easiest. Note that you can only have one mysqld server running against one data directory.

Setting up a common data directory

The steps are:

  1. Create the mysql system user if you don't have it already! (On Linux you do it with the useradd command).

  2. Create the directory (we call it mariadb-data in the example below) or add a symlink to a directory which is in some other place.

  3. Create the mysql permission tables with mariadb-install-db

cd /usr/local/
mkdir mariadb-data
cd mariadb
./bin/mariadb-install-db --no-defaults --datadir=/usr/local/mariadb-data
chown -R mysql mariadb-data mariadb-data/*

The reason to use --no-defaults is to ensure that we don't inherit incorrect options from some old my.cnf.

Setting up different data directories

To create a different data directories for each installation:

cd mariadb
./scripts/mariadb-install-db --no-defaults
chown -R mysql mariadb-data mariadb-data/*

This will create a directory data inside the current directory.

If you want to use another disk you should do:

cd mariadb
ln -s path-to-empty-directory-for-data data
./scripts/mariadb-install-db --no-defaults --datadir=./data
chown -R mysql mariadb-data mariadb-data/*

Running a MariaDB server

The normal steps are:

rm mariadb
ln -s mariadb-# mariadb
cd mariadb
./bin/mysqld_safe --no-defaults --datadir=/usr/local/mariadb-data &

Setting up a .my.cnf file for running multiple MariaDB main versions

If you are going to start/stop MariaDB a lot of times, you should create a ~/.my.cnf file for the common options you are using.

The following example shows how to use a non-standard TCP-port and socket (to not interfere with a main MySQL/MariaDB server) and how to setup different options for each main server:

[client-server]
socket=/tmp/mysql.sock
port=3306
[mysqld]
datadir=/usr/local/mariadb-data

[mariadb-11.0]
# Options for MariaDB 11.0
[mariadb-11.1]
# Options for MariaDB 11.1

If you create an ~/.my.cnf file, you should startmysqld with --defaults-file=~/.my.cnf instead of --no-defaults in the examples above.

This page is licensed: CC BY-SA / Gnu FDL

What to Do if MariaDB Doesn't Start

There could be many reasons that MariaDB fails to start. This page will help troubleshoot some of the more common reasons and provide solutions.

If you have tried everything here, and still need help, you can ask for help on IRC or on the forums - see Where to find other MariaDB users and developers - or ask a question at the Starting and Stopping MariaDB page.

The Error Log and the Data Directory

The reason for the failure will almost certainly be written in the error log and, if you are starting MariaDB manually, to the console. By default, the error log is named host-name.err and is written to the data directory.

Common Locations:

  • /var/log/

  • /var/log/mysql

  • C:\Program Files\MariaDB x.y\data (x.y refers to the version number)

  • C:\Program Files (x86)\MariaDB x.y\data (32bit version on 64bit Windows)

It's also possible that the error log has been explicitly written to another location. This is often done by changing the datadir or log_error system variables in an option file. See Option Files below for more information about that.

A quick way to get the values of these system variables is to execute the following commands:

mariadbd --help --verbose | grep 'log-error' | tail -1
mariadbd --help --verbose | grep 'datadir' | tail -1

Option Files

Another kind of file to consider when troubleshooting is option files. The default option file is called my.cnf. Option files contain configuration options, such as the location of the data directory mentioned above. If you're unsure where the option file is located, see Configuring MariaDB with Option Files: Default Option File Locations for information on the default locations.

You can check which configuration options MariaDB server will use from its option files by executing the following command:

mariadbd --print-defaults

You can also check by executing the following command:

my_print_defaults --mysqld

See Configuring MariaDB with Option Files: Checking Program Options for more information on checking configuration options.

Invalid Option or Option Value

Another potential reason for a startup failure is that an option file contains an invalid option or an invalid option value. In those cases, the error log should contain an error similar to this:

140514 12:19:37 [ERROR] /usr/local/mysql/bin/mariadbd: unknown variable 'option=value'

This is more likely to happen when you upgrade to a new version of MariaDB. In most cases the option file from the old version of MariaDB will work just fine with the new version. However, occasionally, options are removed in new versions of MariaDB, or the valid values for options are changed in new versions of MariaDB. Therefore, it's possible for an option file to stop working after an upgrade.

Also remember that option names are case sensitive.

Examine the specifics of the error. Possible fixes are usually one of the following:

  • If the option is completely invalid, then remove it from the option file.

  • If the option's name has changed, then fix the name.

  • If the option's valid values have changed, then change the option's value to a valid one.

  • If the problem is caused by a simple typo, then fix the typo.

Can't Open Privilege Tables

It is possible to see errors similar to the following:

System error 1067 has occurred.
Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist

If errors like this occur, then critical system tables are either missing or are in the wrong location. The above error is quite common after an upgrade if the option files set the basedir or datadir to a non-standard location, but the new server is using the default location. Therefore, make sure that the basedir and datadir variables are correctly set.

If you're unsure where the option file is located, see Configuring MariaDB with Option Files: Default Option File Locations for information on the default locations.

If the system tables really do not exist, then you may need to create them with mariadb-install-db. See Installing System Tables (mariadb-install-db) for more information.

Can't Create Test File

One of the first tests on startup is to check whether MariaDB can write to the data directory. When this fails, it will log an error like this:

May 13 10:24:28 mariadb3 mariadbd[19221]: 2019-05-13 10:24:28 0 [Warning] Can't create test file /usr/local/data/mariadb/mariadb3.lower-test
May 13 10:24:28 mariadb3 maridbd[19221]: 2019-05-13 10:24:28 0 [ERROR] Aborting

This is usually a permission error on the directory in which this file is being written. Ensure that the entire datadir is owned by the user running mariadbd, usually mysql. Ensure that directories have the "x" (execute) directory permissions for the owner. Ensure that all the parent directories of the datadir upwards have "x" (execute) permissions for all (user, group, and other).

Once this is checked look at the systemd and selinux documentation below, or AppArmor.

Can't Lock Aria Control File

On starting MariaDB, the aria_log_control file is locked. If a lock cannot be obtained, it will log and error like this:

2023-05-01 16:27:03 0 [ERROR] mariadbd: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds

This almost always cause for this is that there is already an existing MariaDB service running on this data directory. Recommend aborting this startup and looking closely for the other MariaDB instance.

The less likely case is there isn't locking available which might occur on a NFS data directory with explictly disable locking.

Unable to lock ./ibdata1 error 11

Like the above for the Aria Control File, this is a attempting to exclusively lock the ibdata1 InnoDB system tablespace. Error 11 corresponds to the system error "OS error code 11: Resource temporarily unavailable" meaning the lock cannot be created.

2023-05-01 16:27:34 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2023-05-01 16:27:34 0 [Note] InnoDB: Check that you do not already have another mariadbd process using the same InnoDB data or log files.
2023-05-01 16:27:34 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2023-05-01 16:27:35 0 [Note] InnoDB: Starting shutdown...

Like the above, this is an indication that a second MariaDB instance is already running on the data directory.

InnoDB

InnoDB is probably the MariaDB component that most frequently causes a crash. In the error log, lines containing InnoDB messages generally start with "InnoDB:".

Cannot Allocate Memory for the InnoDB Buffer Pool

In a typical installation on a dedicated server, at least 70% of your memory should be assigned to InnoDB buffer pool; sometimes it can even reach 85%. But be very careful: don't assign to the buffer pool more memory than it can allocate. If it cannot allocate memory, InnoDB will use the disk's swap area, which is very bad for performance. If swapping is disabled or the swap area is not big enough, InnoDB will crash. In this case, MariaDB will probably try to restart several times, and each time it will log a message like this:

140124 17:29:01 InnoDB: Fatal error: cannot allocate memory for the buffer pool

In that case, you will need to add more memory to your server/VM or decrease the value of the innodb_buffer_pool_size variables.

Remember that the buffer pool will slightly exceed that limit. Also, remember that MariaDB also needs allocate memory for other storage engines and several per-connection buffers. The operating system also needs memory.

InnoDB Table Corruption

By default, InnoDB deliberately crashes the server when it detects table corruption. The reason for this behavior is preventing corruption propagation. However, in some situations, server availability is more important than data integrity. For this reason, we can avoid these crashes by changing the value of innodb_corrupt_table_action to 'warn'.

If InnoDB crashes the server after detecting data corruption, it writes a detailed message in the error log. The first lines are similar to the following:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.

Generally, it is still possible to recover most of the corrupted data. To do so, restart the server in InnoDB recovery mode and try to extract the data that you want to backup. You can save them in a CSV file or in a non-InnoDB table. Then, restart the server in normal mode and restore the data.

MyISAM

Most tables in the mysql database are MyISAM tables. These tables are necessary for MariaDB to properly work, or even start.

A MariaDB crash could cause system tables corruption. With the default settings, MariaDB will simply not start if the system tables are corrupted. With myisam_recover_options, we can force MyISAM to repair damaged tables.

systemd

If you are using systemd, then there are a few relevant notes about startup failures:

  • If MariaDB is configured to access files under /home, /root, or /run/user, then the default systemd unit file will prevent access to these directories with a Permission Denied error. This happens because the unit file set ProtectHome=true. See Systemd: Configuring Access to Home Directories for information on how to work around this.

  • The default systemd unit file also sets ProtectSystem=full, which places restrictions on writing to a few other directories. Overwriting this with ProtectSystem=off in the same way as above will restore access to these directories.

  • If MariaDB takes longer than 90 seconds to start, then the default systemd unit file will cause it to fail with an error. This happens because the default value for the TimeoutStartSec option is 90 seconds. See Systemd: Configuring the Systemd Service Timeout for information on how to work around this.

  • The systemd journal may also contain useful information about startup failures. See Systemd: Systemd Journal for more information.

See systemd documentation for further information on systemd configuration.

SELinux

Security-Enhanced Linux (SELinux) is a Linux kernel module that provides a framework for configuring mandatory access control (MAC) system for many resources on the system. It is enabled by default on some Linux distributions, including RHEL, CentOS, Fedora, and other similar Linux distribution. SELinux prevents programs from accessing files, directories or ports unless it is configured to access those resources.

You might need to troubleshoot SELinux-related issues in cases, such as:

  • MariaDB is using a non-default port.

  • MariaDB is reading from or writing to some files (datadir, log files, option files, etc.) located at non-default paths.

  • MariaDB is using a plugin that requires access to resources that default installations do not use.

Setting SELinux state to permissive is a common way to investigate what is going wrong while allowing MariaDB to function normally. permissive is supposed to produce a log entry every time it should block a resource access, without actually blocking it. However, there are situations when SELinux blocks resource accesses even in permissive mode.

See SELinux for more information.

AppArmor

Add the following to /etc/apparmor.d/tunables/alias if you have moved the datadir:

alias /var/lib/mysql/ -> /data/mariadb/,

The restart AppArmor:

sudo systemctl restart apparmor

This page is licensed: CC BY-SA / Gnu FDL