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.
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.
# 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/
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
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/
When there is an error correct it first before enable the service.
# systemctl start mariadb # systemctl enable mariadb
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.
# mariadb-secure-installation
# 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
# 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)
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` | +------------------------------------------------------------------------------------------------------------------+
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 #
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.