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 short 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 data 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

Today we are going to raise one of the most popular roles of any Linux server, which occupy a leading role in this functional segment. Setting up a CentOS 7 web server based on a bundle of a popular http server apache, interpreter php and database servers mysql, or briefly - installing lamp. This combination is the most popular configuration among web hosting today. Although lately the same company has been hot on its heels, but based on nginx, it may have already gotten ahead, I don’t have exact data on this matter.

This article is part of a single series of articles about the server.

Web server on CentOS 7

So, our centos web server will consist of three main components - http server apache, programming language interpreter php and database servers mysql. Let's get to know each of them a little:

  1. Apache- http server or simply Apache web server. It is cross-platform software that supports almost all popular OS, including Windows. It is valued primarily for its reliability and configuration flexibility, which can be significantly expanded thanks to plug-in modules, of which there are a great many. Among the disadvantages, they note that they are more demanding on resources compared to other servers. Apache will not be able to support the same load as, for example, nginx with similar hardware parameters.
  2. PHP- programming language general purpose, which is most often used in web development. Today it is the most popular language in this application area. Supported by almost all hosting providers.
  3. mysql— database management system. It has gained popularity among small and medium-sized applications, of which there are many on the web. So, like php, today it is the most popular database used on websites. Supported by most hosting providers. On CentOS it is installed instead of mysql mariadb- mysql fork. They are fully compatible; you can switch from one database to another and back at any time. Lately I have come across information that mariadb works faster than mysql and people are slowly moving to it. In practice, I did not have the opportunity to observe this, since I have never worked with loaded databases. But under normal conditions the difference is not noticeable.

The experimental server will be , the characteristics are as follows:

CPU2 cores
Memory8 Gb
Disk150 Gb SSD

This is a custom settings setting. They are not optimal in price, but these are exactly what I needed.

I would like to clarify right away that I am analyzing the basic default setting. To improve performance, increase reliability and ease of use, you need to install several more tools, which I will discuss separately. In general, what is in this article will be sufficient to organize a web server.

If you don't have a server yet, then you need to run . And if the server is already installed, then don’t forget it. I recommend paying attention to the settings, since there are a lot of useful information, which I do not give in the framework of this article - updating the system, setting up a firewall, installing an editor and much more.

Setting up apache on CentOS 7

On CentOS the apache service is called httpd. When I first became acquainted with this distribution, it was unusual for me. In Freebsd and Debian, with which I had previously worked, the web server service was called apache, although I noticed somewhere, it seems in the software, that the configuration file is called httpd.conf. To this day I don’t know why both of these names have spread. I would be glad if someone shared information about this with me in the comments.

Now let's get started installing apache. In CentOS 7 this is done very simply:

# yum install -y httpd

Add apache to startup:

# systemctl enable httpd

Launch apache on CentOS 7:

# systemctl start httpd

Check if the server has started:

# netstat -tulnp | grep httpd tcp6 0 0:::80:::* LISTEN 21586/httpd

Everything is fine, it hung on port 80, as expected. Now you can go to http://ip-address and see the picture:

Now let's set up apache. I prefer the following web hosting structure:

Let's create a structure like this:

# mkdir /web && mkdir /web/site1.ru && mkdir /web/site1.ru/www && mkdir /web/site1.ru/logs # chown -R apache. /web

