Check MySQL Version Ubuntu

Introduction to MySQL

MySQL is an open-source RDMS (Relational Database Management System). Its title is a set of "My" the title of the daughter of the co-founder Michael Widenius, and "SQL", stands for Structured Query Language. The relational database manages data into multiple data tables in which the types of data might be related to each other; the relations aid structure of the data.

Structured Query Language is a language that programmers use to establish, change, and extract data through the relational database and control access of the user to the database. In addition to SQL and relational databases and RDMS such as MySQL operates with an operating system for implementing a relational database in the storage system of the computer. It permits network access and offers backup creation and testing of database integrity.

Some key points are as follows:

• MySQL is open-source and free software under the GNU General Public License terms and it is also present under a range of proprietary licenses.
• It was sponsored and owned by MySQL AB (a Swedish company) which was purchased by Sun Microsystems (Oracle Corporation).
• When Oracle inherited Sun, Widenius divided the open-source project of MySQL for creating MariaDB in 2010.
• MySQL contains stand-alone clients that permit users for interacting with the MySQL database with SQL directly, but MySQL is used with several programs for implementing applications that require the capability of the relational database.
• MySQL is an element of the LAMP web application software stack which stands for Linux, Apache, MySQL, Python/PHP/Perl.
• MySQL is used by several database-driven web applications such as WordPress, phpBB, Joomla, and drupal. Also, MySQL is used by several famous websites including YouTube, Twitter, MediaWiki, Flicker, and Facebook.

Overview of MySQL

MySQL is specified in the C++ and C programming languages. The SQL parser of SQL is specified in yacc, although it utilizes a home-brewed lexical analyzer. Besides, MySQL works on several system platforms, such as Tru64, Sanos, SCO UnixWare, SCO OpenServer, SunOS, Symbian, Oracle Solaris, QNX, OS/2 Wrap, OpenSolaris, OpenBSD, Novell NetWare, NetBSD, Microsoft Windows, macOS, Linux, IRIX, IBM i, eComStation, ArcaOS, HP-UX, FreeBSD, BSDi, and AIX.

Also, a MySQL port to OpenVMS is available. The MySQL client libraries and server software itself apply dual-licensing distribution. They are provided under a proprietary license and GPLv2. Support can be achieved through the official manual. Additionally, free support exists in different IRC forums and channels.

Oracle provides paid support from its MySQL Enterprise products. These products differ in price and service scope. Several third-party organizations are also available to give services and support.

Brief History of MySQL

MySQL was developed by MySQL AB, a Swedish company, founded by Finnish Michael "Monty" Widenius, Allan Larsson, and Swedes David Axmark. Original MySQL development by Axmark and Widenius in 1994. The initial release of MySQL was represented on 23 May 1995.

Initially, it was built for personal use via MySQL which was the low-level language ISAM-based, which the creators acknowledged was inflexible and too slow. They made a new interface of SQL while keeping a similar API to mSQL. Several developers can use MySQL rather than the mSQL antecedent by keeping the API the same as the mSQL system.

Some other MySQL development milestones included:

• Initial internal version on 23 May 1995
• The 3.19 version: End of 1996
• The 3.20 version: January 1997
• The Windows release was published on 8 January 1998 for Windows NT and 98.
• The 3.21 version: production publication 1998, via mysql.com.
• The 3.22 version: beta, alpha from 1998
• The 3.23 version: beta from 2000 June, production publication 22 January 2001.
• The 4.0 version: beta from 2002 August, production publication March 2003.
• The 4.1 version: beta from 2004 June, production publication October 2004 (B-trees and R-trees, prepared statements, subqueries).
• The 5.0 version: beta from 2005 March, production publication October 2005 (cursors, triggers, stored procedures, views, XA transactions).
  • The Federated Storage Engine's developer said that "The Federated Storage Engine is a proof-of-concept storage engine", although the primary distribution of the 5.0 version of MySQL by default added it and activated it. Documentation of a few of the shortcoming occurs in "MySQL Federated Tables: The Missing Manual".
