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.
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.