Mariadb configuration settings. How to configure MariaDB SSL and establish secure connections from various clients. Switching from MySQL to MariaDB on Windows

I decided to stop using MySQL, or rather, completely transfer all my servers to its fork - MariaDB. Taking this opportunity, I would like to talk about the process of installing MariaDB 10.1 on Debian 8. It should be noted that brief description MariaDB installations are available on the official project page. I decided to devote a separate post to this issue, in which I want to describe the necessary actions after installing MariaDB on the server.

Before installing MariaDB, you need to add its repository. The MariaDB website recommends installing the software-properties-common package for this. I don't see any point in this and prefer to do everything manually.

Register the GPG key of the repository in the system:

Apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Add a description of the repository to the sources.list file. Open the file in the nano editor:

Nano /etc/apt/sources.list

Copy the following lines to the end:

Deb http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main deb-src http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main

We update the list of available packages:

Apt-get update

Let's start the MariaDB 10.1 installation process:

Apt-get install mariadb-server

During installation we will be asked to enter a password for the root user. This completes the process of installing MariaDB on Debian 8. Now let's move on to setting up the server.

To increase the reliability of our server, we need to do minimum requirements security. Prohibit authorization under the root user from remote hosts. If there is a test database and an anonymous user, you need to remove them from the server. To make the task easier, use the script:

Mysql_secure_installation

Default storage type

If you need to change the default data storage type, add the following lines to the my.cnf file:

Default-storage-engine = innodb

Make sure MariaDB uses InnoDB tables by default. To do this, run the command:

SHOW ENGINES;

Create a MariaDB user and database

To create a user in MariaDB use the command below:

CREATE USER "USER_NAME"@"localhost" IDENTIFIED BY "PASSWORD";

Create a new database:

CREATE DATABASE database_name;

We give full rights to the user USER_NAME on the databasename database:

GRANT ALL PRIVILEGES ON database_name.* TO "USER_NAME"@"localhost";

Now you need to update all privileges:

FLUSH PRIVILEGES

To view privileges, run the command:

SHOW GRANTS FOR "USER_NAME"@"localhost";

Binary logs

MariaDB writes all database changes to a binary log; it is necessary for the replication mechanism to work. If you did not make backups or they are outdated, binary logs can be used to restore data. However, there is no guarantee that the data will be fully or partially recovered. Success will depend on the size, storage time of binary logs, and frequency of backups.

To disable binary logs, comment out the lines in the my.cnf file:

#log_bin = /var/log/mysql/mariadb-bin #log_bin_index = /var/log/mysql/mariadb-bin.index

I'm looking to install MariaDB SSL (Secure Sockets Layer) as well as secure connections from a MySQL client and a PHP application. How to enable SSL for MariaDB server and client running on Linux or Unix-like system?

MariaDB is a database server that offers functionality wedges for MySQL server.

MariaDB was created by some of the original authors of MySQL, with the help of a wider staff of Free developers and others software open source. In addition to the core MySQL features, MariaDB offers a rich set of feature enhancements, including alternative storage engines, server optimizations, and other fixes. In this guide I'm going to talk about how to set up MariaDB server with SSL and how to establish secure connections using the console and PHP scripts.

When creating SSL certificates, it is important to use 192.168.1.100 as the standard name.

Step 1 – Install MariaDB

Enter the command according to your Linux or Unix variant.

Installing MariaDB server/client on Ubuntu/Debian Linux

Enter one of the following commands: apt-get command or apt command:

$ sudo apt-get install mariadb-server mariadb-client

Installing MariaDB server/client on CentOS/RHEL/Fedora Linux

Enter the following yum command:

$ sudo yum install mariadb-server mariadb

For Fedora Linux users, you need to enter the dnf command:

$ sudo dnf install mariadb-server mariadb

Installing MariaDB server/client on Arch Linux

Enter the following pacman command:

$ sudo pacman -S mariadb

Installing MariaDB server/client on FreeBSD unix

To set the port, run:

# cd /usr/ports/databases/mariadb100-server/ && make install clean # cd /usr/ports/databases/mariadb100-client/ && make install clean

To add a binary package, enter:

# pkg install mariadb100-server mariadb100-client

Step 2 – Ensuring a secure MariaDB installation

