Introduction to MySQL
From the very beginning, computing has served to process, calculate and organize data that needs to be stored before and after working with it.
Related to this was the big problem of storing large amounts of data in a structured, orderly, secure and reliable way.
Databases were created in the early 1960s to facilitate data management.
A database is nothing but an organized and structured stored grouping of related data.
The first solutions were simple text files, but they are inefficient for very large amounts of data, as they are slow to handle due to data volume and physical hardware limitations, they are also easily corrupted and do not guarantee data integrity.
A further development is relational databases, which provide a predefined method of storing data organized into tables with rows and columns, while also maintaining relationships between tables to ensure data integrity.
These systems are called Relational database of the management system (RDBMS) and are responsible for managing the structure of the database, the integrity of the stored data, controlling access to it and providing the ability to regularly perform operations on the database Structured Query Language (SQL). It also controls that the result follows the ACID rules, meaning that they are atomic, coherent, isolated, and persistent.
There are many types of relational databases on the market, some are free, some are paid, some are designed for specific market niches or types of applications.
However, the database that has been at the top of the game for the past twenty years is undoubtedly MySQL, a system that, apart from being free, is also one of the easiest to install and maintain, which is why it has become a favorite of developers.
MySQL history
It was created in Sweden in 1995 by a company called MySQL AB and its main developers were Michael Widenius, David Axmark Y Alan Larson who, despite the complicated and/or expensive existing solutions, tried to develop a solution that is both effective and simple and economical for private and professional users.
Although MySQL is known as the longest-running open source database, it was not always open source, being released as free software under the GPL license as recently as 2000 before the code was closed, which was initially a risky bet and lost revenue, although over time recovered, and MySQL’s open source status led to third-party collaboration on the project.
The company continued to grow strongly in terms of both users and revenue until it was sold to Sun Microsystems for $1 billion in 2008, and a year later acquired by (Sun) oracle and where the MySQL project was somewhat stalled by disputes and litigation that took several years to resolve.
On the other hand, Oracle already had its own database, so the continuation of MySQL was not of much interest to the company before disagreements and uncertainty about the future of the project led to some of the original developers leaving Sun and creating a fork under named Fork MariaDB. which quickly gained a following in the free software community.
For several years, the MySQL project was half-abandoned and aimless while MariaDB was developed. MySQL has only come to life in the last few years, although both MySQL and MariaDB start with the same code, one of the main disadvantages of MySQL compared to MariaDB is the restrictions that Oracle imposes on the Community version, which allows certain features for the Enterprise Version reserved, while MariaDB lifts those restrictions, although its performance has been questioned.
characteristics
Now let’s see what are the characteristics of MySQL.
SQL support
It is the most widely used language for retrieving, inserting, updating and deleting data from a relational database, although each database engine adopts a slightly different SQL syntax than others, it is usually quite familiar, so adaptation to standard MySQL-SQL is extremely demanding. easy.
look
Views are virtual tables created from queries using data from other tables. The data does not physically exist in this table but in the original one, and changes in it are visible in the display. This is an important database feature that you need to manage large amounts of data, which is why MySQL has been criticized, but since version 5 it already supports this feature.
Stored procedures
It allows adding functions directly to the database that can be used instead of manipulating the data directly in the tables. They have the benefit of improved performance by running this compiled code and cache.
This is particularly useful, for example, when it is necessary to repeat the results to perform a task, instead of doing it directly from the client, it is performed directly on the server, avoiding data transfer and integration on the client side, using cache and server resources.
trigger (or trigger)
It allows the automation of some tasks within the database, for example that a certain action is performed before or after an event occurs in a certain table, there are three events that can trigger a trigger, they are the insert operation (INSERT) . delete (DELETE) or update (UPDATE).
transactions
Transactions are a set of different operations that can be of different types (INSERT, UPDATE, DELETE, among others) and that change the database only if they are all correct, if one fails or if the end of the transaction is not reached. No changes to the database, allowing database integrity to be maintained in the event of software or hardware failure, resource exhaustion, power outages, server crashes, and business interruptions.
Pros and Cons of MySQL
Free
The main advantage is that it is free, although much of the code is exclusively owned by Oracle, the basic version is distributed under the GNU General Public License and allows anyone to use it, you only have to pay for the license if you try to install another proprietary product of theirs or if you want support and access to additional tools such as monitoring tools.
easy to install
It is a database that is very easy to install, great technical knowledge is not required because it requires very little configuration and is available for a large number of operating systems, it is the most used database on the web and is supported by almost all hosting
Popular among developers
Thanks to its popularity for web projects, small and medium-sized applications, it is very easy to find developers who know MySQL. There are also libraries for use in almost all modern programming languages such as PHP, Python, Ruby, JavaScript (Node). , C/C++, Java, Go, ASP, the .Net family and more.
Fast with little data
It is a lightweight database engine, requires less hardware resources than other databases, but is not as efficient for large data sets, although its configuration can be highly optimized to improve performance when a table has several million queries. Queries can take a long time, and in high-traffic projects this can be a problem.
requirements
It is difficult to talk about the requirements, although there is a list of minimum and recommended requirements, the truth is that it can work just as well with less resources, in fact the vast majority of cloud VPS services have less resources than the recommended Minimum and MySQL does the same, it all depends on the purpose we give it.
minimum characteristics
- CPU with 2 cores
- 2 GB RAM memory
- A disk I/O subsystem applicable to a write-intensive database
Featured Features
- CPU with 4 cores
- 8 GB of RAM or more
- Disk array in RAID 10 or RAID 0+1
The system is operational
- Linux/Unix
- Microsoft Windows
- macOS
Installing MySQL Server on CentOS/RHEL
Some time ago MySQL was replaced by MariaDB in the CentOS repository, we can still add the official MySQL repository as follows, first we download the installer for CentOS/RHEL:
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
We continue with the installation:
rpm -ivh mysql-community-release-el7-5.noarch.rpm
The system has been updated:
yum update
Finally, let’s install the MySQL server:
yum install mysql-server
Installing MySQL server on Ubuntu/Debian
In the case of Ubuntu, MySQL is still in the repositories, but in Debian it has been replaced by MariaDB. To install the MySQL demos, first add the repositories:
debian
Let’s update the repositories first:
apt-get update
We ensure that the following packages are installed as they are required dependencies:
apt-get -y install gnupg wget lsb-release
We download and install the following Debian installer program:
wget http://repo.mysql.com/mysql-apt-config_0.8.13-1_all.deb dpkg -i mysql-apt-config_0.8.13-1_all.deb
In the menu, we can choose between versions mysql-8.0 (default) or mysql-5.7, after selecting the options we want to confirm with 4
[email protected]:/# dpkg -i mysql-apt-config_0.8.13-1_all.deb Selecting previously unselected package mysql-apt-config. (Reading database ... 8257 files and directories currently installed.) Preparing to unpack mysql-apt-config_0.8.13-1_all.deb ... Unpacking mysql-apt-config (0.8.13-1) ... Setting up mysql-apt-config (0.8.13-1) ... debconf: unable to initialize frontend: Dialog debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.) debconf: falling back to frontend: Readline debconf: unable to initialize frontend: Readline debconf: (Can't locate Term/ReadLine.pm in @INC (you may need to install the Term::ReadLine module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.28.1 /usr/local/share/perl/5.28.1 /usr/lib/x86_64-linux-gnu/perl5/5.28 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.28 /usr/share/perl/5.28 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base) at /usr/share/perl5/Debconf/FrontEnd/Readline.pm line 7.) debconf: falling back to frontend: Teletype Configuring mysql-apt-config ---------------------------- MySQL APT Repo features MySQL Server along with a variety of MySQL components. You may select the appropriate product to choose the version that you wish to receive. Once you are satisfied with the configuration then select last option 'Ok' to save the configuration, then run 'apt-get update' to load package list. Advanced users can always change the configurations later, depending on their own needs. 1. MySQL Server & Cluster (Currently selected: mysql-8.0) 2. MySQL Tools & Connectors (Currently selected: Enabled) 3. MySQL Preview Packages (Currently selected: Disabled) 4. Ok Which MySQL product do you wish to configure? 4 Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config) OK
Ubuntu and Debian
Once the repository is added to Debian, the last part is the same for both systems
apt-get update
apt-get install mysql-server mysql-client
On Debian, the installer asks for the password of the root database user, and when installing version 8.0, it asks for an authentication method, which can be used to select a new method Use strong password encryption or the old one with Use a legacy authentication method
Configuring mysql-community-server ---------------------------------- Please provide a strong password that will be set for the root account of your MySQL database. Leave it blank to enable password less login using UNIX socket based authentication. Enter root password: MySQL 8 uses a new authentication based on improved SHA256-based password methods. It is recommended that all new MySQL Server installations use this method going forward. This new authentication plugin requires new versions of connectors and clients, with support for this new authentication method (caching_sha2_password). Currently MySQL 8 Connectors and community drivers built with libmysqlclient21 support this new method. Clients built with older versions of libmysqlclient may not be able to connect to the new server. To retain compatibility with older client software, the default authentication plugin can be set to the legacy value (mysql_native_password) This should only be done if required third-party software has not been updated to work with the new authentication method. The change will be written to the file /etc/mysql/mysql.conf.d/default-auth-override.cnf After installation, the default can be changed by setting the default_authentication_plugin server setting. 1. Use Strong Password Encryption (RECOMMENDED) 2. Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) Select default authentication plugin 1 update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode Setting up mysql-server (8.0.19-1debian10) ... Processing triggers for libc-bin (2.28-10) ...
We can then adjust the configuration we deem necessary from the appropriate configuration files.
Installing MySQL on Windows Server
To install it on Microsoft Windows, we first need to ensure that some of them are respected Claimincluding installed Microsoft Visual C++ 2019 Redistributable Package and then we can download the MySQL installer with here Once the ZIP file is downloaded, let’s unzip it and run the file: mysql-installer-community-X.XX.XX
The installation is typical for any Windows application and almost all of them can be passed without having to configure anything, except below where we have to choose the type of installation, in our case we just choose Servers only:
From there, the other screens are Next, Next, and Next, until the moment the password is requested. We enter the password for the root user and then Next
Other screens offer the possibility to change other parameters, although those selected by default are sufficient, if necessary we can adjust them later.
Conclusion
Despite the ups and downs the MySQL project has experienced over the years, it is one of the most popular database engines.
Although it is not perfect, especially in highly competitive systems, it is one of the easiest engines to work with, is available for a large number of platforms, and since it is open source, it is cheaper than other similar solutions.
The latter has made it the default database engine on the web and a huge favorite of millions of developers around the world.
