I decided to abandon the use of MySQL, and more precisely to completely translate all my servers on its Fork - MariaDB. Taking this opportunity, I want to tell about the installation process of MariaDB 10.1 in Debian 8. It should be noted that a brief description of the MARIADB installation is on the official project page. I decided to allocate this issue a separate post in which I want to describe the necessary actions after installing the MariaDB on the server.

Before starting installing MariaDB, add its repository. The MariaDB website recommends to install the Software-Properties-Common package. I do not see any point in this and prefer to do everything manually.

We register a GPG key 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 at the end:

DEB http://lon1.mirrors.digitalcean.com/mariadB/repo/10.1/Debian Jessie Main DEB-SRC http://lon1.mirrors.digitalcean.com/mariadB/repo/10.1/debian Jessie Main

We update the list of available packages:

APT-GET UPDATE

Running 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. On this process of installing the MariaDB in Debian 8 is complete. Now go to the configuration of the server.

To increase the reliability of our server, you need to fulfill the minimum security requirements. Prohibit authorization under the ROOT user from remote hosts. If there is a TEST base and Anonymous user, you need to remove them from the server. To facilitate the task, use the script:

Mysql_secure_installation

Default storage type

If you need to change the default storage type, add the following lines to the My.CNF file:

Default-Storage-Engine \u003d InnoDB

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

Show engines;

Create a user and a MARIADB 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;

Let us fulfill the user user_name to the databasename database:

Grant all privileges on database_name. * To "user_name" @ "localhost";

Now you need to update all the privileges:

Flush Privileges.

To view the privileges, run the command:

Show grants for "user_name" @ "LocalHost";

Binary logs

MariaDB records all the changes in the BD in the binary magazine, it is necessary for the operation of the replication mechanism. If you did not make backups or they are outdated, binary logs can be used to recover data. However, there is no guarantee that the data will be fully or partially restored. Success will depend on the size, storage time of binary logs, frequencies of the bag.

To disable binary logs, comment on the strings in the file My.cnf:

#LOG_BIN \u003d / var / log / mysql / mariadb-bin #log_bin_index \u003d /var/log/mysql/mariadb-bin.Index

I am going to install MariaDB SSL (Secure Sockets Layer) as well as secure connections from the MysQL client and PHP applications. How to enable SSL for server and MariaDB client working in a Linux or Unix-like system?

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

MariaDB was created by some of the original authors of MySQL, using the wider state of the Free FREE developers and other open source software. In addition to the basic functions of MySQL, MariaDB offers a rich set of functions improvements, including alternative storage mechanisms, servers optimization, and other corrections. In this manual i'm going to talk about how to configure the mariadb server using SSL and how to install secure connections Using console and PHP scripts.

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

Step 1 - Installation MariaDB

Enter the command in accordance with your Linux or Unix option.

Installing the 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 must 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 install 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 - Secure MARIADB Secure Installation

Enter the following command:

$ mysql_secure_installation

Figure .01: Confine MARIADB Installation

Step 3 - Create CA Certificate

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

$ CD / ETC / MySQL $ sudo mkdir ssl $ cd ssl

Value: The standard name value (Common Name) used for server and client certificates must differ from the standard name (Common Name) used for CA certificate. To avoid any problems, I install them as follows:

Standard name CA: MariaDB Admin.
Standard server name: MariaDB Server
Standard customer name: MariaDB Client.

Enter the following command to create a new CA key:

$ sudo OpenSSL Genrsa 2048\u003e CA-KEY.PEM

Examples of possible data conclusions:

Figure 10: Creating a CA key

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 the CA key, generate CA certificate for MariaDB

Examples of possible data conclusions:

Now you have to have two following files ::

1. /etc/mysql/ssl/ca-cert.pem - certificate file for certification authority (CERTIFICATE AUTHORITY (CA)).
2. /etc/mysql/ssl/ca-key.pem - key file for the certification center (CERTIFICATE AUTHORITY (CA)).

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

Step 4 - Creating 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 conclusions:

Figure 04: Creating a server key for MARIADB server