Enter the following command:

$mysql_secure_installation

Step 3 – Create a CA certificate

Create a directory called ssl in /etc/mysql/ directory:

$ cd /etc/mysql $ sudo mkdir ssl $ cd ssl

Meaning: The Common Name used for the server and client certificates/keys must be different from the Common Name used for the CA certificate. To avoid any problems I install them like this:

Standard CA name: MariaDB admin
Standard server name: MariaDB server
Standard client name: MariaDB client

Enter the following command to create a new CA key:

$ sudo openssl genrsa 2048 > ca-key.pem

Examples of possible data outputs:

Enter the following command to create a certificate using this key:

$ sudo openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
Figure.03: Using CA key, generate CA certificate for MariaDB

Examples of possible data outputs:

You should now have the following two files::

1. /etc/mysql/ssl/ca-cert.pem – Certificate file for the Certificate Authority (CA).
2. /etc/mysql/ssl/ca-key.pem – Key file for the Certificate Authority (CA).

I'm going to use both files to create server and client certificates.

Step 4 – Create a Server Certificate

To create a server key, run:

$ sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

Examples of possible data outputs:

Then process the server's RSA key by entering:

$ sudo openssl rsa -in server-key.pem -out server-key.pem

Examples of possible data outputs:

Writing RSA key

Finally, sign the server certificate by running:

$ sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Examples of possible data outputs:

Signature ok subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=MariaDB server Getting CA Private Key

You should now have additional files:

1. /etc/mysql/ssl/server-cert.pem– MariaDB server certificate file.
2. /etc/mysql/ssl/server-key.pem – MariaDB server key file.

You must use at least two files on the MariaDB server and any other nodes you intend to use for cluster/replication traffic. These two files will secure the communication on the server side.

Step 5 – Create a Client Certificate

mysql client and application PHP/Python/Perl/Ruby will use the client certificate to secure client connectivity. You must install the following files on all your clients, including the web server. To create a client key, run:

$ sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem

Examples of possible data outputs:

Then process the RSA client key by entering

$ sudo openssl rsa -in client-key.pem -out client-key.pem writing RSA key

Finally, sign the client certificate by running:

$ sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Examples of possible data outputs:

Signature ok subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=MariaDB client Getting CA Private Key

Step 6 – How to verify certificates?

Enter the following command to check the certificates to ensure everything was created correctly:

$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

Examples of possible data outputs:

Server-cert.pem: OK client-cert.pem: OK

There should be no errors and you should receive an OK response for the server and client certificates.

Step 7 – Configure MariaDB Server to Use SSL

Edit the file vi /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/mariadb.cnf as follows:

$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

Add like this:

###My SQL Server### ## Securing the Database with ssl option and certificates ## ## There is no control over the protocol level used. ## ## mariadb will use TLSv1.0 or better. ## ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key. pem

Save and close the file. You can restart mariadb like this:

$ sudo /etc/init.d/mysql restart

$ sudo systemctl restart mysql

Step 8 – Configure the MariaDB client to use SSL

Configure MariaDB client as 192.168.1.200 to use SSL (add to /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf):

$ sudo vi /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

Add to section:

## MySQL Client Configuration ## ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-key=/etc/mysql/ssl/ client-key.pem ### This option is disabled by default ### ### ssl-verify-server-cert ###

Save and close the file. You should copy the files /etc/mysql/ssl/ca-cert.pem . /etc/mysql/ssl/client-cert.pem and /etc/mysql/ssl/client-key.pem for all your clients. For example:

(vivek@server): rsync /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/client-cert.pem /etc/mysql/ssl/client-key.pem\user@client:/etc /mysql/ssl

Step 9 – Check

Enter the following command:

$ mysql -u (User-Name-Here) -h (Server-IP-here) -p (DB-Name-Here) $ mysql -u root -h 192.168.1.100 -p mysql $ mysql -u root -h 127.0 .0.1 -p mysql

Enter the following SHOW VARIABLES LIKE '%ssl%'; command in MariaDB [(none)]> line:

MariaDB [(none)]> SHOW VARIABLES LIKE "%ssl%";

OR run the status command:

MariaDB [(none)]> status;

Examples of possible data outputs:

Check SSL connections and TLS. The following command should fail because ssl 3 is not supported and therefore not configured for use:

$ openssl s_client -connect 192.168.1.100:3306 -ssl3 140510572795544:error:140A90C4:SSL routines:SSL_CTX_new:null ssl method passed:ssl_lib.c:1878:

Check TLS v 1/1.1/1.2:

$ openssl s_client -connect 192.168.1.100:3306 -tls1 $ openssl s_client -connect 192.168.1.100:3306 -tls1_1 $ openssl s_client -connect 192.168.1.100:3306 -tls1_2

Examples of possible data outputs:

CONNECTED(00000003) --- no peer certificate available --- No client certificate CA names sent --- SSL handshake has read 5 bytes and written 7 bytes --- New, (NONE), Cipher is (NONE) Secure Renegotiation IS NOT supported Compression: NONE Expansion: NONE No ALPN negotiated SSL-Session: Protocol: TLSv1 Cipher: 0000 Session-ID: Session-ID-ctx: Master-Key: Key-Arg: None PSK identity: None PSK identity hint: None SRP username: None Start Time: 1485335036 Timeout: 7200 (sec) Verify return code: 0 (ok) ---

How to read a tcpdump packet capture file to test secure communications

Finally, you can use the tcpdump command packet analyzer, which runs under the command line, to look at port 3306:

$ sudo tcpdump -i eth0 -s 65535 port 3306 -w /tmp/mysql.pcap

Now connect to your application PHP/Python/Perl/Ruby mysql or mysql console application:

$ mysql -u bar -h 192.168.1.100 -p foo

Use tcpdump to verify that no text information, including passwords, is exchanged between the server and client. This is done as follows:

$ tcpdump -r /tmp/mysql.pcap | less

Step 10 – Adding a user to the MariaDB server

Enter the following command:

$ mysql -u root –p

Create a database called foo :

CREATE DATABASE foo;

Create a user named bar for for a database called foo :

GRANT ALL ON foo.* TO bar@localhost IDENTIFIED BY "mypassword" REQUIRE SSL;

Providing access from a web server located at 192.168.1.200:

GRANT ALL ON foo.* TO [email protected] IDENTIFIED BY "mypassword" REQUIRE SSL;

Create a secure connection from a bash shell

You can login from the console like this:

$ mysql -u bar -p -h 192.168.1.100 foo

Create a secure connection from Python

First install the interface Python for MySQL:

$ sudo apt-get install python-mysql.connector

OR for Python v3.x

$ sudo apt-get install python3-mysql.connector

Here's an example Python code for a secure connection using:

#!/usr/bin/python import MySQLdb ssl = ("cert": "/etc/mysql/ssl/client-cert.pem", "key": "/etc/mysql/ssl/client-key.pem" ) conn = MySQLdb.connect(host="192.168.1.100", user="bar", passwd="mypassword", ssl=ssl) cursor = conn.cursor() cursor.execute("SHOW STATUS LIKE "Ssl_cipher"" ) print cursor.fetchone()

#!/usr/bin/python # Note (Example is valid for Python v2 and v3) from __future__ import print_function import sys import mysql.connector from mysql.connector.constants import ClientFlag config = ( "user": "bar", " password": "mypassword", "host": "192.168.1.100", "client_flags": , "ssl_ca": "/etc/mysql/ssl/ca-cert.pem", "ssl_cert": "/etc/mysql /ssl/client-cert.pem", "ssl_key": "/etc/mysql/ssl/client-key.pem", ) cnx = mysql.connector.connect(**config) cur = cnx.cursor(buffered= True) cur.execute("SHOW STATUS LIKE "Ssl_cipher"") print(cur.fetchone()) cur.close() cnx.close()

Examples of possible data outputs:

("Ssl_cipher", "DHE-RSA-AES256-SHA")

Now I’ll tell you how to install MariaDB on Debian. I’ll also tell you how to use it in my topic “Installing MariaDB on Debian”; all this will be described in detail.

MariaDB is a lightweight replacement for MySQL. MariaDB is similar to MySQL and it aims to be best choice for database professionals looking for reliable, scalable SQL Server. This guide will help beginners install and understand MariaDB on Debian 7 and 6. To achieve this goal, the MariaDB Foundation works closely and collaboratively with a large community of users and developers in the true spirit of free and open source software, and releases software in a way that it was highly reliable.