• In 2008, Sun Microsystems inherited MySQL AB.
• The 5.1 version: production publication 27 November 2008 (partitioning, event scheduler, plugin API, server log tables, row-based replication).
  • The 5.1 version included 20 known wrong and crashing result bugs in inclusion to the 35 available in the 5.0 version.
  • The 6.0-alpha and 5.1 versions of MySQL displayed poor performance during usage for data warehousing- somewhat because of its drawback to use two or more CPU cores to process only one query.
• On 27 January 2010, Oracle inherited Sun Microsystems.
• At the time Oracle disclosed the Sun purchase, Michael "Monty" Widenius divided MySQL, releasing MariaDB.
• The 5.5 version of MySQL Server was usually available. Some features and enhancements are:
  • InnoDB is the default storage engine, which supports referential integrity constraints and transactions.
  • Upgraded InnoDB I/O subsystem
  • Semisynchronous replication
  • Upgraded SMP support
  • Supports supplementary character sets of Unicode utf8mb4, utf32, and utf16.
  • RESIGNAL and SIGNAL statement in consent with the SQL standard.
  • New options for partitioning (user-defined).
• As the final version of the 6.0 line, the 6.0.11-alpha version of MySQL Server was declared on 22 May 2009. The future development of MySQL Server applies a New Release Model. Aspects developed for the 6.0 version are being merged into future releases.
• MySQL 5.6 general availability was declared in February 2013. New aspects added higher transactional bandwidth in InnoDB, performance upgrades to the query optimizer, new NoSQL-style Memcached APIs, developments to partitioning for managing and querying too large tables, replication improvements, TIMESTAMP column type that accurately stores milliseconds, and better performance considerations by increasing the data exists from the PERFORMANCE_SCHEMA. Also, the InnoDB storage engine added support for upgraded group commit performance and full-text search.
• MySQL 5.7 general availability was declared in October 2015. MySQL supports a JSON data type specified by RFC 7159 as of the 5.7.8 version of MySQL.
• The 8.0 version of MySQL Server was declared in April 2018. It includes NoSQL Document Store, JSON Extended syntax, crash safe and atomic DDL sentences, new functions, including partial updates, improved sorting, and JSON table functions.
• MySQL was selected for the 2019 year's DBMS through the DB-Engines ranking.

Features of MySQL

MySQL comes in two different editions: the proprietary Enterprise Server and the open-source MySQL Community Server. MySQL Enterprise Server is characterized by a proprietary extension series which install as the server plugins, but shares the numbering system of the version and is created from a similar codebase.

Some major features of MySQL are as follows:

• A broad ANSI SQL 99 subset as well as extensions
• Support for cross-platform
• Stored procedures with a procedural language that adheres to PSM/SQL closely
• Cursors
• Triggers
• Updatable views
• Information schema
• Online DDL (Data Definition Language) when applying the InnoDB Storage Engine
• A group of SQL Mode options for controlling runtime behavior including the strict mode for adhering to SQL standards.
• Performance schema that aggregates and collects statistics about query performance and server execution to monitor purposes
• Transactions using savepoints if applying the default InnoDB Storage Engine. Also, the NDB Cluster Storage Engine supports transactions.
• Supports X/Open XA DTP (distribute transaction processing); two-phase commit as a part of it with the default InnoDB storage engine
  SSL support
• ACID compliance if using an NDB Cluster and InnoDB storage engines
• Sub-SELECTs (such as nested SELECTs)
• Query caching
• Supports built-in replication:
  
  
  • Synchronous replication: In this type of replication, multi-master replication is offered in MySQL Cluster.
  • Semi synchronous replication: It is a master to slave replication in which the master wait for replication.
  • Asynchronous replication: It is also a master-slave replication from one master to several slaves or several masters to one slave.
  • Virtual synchronous: This type of replication is self-managed MySQL server groups with support for multi-master can be done with the built-in
