How to configure MariaDB SSL and establish secure connections from various clients. Installing MariaDB on Debian Installing mariadb

Hello!

I fully support your decision! I migrated my servers to MariaDB from MySQL 3 years ago and not in a good way. MySQL versions became worse and worse after Oracle bought it. Constant crashes and errors in tables had a negative impact on the availability of sites, so the decision was made to switch to MariaDB.

The result is completely justified; MariaDB is very stable and well optimized. A three-year uptime without interruptions on several servers perfectly proves this.

Selecting MariaDB version

Should /var/run/php5-fpm.sock already be present on the server? in ubuntu 16.04 I did not find such a file in /var/run

There are 2 branches of MariaDB - 5.x and 10.x.

5.x are versions that are as close as possible to the latest MySQL versions; they are fully compatible with MySQL. If you are just moving to MariaDB from MySQL, this line of versions is for you.

10.x - new line versions that started after version 5.5. These versions do not completely copy all the new features of the latest versions of MySQL, but contain their own unique features. They are still compatible with MySQL, but if you are just starting to use MariaDB, it is better to start with version 5.5.

Installing MariaDB on a Linux server

MariaDB distributions are usually not included in the repositories of major Linux distributions (for example, CentOS 6 and CentOS 7). But, even if there were, it is better to install the native MariaDB repository on the system in order to always have current versions. To install the yum repository, follow the instructions:

1. Create new file repository using the command:

vi /etc/yum.repos.d/MariaDB.repo

Press the i key to enter the edit mode of the vi editor.

2. Get the text file for your version of Linux distribution from this link. You will need to select the name of the distribution, its version and bitness:

Copy the text for the repository file, paste it into your file and press ":wq" to exit the vi editor and save.

Now you can install MariaDB Server and environment programs using yum:

yum install MariaDB-server MariaDB-client

Libraries for various programming languages ​​can be installed for MySQL and are fully compatible. For example, for PHP:

Now you need to start the installed MariaDB server and proceed to configure it:

If you did everything correctly, you will see this message:

Setting up MariaDB

To get started with MariaDB normally, you need to set a root password. I will tell you in detail about tuning and optimizing the database in a special article; these are not priority things.

Initial MySQL root password setting:

The easiest way to set the root password is to use a special script, it is included in the MariaDB distribution:

/usr/bin/mysql_secure_installation

Follow the instructions and set a root password.

If you have any questions or need clarification, please ask a question or leave a comment.

I'm always happy to help!

I think everyone knows that MariaDB is a branch of MySQL. MariaDB is developed and supported by MariaDB Corporation Ab and the MariaDB Foundation. The lead developer of MariaDB is the well-known Michael Widenius, the author of the original version of MySQL.

The current version is the one that was released on February 13, 2018. In the new version of MariaDB, the InnoDB store was updated to release 5.7.21, and more than 100 bugs were fixed, including those that could be used to initiate a remote denial of service.

Let's try installing the new version on a clean Debian 9.3 (Stretch) system.

I won’t hide the fact that I really like MariaDB and I long ago abandoned using Oracle MySQL in favor of MariaDB or Percona Server for MySQL, and I don’t regret it one bit. MariaDB is used as a replacement for Oracle MySQL in many Linux distributions. MariaDB is also used as a database on a large number of large sites, in fact, my blog is no exception - I use MariaDB 10.2 as a database.

Initial data: OS Debian 9.3 (Stretch);
Task: Install MariaDB 10.2.13 with a minimum of effort and perform basic database setup;

You can find many articles on the Internet about how to install MariaDB on Debian, but they all offer rather cumbersome solutions.
There is also, but it also does not contain simple solution, but it exists!

Installing MariaDB 10.2.13 on Debian 9:

1. Installation of necessary additional equipment. packages:

Apt-get update apt-get install dirmngr wget -y

2. And now the simple magic that is not written about anywhere - downloading and running the repository installation script:

Wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup && chmod a+x mariadb_repo_setup ./mariadb_repo_setup --mariadb-server-version=10.2

For information:
This script will analyze your system, install GPG keys and add the repository to the file /etc/apt/sources.list.d/mariadb.list
The script is official and supports distributions: RHEL/CentOS 6 & 7, Ubuntu 16.04 LTS (xenial) & 18.04 (bionic), Debian 8 (jessie) & 9 (stretch) and SLES 12 and 15
The script also supports installing repositories for different versions of MariaDB, MaxScale and MariaDB Tools. You can download it and see all launch options:

./mariadb_repo_setup --help

P.S. For those who like to do everything by hand, where you can more flexibly select the desired repository depending on the geographical location of your server.

3. Update the list of packages:

Apt-get update

4. Install latest version MariaDB 10.2.x:

Apt-get install mariadb-server -y

During the installation process, the installer will ask us to create a password. root user, at this stage we will leave it empty and change it later.

After installation, check the status of MariaDB:

# systemctl status mariadb ● mariadb.service - MariaDB 10.2.13 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Drop-In: /etc/systemd/system/mariadb. service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Thu 2018-02-15 12:14:17 +05; 19s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 7270 (mysqld) Status: "Taking your SQL requests now..." CGroup: /system. slice/mariadb.service └─7270 /usr/sbin/mysqld # netstat -ltupn | grep mysql tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 7270/mysqld # ps -ef | grep [m]ysql mysql 7270 1 0 12:14 ? 00:00:00 /usr/sbin/mysqld

We will also try to connect to the database using the mysql command (since at the installation stage we left the root password empty, we will not use the -u root -p options):

# mysql Welcome to the MariaDB monitor. Commands end with ; or\g. Your MariaDB connection id is 10 Server version: 10.2.13-MariaDB-10.2.13+maria~stretch-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type "help;" or "\h" for help. Type "\c" to clear the current input statement. MariaDB [(none)]>

We have successfully connected to the MariaDB server.

5. Now let’s launch the Security Configuration Wizard:

Mysql_secure_installation

To the question:
Enter current password for root (enter for none):
Press Enter, the current root password is empty.

And the last question:
Reload privilege tables now?
enter Y, yes we want to reload the privilege table for them to take effect.

After this, we performed the minimum security measures on our MariaDB instance.

Let's now try to connect to the database:

# mysql ERROR 1045 (28000): Access denied for user "root"@"localhost" (using password: NO)

As we can see, with an empty root password we are no longer allowed in.

Let's connect by specifying additional options and entering a password:

# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or\g. Your MariaDB connection id is 20 Server version: 10.2.13-MariaDB-10.2.13+maria~stretch-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type "help;" or "\h" for help. Type "\c" to clear the current input statement. MariaDB [(none)]>

Great, everything worked out.

That's all, see you soon. If you have any questions or want me to help you, you can always.

In this article we will look at how to install and configure Linux, Apache, MariaDB, PHP on Centos7 / RHEL 7. There are many changes in the new version of Centos7 / RHEL 7.

What is LAMP?

LAMP - kit software on an OS consisting of an operating system Linux systems, Apache web server, MySQL database server and language PHP programming(or Perl/Python). LAMP is used to run heavy dynamic websites consisting entirely of free open source software source code. In this article, I am going to explain how Linux, Apache, MySQL/MariaDB (replacement for MySQL), PHP (LAMP) are installed on CentOS 7 or RHEL 7.

• Install RHEL 7 or CentOS 7 server. Open a terminal to the server via ssh, you must have root superuser rights.
• You will also need knowledge of yum commands
• You will need the IP address of your server, use the following command to determine the IP address for the eth0 interface

ifconfig eth0 or ip a show eth0 or ip addr list eth0 | awk "/inet /(sub(/\/+/,"",$2); print $2)" or ifconfig eth0 | awk "/inet /(print $2)" 10.180.10.10

• We will use the resulting IP address 10.180.10.10 to test the installation

