MariaDB is a Relational Database Management System (RDBMS). It is fully open source, released with a GNU (General Public License), version 2. MariaDB is a fork of MySQL, started by its original author, Michael Widenius and some of MySQL's core developers.
Like other relational database management systems, MariaDB allows us to create and manage relational databases. It can modify the data structure and the data itself as well as answer questions (queries) on that data. The user can communicate with MariaDB by writing statements in SQL (Structured Query Language). The statements can also be composed by a program, which can send them to MariaDB. This allows programs of any type, including web applications, to interact with MariaDB to manage their data.
This chapter describes the steps that need be taken before one starts using MariaDB. In particular, we will discuss the following topics:
Choosing a MariaDB version
Installing MariaDB on various operating systems
Starting and stopping the server
Getting started with the command-line client
Installing and configuring plugins
A major version
A minor version
A patch number
The major and the minor versions identify a tree, for example: 10.0. Each new tree adds some features that were not in the previous tree, but could also add minor incompatibilities. Each patch fixes some bugs, and may introduce some minor features.
We generally want to install the latest stable tree. Older trees can be useful in the rare case where we need to run an application that is not compatible, or runs slower, with the most recent trees. Development trees are not stable, and should not be used in production. However, they are useful for testing new features before they become stable.
Before becoming stable, a tree goes through the following states:
At the time of writing, the most recent stable tree is 10.0, whereas a 10.1 development tree exists.
We always download the most recent release from the chosen tree. The older releases remain available for people who are affected by a recently introduced bug. However, before deciding to downgrade, consider that the recent versions may also fix security vulnerabilities.
The MariaDB documentation contains a list of the trees that are currently supported and a table showing when the support for current versions will be discontinued. The support also depends on the system on which MariaDB is installed, because very old versions of operating systems are not supported. This information is available at the following URL: https://mariadb.com/kb/en/mariadb/development/deprecation-policy/.
All MariaDB packages can be downloaded from https://downloads.mariadb.org/. On Linux, a repository can be used to download the software and automate the upgrades. More details on this topic are provided in the Installing on Linux subsection.
Installing MariaDB as a service means that it will be started on system boot and stopped properly on system shutdown. If MariaDB is not installed as a service, we will need to start and stop it manually.
noinstall package is a ZIP archive that allows us to execute MariaDB on Windows systems without installing it. This is not the optimal way for executing MariaDB; however, this allows us to start using it quickly. For testing purposes, studying purposes, or for any trivial usage, a noinstall package can be acceptable.
To start using MariaDB without installation, all we have to do is to unpack the archive using a file archiver utility that is able to work with the ZIP format.
A good open source program that we can use is 7-zip. It can be downloaded from www.7-zip.org.
MariaDB can be copied to any path, and any valid name can be used for its directory. Commonly used paths for MariaDB on Windows are
C:\MariaDB and C:\MariaDB 10.0.
.MSIfile to use the Windows installation wizard.
This allows us to install MariaDB normally or as a service. We will go through a series of simple steps. We are supposed to read the informative text and set some available options.
Click on the Next button. A Back button is also available in case we are not sure about the previously set options. A Cancel button allows us to abort the installation process.
The first step is merely a welcome text which informs us about the MariaDB version that we are going to install.
Then we are asked to accept the GPL license, which states the user's rights. We have a Print button here in case we prefer to read the license on paper.
To be able to go to the next step, we need to declare that we accept the license by checking the I accept the terms in the License Agreement checkbox, as seen in the following screenshot:
In the next step, we are asked to select the components to be installed. The component Database instance is necessary for a new installation. But this step can be skipped if we want to use an existing database, perhaps one created with an older version of MariaDB or with MySQL. However, in this case, please go through the Upgrading MariaDB section given later in this chapter for details.
Then we are asked to set some basic options. First, we need to choose a password for the user root. We need to set this twice. If we skip this, the root user will have no password. This is acceptable if we are just installing MariaDB on our local machine for development purposes, but is usually a bad idea in other cases. Moreover, disabling remote access for the root user is usually recommended.
By creating an anonymous account, we allow non-authenticated users to access MariaDB. This can be convenient on a local development machine, but, again, this is generally a bad practice in other cases.
By default, MariaDB uses the
latin1 character set. This is usually acceptable for American users as well as many European users, though with
latin1, it is not possible to store names using non-Latin character sets. Nowadays, in most cases, it is preferable to use
Now we need to decide if we want to install MariaDB as a service or not. If it is installed as a service, it starts at the time of system startup, and gracefully stops on system shutdown. In this case, the default name for the service is MySQL for compatibility reasons, but we can change it. We can also change the port number. The Optimize for transactions option should usually be checked. It means that InnoDB will be used as the default storage engine, which is the optimal choice in almost all situations. We are also asked to set the size for the InnoDB cache. For production purposes, it's a good choice to set it up at two-thirds of the available memory. It should also be able to contain all the data that we store in the InnoDB tables. If we are not sure, we can use the default value.
Some Linux distributions include MariaDB in their repositories. If we use one of those distributions, we can easily install MariaDB with a trivial command-line statement or even a graphical utility. In this case, we can check our system's documentation to find out the way to install MariaDB or, more generally speaking, any software package.
However, we may prefer to use the official MariaDB packages and the official MariaDB repositories. If we do so, we can choose any of the MariaDB versions. Moreover, the official repositories guarantee that we are constantly up-to-date with the latest features and bug fixes.
The MariaDB-generic Linux binaries are also available. These packages can be used on any Linux distribution for which a specific package is not available. They also allow a more customized installation: for example, with these binaries, we can install MariaDB in any path we choose. However, note that, if we choose to use a generic binary, we will need to take care of the dependencies.
First, we must inform the tool about what we are using and what we want to install. The following screenshot shows the tool:
Choose a Distro: Click on the name of the Linux distribution. For example: Mint.
Choose a Release: Click on the name of the version of our Linux distribution. For example: Min 17 "Qiana".
Choose a Version: Choose the MariaDB version that we want to install. For example: 10.0.
Choose a Mirror: This is optional, because a mirror is already selected. However, we can choose a mirror that is closer to us, or another mirror if we find it slow.
At this point, in the lower part of the page, we can see the exact steps to be followed for setting up the specified official mirror in our system. The steps to install MariaDB may or may not be included. If they are not, we will follow one of the next sections, or our system's documentation.
.deb packages are used in Debian GNU/Linux and in all the derived Linux distributions, including Ubuntu. These packages can be installed using
apt-get, aptitude, or the graphical package manager synaptic. These tools are generally preinstalled in the distributions derived from Debian.
We can install the
mariadb-client meta-packages to install the most recent stable MariaDB version that is available in the repository, including the command-line clients and tools. The following packages are optional but useful, and provide features that will be discussed in this book:
We can update the local list of packages, and then install MariaDB using apt-get using the following commands:
sudo apt-get update sudo apt-get upgrade sudo apt-get install mariadb-server mariadb-client mariadb-connect-engine-10.0 mariadb-oqgraph-engine-10.0
Note that this code snippet includes the optional packages mentioned previously.
.rpm packages are used in Linux distributions derived from Red Hat, including the community distribution Fedora, the enterprise-level CentOS, SuSE, and Mandriva. To manage these packages, we can use the YUM or up2date tools.
To install the latest version of the MariaDB server and client tools, we need to install the MariaDB-server and MariaDB-client, as follows:
sudo yum update sudo yum install MariaDB-server MariaDB-client
emerge –sync emerge --ask mariadb
Only in cases where we want MariaDB to automatically start on system boot, can we run the following:
rc-update add mysql default
The MariaDB generic binaries can be useful for installing MariaDB on Linux distributions and other UNIX systems for which a specific package does not exist. Moreover, advanced users can modify the general installation procedure to customize the installation.
Users who install MariaDB from generic binaries for Linux/UNIX should be aware of two aspects:
They will need to take care of the dependencies manually
MariaDB updates will not be automatic
The following procedure should work on all Linux systems. If a problem occurs, we should check our system's documentation.
useradd -r mysql cd /usr/local tar zxvf /path/to/<package_name> ln -s <mariadb_dir> mysql cd mysql chown -R mysql . chgrp -R mysql . scripts/mysql_install_db --user=mysql chown -R root . chown -R mysql data
We will need to replace
<package_name> with the name of the file, and
<mariadb_dir> with the name of the file without the
Note that, with this procedure, we can install any number of MariaDB or MySQL versions. Each version will have its own subdirectory inside
/usr/local. A symbolic link called
/usr/local/mysql will point to the version of MariaDB/MySQL in use. Also note that, with this procedure, each installation will have its own data directory, which means that data will not be shared. The data directory should be configured in the
my.cnf file. You also need to configure a different port or Unix socket for each instance if you want to run them at the same time.
The best way to install MariaDB on a MacOS X system is by using Homebrew. It is an unofficial, yet high-quality, open source package manager for MacOS. It is written in the Ruby language, and it requires Apple Xcode, which can be installed from the Apple Store.
So, if Homebrew is not installed on our system, first we need to install it. The following line usually does the trick:
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
To make sure that the setup was successful, we may execute this command:
With Homebrew properly installed, we need to update the packages list and install MariaDB. We can do this with commands that are similar to those supported by Debian's
brew update brew install mariadb
Depending on how MariaDB is installed, it may or may not start automatically at system startup. On Linux, it starts automatically if it is installed using the deb or rpm package. On Windows, it starts automatically if it is installed as a service. Whether or not MariaDB starts automatically, we will be able to start and stop it manually using the command line. The executable files that we need to call, as well as the client and other tools, are situated in the MariaDB binary directory. Since typing this path every time is not convenient, we will add it to the system paths.
On Linux, this is done by adding the path to the
$PATH variable, as in the following example:
However, this change will be lost when the current user logs out. To make it permanent, we must add the preceding line to the
.profile start in our home directory, like in the following example:
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> .bash_profile
On Windows, the procedure to add a path to the
PATH variable is easy but it depends on the system version. Here we will see how to add the MariaDB path on Windows 8:
Open the Control Panel. Click on the System icon, and then on Advanced. Click on the Environment Variables button. Select
PATH, and modify it in the Edit window. Add the path to your MariaDB binary directory. Click on OK.
Now we can start MariaDB by invoking the server executable, as follows:
This command starts a MariaDB demon, the programs which will remain active and waiting for client connections. However, this is not the recommended way to start MariaDB on Linux/UNIX systems. Instead, we can run the
mysqld_safe script, which starts
mysqld, and constantly checks if it is active. If
mysqld_safe tries to restart it. It can be invoked in the following way:
Many options can be passed to
mysqld_safe. Most of them should only be used by advanced users and only on rare occasions; thus, they are beyond the purpose of this book. However, we will see some basic options in the Configuring MariaDB section of this chapter. Meanwhile, we can simply start MariaDB with the default values.
To stop MariaDB, we need a user with the
SHUTDOWN privilege. We will pass the user's credentials (username and password) to the
mysqladmin utility along with the
shutdown option. So, provided that the password for the
root user is
saoirse, the following example will work:
mysqladmin shutdown -uroot -psaoirse
-u option specifies a username, and the
-p option specifies a password. Note that no space is needed after these options.
Sometimes, it could be more convenient to use the
SHUTDOWN SQL command, as shown in the following example:
MariaDB [(none)]> SHUTDOWN; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT version(); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2 "No such file or directory") ERROR: Can't connect to the server
In this book, we will use the
mysql command-line client to run SQL statements. Many open source graphical clients are available for free. They provide an easy way to perform most operations without the need for remembering the syntax of all the SQL statements. The actions performed on the graphic interface are internally converted to SQL statements. Most of these clients also provide the ability to manually type the statements for execution. However, by manually typing the SQL statements into
mysql we will always exactly know what we are doing. Additionally, once we accumulate some experience, this method will probably be the faster one in most situations.
In this section, we will learn to start and quit the command-line client, run statements, and to use the client commands.
To start the client, we need to specify at least the username and the password that we want to use for this session. The options to do this are the same as we used earlier for
mysqladmin. The following example shows how to start mysql and the typical output that appears on the screen:
Now the client is running in an interactive mode, which means that we can enter queries and it will show us an output. When we want to quit the client, we can type the
\quit command or its brief version,
MariaDB [test]> \quit Bye
There are two non-interactive ways to use the command-line client as well. One of them is to pass it a single statement. The client will send the query to the server, which will show us the output and then terminate. This is useful if we do not have other statements to execute. The
-e option can be used to pass a query to mysql, like in the following example:
[email protected]:~$ /usr/local/mysql/bin/mysql -uroot -psaoirse -e "SELECT VERSION()" +---------------------+ | VERSION() | +---------------------+ | 10.0.13-MariaDB-log | +---------------------+
It is also possible to pass the path of a text file containing the SQL statements to MySQL for execution. The client will read the file, execute all the commands, and show us the output. We can do this with a system-independent syntax:
mysql -uroot -proot < 1.sql
Let's look at the prompt, the final line in the preceding example. It starts with the words: MariaDB. This is useful, because the mysql client also allows us to connect to the MySQL databases. But this string informs us that we are connected to MariaDB. Then we see none, which means that no default database is selected.
This means that, in our SQL statements, we always have to specify a database name. For example, if we want to list all the tables in a database, we will type the following:
MariaDB [(none)]> SHOW TABLES FROM test; +-----------------+ | Tables_in_test | +-----------------+ | _xy | +-----------------+ 1 rows in set (0.00 sec)
With a view to typing less verbose statements, when working with a database we can select it with the
USE command. Consider the following example:
MariaDB [(none)]> USE test; Database changed MariaDB [test]> SHOW TABLES; +-----------------+ | Tables_in_test | +-----------------+ | _xy | +-----------------+ 1 rows in set (0.00 sec)
As we can see from these examples, to run an SQL statement, we just need to type it into the
mysql command line. We can separate the words with any number of spaces, tabs, and new line characters. The client knows that a statement is finished when it finds a delimiter, which is, by default, the semicolon character (
;). It is even possible to write more than one statement in one line. Take a look at the following two examples:
MariaDB [test]> SELECT -> VERSION(); +---------------------+ | VERSION() | +---------------------+ | 10.0.13-MariaDB-log | +---------------------+ 1 row in set (0.00 sec) MariaDB [test]> SELECT VERSION(); SELECT PI(); +---------------------+ | VERSION() | +---------------------+ | 10.0.13-MariaDB-log | +---------------------+ 1 row in set (0.00 sec) +----------+ | PI() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec)
In all the preceding examples, the output of the queries is shown in tabular form. But when an output contains many columns, it can be useful to print it vertically. This can be done by terminating a statement with the character,
\G instead of a semicolon. For example:
MariaDB [test]> SHOW CHARACTER SET LIKE 'ascii' \G *************************** 1. row *************************** Charset: ascii Description: US ASCII Default collation: ascii_general_ci Maxlen: 1 1 row in set (0.00 sec)
Even if the client is running in interactive mode, we can still execute the SQL statements from a test file by using the
In the Linux and UNIX systems,
mysql maintains a history of the statements that we execute. We can recall such statements and execute them again. To move backwards and forward through the statement history, we can use the arrow up and arrow down keys. You can also use Ctrl + R, as in any standard shell, to do a reverse search in the history.
In the previous examples, we used some client commands, such as
\quit) to leave the client, and
\G to get the output displayed vertically. A client command is a statement that affects the behavior of
mysql in some way. These commands are never sent to the server. Most client commands are brief strings starting with the backslash character (
SOURCE are client commands too. Here we will see the most useful commands.
\h command, or
\help, shows a list of the available client commands.
Sometimes, we want to completely delete the statement we are typing because of an error. A faster way to achieve the same result is by using the
\c command and pressing Enter.
mysql will simply ignore the line, and will not send it to the server.
\W command (upper case) causes server warnings to be shown in the command line along with fatal errors. The
\w causes warnings to be hidden, but errors will still appear. While the default behavior hides the warnings, examining them could be important to find out if a problem occurs during the execution of statements.
\P can be used to set a pager. A pager is a program used to see statement results. For example, if a query produces large results, we can use less as a pager to be able to scroll through the results. Then,
\n can be used to unset the pager. For example:
MariaDB [(none)]> \P less PAGER set to 'less' MariaDB [(none)]> SELECT * FROM information_schema.COLUMNS \G 2042 rows in set (0.68 sec) MariaDB [(none)]> \n PAGER set to stdout
On the Linux and UNIX systems, we can use the
\e command to compose a statement in an external editor such as Vim or GNU Emacs. The choice of the editor depends on the
$EDITOR system variable.
With the system or
\! command, we can execute a system command, and see its output on the screen. This can be useful in several situations. For example, if we want to install a plugin but we do not remember the file name, we can use one of the following commands to list the contents of the
system ls /usr/local/mysql/lib/plugin/ \! ls /usr/local/mysql/lib/plugin/
The values of the server variables are set in the following ways:
For each variable, there exists a default value.
One or more configuration files can be read by MariaDB at startup. Each file sets some variables, overriding the default values.
mysqldcan be called with some options. In this case, each option overrides the value of a server variable.
At runtime, it is still possible to change the values of some variables. These variables are called dynamic variables. The values of static variables cannot be changed at runtime; thus, modifying such variables requires a server restart.
Some variables exist in the global and session context. This means that every session can have a specific value for those variables. In this case, the global value serves as the default: when a new session is created, its value is copied from the global value. The session value can be changed later. Changing the global value does not affect existing connections but only new ones.
As explained earlier, MariaDB can read more than one configuration system. On startup, MariaDB accesses some paths in a predetermined order, seeking configuration files. Each file overrides the settings that were previously read from other files.
The paths in which MariaDB looks for configuration files are system-dependent. On Linux, it reads the following paths, in the given order:
The file indicated with the
On Windows, the following paths are read:
%PROGRAMDATA%\MariaDB\MariaDB Server 10.0
MariaDB installation directory
The file indicated with the
On Linux, configuration files are expected to be called
my.cnf. On Windows, they can be called
Note that installers create a default configuration file with values that are usually acceptable if we want to develop applications with MariaDB on our local machine only. On Linux, this file is usually located in
For each file, MariaDB reads the following option groups in the same order (replace
X.X with the proper version number, for example 10.0):
The client-server group is very useful, because it is read by both the client and the server. This allows specifying the parameters that need to be used by both (for example, the port number or the path for the socket file) only once.
The following minimal example shows the syntax to be used in the configuration files:
[client-server] port=3306 socket=/tmp/mysql.sock
As mentioned before, it is possible to pass options to
mysqld at startup. These options override the settings in the configuration files. On Linux, the same options can also be passed to
mysqld_safe, which will pass them to
The names of the command-line options are very similar to the configuration files options, except that they generally begin with a double dash (
-), and use dashes as word separators instead of underscores (
For example, the
innodb_buffer_pool_size server variable determines the size of the main MariaDB cache in bytes. It can be set in the configuration files with this syntax:
innodb_buffer_pool_size = 134217728
Or by using
A server variable is a setting whose value somehow affects the behavior of MariaDB. If a variable is only global, its value applies to all the connections or to some internal mechanism of the server. If a corresponding session variable exists, each session value affects a particular connection. The global value is still important, because it represents the initial value for the session variable. However, modifying a global variable affects only new connections; the corresponding session variables will remain untouched for the existing connections.
Also note that we can only modify dynamic variables. We can read the value of static variables, but trying to modify them will cause an error.
Server variables can be read and modified using the
SET SQL statements.
If we need to read or modify a session variable, we can use the following syntax:
MariaDB [(none)]> SET @@session.sql_mode = 'strict_trans_tables'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT @@session.sql_mode; +---------------------+ | @@session.sql_mode | +---------------------+ | STRICT_TRANS_TABLES | +---------------------+ 1 row in set (0.00 sec)
Similarly, to read or modify a global variable, we will use the following syntax:
MariaDB [(none)]> SET @@global.sql_mode = 'no_zero_date'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT @@global.sql_mode; +-------------------+ | @@global.sql_mode | +-------------------+ | NO_ZERO_DATE | +-------------------+ 1 row in set (0.00 sec)
Some global variables, but not all, can be set to their default value by specifying
DEFAULT instead of a value. It is possible to set a session variable to the value of the corresponding global variable with the same syntax.
If a package manager has been used to install MariaDB, the patch upgrades, like 10.0.12 to 10.0.13, are automatic. Each package manager supports a command to upgrade the packages, and will also take care to upgrade the dependencies if needed. If no package manager is used (for example, when we are working on Windows or if we have installed the generic Linux/UNIX binaries), we will need to manually uninstall and re-install MariaDB. This is also true for minor or major version upgrades such as 10.0 to 10.1. The following commands can be used with the package managers that have been used in this chapter:
sudo apt-get update sudo apt-get upgrade
sudo yum update MariaDB-server MariaDB-client
emerge -avDuN mariadb
With Homebrew (MacOS X):
brew upgrade mariadb
Usually, we want the new version to read our old database. So, first we need to set up the data directory, which is the directory in which the databases are written by default. A common path is
/var/mysql. To use this path, we will add the following line in the configuration file:
This setting will not take effect until MariaDB is restarted.
At this point, we need to upgrade the format of our databases. Since the data has been written with an older release, some modifications to the files could be necessary before the new version of MariaDB can read it. For this purpose, MariaDB distributions include a tool called
mysql_upgrade. This must be done even if a package manager has been used for upgrading MariaDB.
mysql_upgrade tool must be run when the server is running. It reads the options from the configuration files. However, if the login credentials are not written in the configuration files, the typical invocation for
mysql_upgrade is the following:
mysql_upgrade -u<user_name> -p<password>
<user_name> with a valid username and
<password> with the corresponding password.
mysql_upgrade, we will be able to work again without databases!
MariaDB has several functionalities, but more can be added by installing plugins. This allows the users to deactivate some unneeded functionality by uninstalling a plugin, or activating functionalities that are not needed by the majority of users. More importantly, some plugins implement the same class of functionalities in different ways. This is the case with storage engines, a special plugin type that will be discussed in Chapter 2, Databases and Tables. Some plugins are developed by the MariaDB team, others by individuals or companies that are members of the community. Several plugins, developed by the MariaDB team or by third parties, are included in the official MariaDB distributions. Others are available at their respective developer's websites.
Plugins are contained in files with the
.so extension on Linux and with the
.ddl extension on Windows. Each file is a library that can contain one or more plugins. These files need to be located in the plugins directory. To discover the path of such a directory in our MariaDB installation, we can query the
@@plugin_dir server variable:
MariaDB [(none)]> SELECT @@plugin_dir; +------------------------------+ | @@plugin_dir | +------------------------------+ | /usr/local/mysql/lib/plugin/ | +------------------------------+ 1 row in set (0.00 sec)
MariaDB provides some SQL statements to manage plugins at runtime. The following list shows these statements, before discussing them in detail:
SHOW PLUGINSdisplays a list of available plugins
INSTALL SONAMEinstalls all the plugins from a file
UNINSTALL SONAMEuninstalls all the plugins contained in a library
INSTALL PLUGINinstalls an individual plugin
UNINSTALL PLUGINuninstalls an individual plugin
MariaDB [(none)]> SHOW PLUGINS; +-----------------------------+----------+--------------------+---------------------+---------+ | Name | Status | Type | Library | License | +-----------------------------+----------+--------------------+---------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | ... +-----------------------------+----------+--------------------+---------------------+---------+ 54 rows in set (0.00 sec)
The list has been truncated because it was very long. However, from the example, we can see that five columns are returned by this statement:
Name: Plugin name.
ACTIVEmeans that the plugin is installed,
INACTIVEmeans that the plugin is not installed,
DISABLEDmeans that the plugin has been disabled with a server option and cannot be installed, and
DELETEDmeans that the library file has been removed.
Type: This value indicates the plugin type. For example, the value
AUTHENTICATIONmeans that the plugin handles the user's login, and
INFORMATION SCHEMAmeans that the plugin provides metainformation to the user.
Library: This indicates the library file name. If this value is
NULL, the plugin is built-in and cannot be uninstalled.
License: Indicates the plugin's license, which determines the user's rights. This is just the license name: the complete text should be provided as a file distributed along with the plugin.
If a library contains more than one plugin, we will want to install them all to enable the whole set of related functionalities. For this reason, we will usually prefer the
INSTALL SONAME statement. The name of the file must be passed to this statement. The file extension is optional, which allows us to install a library on any system using the identical command. For example, to install the
SEQUENCE storage engine, we use the following command:
INSTALL SONAME 'ha_sequence';
Similarly, we can uninstall the whole set of plugins with
UNINSTALL SONAME, like in the following example:
UNINSTALL SONAME 'ha_sequence';
In very rare cases, we may want to install or uninstall a single plugin. In such cases, we will use the
INSTALL PLUGIN or
UNINSTALL PLUGIN statement, specifying the name of the plugin that we want to install or uninstall, and the file name. For example:
INSTALL PLUGIN sequence SONAME 'ha_sequence'; UNINSTALL PLUGIN sequence;
Some plugins create a set of server variables that can be used to configure them at runtime. Such variables do not exist until the plugin is installed or after it is uninstalled. By convention, usually all these variables have the same prefix, which is the plugin name. This makes it easier to discover them with the
SHOW VARIABLES statement. The following example shows how this mechanism works:
MariaDB [(none)]> SHOW VARIABLES LIKE 'spider%'; Empty set (0.00 sec) MariaDB [(none)]> INSTALL SONAME 'ha_spider'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'spider%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | spider_auto_increment_mode | -1 | | spider_bgs_first_read | -1 | ... +---------------------------------------+-------+ 99 rows in set (0.00 sec)
In this chapter, we discussed the preliminary tasks that we need to perform before we can start working with MariaDB. In particular, we discussed the way to choose a MariaDB version and install it. We learned how to start and stop MariaDB, and how to efficiently use the
mysql command-line client. Several other clients exist, but this is the one we will use for all the examples in this book. We learned how to set the MariaDB options in the configuration files or at the mysqld invocation. Some settings can be set at runtime, and sometimes they can be adjusted on a per-session basis: we discussed how to do this. MariaDB upgrades were also been discussed in the chapter. Finally, we learned how to install, uninstall, and configure MariaDB plugins.
In the next chapter, we will learn how to create new databases and tables, the data types that are available, how storage engines work, and the use of indexes.