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.
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.
sysVinit and systemd are the most common Linux service managers. launchd is used in MacOS X. Upstart is a less common service manager.
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.
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 is used in MacOS X.
Ubuntu 14.10 and below use Upstart by default.
mariadbd is the actual MariaDB Server binary. It can be started manually on its own.
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 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 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
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.
This page is licensed: CC BY-SA / Gnu FDL
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
.
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.
The contents of the mariadb.service
file can be examined with systemctl show mariadb.service
.
The service can be interacted with by using the systemctl command.
MariaDB's systemd
service can be configured to start at boot by executing the following:
sudo systemctl enable mariadb.service
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.
MariaDB's systemd
service can be stopped by executing the following:
sudo systemctl stop mariadb.service
MariaDB's systemd
service can be restarted by executing the following:
sudo systemctl restart mariadb.service
The status of MariaDB's systemd
service can be obtained by executing the following:
sudo systemctl status mariadb.service
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.
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, and
ExecStartPre=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'
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.
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.
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.
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.
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.
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 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.
no option
Service startup timeout. See Configuring the Systemd Service Timeout.
no option (see MDEV-9264)
e.g. -600 to lower priority of OOM killer for mariadbd
Limit on number of open files. See Configuring the Open Files Limit.
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
ExecStartPre=/usr/sbin/sysctl -q -w vm.drop_caches=3
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
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.
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.
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
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
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 StandardError=syslog.
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.
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.
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
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.
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
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
.
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.
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.
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.
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.
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.
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.
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.
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.
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..
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.
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 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
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
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
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.
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 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
.
The service can be interacted with by using the service command.
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
service mysql start
service mysql stop
service mysql restart
service mysql status
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.
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
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.
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>
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:
--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.)
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:
--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.
mariadbd-safe
reads options from the following option groups from option files:
[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:
[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:
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
.
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.
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.
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.
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.
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
.
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.
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:
Runs a number of system and option checks.
Runs a check on MyISAM tables.
Provides a screen presence for the MariaDB server.
Starts mariadbd, monitors it, and restarts it if it terminates in error.
Sends error messages from mariadbd to the host_name.err file in the data directory.
Sends mariadbd-safe screen output to the host_name.safe file in the data directory.
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)"
This page is licensed: CC BY-SA / Gnu FDL
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.
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
mariadbd-multi
supports the following options:
--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.
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:
--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.
mariadbd-multi
reads options from the following option groups from option files:
[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
:
[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:
[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
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
.
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.
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
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.
--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
.
--defaults-extra-file
Commandline: --defaults-extra-file=name
Description: Read this extra option file after all other option files are read.
--defaults-file
Commandline: --defaults-file=name
Description: Only read options from the given option file.
--defaults-group-suffix
Commandline: --defaults-group-suffix=name
Description: In addition to the default option groups, also read option groups with the given suffix.
--no-defaults
Commandline: --no-defaults
Description: Don't read options from any option file.
--print-defaults
Commandline: --print-defaults
Description: Read options from option files, print all option values, and then exit the program.
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.
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.
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.
The following options and system variables are related to using MariaDB on Windows:
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.
The following options and system variables are related to replication and the binary log:
The options and system variables related to Semisynchronous Replication are described here.
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
--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.
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.
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.
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
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.
innodb-spin-wait-delay
skip-innodb
Options related to the Aria storage engine are listed below:
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.
The options and system variables related to the MyRocks storage engine can be found here.
The options and system variables related to the S3 storage engine can be found here.
The options related to the CONNECT storage engine are described below.
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.
The options and system variables related to the Spider storage engine can be found here.
The options and system variables related to the Mroonga storage engine can be found here.
The options and system variables related to the TokuDB storage engine can be found here.
The options and system variables related to Vectors storage engine (beginning with mhnsw
) can be found here.
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
Some options and system variables related to the Performance Schema can be found here. Direct links to many of them can be found below.
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.
Some options and system variables related to Galera Cluster can be found here. Direct links to many of them can be found below.
--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!
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.
ed25519
The options related to the ed25519 authentication plugin can be found here.
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.
named_pipe
The options related to the named_pipe authentication plugin can be found here.
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.
unix_socket
The options related to the unix_socket authentication plugin can be found here.
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.
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.
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.
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.
server_audit
Options and system variables related to the server_audit audit plugin can be found here.
SQL_ERROR_LOG
The options and system variables related to the SQL_ERROR_LOG audit plugin can be found here.
The options related to the QUERY_RESPONSE_TIME_AUDIT audit plugin can be found here.
handlersocket
The options for the HandlerSocket plugin are all described on the HandlerSocket Configuration Option page.
DISKS
The options related to the DISKS information schema plugin can be found here.
feedback
The system variables related to the feedback plugin can be found here.
The options related to the feedback plugin can be found here.
LOCALES
The options related to the LOCALES information schema plugin can be found here.
METADATA_LOCK_INFO
The options related to the METADATA_LOCK_INFO information schema plugin can be found here.
QUERY_CACHE_INFO
The options related to the QUERY_CACHE_INFO information schema plugin can be found here.
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.
user_variables
The options related to the user_variables information schema plugin can be found here.
WSREP_MEMBERSHIP
The options related to the WSREP_MEMBERSHIP information schema plugin can be found here.
WSREP_STATUS
The options related to the WSREP_STATUS information schema plugin can be found here.
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.
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.
You can verify the default values for an option by doing:
mariadbd --no-defaults --help --verbose
This page is licensed: GPLv2
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
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.
The command to use mysql.server
and the general syntax is:
mysql.server [ start | stop | restart | status ] <options> <mariadbd_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:
--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.
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:
--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.
mysql.server
reads options from the following option groups from option files:
[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:
[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.
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
mysql.server
can be found in the support-files
directory under your MariaDB installation directory or in a MariaDB source distribution.
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.
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
You can run mariadbd directly from the build directory (without doingmake install
).
On Windows, the data directory is produced during the build.
The simplest way to start database from the command line is:
Go to the directory where mariadbd.exe is located (subdirectory sql\Debug or sql\Relwithdebinfo of the build directory)
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
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
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
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.
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
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.
sysVinit and systemd are the most common Linux service managers. launchd is used in MacOS X. Upstart is a less common service manager.
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.
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 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 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.
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
By default MariaDB uses the following permissions for files and directories:
Files
0660
-rw-rw----
Directories
0700
drwx------
You can configure MariaDB to use different permissions for files and directories by setting the following environment variables before you start the server:
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.
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
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/.
If MySQL/MariaDB is already installed and running, you have two options:
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.
Stop mysqld with /etc/rc.d/mysql stop
or mariadb-admin shutdown
.
Note that you don't have to uninstall or otherwise remove MySQL!
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.#
.
You will then be left with a tar file named something like:mariadb-11.0.1-MariaDB-linux-x86_64.tar.gz
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
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.
The steps are:
Create the mysql
system user if you don't have it already!
(On Linux you do it with the useradd
command).
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.
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.
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/*
The normal steps are:
rm mariadb
ln -s mariadb-# mariadb
cd mariadb
./bin/mysqld_safe --no-defaults --datadir=/usr/local/mariadb-data &
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
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 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
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.
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.
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.
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.
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.
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 is probably the MariaDB component that most frequently causes a crash. In the error log, lines containing InnoDB messages generally start with "InnoDB:".
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.
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.
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.
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.
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.
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