MySQL: Open Source Database for Web Apps

What Is MySQL and What Is It For

MySQL is the most popular relational database management system in the world, based (as you might guess) on the SQL language (Structured Query Language).

It's a solution that lets you store and manage data efficiently, widely appreciated for being open source and compatible with all major operating systems.

To really understand how MySQL works, you need to understand the characteristics of a database, which is essentially a structured collection of data organized so it can be easily retrieved and used.

On a WordPress site, for example, this data isn't limited to the text content of articles — it also includes setting configurations, auto-loaded data, and more.

The History of MySQL

MySQL is an application that was first released in the mid-1990s and has been part of Oracle Corporation since 2009.

Today, LAMP platforms (which we covered in this article) and WAMP platforms both incorporate MySQL to run servers for dynamic websites.

In addition, many popular CMS platforms such as WordPress, Joomla, and Drupal were built with default support for MySQL; in any case, we're talking about open source software, which means it can be used and modified without restrictions.

The approach this application uses for storing information in a database is known as a "relational database."

In practice, data is split across different storage areas, called tables, rather than being collected in one large storage unit.

If you were to group a large amount of data into a single container, you'd end up facing several potential drawbacks: the risk of duplicate data, for example, as well as a lack of organization.

That's exactly the kind of problem a relational database tries to solve!

Why MySQL Is Called a "Relational" Database

A database is called "relational" because it uses a key that allows data across tables to be linked, so information can be (when needed) combined and manipulated across multiple tables.

A key can be compared to a numeric ID: a unique entity that isn't repeated.

WordPress sites rely on this relational model, but MySQL is also built on a client-server model: to get the data it needs, the client sends a specific request to the database server.

PHP, Perl, and Python

What are PHP, Perl, and Python, and how do they work? That's a topic for another article...

Basic Administration Commands

Beyond the theory, day-to-day work with MySQL comes down to a handful of commands used constantly. To connect to the server from a terminal:

mysql -u root -p

Once connected, these are the commands you'll reach for most to explore what's on the server:

SHOW DATABASES;
USE nome_database;
SHOW TABLES;
DESCRIBE nome_tabella;

Creating a new database together with a dedicated user — rather than having every application connect as root — is standard practice:

CREATE DATABASE app_produzione;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password_sicura';
GRANT ALL PRIVILEGES ON app_produzione.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

Limiting each account to only the database (and the privileges) it actually needs is one of the simplest ways to contain the damage if a single application is ever compromised.

Backup and Restore with mysqldump

mysqldump is the standard tool for logical backups of a MySQL database. To back up a single database:

mysqldump -u root -p nome_database > backup.sql

To back up every database on the instance in one file:

mysqldump -u root -p --all-databases > full_backup.sql

Restoring is the same command in reverse:

mysql -u root -p nome_database < backup.sql

For anything beyond a small, low-write database, this logical export should be scheduled (typically via cron) and paired with the regular backup of the rest of the server — a .sql dump sitting on the same disk as the database it protects isn't a backup, it's a copy.

Errors You'll Actually Run Into

A handful of errors come up often enough in real environments to be worth knowing in advance:

• ERROR 2002 (HY000): Can't connect to local MySQL server through socket — the MySQL/MariaDB service isn't running, or the application is pointing at the wrong socket path. Check the service status before touching the configuration.
• ERROR 1045 (28000): Access denied for user — wrong username, password or host in the grant (a user created for 'app_user'@'localhost' won't be able to connect from a different host).
• "Too many connections" — the max_connections limit in my.cnf has been reached, usually because an application isn't closing its connections properly rather than because traffic genuinely grew. Raising the limit is a short-term patch; finding the connection leak is the actual fix.
• InnoDB corruption after an unclean shutdown — this is the case we see most often on client servers, and it's rarely a MySQL bug: it follows a power outage or a RAID failure that cut power to the disks mid-write. On restart, InnoDB normally replays its redo log automatically; when the tablespace itself is damaged and recovery fails, the practical fix is starting the instance with innodb_force_recovery set to an increasing value (1 to 6) in my.cnf, exporting the data with mysqldump in that degraded mode, and reimporting it into a fresh instance. It's also the clearest argument for a UPS on any server running a database, and for backups that don't live on the same array as the data.
• Disk full (Error writing file ... Errcode: 28 - No space left on device) — binary logs and slow query logs left without rotation are a common cause; check /var/log/mysql and the binlog directory before assuming it's the data itself that grew.

A Few Notes on Tuning

On a server dedicated to MySQL, innodb_buffer_pool_size — the amount of RAM InnoDB uses to cache data and indexes — is usually the single setting with the biggest impact on performance, and is typically set to roughly 60-70% of available RAM. Enabling the slow query log (slow_query_log = 1, with a long_query_time threshold in seconds) is the fastest way to find out which specific queries are actually responsible for a sluggish application, instead of guessing.

MySQL or MariaDB: Which One to Choose

MariaDB is a fork of MySQL created in 2009 by part of the original MySQL development team, right after Oracle's acquisition of MySQL, and it remains a drop-in replacement in the large majority of cases: the same mysql client commands, the same SQL dialect, the same file formats for InnoDB. In practice, most current Linux distributions (Debian, Ubuntu) ship MariaDB by default under the mysql-* package and command names. TN Solutions works with both engines; the choice usually comes down to what a given CMS or application officially certifies (WordPress and most PHP applications work equally well with either) and whether the client's existing infrastructure already standardised on one of the two.

MySQL Support, Assistance, and Maintenance

Your Solution Is Just a Click Away

Are you about to install MySQL, or dealing with a different kind of issue and need IT support? Whether you're an individual or run a business, contact us with no obligation!

TN Solutions offers tailored solutions for any kind of technical or IT need or consulting request, whether it's software, hardware, systems administration, servers, and more.