Rob's web

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.

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

Mariadb logo

# curl -LsSO https://r.mariadb.com/downloads/mariadb_repo_setup
# echo "ceaa5bd124c4d10a892c384e201bb6e0910d370ebce235306d2e4b860ed36560 mariadb_repo_setup" | sha256sum -c -
# chmod +x mariadb_repo_setup
# ./mariadb_repo_setup --mariadb-server-version="mariadb-10.6"
# dnf install MariaDB-server MariaDB-backup
# systemctl status mariadb

If enabled and running:

# systemctl stop mariadb
# systemctl disable mariadb

Now we make a directory in the /srv directory.

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

Configuration

We edit the server-configfile.

# vi /etc/my.cnf.d/mariadb-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
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

Moving datadir

If /var/lib/mysql is empty you can skip the database move.

The mysql.sock file must stay here. If moved move it back.

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

Starting the sql server

When there is an error correct it first before enable the service.

# 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

# mariadb-secure-installation

secure

Testing the sql server

Testing mysql.sock

# stat /var/lib/mysql/mysql.sock
  File: /var/lib/mysql/mysql.sock
  Size: 0               Blocks: 0          IO Block: 4096   socket
Device: 813h/2067d      Inode: 25165972    Links: 1
Access: (0777/srwxrwxrwx)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Context: system_u:object_r:mysqld_var_run_t:s0
Access: 2024-10-22 17:08:06.112143286 +0200
Modify: 2024-10-22 17:05:36.509580131 +0200
Change: 2024-10-22 17:05:36.509580131 +0200
 Birth: 2024-10-22 17:05:36.509580131 +0200

Connecting with mariadb

# mariadb -u root -p  //Never add the password after -p because it will be shown in the bash history.
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.5.22-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.5.22-MariaDB |
+-----------------+
1 row in set (0.000 sec)

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

Create users

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`                                                 |
+------------------------------------------------------------------------------------------------------------------+

Set or alter password

After installation de root is not required to enter a password.

# mariadb -u root
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit;
Bye
#

CMS users

When your CMS use an sql database never let it use root as user. Create for every cms or application an own user.

In some CMS you need to create them self others can create them in the setup. Check the setup info.

Links

Videos