MySQL

How MySQL works

MySQL is based on a client-server model. The core of MySQL is MySQL server, which handles all of the database instructions (or commands). MySQL server is available as a separate program for use in a client-server networked environment and as a library that can be embedded (or linked) into separate applications.

MySQL operates along with several utility programs which support the administration of MySQL databases. Commands are sent to MySQLServer via the MySQL client, which is installed on a computer.

MySQL was originally developed to handle large databases quickly. Although MySQL is typically installed on only one machine, it is able to send the database to multiple locations, as users are able to access it via different MySQL client interfaces. These interfaces send SQL statements to the server and then display the results.

Core MySQL features

MySQL enables data to be stored and accessed across multiple storage engines, including InnoDB, CSV, and NDB. MySQL is also capable of replicating data and partitioning tables for better performance and durability. MySQL users aren't required to learn new commands; they can access their data using standard SQL commands.

MySQL is written in C and C++ and accessible and available across over 20 platforms, including Mac, Windows, Linux and Unix. The RDBMS supports large databases with millions records and supports many data types including signed or unsigned integers 1, 2, 3, 4, and 8 bytes long; FLOAT; DOUBLE; CHAR; VARCHAR; BINARY; VARBINARY; TEXT; BLOB; DATE; TIME; DATETIME; TIMESTAMP; YEAR; SET; ENUM; and OpenGIS spatial types. Fixed- and variable-length string types are also supported.

For security, MySQL uses an access privilege and encrypted password system that enables host-based verification. MySQL clients can connect to MySQL Server using several 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.

Offshoots of MySQL, 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 horizontal scalability. 

MySQL vs SQL

Before 2016, the main difference between MySQL and SQL was that the former could be used on multiple platforms, whereas the latter could only be used on Windows. Microsoft has since expanded SQL to support Linux, a change which went into effect in 2017. When MySQL is installed via Linux, its package management system requires custom configuration to adjust security and optimization settings.

MySQL also allows users to choose the most effective storage engine for any given table, as the program is able to utilize multiple storage engines for individual tables. One of MySQL's engines is InnoDB. InnoDB was designed for high availability. Because of this, it is not as quick as other engines. SQL uses its own storage system, but it does maintain multiple safeguards against loss of data. Both systems are able to run in clusters for high availability.

SQL Server offers a wide variety of data analysis and reporting tools. SQL Server Reporting Services is the most popular one and is available as a free download. There are similar analysis tools for MySQL available from third-party software companies, 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 by using database migration tools like AWS Schema Conversion Tool and the AWS Database Migration Service.

The database object semantics between SQL Server and MySQL are similar, but not identical. There are architectural differences that must be considered 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.