Then handle the RSA server key, enter:

$ sudo OpenSSL RSA -IN Server-Key.pem -out Server-key.pem

Examples of possible data conclusions:

Writing RSA Key

Finally, sign the server certificate, for this, run:

$ 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 conclusions:

Signature OK SUBJECT \u003d / C \u003d AU / ST \u003d Some-State / O \u003d Internet Widgits Pty Ltd / CN \u003d MariaDB Server Getting CA Private Key

Now you need to appear 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 that you are going to use for cluster / replication traffic. These two files will protect the connection on the server side.

Step 5 - Creating a Customer Certificate

MYSQL client, and app PHP / Python / Perl / Ruby will use the Customer Certificate to protect the client connectivity. You must set the following files on all your clients, including a 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 conclusions:

Figure 05: Creating a client key for the MariaDB server

Then handle the RSA client key, for this enter

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

Finally, sign the client certificate, for this, start:

$ 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 conclusions:

Signature OK SUBJECT \u003d / C \u003d AU / ST \u003d Some Pty Ltd / CN \u003d MariaDB Client Getting Ca Private Key

Step 6 - How to Check Certificates?

Enter the following command to verify certificates to make sure that everything was created correctly:

$ OpenSSL Verify -cafile CA-CERT.PEM Server-Cert.Pem Client-Cert.pem

Examples of possible data conclusions:

Server-CERT.PEM: OK Client-Cert.pem: OK

There should be no errors, and you must get an OK answer for server and client certificates.

Step 7 - Configure the MARIADB server to use SSL

Edit file. vi /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/mariadb.cnf in the following way:

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

Add as follows:

### MySQL 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 \u003d / ETC / MYSQL / SSL / CA-CERT.PEM SSL-CERT \u003d / ETC / MYSQL / SSL / SERVER-CERT.PEM SSL-KEY \u003d / ETC / MYSQL / SSL / SERVER-KEY. pem.

Save and close the file. You can restart MariaDB as follows:

$ sudo /etc/init.d/mysql Restart

$ Sudo SystemCTL Restart MySQL

Step 8 - Configure the MariaDB client to use SSL

Configure the 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 \u003d / etc / mysql / ssl / cart.pem ssl-cert \u003d / etc / mysql / ssl / client-cert.pem ssl-key \u003d / etc / mysql / ssl / Client-Key.Pem ### this option is disabled by default ### ### SSL-Verify-Server-Cert ###

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

{[Email Protected]): rsync /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/client-cert.pem /etc/mysql/ssl/client-key.pem \\ [Email Protected]: / 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)]\u003e Row:

MariaDB [(None)]\u003e Show Variables Like "% SSL%";

Or execute the Status command:

MariaDB [(None)]\u003e Status;

Examples of possible data conclusions:

Figure 06: Establishing a secure connection with the console and checking it

Check SSL and TLS connections. The following command must complete failure, since SSL 3 is not supported and, accordingly, it is not configured to 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 conclusions:

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 package capture file to check a secure connection

Finally, you can use TCPDUMP command package analyzer, which runs under the command line to view 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, does not exchange between the server and the client. This is done as follows:

$ TCPDUMP -R /Tmp/MYSQL.PCAP | less

Step 10 - add user to MariaDB server

Enter the following command:

$ mysql -u root -p

Create a database called Foo:

CREATE DATABASE FOO;

Create a user with the BAR FOR estring for a database called Foo:

GRANT ALL ON FOO. * TO [Email Protected] IDENTFIED by "MyPassword" Require SSL;

Providing access from a web server posted at 192.168.1.200:

GRANT ALL ON FOO. * TO [Email Protected] IDENTFIED by "MyPassword" Require SSL;

Create a safe bash shell connection

You can log in with the console as follows:

$ 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 is an example of a Python code for a secure connection using:

#! / usr / bin / python import mysqldb ssl \u003d ("CERT": "/etc/mysql/ssl/client-cert.pem", "key": "/etc/mysql/ssl/client-key.pem" ) Conn \u003d mysqldb.connect (host \u003d "192.168.1.100", user \u003d "bar", passwd \u003d "mypassword", ssl \u003d ssl) cursor \u003d 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.constracts import clientflag config \u003d ("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 \u003d mysql.connector.connect (** config) cur \u003d cnx.cursor (buffered \u003d True) CUR.EXECUTE ("SHOW STATUS LIKE" SSL_CIPHER "") PRINT (CUR.FETCHONE ()) CUR.CLOSE () CNX.CLOSE ()

Examples of possible data conclusions:

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

Today we will take on a raising one of the most sought-after roles of any Linux servers, which occupy a leading role in this functional segment. Configuring CentOS 7 Server Web on the Base Base Popular HTTP Server apache.Interpreter php. and database server mysql, or briefly - install LAMP. This bundle is the most popular configuration today among Web Hosting. Although the same company, she comes to the heel recently, but on the basis of NGINX, it may already have ahead, I don't have accurate data on this.

This article is part of the unified cycle of the article about the server.

WEB server on CentOS 7

So, our web server CENTOS will consist of three main components - HTTP server apache., programming language interpreter php.and database server mysql. We will get acquainted a little with each of them:

1. Apache. - HTTP server or simply web server Apache. It is a cross-platform software that supports almost all popular operating systems, including Windows. It is appreciated primarily for its reliability and flexibility of the configuration, which can be significantly expanded by connecting modules, which there are a great set. Of the disadvantages, there are greater demands for resources, compared with other servers. To keep the same load as, for example, Nginx, Apache will not be able to have similar iron parameters.
2. Php. - General purpose programming language, which is most often applied in web development. To date, this is the most popular language in this area of \u200b\u200bapplication. Supported by almost all hosting providers.
3. Mysql - Database management system. Won its popularity in the environment of small and medium-sized applications, which are very many on the web. So, like PHP, today is the most popular database used on web sites. Supported by most hosting. In the centos instead of MySQL sets mariaDB. - Branch MySQL. They are fully compatible, it is possible at any time the transition from one DBMS to another and back. Recently, I met the information that MariaDB is laughing MySQL and people slowly move onto it. In practice, I did not happen to observe this, since I never worked with loaded databases. And under normal conditions, the difference is not noticeable.

The experimental server will act, the following characteristics:

CPU	2 nuclei
Memory	8 GB.
Disk	150 GB SSD.

This is custom setting parameters. They are not optimal for the price, but I needed exactly that.

I want to immediately clarify that I deal with the basic default setting. To improve speed, enhance reliability and ease of use, you need to install several more tools, which I will tell separately. In general, it will be enough for organizing a web server what is in this article.

If you still do not have a server, then you need to execute. And if the server is already installed, do not forget it. I recommend paying attention to the setting, since there are a lot of useful information that, within this article, I do not give - a system update, a firewall setting up, installing the editor and much more.

Configure Apache in CentOS 7

In Centos, the Apache service is called httpd.. When I just got acquainted with this distribution, I was unusual. In FreeBSD and Debian, with whom I have worked, the web server service was called Apache, although somewhere I noticed, it seems in Fruch that the configuration file has the name httpd.conf. Until now, I do not know why both these names spread. I would be glad if someone shared with me information on this bill in the comments.

Now proceed to installation Apache. In CentOS 7 this is done very simple:

# yum install -y httpd

Add Apache to autoload:

# SystemCTL Enable Httpd

We launch Apache in CentOS 7:

# SystemCTL start httpd

Check if the server started:

# Netstat -Tulnp | Grep httpd tcp6 0 0 :::: 80 ::: * listen 21586 / httpd

Everything is in order, hoving in the 80th port, as it should be. Already, you can go to http: // IP-Address and see the image:

Now we will configure Apache. I prefer the following web hosting structure:

Create a similar structure:

# MKDir / Web && mkdir /web/site1.ru && mkdir /web/site1.ru/www && mkdir /web/site1.ru/logs # chown -r Apache. / Web.

IncludeOptional conf.d / *. Conf