The impetus for its creation was the need to ensure the free status of the DBMS (under the GPL license), as opposed to the vague licensing policy of MySQL by Oracle. The lead developer is Michael Widenius, author of the original version of MySQL and founder of Monty Program AB.

MariaDB abandoned the InnoDB storage subsystem and replaced it with XtraDB. Also included are the Aria (en:Aria (storage engine)), PBXT and FederateX subsystems.

First, let's update the OS (so that everything is new):

# apt-get update # apt-get upgrade

Installing Python add-ons:

# sudo apt-get install python-software-properties

Installing MariaDB

First, you need to import the GPG key so that APT will check the integrity of the packages and download everything:

# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

We create our own MariaDB sources.list file to add the repository to it:

# vim /etc/apt/sources.list

Installing MariaDB 5.5 for Debian 7 wheezy

# MariaDB 5.5 repository list - created 2014-11-15 17:08 UTC deb http://mirror.23media.de/mariadb/repo/5.5/debian wheezy main deb-src http://mirror.23media.de/mariadb /repo/5.5/debian wheezy main

Installing MariaDB 5.5 for Debian 6 squeeze

# MariaDB 5.5 repository list - created 2014-11-15 17:14 UTC deb http://mirror.23media.de/mariadb/repo/5.5/debian squeeze main deb-src http://mirror.23media.de/mariadb /repo/5.5/debian squeeze main

Installing MariaDB 10.0 for Debian 7 wheezy

# MariaDB 10.0 repository list - created 2014-11-15 17:21 UTC deb http://mirror.23media.de/mariadb/repo/10.0/debian wheezy main deb-src http://mirror.23media.de/mariadb /repo/10.0/debian wheezy main

Installing MariaDB 10.0 for Debian 6 squeeze

# MariaDB 10.0 repository list - created 2014-11-15 17:22 UTC deb http://mirror.23media.de/mariadb/repo/10.0/debian squeeze main deb-src http://mirror.23media.de/mariadb /repo/10.0/debian squeeze main

Installing MariaDB 10.1 for Debian 7 wheezy

# MariaDB 10.1 repository list - created 2014-11-15 17:23 UTC deb http://mirror.23media.de/mariadb/repo/10.1/debian wheezy main deb-src http://mirror.23media.de/mariadb /repo/10.1/debian wheezy main

Installing MariaDB 10.1 for Debian 6 squeeze

# MariaDB 10.1 repository list - created 2014-11-15 17:23 UTC deb http://mirror.23media.de/mariadb/repo/10.1/debian squeeze main deb-src http://mirror.23media.de/mariadb /repo/10.1/debian squeeze main

After that, we update the system (list of all repositories) and install the server with MariaDB:

# sudo apt-get update # sudo apt-get install mariadb-server

After which the installation of the MariaDB server will begin. I chose version 10.0 and for this reason I will give an installation example. During installation, you will be asked to enter the password for the MariaDB user - root.

Enter the password and click OK. You also need to enter the 2nd time (confirmation). The installation process will take a couple of minutes.

Using MariaDB

In this section, you will learn how to connect to MariaDB and how to use basic SQL commands.

The standard tool for interacting with MariaDB is the MySQL client program. To get started, run the following command to connect to MariaDB as root:

# mysql -u root -p

Enter the password that you entered (created) during installation.

Let's try to create a simple database that we will later populate with data. Enter the following commands to create a database named TEST_DB, owned by new user Test_User, we will also set the secret_password password for the user with the command:

MariaDB [(none)]> CREATE DATABASE test_db; MariaDB [(none)]> GRANT ALL PRIVILEGES ON test_db.* TO test_user@localhost IDENTIFIED BY "secret_password"; MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> quit

The last line (command) is coming out of the root user on MariaDB. And now let's log in as user test_user:

# mysql -u testuser -p

Let's enter the command to use the newly created database (test_db):

MariaDB [(none)]> USE test_db;

Create a new table and fill it with some data:

MariaDB [(none)]> CREATE TABLE products (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), price DECIMAL(6,2)); MariaDB [(none)]> INSERT INTO products (name, price) VALUES ("MacBook_Pro", 3200.0); MariaDB [(none)]> INSERT INTO products (name, price) VALUES ("Asus", 340.0); MariaDB [(none)]> INSERT INTO products (name, price) VALUES ("HP", 745.0);

