SQL (The Structured Query Language)

For more information, see the Wikipedia Article.

• SQL is a language that is specifically designed to query data in a relational database management system (RDBMS).
• SQL Statements were based on relational algebra and tuple relational calculus.
• SQL became an ANSI standard in 1986, and an ISO standard since 1987.

SQL being a standard suggests that different database engines should be compatible with each other. For example, an application written for MySQL should be able to communicate with a Microsoft SQL Server using the same SQL queries. However, that is not the case, because most vendors do not fully follow the ANSI and ISO standards. SQL code can rarely be ported between database systems without modifications, so applications that use database back-ends have dependencies on specific database engines. The reason for inconsistency and incompatibility between different engines and vendors' products ranges from ambiguities in the standards to commercial interests of vendor lock-ins and a lack of incentive from vendors. If you, as an IT technician or a sysadmin, are tasked with installing or providing a database service for an application, you must know which specific database to install.

Relational Database

For more information, see the Wikipedia Article.

Relational databases are a class of database systems which meet the following minimum requirements:

• represents data as relations
• Provides relational operators to manipulate the data

The relational models are usually implemented as tables (relations) of rows (records or tuple) and columns (attributes or fields). The way the tables are defined for a set of data is called schema. The software architects of an application that will be using a relational database design the schema to store the information from that application inside a database. They basically define what columns (attributes) are in each table. The rows (fields) are the information being stored. Therefore, the database schema is application and database engine-specific.

In short, the following table summarizes the terminology:

MySQL

MySQL is an open-source (GPL v2) relational database management system (RDBMS). There are other variants of MySQL which may or may not be drop-in compatible with MySQL. MySQL Enterprise Server by Oracle Corporation is under a proprietary license, while MariaDB (GNU GPL) was initially a fork of MySQL and could be used as a drop-in replacement, but has diverged from MySQL with some features and functions (see the MariaDB documentation regarding compatibility).

MySQL was created, owned, and operated by MySQL AB (a Swedish company) for its own internal use in 1995 as a replacement for mSQL (proprietary license). MySQL AB was acquired by Sun Microsystems in 2008, and Sun was acquired by Oracle Corporation in 2010. Due to previous incidents of interactions between Oracle and other software developers and vendors in the past, the original creators of MySQL and 50,000+ MySQL developers petitioned the European Commission to block the Sun Microsystems acquisition to "save MySQL from Oracle". The sale of Sun to Oracle was approved by Sun shareholders, the US government, and eventually by the EU Commission due to pressure exerted by the US government at the request of Oracle. The MySQL project was forked in 2010, just before the merger to create the Open Source MariaDB (GNU GPL) as a drop-in replacement.

MySQL is currently offered by Oracle under two different licenses:

• MySQL Community Edition (open source), provided in most Linux Distros through package management software
• MySQL Enterprise Edition (Oracle proprietary license)

MySQL is part of the LAMP (Linux, MySQL, Apache, PHP) software stack, and is popular for single servers from small to medium deployments. The community edition can be installed in most distros from the repositories (sudo apt install mysql-server). The default installation comes with sample databases and users to assist new developers. MySQL supports various methods of authentication, which may differ from distro to distro. Ubuntu supports root authentication via Unix socket (root can log in as the MySQL root user without a password) by default.

MySQL Configuration Files, Logs, and Utilities

Configuration Files:

• MySQL daemon: /etc/mysql/my.cnf.
• MySQL User Config: ~/.my.cnf.
• Default data storage Directory: /var/lib/mysql.
• Configuration changes require the MySQL service to be restarted for changes to take effect.

Service Management via the OS:

• sudo service mysql [start | stop | restart | status]

Logs:

• /var/log/mysql
• /var/log/mysql/error.log # Contains errors from mysql server.

Utilities:

• The MySQL CLI client: mysql
  • mysql –u root –p # -u to authenticate using MySQL user root, -p to use password
  • mysql –u –p new_db < db_backup.sql # import DB from a text file containing SQL code
• The mysqldump utility: A simple tool to "dump" the text of the SQL commands needed to recreate the specified database
  • mysqldump –u root –p db_name > db_backup.sql
• The mysqladmin utility: used to perform administrative tasks
  • mysqladmin status
  • mysqladmin reload
  • mysqladmin flush-privileges

MySQL Security

The MySQL user accounts and authentication are NOT the same as the Linux user accounts since MySQL has its own users and authentication. The database that stores the configurations and the user accounts is stored inside MySQL and is named mysql. Do not remove the mysql database as it will break the software. For example, you can query this database to see the current users and their authentication methods and if they are using encryption or safer/stronger ciphers.

After installing the MySQL server, it is recommended to harden the database by setting strong root passwords, limiting access to the database, enforcing password quality and encryption, and removing unused and sample databases from MySQL. There is an interactive script available called mysql_secure_installation which will automate many of these tasks.

If the database and the application are on the same server, it is recommended to only allow local traffic from the application. You can enforce this in MySQL local configuration files and additional layered protection using the firewall. Additionally, the front-end applications themselves can be an attack vector in various ways, such as SQL injection attacks due to application input sanitization problems, or simply Apache/PHP errors displaying the PHP source code to the browser, where the MySQL authentication credentials are hard-coded by novice developers. While displaying internal errors to the web client is desired by developers on their development servers, you should disable the display of errors in servers that are reachable over the network. Generally, you should attempt to limit exposing the database engines to the outside as much as possible.

Other MySQL Client Software

Your users and developers may demand access to the database in various ways. These are only two examples of how users may connect to a MySQL database aside from using the mysql CLI utilities:

• MySQL Workbench

  • Graphical application for Windows and Linux Desktop, and is provided as part of MySQL Community and MySQL Enterprise
  • Uses proprietary Oracle licences, but the Community Edition is free to use
    

• The phpMyAdmin web application:

  • A FOSS (Free and Open Source Software) based on PHP
  • Can work with Apache, nginx, and other web servers with PHP support
  • It is often provided as an optional application by LAMP web hosting service providers
  • Included in Ubuntu software repositories: sudo apt install phpmyadmin
    

Note: Before allowing or deploying remote clients, please consider security risks and implications!