If not, we unconsume and go to the /etc/httpd/conf.d directory. With I pull there a file Site1.ru.conf:

ServerName Site1.ru Serveralias www.site1.ru Documentroot /Web/site1.ru/www Options FallowSymlinks AllowOverride All Require All Granted ErrorLog /Web/site1.ru/logs/error.log CustomLog /Web/Site1.ru/logs/Access.log Common

Restart Apache in Centos

Now we make Restart Apache:

# SystemCTL Restart httpd

If there are any errors - see Apache / Var / log / httpd / error_log log. If everything is in order, then check whether our virtual host is normal. To do this, create in the folder /Web/site1.ru/www file index.html Next content:

# MCedit /Web/Site1.ru/www/index.html

Apache is configured!

# Chown Apache. /Web/Site1.ru/www/index.html

192.168.1.25 Site1.ru.

where 192.168.1.25 IP address of our web server.

Now in the browser, we recruit the address http://site1.ru. If you see a picture:

so everyone was configured correctly. If any errors, then go to watch logs. Moreover, in this case, there is not a common httpd log, and the log error log of a particular virtual host to the address /Web/site1.ru/logs/error.log.

Immediately I will pay your attention to the rotation of the logs of virtual hosts. It often happens that if you do not confuse immediately, then you forget. But if the site is with good attendance, the logs will grow rapidly and can take a lot of space. It is better to configure the rotation of the web server logs immediately after creation. Make it is not difficult.

To configure the rotation of virtual host logs, you must edit the /etc/logrotate.d/httpd file. It is created during the Apache installation and includes the rotation setting of the standard location of the logs. And since we have transferred the logs of each virtual host to an individual folder, you must add these folders to this file:

# mcedit /etc/logrotate.d/httpd. /Web/*/logs/*.log. / var / log / httpd / * log (Missingok Notifempty SharedScripts DelayCompress Postrotate / Bin / SystemCTL RELOAD HTTPD.Service\u003e / dev / null 2\u003e / dev / null || True EndScript)

In principle, the simplest web server is ready and they can be used. But it is unlikely that there are sites with static content, which is enough to support only HTML. So we will continue our setting.

If you need to organize the work of the site by the protocol https, Take advantage of the software guide.

Install PHP in CentOS 7

To support dynamic content content, perform the next step. Installation php. In Centos 7:

# YUM Install -Y PHP

And next few more useful components. We install popular modules for PHP.:

# YUM Install -Y PHP-MySQL PHP-MBSTRING PHP-MCRYPT PHP-DEVEL PHP-XML PHP-GD

Let's restart Apache:

# SystemCTL Restart httpd

Create a file in the directory of the virtual host and check the PHP work:

# MCedit /Web/Site1.ru/www/index.php. # Chown Apache. /Web/Site1.ru/www/index.php.

We go to the address http://site1.ru/index.php

You must see the output of information about PHP. If something is wrong, there are some errors, see the virtual host error log, PHP errors will also be there.

Where lies php.ini

After installation, the question often arises, and where is the PHP settings stored? Traditionally, they are in a single settings file. In Centos. php.ini lies in / etc, right in the root. There you can edit global settings for all virtual hosts. Personal settings of each site can be made separately in the virtual host configuration file that we have done before. Let's add several useful settings there:

# mcedit /etc/httpd/conf.d/site1.ru.conf.

Add to the easiest, before

Php_admin_value date.timezone "Europe / Moscow" php_admin_value max_execution_time 60 php_admin_value upload_max_filesize 30m

To apply settings you need to make Restart Apache. Now in the PHPINFO output you can see the change in settings.

Update to PHP 5.6 in CentOS 7

In our example, we installed on CentOS 7 pHP 5.4. From the standard repository. And what if we need a newer version, for example pHP 5.6.? In this case, you need to update PHP.

# wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm # rpm -uvh Remi-Release-7 * .rpm

Now refresh PHP 5.4 to PHP 5.6:

# YUM --EnableRepo \u003d Remi, Remi-PHP56 Install PHP PHP-Common PHP-Mysql PHP-MBSTRING PHP-MCRYPT PHP-DEVEL PHP-XML PHP-GD