Make sure the new data is inserted properly:

MariaDB [(none)]> SELECT * FROM products;

Exit the MariaDB client by running:

MariaDB [(none)]> quit

To receive additional information O SQL commands do:

MariaDB [(none)]>\h

Setting up MariaDB

To configure MariaDB you need to edit the configuration file. This file controls most of the server's system variables, which you would normally leave at default.

# vim /etc/mysql/my.cnf

restart the server by running the following command:

# service mysql restart

MariaDB Security

# mysql_secure_installation

You will be prompted to change the administrator password, remove anonymous users, disable logins outside the local host, delete all anonymous users, and delete the test database. It is recommended that you answer “Y” to all questions.

Remote user connections

Let's take a look at how to allow the previously created Test_User to connect to MariaDB remotely (by default, MariaDB only allows connections from the localhost).

Exposing the MariaDB server to the Internet makes it less secure. If you need to connect from another server, make sure you apply firewall rules that only allow connections from certain IP addresses.
First, we need to provide custom connections to remote hosts for the Test_user user by logging into MariaDB as root:

# mysql -u root -p

Allow the Test_User user to connect from remote hosts:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON test_db.* TO test_user@"%" IDENTIFIED BY "secret_password"; MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> quit

Configuring MariaDB to listen on all network interfaces. Open the /etc/mysql/my.cnf file:

# vim /etc/mysql/my.cnf

[…]
bind-address = 0.0.0.0
[…]

Restart the server:

# service mysql restart

Checking connection with local computer on your MariaDB server, replacing Test_User with your username, and test_domain.com with your domain or IP address:

# mysql -u test_user -h test_domain.com -p

If you logged in successfully, you should see the MariaDB greeting and shell prompt.

Tuning MariaDB

MySQL tuner is useful tool, which connects to a running MariaDB instance and provides configuration recommendations based on the workload. You must let your MariaDB instance run for at least 24 hours before running the tuner. The longer the copy has been running, the better advice the tuner will offer you.

Install the MySQL tuner by running the following command:

# apt-get install mysqltuner

Start the MySQL tuner with the following command:

# mysqltuner

After which he will give you a lot useful information. Please note the recommendations at the end. This will tell you what needs to be changed (which variables need to be configured) in the /etc/mysql/my.cnf section of your file.

How to reset MariaDB root password?

If you have forgotten your superuser password (root password), you can easily reset it by following the instructions below.

Stop the MariaDB server:

# service mysql stop

Start the server with skip-grant-tables so you can login to MariaDB without a password:

# mysqld_safe --skip-grant-tables &

You can now connect to the MariaDB server as root without a password:

# mysql -u root

In the MariaDB client, enter the following commands to reset the root password and exit:

MariaDB [(none)]> USE mysql MariaDB [(none)]> UPDATE user SET password=PASSWORD("yournewpassword") WHERE user="root"; MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> quit

Restart MariaDB server:

# service mysql restart

Connect to MariaDB server using New Password:

# mysql -u root -p

Installation of MariaDB on Debian is complete. I hope it was clear.

1: Install MariaDB

Debian 9 contains the MariaDB 10.1 package in the standard repository. This is its default MySQL option.

To install it, update the package index:

Now install the package:

sudo apt install mariadb-server

The command will install MariaDB, but will not prompt you to choose a password or change other settings. Currently, the MariaDB installation has several vulnerabilities that need to be addressed.

2: Setting up MariaDB

After installation is complete, you need to run a security script that will remove untrusted parameters and protect the database from unauthorized access.

sudo mysql_secure_installation

The script will ask a series of questions. First you need to provide your MariaDB root password. This is a MariaDB administrative account that has elevated privileges. You just installed MariaDB and haven't made any configuration changes yet, you don't have this password yet, so just press Enter.

In the next request, the script will ask you to configure the root password for the database. Type N and press Enter. On Debian, the MariaDB root account is closely associated with automated maintenance systems, so change standard methods This account cannot be authenticated. Otherwise, when updating the package, the database may be damaged, and access to the root account may be lost. Later we will look at how to configure additional account administrator if socket authentication is not suitable for you.

