MySQL is an open-source relational database management system (RDBMS) supported by Oracle and based on the Structured Query Language (SQL). MySQL runs on virtually all platforms, including Linux, UNIX, and Windows. Although it can be used in a wide range of applications, MySQL is most commonly associated with web applications and online publishing.
MySQL is an important component of an open source enterprise stack called LAMP. LAMP is a web development platform that uses Linux as the operating system, Apache as the web server, MySQL as the relational database management system, and PHP as the object-oriented scripting language. (Sometimes Perl or Python is used instead of PHP.)
Originally designed by Swedish company MySQL AB, MySQL was acquired by Sun Microsystems in 2008, and then by Oracle when it bought Sun in 2010. Developers can use MySQL under the GNU General Public License (GPL), but companies must obtain a commercial license from Oracle.
Today, MySQL is the RDBMS behind many of the world’s top websites and countless business and consumer web applications, including Facebook, Twitter, and YouTube.
How MySQL Works
MySQL is based on a client-server model. The heart of MySQL is the MySQL server, which handles all the instructions (or commands) in the database. MySQL Server is available as a separate program for use in a client-server network environment and as a library that can be embedded (or linked) into separate applications.
MySQL works with several utility programs that support the administration of MySQL databases. Commands are sent to MySQLServer through the MySQL client, which is installed on a computer.
MySQL was originally developed to handle large databases quickly. Although MySQL is usually installed on a single machine, it is capable of sending the database to multiple locations because users can access it through different MySQL client interfaces. These interfaces send SQL statements to the server and then display the results.
Basic MySQL Features
MySQL allows data to be stored and accessed on multiple storage engines, including InnoDB, CSV, and NDB. MySQL is also able to replicate data and partition tables for better performance and durability. MySQL users don’t need to learn new commands; they can access their data using standard SQL commands.
MySQL is written in C and C++ and accessible and available on over 20 platforms, including Mac, Windows, Linux, and Unix. The RDBMS supports large databases with millions of records and supports many data types, including signed or unsigned integers 1, 2, 3, 4, and 8 bytes long; FLOAT; DOUBLE; CARBONIZE; VARCHAR; BINARY; VARBINARY; TEXT; DROP; DATED; TIME; DATE HOUR ; TIMESTAMP; YEAR; TOGETHER; ENUM; and OpenGIS spatial types. Fixed and variable length string types are also supported.
For security reasons, MySQL uses a system of access privileges and encrypted passwords that allows for host-based verification. MySQL clients can connect to MySQL Server using multiple protocols, including TCP/IP sockets on any platform. MySQL also supports a number of client and utility programs, command line programs, and administration tools such as MySQL Workbench.
MySQL offshoots, also known as forks, include the following:
- Drizzle, a lightweight open source database management system in development based on MySQL 6.0;
- MariaDB, a popular community-developed “drop-in” replacement for MySQL that uses MySQL APIs and commands; and
- Percona Server with XtraDB, an enhanced version of MySQL known for its horizontal scalability.
MySQL versus SQL
Before 2016, the main difference between MySQL and SQL was that the former could be used on multiple platforms while the latter could only be used on Windows. Microsoft has since extended SQL to support Linux, a change that took effect in 2017. When MySQL is installed through Linux, its package management system requires custom configuration to adjust security and optimization settings.
MySQL also allows users to choose the most efficient storage engine for a given table, as the program is able to use multiple storage engines for individual tables. One of MySQL’s engines is InnoDB. InnoDB was designed for high availability. For this reason, it is not as fast as other engines. SQL uses its own storage system, but it maintains several data loss protections. Both systems can be clustered for high availability.
SQL Server offers a wide variety of data analysis and reporting tools. SQL Server Reporting Services is the most popular and is available as a free download. Similar analysis tools for MySQL are available from third-party software vendors., such as Crystal Reports XI and Actuate BIRT.
Compatibility with other services
MySQL was designed to be compatible with other systems. It supports deployment in virtualized environments, such as Amazon RDS for MySQL, Amazon RDS for MariaDB, and Amazon Aurora for MySQL. Users can transfer their data to a SQL Server database using database migration tools like AWS Schema Conversion Tool and AWS Database Migration Service.
The semantics of database objects between SQL Server and MySQL are similar, but not identical. There are some architectural differences to consider when migrating from SQL Server to MySQL. In MySQL, there is no difference between a database and a schema, while SQL Server treats the two as separate entities.