• Group Replication plugin or Galera Cluster.
• Embedded library of database
• Full-text searching and indexing
• Unicode support
• Shared-nothing clustering from MySQL Cluster
• Partitioned tables with partition pruning in the optimizer
• More than one storage engine, permitting one to select the one that is most efficient for all tables within the application.
• Gathering more than one transaction and commit grouping from more than one connection together for increasing the number of commits/second
• Native storage engines such as NDB Cluster Blackhole, CSV, Archive, Federated, Memory (heap), Merge, MyISAM, and InnoDB.
• The developers publish minor MySQL server updates every two months approximately.
• The sources can be accessed from the website of MySQL or the GitHub repository of MySQL, both upon the GPL license.

Limitations of MySQL

When using the storage engines other than the InnoDB default, MySQL doesn't comply with the complete SQL standard for a few of the implementing functionality such as foreign key references. Also, check constraints are determined but avoided by every storage engine before the 8.0.15 version of MySQL.

Deployment of MySQL

MySQL can be manually created and installed from source code but it's more generally installed using a binary package unless unique customizations are needed. The package management system can install MySQL with basic efforts on almost all distributions of Linux, though the further configuration is needed for adjusting optimization and security settings.

MySQL has evolved gradually for supporting higher-scale requirements as MySQL started as a low-end replacement for more robust proprietary databases. Still, it is more generally used in small-medium scale single-server deployment either as a standalone database server or a component within the LAMP-based web applications. Much of the appeal of MySQL originates in its corresponding ease of use and simplicity which is activated by an open-source tool's ecosystem like phpMyAdmin.

Some key points are as follows:

• MySQL can be extended by deploying it over more robust hardware like a multi-processor server with GBs of memory.
• However, some limits are there to how far work can scale on an individual server.
• Multi-server MySQL deployments are needed to give improved reliability and performance on larger scales.
• A common high-end configuration can contain a robust master database that manages data write actions and it is replicated to more than one slave that manages every read action.
• Further performance improvements can be achieved by taking the outcomes from the database queries using memcached in memory or breaking down the database into smaller parts known as 'shards' which can be increased around several distributed server clusters.

Cloud deployment

MySQL can be executed on many platforms of cloud computing such as Oracle Cloud Infrastructure, Amazon EC2, Microsoft Azure, etc. A few basic MySQL development models on the cloud are as follows:

Virtual machine image

Users of the cloud can upload their machine image using installed MySQL or apply a ready-made machine image using MySQL optimized installation on it like the one offered by Amazon EC2 with this implementation.

MySQL as a service

MySQL 'as a service' can be offered by a few cloud platforms. In this implementation, the owners of the application don't need to install and manage their MySQL database. The database service provider holds the responsibility to install and maintain the database rather, and the owners of the application pay according to their usage.

Some remarkable services of cloud computing MySQL are the Jelastic, Heroku, HP Converged Cloud, Rackspace, Azure Database for MySQL, Oracle MySQL Cloud Service, and Amazon Relational Database Service.

High availability application

Oracle MySQL provides a high availability solution along with a mix of tools such as the MySQL shell and the MySQL router. They are based on open-source, Group replication tools. MariaDB provides the same offer in product terms.

User interface of MySQL

Graphical user interfaces

A GUI is a kind of interface that permits users for interacting with programs or electronic devices through visual indicators and graphical icons like secondary notation, as disputed to text navigation, typed command labels, or text-based interfaces.

Free graphical administration and third-party proprietary applications are available that develop with MySQL and let users visually implement data and database structure.

MySQL Workbench

For MySQL, MySQL Workbench is the developed environment. It was integrated by MySQL AB and let users visually create database structures and administer MySQL database graphically.

MySQL Workbench is present in three different editions. The first one is the basic open-source and free Community Edition which can be downloaded through the MySQL website, the second one is the proprietary Standard Edition which improves and extends the Community Edition's features set, and the third one is the MySQL Cluster CGE.

Some other tools of GUI:

• Webmin
• Toad for MySQL
• SQLyog
• SQLBuddy
• phpMyAdmin
• org Base
• Navicat
• LibreOffice Base
• HeidiSQL
• DBEdit
• DBeaver
• Database Workbench
• Adminer