Restart Apache:

# SystemCTL Restart httpd

And we go look at the output phpinfo - http://site1.ru/index.php

Excellent, we updated PHP to version 5.6.

Installing MySQL in CentOS 7

As I have already written earlier, the MYSQL fork is becoming increasingly distribution - mariaDB.. It has full compatibility with MySQL, so you can safely use. I prefer to use it.

Install MariaDB. on CentOS 7:

# YUM Install -Y MariaDB MariaDB-Server

We add MARIADB to autorun:

# SystemCTL Enable MariaDB.Service

Run MariaDB:

# SystemCTL start mariadb

Checking, started or not:

# Netstat -Tulnp | GREP MYSQLD TCP 0 0 0.0.0.0:3306 0.0.0.0:* Listen 22276 / MYSQLD

Please note that it is even displayed in the system as MYSQLD service. Now launch the standard security setup script:

# / usr / bin / mysql_secure_installation

I will not give the whole output of this script, everything is quite simple and understandable. First, you specify a password for root (current password after installing empty), then delete anonymous users, turn off the ability to connect root remotely, delete the test user and the database.

File settings MySQL / MARIADB lies in /etc/my.cnf.. For normal operation, there is enough default settings. But if you decide to change them, do not forget to restart the database service.

Restarting MariaDB./ MySQL in CentOS 7:

# SystemCTL RESTART MARIADB

That's all. The basic functional of the Web server on CentOS 7 is configured.

I will be glad to comments and comments on the topic of the article. I remind that this article is part of a single cycle of an article about the server.

Workshop on Kali Linux

The course for those who are interested in conducting tests to penetrate and want to practically try themselves in situations close to real. The course is designed for those who have no experience in information security. Training lasts 3 months to 4 hours a week. What will give you this course:

• Search and operate vulnerabilities or flaw configurations in corporate networks, Web sites, servers. Focus on the Windows Penstress and the security of the corporate segment.
• Study of tools such as Metasploit, SQLMAP, Wireshark, Burp Suite and many others.
• The development of the Kali Linux toolkit in practice - with it there should be any specialist in IB.

Check yourself on the introductory test and see more of the software program.

Starting from the Debian version of the 9th, the popular MYSQL database management system was replaced by MariaDB. This DBMS is a branch from MySQL created by its initial developers, which were unhappy with the Oracle licensed policy and experienced that MySQL could become a more closed product. MariaDB is fully compatible with MySQL, which means that the replacement will be as transparent as possible and all applications that worked with MySQL will also work with MariaDB. And we will consider some features of this transition.

First of all, let's say that all teams, instructions, scripts, etc. etc. Which previously worked with MySQL will also work with MariaDB, no changes need to be made and many users simply may not notice that they work with another DBMS.

But there are also differences, first of all, they relate to security and were made by the Debian team. One of the main differences is that MARIADB is part of Debian 9 does not request a root password when installing. After that, the user remains in some confusion, and what to do next? Oils in the fire poured the fact that most instructions on the Internet are considering the MySQL management purely via the PHPMYAdmin control panel, and not having to work skills in the command prompt users are in particularly helpless state.

"How to install root password in mariadb"In different variations, it is one of the popular search queries related to this DBMS. But we will not chop off the shoulder, but first we will deal with Debian developers and for what.

MySQL's biggest security problem is that the database access credentials are stored in the configuration files of web applications in the open text. Taking into account the fact that many users do not bother and make the owner of all databases of the root superuser - the problem becomes quite serious. And if you consider that access to web application files can have a fairly wide range of persons, including not only employees, but also freelancers - becomes very bad.

Therefore, in Debian for the root superuser, MARIADB provides authentication via UNIX socket and it is implemented in such a way that unlimited access to MariaDB can only receive a superfolder system and only in command line mode. From the point of view of security, it is very correct, since now third-party users and web applications will not be able to access ROOT rights, even if they somehow learned the password.

