SQL server

SQL; Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data where there are relations between different entities/variables of the data. SQL offers two main advantages over older read/write APIs like ISAM or VSAM. First, it introduced the concept of accessing many records with one single command; and second, it eliminates the need to specify how to reach a record, e.g. with or without an index.

MySQL vs MariaDB

The original MySQL was created by a Finnish/Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius. The first version of MySQL appeared in 1995. It was initially created for personal usage but in a few years evolved into a enterprise grade database and it became the worlds most popular open source relational database software - and it still is. In January 2008, Sun Microsystems bought MySQL for $1 billion. Soon after, Oracle acquired all of Sun Microsystems after getting approval from the European Commission in late 2009, which initially stopped the transaction due to concerns that such a merger would harm the database markets as MySQL was the main competitor of Oracle's database product.

MariaDB (new logo)Out of distrust in Oracle stewardship of MySQL, the original developers of MySQL forked it and created MariaDB in 2009. As time passed, MariaDB replaced MySQL in many places and everybody reading this article should consider it too.

Learn more.

Installation MariaDB

Make a repo file for the MariaDB:

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

Insert:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

# yum makecache fast
# yum -y install MariaDB-client MariaDB-server
# systemctl stop mariadb

Now we make a directory in the /srv directory.

# cd /srv
# mkdir /srv/mysql
# chcon unconfined_u:object_r:mysqld_var_run_t:s0 mysql
# chown mysql:mysql mysql

Configuration

We edit the server-configfile.

# vi /etc/my.cnf.d/server.cnf

# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
datadir=/srv/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Moving datadir

# mv /var/lib/mysql/* /srv/mysql/
# cd /var/lib/mysql/
# touch mysql.sock
# chown mysql:mysql mysql.sock
# chcon -t mysqld_var_run_t mysql.sock
# cd /srv/mysql/
# chown -R mysql:mysql *
# cd /srv/
# chcon -R -t mysqld_var_run_t mysql/

Starting the sql server

# systemctl start mariadb
# systemctl enable mariadb

Firewall

If you need other systems on the network to access the database server, please allow port 3306:

# firewall-cmd --permanent --add-service=mysql
# firewall-cmd --reload

Do NOT open this port on your WAN-router.

Initialisation of the sql server

# mysql_secure_installation

secure

Testing the sql server

[root@server4 mysql]# mysql -u root -p  //Never add the password after -p because it will be shon in the bash history.
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.17-MariaDB MariaDB Server

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)]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.4.17-MariaDB |
+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

As off MariaDB 10.4 the creation of users is changed. Check the manuals.


MariaDB [(none)]> grant all privileges on wordpress.* TO 'wordpress'@'localhost' identified by 'Password';
MariaDB [(none)]> show grants for 'wordpress'@localhost;
+------------------------------------------------------------------------------------------------------------------+
| Grants for wordpress@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wordpress`@`localhost` IDENTIFIED BY PASSWORD 'encrypted password'                        |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO `wordpress`@`localhost`                                                 |
+------------------------------------------------------------------------------------------------------------------+

Rob's web