MySQL - what is MySQL & what do I need it for

In the early days of the Internet, websites used static code - in this case, all content and additional information for display were embedded directly in an HTML document. This meant that any change had to be made across a multitude of documents just to update a simple text. This is why dynamic websites are preferred today, where information is stored in a MySQL database.

What is MySQL and how is it used?

MySQL is one of the oldest database systems and has been continuously developed since 1994. It is a relational database with a syntax resembling a programming language, where tables and data can be read, modified, or created using a simple command.

However, for users, it is only necessary to work directly with the database in exceptional cases. Instead, this task is taken over by PHP scripts, which read and manipulate the database through their embedded instructions. The output then occurs on web pages with a graphical interface, as is the case with configuration or the user interface of a CMS. Additionally, for direct work with a database, there are graphical tools available such as phpMyAdmin, written in PHP, which allow manual access.

What are the benefits of web hosting with a MySQL database and PHP?

A database is essential for Content Management Systems (CMS). In addition to MySQL, some formats like SQLite, where the database is stored in a file, are supported. However, with MySQL, information is stored in memory, significantly increasing performance. The processing speed of the data in turn affects the speed of the PHP interpreter, and therefore, slow access leads to an increased Server Response Time (SRT) and delayed page loading. For this reason, MySQL or MariaDB should always be preferred over other formats whenever possible. In almost all cases, paid web hosting is combined with a MySQL database. However, with free hosting, it may be necessary to purchase this as a paid option.

What are the advantages of MySQL over other databases?

Over the past decades, MySQL has become one of the most popular database systems and is the standard choice for most CMS. Its advantages include:

• Open-source code under an Open Source license
• Free version allows free usage in commercial projects
• Support for various purpose-optimised engines (storage systems)
• High performance even with large amounts of data
• Extensive support from applications and a large community
• Stability in productive systems
• Server-client architecture allows operation of numerous databases
• No direct limit on table and database size

These characteristics make MySQL ideal for applications such as dynamic websites, where efficient syntax and high responsiveness are crucial. Due to its open availability, web hosting with MySQL databases has therefore become an unofficial standard. It is often combined with a Linux operating system, PHP, and an Apache web server, known in this combination as LAMP (Linux, Apache, MySQL, PHP), or when using nginx as LEMP.

What is the difference between MySQL and MariaDB?

After the acquisition of MySQL AB by Oracle in 2010, many leading programmers criticised that the open-source version was being neglected in favour of the commercial version. This led Michael Widenius - one of the founders of MySQL AB and its chief developer - to leave the project and start a fork based on the open-source code. He named this fork (as he had previously named MySQL) after one of his daughters, MariaDB. MariaDB is fully compatible in syntax and allows (almost always) a hassle-free migration from one system to another. However, MariaDB has some features that are reserved for the commercial version of MySQL. While transitioning to MariaDB does not cause conflicts, returning to MySQL requires adjustments. MariaDB has now gained widespread acceptance and is used as a standard by popular Linux distributions such as Debian, Ubuntu, OpenSUSE, Arch, Fedora, or Slackware. More information can be found in our MySQL vs. MariaDB comparison.

What information does the MySQL database store?

In web hosting with MySQL databases, it serves as a central instance where contents required by PHP scripts are stored. These include:

• Texts including headings, meta tags, and forms
• Users such as customers, administrators, moderators, or registered users
• Rights for manipulating and creating new content
• Passwords for different accounts
• External and internal links
• References to necessary files such as images, videos, or audio
• Contents of menus or buttons
• Email addresses

Sensitive data such as passwords are not stored in plain text by MySQL and MariaDB, but are encrypted by secure methods like one-way encryption by PHP scripts. Decryption of the password is not possible. The security of a web hosting with MySQL database and PHP is therefore guaranteed even in the event of a database theft, provided secure passwords of a certain length and special characters were used.

Photo: mcmurryjulie | pixabay.com