Command-line interfaces

The command-line interface is a definition of collaborating with a computer program in which the user problem commands to the program by entering successive text lines (command lines). MySQL moves with several command-line tools through which the primary interface is the mysql client.

The utilities of MySQL are a group of utilities established for performing common administrative and maintenance operations. The utilities are the stand-alone download accessible from Oracle which was originally added as a part of MySQL Workbench.

MySQL shell is interactively used for the MySQL database administration. It supports SQL, Python, or JavaScript modes and it can also be used for access and administration purposes.

MySQL interfaces for application programming

Several programming languages include libraries to access MySQL databases with language-specific APIs. These include MySQL Net/Connector for CLI/.NET languages and the JDBC driver for Java.

An ODBC interface known as MySQL Connector/ODBC permits extra programming languages that support the interface of ODBC for communicating with the MySQL database such as ColdFusion or ASP. The HTSQL (a method of URL-based query also ports with the MySQL adaptor, permitting direct interaction between any web client and a MySQL database by structured URLs). Several other drivers are also available for languages such as Node.js or Python.

MySQL Project Forks

A range of MySQL forks are available, including the below:

Current

Percona Server for MySQL

It was forked by Percona, which focuses on retaining close compatibility with the MySQL official releases. XtraDB was also added to the Percona server for MySQL which is the fork of Percona of the InnoDB Storage Engine.

MariaDB

It is a community-developed MySQL relational database management system fork intended for remaining free upon the GNU GPL. This fork has been led by the real MySQL developers who forked it because of some concerns about its execution via Oracle.

Abandoned

OurDelta

It was created by the Open Query (an Australian company) but later inherited by Catalyst IT Australia. It had two different versions. The first one is the 5.0 version which was MySQL-based and the second one is the 5.1 version which was MariaDB-based. It added patches integrated by Open Query and other notable team members of the MySQL community including Google and Jeremy Cole.

WebScaleSQL

It was a MySQL 5.6 software branch and was published on 27 March 2014 by Twitter, LinkedIn, Google, and Facebook as a joint effort for providing a centralized development structure to extend MySQL with new aspects specific to its large-scale formations like building bigger replicated databases executing on server farms.

Drizzle

It was an open-source and free software relational database management system that was forked through the now-defunct 6.0 development branch of MySQL DBMS. Drizzle contains a client/server architecture and applies SQL as its main command language like MySQL. Drizzle was deployed upon the second and third versions of the GNU GPL with many portions including the replication messaging and protocol drivers upon the BSD license.

Checking the MySQL version in Ubuntu

Prerequisites

Before we start, ensure that we have:

• Installed and mounted the MySQL server
• Have root access or sudo account enabled

We can get begun once we have the requirements which are mentioned above.

Check Status of MySQL using Systemd

Systemd is a robust Linux servicer manager and init system that permits monitors, stops, and starts the statuses of services and daemons. Additionally, it provides features such as tracking and logging usage, etc. So, it is a basic tool for all system administrators.

We need to use the following command for using systemd to check the MySQL service:

Once we run the above command successfully, systemd will begin the service thinking it doesn't execute into the errors. We need to use the following command for checking the service status:

It will provide us with the output below displaying that the service is active.

Check Status of MySQL using MySQLadmin

We can use a tool like mysqladmin as well. MySQL admin is a command-line utility of the MySQL server administration for checking the MySQL server status. We can use the following command:

We will get the result if the MySQL server is active and running:

Check MySQL Version

Method 1

We can use the following command in the terminal window to see the version of MySQL:

Method 2

We can use the command, i.e., mysqld with the option, i.e., -V to check the version of MySQL active on the local host system. The following examples can only be used for a localhost:

Method 3

We can also get the version from inside the MySQL shell by entering the following statement:

Method 4

We can also see the information of the MySQL version which is saved inside a variable called 'version'. We need to enter the following statement to see the server version of MySQL.