All this is good, but what to do the usual user who put MariaDB to his server and wants to pour the site base dump on it? First of all, you have a user, even not one. To do this, raise your rights in the system to root via SU or Sudo and execute the command:

Mysql -u root.

After that you will find yourself on the MARIADB command line. In order to create a new user to execute the command:

Create user "Andrey" @ "Localhost" identified by "Password";

In our example, we created a user andrey. with password password..

Now we will appoint him right. First of all, we clearly take the rights to other people's bases:

gRANT USAGE ON *. * TO "Andrey" @ "LocalHost";

And I will issue full rights to the database with the Andrey_Basename template, this approach will automatically give the rights to all new bases that the user will create.

gRANT ALL PRIVILEGES ON `Andrey \\ _%`. * To "Andrey" @ "LocalHost";

Note that the template is wrapped with symbols gravis (`)which is on the key with the Russian letter E.

It remains only to restart the privileges and exit the MariaDB console.

Flush Privileges;
quit;

After that, you can return to the familiar tools to work with MySQL / MariaDB, for example, phpmyAdmin:

Please note that this method, in contrast to the common recommendations to include Root in MariaDB, allows you to maintain the increased security of the system, which is important if access to it will have third parties. We also do not advise to keep all the databases under one user, ideally one site (or other application) - one user, this will allow you to change the password without unnecessary time and effort to change the password in case of its compromise or its potential opportunity (let's say you attracted to work with the site Freelancer).

MARIADB installation from developer repository

The Debian operating system has many advantages, one of them, for which it is most likely like is stability. I set up and forgot - this is just about it, but the revolving side of this approach is conservatism, many packages have not so fresh versions as sometimes I would like. At the moment, MARIADB 10.1 comes with Debian 9, while current versions are 10.2 and 10.3.

Therefore, if you need some new MARIADB functions or you just want to use the latest stable software versions, you can install MariaDB directly from the developer's repository. This is easy, but before any potentially dangerous action, you should make a full backup of your server.

To do this, run the following command in the server console with superuser:

Mysqldump -u root --all-databases\u003e ~ / My_backup.sql

This command will save all MARIADB databases, including service, file my_backup.sql in directories / root. And if something goes wrong, you can always restore the status of your server at the time of creating a copy.

You can get installation instructions on the special page of the official website.

It is made very convenient: you choose your distribution, its release, version of the DBMS and the Mirror - after which you get the finished instructions for installation. To save you time we give it here (we will install MariaDB 10.3 on Debian 9):

APT-Get Install Software-Properties-Common DirmnGr
aPT-KEY ADV --RECV-KEYS --KEYSERVER KEYSERVER.UBUNTU.com 0xF1656F24C74CD1D8
add-apt-repository "DEB http://mirror.mephi.ru/mariadb/repo/10.3/debian Stretch Main"

So that you understand that you make briefly comment on these commands. The first of them adds the necessary dependencies to the system, the second sets the key to which the packages and the third are signed, finally adds to the repository itself.

Now update the list of packages:

APT-GET UPDATE

and install a new version of MARIADB:

APT-Get Install Mariadb-Server

Please note that a batch manager will correctly delete the previous version and establishes a new one, all databases will be saved and continue to work with the new version of MariaDB.

The second point, when installing MariaDB from developers, the installer will ask you to set the root password, as in this version, the authentication via UNIX socket is not used.

Someone may delight this, but if you managed to understand and evaluate all the advantages of the method of Debain developers, there will be a reasonable question: Is it possible to return as it was? Can. To do this, open the configuration file. /etc/mysql/mariadb.conf.d/50-server.cnf. and add to section Line:

Plugin-load-add \u003d auth_socket.so

Restart the DBMS:

Service MySqld Restart.

Let's try to enter from the customary user account:

Despite the fact that the system will ask us the password and we will correct it correctly - we will still be denied access to us. We will repeat the attempt through phpMyAdmin:

And here we will not have success. Excellent! Now access with the superuser rights of the DBMS has only a superuser system and we do not need to worry that the Root MariadB password will be known for anyone.