IncludeOptionalconf.d/*.conf

If not, uncomment it and go to the /etc/httpd/conf.d directory. Let's create a file site1.ru.conf there:

ServerName site1.ru ServerAlias ​​www.site1.ru DocumentRoot /web/site1.ru/www Options FollowSymLinks AllowOverride All Require all granted ErrorLog /web/site1.ru/logs/error.log CustomLog /web/site1.ru/logs/access.log common

Restarting apache on centos

Now we restart apache:

# systemctl restart httpd

If any errors occur, look at the apache log /var/log/httpd/error_log. If everything is in order, then we will check whether our virtual host is configured normally. To do this, create in the folder /web/site1.ru/www file index.html the following content:

# mcedit /web/site1.ru/www/index.html

Apache is set!

# chown apache. /web/site1.ru/www/index.html

192.168.1.25 site1.ru

where 192.168.1.25 is the IP address of our web server.

Now in the browser we type the address http://site1.ru. If we see the picture:

it means everything is configured correctly. If there are any errors, then go look at the logs. Moreover, in this case it’s not the general httpd log, but the specific error log virtual host at /web/site1.ru/logs/error.log.

I’ll immediately draw your attention to setting up the rotation of virtual host logs. It often happens that if you don’t set it up right away, then you forget. But if the site has good traffic, then the logs will grow rapidly and can take up a lot of space. It is better to set up rotation of web server logs immediately after creation. It's not difficult to do this.

To configure virtual host log rotation, you need to edit the /etc/logrotate.d/httpd file. It is created during the installation of apache and includes setting the rotation of the default log location. And since we transferred the logs of each virtual host to an individual folder, we need to 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 > /dev/null 2>/dev/null || true endscript )

In principle, the simplest web server is already ready and can be used. But it is unlikely that now there will be sites with static content for which only html support is sufficient. So let's continue with our setup.

If you need to organize the operation of the site according to the protocol https, then use the manual for .

Installing php on CentOS 7

To support dynamic website content, let's take the next step. Let's install php on CentOS 7:

# yum install -y php

And then a few more useful components. Let's 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

Let's create a file in the virtual host directory and check php job:

# mcedit /web/site1.ru/www/index.php# chown apache. /web/site1.ru/www/index.php

Go to http://site1.ru/index.php

You should see php information output. If something is wrong, some errors have arisen, look at the virtual host error log, php errors will also be there.

Where is php.ini?

After installation, the question often arises: where are they stored? php settings? Traditionally, they are located in a single settings file. On CentOS php.ini is in /etc, right at the root. There you can edit global settings for all virtual hosts. Personal settings for each site can be made separately in the virtual host configuration file that we made earlier. Let's add a few there useful settings:

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

Add at the very end, before

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

To apply the settings you need to restart Apache. You can now see the settings change in the phpinfo output.

Upgrading to php 5.6 on CentOS 7

In our example we installed on CentOS 7 php 5.4 from the standard repository. 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 update php 5.4 to php 5.6:

# yum --enablerepo=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 let's go look at the output of phpinfo - http://site1.ru/index.php

Great, we've updated php to version 5.6.

Installing MySQL on CentOS 7

As I wrote earlier, the mysql fork is now becoming increasingly widespread - mariadb. It is fully compatible with mysql, so you can use it with confidence. I prefer to use it.

Installing mariadb on CentOS 7:

# yum install -y mariadb mariadb-server

Add mariadb to autostart:

# systemctl enable mariadb.service

Launch mariadb:

# systemctl start mariadb

We check whether it has 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 a mysqld service. Now we run the standard security configuration script:

# /usr/bin/mysql_secure_installation

I will not give the entire output of this script, everything is quite simple and clear. First, we set a password for root (the current password after installation is empty), then we delete anonymous users, disable the ability to connect root remotely, and delete the test user and database.

File settings mysql/mariadb is in /etc/my.cnf. For normal work, the default settings are sufficient. But if you decide to change them, don't forget to restart the database service.

Restart mariadb/mysql on CentOS 7:

# systemctl restart mariadb

That's all. The basic functionality of the web server on CentOS 7 is configured.

I will be glad to receive comments on the topic of the article. Let me remind you that this article is part of a single series of articles about the server.

Kali Linux Workshop

The course is for those who are interested in conducting penetration tests and want to practically try themselves in situations close to real ones. The course is designed for those who do not yet have experience in information security. The training lasts 3 months, 4 hours per week. What this course will give you:
  • Search for and exploit vulnerabilities or configuration flaws in corporate networks, web sites, servers. Emphasis on pentesting of Windows OS and security of the corporate segment.
  • Learning tools such as metasploit, sqlmap, wireshark, burp suite and many others.
  • Mastering the tools Kali Linux in practice, any information security specialist should be familiar with it.
Test yourself on the entrance test and see the program for more details.

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 and other developers 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

Figure.01: Secure your MariaDB 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:


Figure.02: 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 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:


Figure 04: Creating a server key for the MariaDB server

Then process RSA key server, to do this enter:

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


Figure.05: Creating a client key for the MariaDB server

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 both 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 in the following way:

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

Add like this:

### 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=/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:

Figure 06: Establishing a secure connection to the console and testing it

Check SSL and TLS connections. 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, are not exchanged between 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")

Since Debian 9, the popular database management system MySQL data replaced MariaDB. This DBMS is a fork of MySQL created by its original developers who were dissatisfied licensing policy Oracle had concerns that MySQL might become a more proprietary product. MariaDB is fully compatible with MySQL, which means that the replacement will happen as transparently as possible and all applications that worked with MySQL will also work with MariaDB. And we will look at some of the features of this transition.

First of all, let's say that all commands, instructions, scripts, etc. and so on. who previously worked with MySQL will also work with MariaDB; no changes need to be made, and many users simply may not notice that they are working with a different DBMS.

But there are also differences, primarily related to security and were introduced by the Debian team. One of the main differences is that MariaDB is included in Debian 9 does not ask for root password during installation. After which the user is left somewhat confused, what to do next? Adding fuel to the fire is the fact that most instructions on the Internet consider managing MySQL purely through the phpMyAdmin control panel, and those who do not have the skills to work in command line users find themselves in a particularly helpless state.

"How to set root password in MariaDB"in different variations is one of the popular search queries associated with this DBMS. But let’s not be too harsh, but first let’s figure out what the Debian developers did and why.

The biggest security problem with MySQL is that database access credentials are stored in clear text in web application configuration files. Considering that many users do not bother and make the root superuser the owner of all databases, the problem becomes quite serious. And if you consider that a fairly wide range of people can have access to web application files, including not only employees, but also freelancers, things get really bad.

Therefore, in Debian, authentication via a UNIX socket is provided for the root superuser in MariaDB and is implemented in such a way that only the system superuser can get unrestricted access to MariaDB and only in command line mode. From a security point of view, this is very correct, since now third-party users and web applications will not be able to gain access from root rights, even if they somehow found out the password.

All this is good, but what should an ordinary user do who has installed MariaDB on his server and wants to upload a site database dump to it? First of all, create a user, preferably more than one. To do this, raise your rights in the system to root via su or sudo and run the command:

Mysql -u root

This will take you to the MariaDB command line. To create a new user, run the command:

create user "andrey" @ "localhost" identified by "password" ;

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

Now let's assign him rights. First of all, let’s explicitly take away the rights to other people’s databases:

grant usage on *.* to "andrey"@"localhost";

And we will issue full rights to databases with the name template andrey_basename, this approach will allow us to automatically grant rights to all new databases that the user will create.

grant all privileges on `andrey\_%`.* to "andrey"@"localhost";

Note that the pattern is wrapped in characters grave (`), which is located on the key with the Russian letter E.

All that remains is to reload the privileges and exit the MariaDB console

flush privileges;
quit;

After which you can return to the usual tools for working with MySQL/MariaDB, for example, phpMyAdmin:


note that this method, unlike the common recommendations “enable root in MariaDB,” allows you to maintain increased system security, which is important if third parties have access to it. We also do not recommend keeping all databases under one user, ideally one site (or other application) - one user, this will allow you to change the password without wasting time and effort in case of compromise or its potential (for example, you were hired to work with the site freelancer).

Installing MariaDB from the developer repositories

The Debian operating system has many advantages, one of them for which it is most loved is its stability. Set it and forget it - that’s exactly what it’s about, but the downside of this approach is conservatism; many packages do not have as recent versions as we would sometimes like. Currently, MariaDB 10.1 ships with Debian 9, while the current versions are 10.2 and 10.3.

Therefore, if you need some new MariaDB features or just want to use the latest stable versions of the software, you can install MariaDB directly from the developer's repositories. This is not difficult to do, but before any potentially dangerous action you should do a thorough backup copy your server.

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

Mysqldump -u root --all-databases > ~/my_backup.sql

This command will save all MariaDB databases, including service ones, to a file my_backup.sql in the directory /root and, if something goes wrong, you can always restore the state of your server at the time the copy was created.

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

It is made very conveniently: you select your distribution, its release, DBMS version and mirror - after which you receive ready-made instructions for installation. To save you time, we will present it here (we will be installing 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 what you are doing, let’s briefly comment on these commands. The first of them adds the necessary dependencies to the system, the second installs the key with which the packages are signed, and the third, finally, adds the repository itself to the system.

Now let's update the list of packages:

Apt-get update

and install the new version of MariaDB:

Apt-get install mariadb-server

Please note that the package manager will correctly remove the previous version and install the new one, all databases will be saved and will continue to work with new version MariaDB.

Secondly, when installing MariaDB from the developers, the installer will ask you to set the root password, since authentication via a UNIX socket is not used in this version.

This may please some, but if you have managed to understand and appreciate all the advantages of the method from the Debain developers, then a reasonable question will arise: is it possible to return it to the way 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 = auth_socket.so

Let's restart the DBMS:

Service mysqld restart

Let's try to log in from account regular user:

Even though the system will ask us for a password and we will enter it correctly, we will still be denied access. Let's try again via phpMyAdmin:

And here we will not have success. Great! Now only the system superuser has access with DBMS superuser rights and we do not need to worry that the MariaDB root password will become known to anyone.

January 2, 2017 12:16 pm 13,224 views | no comments

Requirements

  • CentOS 7 server.
  • User with sudo access.

All necessary instructions can be found in .

1: Install MariaDB

To install MariaDB, use the Yum repository. Run the following command and press y to continue.

sudo yum install mariadb-server

After installation is complete, start the daemon:

sudo systemctl start mariadb

The systemctl command does not show the output of some commands. To verify that the daemon is running, enter:

sudo systemctl status mariadb

If the MariaDB daemon was running, the command output will be:

Active: active (running)
[…]
Dec 01 19:06:20 centos-512mb-sfo2-01 systemd: Started MariaDB database server.

Now you need to configure MariaDB autostart. To do this, enter:

sudo systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

After this, you need to ensure data security.

2: MariaDB Security

Once the installation is complete, you need to run MariaDB's built-in security script, which changes some default options and blocks remote root logins. To run the script, enter:

sudo mysql_secure_installation

The script provides detailed description every step. It will first ask for the root password. Since in new installation There is no such password yet, just press Enter. After this, the script will prompt you to create such a password. Enter complex password and confirm it.

The script will then ask you a series of questions. To accept the default data, you can simply press Y and Enter. The script will block anonymous users and remote root logins, delete test tables and reset privileges.

3: Testing MariaDB

Now you need to make sure that the MariaDB installation was successful.

Try connecting to the database using the mysqladmin tool (this is a client for running administration commands). To connect to MariaDB as root (-u root), enter the password (-p), and prompt for the program version, issue the command:

mysqladmin -u root -p version

The command will output:

mysqladmin Ver 9.0 Distrib 5.5.50-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Server version 5.5.50-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 min 4 sec
Threads: 1 Questions: 42 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.172

This means that the installation of the MariaDB DBMS was successful.

Conclusion

You now know how to perform basic installation and configuration of MariaDB.


Close