For other questions, you can press Y and Enter. This will remove anonymous users and test databases, disable remote root logins, and update the current MariaDB settings.

3: Configuring password authentication support

On new Debian installations, the MariaDB root user by default supports authentication using the unix_socket plugin rather than using a password. This improves security and usability in many cases, but can also make things more difficult if you need to allow access external program(for example phpMyAdmin).

Since the server uses the root user for tasks such as log rotation and starting and stopping the server, it is best not to change the root account authentication. Changing the credentials in the /etc/mysql/debian.cnf file may work initially, but further package updates will overwrite these changes. Instead, the developers recommend creating a separate administrator account with password authentication.

So, create an account called admin with the same rights as root, but with support for password authentication. To do this, open command line MariaDB in terminal:

Now create a new user with root privileges and password authentication support. Specify your username and password in the command.

GRANT ALL ON *.* TO "admin"@"localhost" IDENTIFIED BY "password" WITH GRANT OPTION;

Reset privileges:

FLUSH PRIVILEGES;

Close the MariaDB shell:

4: Testing MariaDB

When installed from the standard repository, MariaDB starts automatically. To verify this, check the service status:

sudo systemctl status mariadb
mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2018-09-04 16:22:47 UTC; 2h 35min ago
Process: 15596 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSIT
Process: 15594 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Process: 15478 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||
Process: 15474 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITI
Process: 15471 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysql
Main PID: 15567 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 27 (limit: 4915)
CGroup: /system.slice/mariadb.service
└─15567 /usr/sbin/mysqld
Sep 04 16:22:45 deb-mysql1 systemd: Starting MariaDB database server...
Sep 04 16:22:46 deb-mysql1 mysqld: 2018-09-04 16:22:46 140183374869056 /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 15567 ...
Sep 04 16:22:47 deb-mysql1 systemd: Started MariaDB database server.

If the DBMS does not start for some reason, enter:

sudo systemctl start mariadb

To further check, you can try connecting to the database using the mysqladmin tool (this is a client that allows you to run administrative commands). For example, this command will connect to MariaDB as root and output the version using a Unix socket:

sudo mysqladmin version
mysqladmin Ver 9.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Server version 10.1.26-MariaDB-0+deb9u1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 hours 44 min 46 sec
Threads: 1 Questions: 36 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables: 15 Queries per second avg: 0.003

If you have created an additional administrator, you can perform this operation using the command:

mysqladmin -u admin -p version

MariaDB is running and working properly.

What is MariaDB

MariaDB is a database management system (DBMS) that is based on MySQL and is largely compatible with it.

MariaDB and MySQL are fully compatible in syntax SQL queries. That is, if your program uses MySQL databases (for example, a website in PHP), then when switching to MariaDB you do not need to change anything in the program.

MariaDB is also binary compatible with MySQL connectors. Those. If you use MySQL connectors, you don't need to change them when moving to MariaDB.

MariaDB is compatible with MySQL database formats, but there are some caveats. If you transfer databases via export/import (for example, using a .SQL file), then the databases transferred in this way will be fully compatible between any versions and do not require any further actions. However, if you installed MariaDB on top of MySQL, that is, MariaDB uses database files from MySQL, then you need to consider compatibility:

• MariaDB 10.2 is compatible with previous versions MariaDB data files, as well as MySQL 5.6 and MySQL 5.7, but is not compatible with MySQL 8.0.
• MariaDB 10.1 is compatible with previous versions of MariaDB data files, as well as MySQL 5.6.

More information: https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/

MariaDB runs on Windows and Linux. This program is completely open source. It is distributed both in the form of source codes and compiled executable files for Windows and all popular Linux distributions.

Why MariaDB is better than MySQL

MariaDB supports more storage engines (Storage Engines).

In addition to the standard MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE storage engines, MariaDB also includes the following:

• ColumnStore, a column-oriented storage system, is optimized for data warehousing.
• MyRocks, a highly compressed storage system, added in version 10.2
• Aria, a replacement for MyISAM with improved caching.
• FederatedX (replacement for Federated)
• OQGRAPH.
• SphinxSE.
• TokuDB.
• CONNECT.
• SEQUENCE.
• Spider.
• Cassandra.