So let's get started

Installing Apache on a CentOS 7 /RHEL 7 server

To install the web server we use the command

Yum install httpd

Enable HTTPd service in startup

systemctl enable httpd.service ln -s "/usr/lib/systemd/system/httpd.service" "/etc/systemd/system/multi-user.target.wants/httpd.service"

To disable automatic downloads

Systemctl disable httpd.service rm "/etc/systemd/system/multi-user.target.wants/httpd.service"

Start HTTPd service on CentOS 7/RHEL 7

systemctl start httpd.service

At this point, you can point your web browser to the IP address of your server, http://10.180.10.10. You will see home page apache:

Stopping HTTPd service on CentOS 7 / RHEL 7

systemctl stop httpd.service

Restarting HTTPd service on CentOS 7 / RHEL 7

View apache service status on CentOS 7/RHEL 7

Make sure the web server is running

Systemctl status httpd.service

Also, restarting the web server can be done with the following command

Apachectl graceful

Checking apache/httpd for configuration errors on Centos 7/ RHEL 7

Apachectl configtest

Default HTTPD server configuration:

1. Default configuration file: /etc/httpd/conf/httpd.conf
2. Configuration files for loadable modules: /etc/httpd/conf.modules.d/ (for example, PHP)
3. Select MPMs as loadable modules and events: /etc/httpd/conf.modules.d/00-mpm.conf
4. Standard ports: 80 and 443 (SSL)
5. Default log files: /var/log/httpd/(access_log,error_log)

Installing MariaDB on a CentOS 7/RHEL server

MariaDB is an updated replacement for the MySQL server. On RHEL/CentOS 7, the MariaDB database management system is used instead of MySQL. Enter the following yum command to install MariaDB server:

Yum install mariadb-server mariadb

To start MariaDB, use the command:

Systemctl start mariadb.service

To ensure that the MariaDB service starts automatically during boot, enter:

Systemctl enable mariadb.service

Command output

Ln -s "/usr/lib/systemd/system/mariadb.service" "/etc/systemd/system/multi-user.target.wants/mariadb.service"

To stop/restart and disable MariaDB use the following commands:

Sudo systemctl stop mariadb.service #-- Stop mariadb server sudo systemctl restart mariadb.service #-- Restart mariadb server sudo systemctl disable mariadb.service #-- Disable autoload mariadb server sudo systemctl is-active mariadb.service #-- Check running is there a server?

First launch of MariaDB

Enter the following command:

/usr/bin/mysql_secure_installation

By answering the questions you will be able to configure the initial security of the database

Verifying your MariaDB installation

Enter the following command

Mysql -u root -p

Example output:

Installing PHP on CentOS 7 / RHEL 7

To install PHP and modules such as GD/mysql enter the following command

Yum install php php-mysql php-gd php-pear

You need to restart the HTTPD (Apache) server, enter:

Systemctl restart httpd.service

To search for all other PHP modules:

Yum search php-

To get more detailed information about the module:

Yum info php-pgsql

Checking PHP on the server

Create a file called /var/www/html/test.php like this:

Vi /var/www/html/test.php

Add the following code:

LAMP server is installed, if you have problems setting up the server, we perform one-time work on installing and configuring the LAMP web server.

Today we are going to raise one of the most sought-after roles of any linux servers, which occupy a leading role in this functional segment. Web setup CentOS 7 server based on a combination of the 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 a greater requirement for 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 is a general-purpose programming language that 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:

CPU	2 cores
Memory	8 Gb
Disk	150 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 is a lot of useful information that I do not give in 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, not the general httpd log, but the error log of a specific 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 more a new 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 Kali Linux tools in practice - any information security specialist should be familiar with it.

Test yourself on the entrance test and see the program for more details.

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

For getting 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

Setting up MariaDB to listen to everything 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 instance has been running, the best 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 of very 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 the MariaDB server using the new password:

# mysql -u root -p

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