MariaDB has many different improvements and optimizations in data processing speed.

MariaDB has been updated with new extensions and features.

Download MariaDB for Windows

MariaDB is free and it is highly recommended to download it from the official website. MariaDB download page: https://downloads.mariadb.org/

You will see several episodes - several major versions of the program. If you don't need any specific MySQL compatibility, then just choose the most latest version and click the button with “Download”

Since this program works on different operating systems, then in the next window you will see a large selection of files for downloading.

Files Windows x86_64- these are 64-bit versions, and Windows x86- 32-bit.

.zip- These are portable versions that need to be installed independently, but which give complete freedom in fine tuning. A .msi is an installer for Windows.

In this instruction I will show you an example of working with the version .zip.

On next page just press the button: “ No thanks, just take me to the download»:

Installing MariaDB on Windows

For Windows, the MariaDB DBMS is distributed as an installer and ZIP archive. I prefer installing from a ZIP archive because it gives me complete control over the process.

In all examples I install in the folder C:\Server\bin\, since I have MariaDB as part of a web server installed with . If yours is different, then take this into account and make appropriate adjustments.

mariadb and move to C:\Server\bin\.

Move the folder C:\Server\bin\mariadb\data\ to a folder C:\Server\data\DB\.

In a folder C:\Server\bin\mariadb\ create a file my.cnf and copy into it:

Switching from MySQL to MariaDB on Windows

Switching from MySQL to MariaDB while maintaining databases

You can make the transition in different ways. I will show you the most universal method that guarantees full compatibility and no further problems.

You need to start by creating a backup copy of your databases. We'll do this on the command line using a utility (comes with MySQL and is located in the folder bin).

Open a command prompt Windows string. To do this, click Win+x and select Windows PowerShell(administrator). In the window that opens, do

Let's go to the folder where this utility is located (you may have a different path):

Cd C:\Server\bin\mysql-8.0\bin\

Make a dump (backup) of all databases with the following command:

Mysqldump.exe -u root -p --all-databases > all-databases.sql

Now in the folder C:\Server\bin\mysql-8.0\bin\ the file will appear all-databases.sql- be sure to copy it to a safe place!

Now stop the MySQL service and remove it from startup:

Additionally, copy the folder to a safe place C:\Server\data\DB\data\- this is an additional backup copy of the MySQL database files - in case something goes wrong with MariaDB and you want to return to MySQL.

Now delete the folders C:\Server\bin\mysql-8.0\ (binary files) And C:\Server\data\DB\data\(databases).

Unpack the downloaded MariaDB archive, rename the folder to mariadb and move to C:\Server\bin\.

Move the folder C:\Server\bin\mariadb\data\ to a folder C:\Server\data\DB\.

In a folder C:\Server\bin\mariadb\ create a file my.cnf and copy into it:

Datadir="c:/Server/data/DB/data/"

To install and start the service, run the commands:

C:\Server\bin\mariadb\bin\mysqld --install net start mysql

To deploy databases from a backup, go to the folder C:\Server\bin\mariadb\bin\:

Cmd cd C:\Server\bin\mariadb\bin\

And run a command like:

Mysql -uroot< C:\путь\до\файла\резервной_копии.sql

For example, I have a file all-databases.sql With backup copy databases are located in the folder h:\Dropbox\!Backup\, then my command is like this:

Mysql -uroot< h:\Dropbox\!Backup\all-databases.sql

Wait until the import is completed - if the file is large, the process may take longer.

Switching from MySQL to MariaDB without saving databases

Stop the MySQL service and remove it from startup:

Net stop mysql c:\Server\bin\mysql-8.0\bin\mysqld --remove

Delete folders C:\Server\bin\mysql-8.0\(binary files) and C:\Server\data\DB\data\(databases).

Unpack the downloaded MariaDB archive, rename the folder to mariadb and move to C:\Server\bin\.

Move the folder C:\Server\bin\mariadb\data\ to a folder C:\Server\data\DB\.

In a folder C:\Server\bin\mariadb\ create a file my.cnf and copy into it:

Datadir="c:/Server/data/DB/data/"

To install and start the service, run the commands:

C:\Server\bin\mariadb\bin\mysqld --install net start mysql