Tech News, Tech & Coding · Free Online Tools

IT Question Answer
Tools
Back to What Is
What is MySQL and How Does it Work?

What is MySQL and How Does it Work?

What IsMySQLBy Muhammad Fareed
mysql

Short answer: MySQL is a free, open-source relational database management system (RDBMS). It stores data in tables made of rows and columns and is queried with SQL (Structured Query Language). It powers a huge share of websites and applications, including many WordPress and PHP sites.

How MySQL works

MySQL runs as a server program. Applications connect to it over the network (default port 3306), send SQL statements, and receive results.

  1. A client such as your app or the mysql command-line tool connects and authenticates.
  2. The server parses the SQL and the query optimizer chooses an execution plan, using indexes when possible.
  3. A storage engine reads or writes the data on disk. The default and most used engine is InnoDB, which supports transactions, row-level locking and foreign keys.
  4. The result goes back to the client.

Tables, rows and columns

CREATE DATABASE shop;
USE shop;

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Each column has a data type and rules (constraints). The primary key uniquely identifies each row.

Basic SQL (CRUD)

-- Create
INSERT INTO customers (name, email) VALUES ('Asha', 'asha@example.com');

-- Read
SELECT id, name FROM customers WHERE email LIKE '%@example.com' ORDER BY name LIMIT 10;

-- Update
UPDATE customers SET name = 'Asha K' WHERE id = 1;

-- Delete
DELETE FROM customers WHERE id = 1;

Always include a WHERE clause with UPDATE and DELETE, or every row will change.

Relationships and joins

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Learn all join types in MySQL joins explained.

Common data types

CategoryTypes
NumbersINT, BIGINT, DECIMAL, FLOAT
TextVARCHAR, TEXT, CHAR
Date and timeDATE, DATETIME, TIMESTAMP
OtherBOOLEAN, JSON, ENUM, BLOB

Use DECIMAL for money, not FLOAT.

Transactions and ACID

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- or ROLLBACK; to cancel both

InnoDB follows ACID rules so either all steps succeed or none do.

Performance basics

Installing MySQL

  1. Download MySQL Community Server from mysql.com, or install with a package manager (brew install mysql, sudo apt install mysql-server).
  2. Secure it with mysql_secure_installation and set a strong root password.
  3. Connect with mysql -u root -p.
  4. Create a dedicated user for each application instead of using root.
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'appuser'@'localhost';

Where MySQL fits

MySQL is a strong choice for structured, relational data such as users, orders and content. Compare options in SQL vs NoSQL, and design tables well with normalization.

Storage engines: InnoDB and others

MySQL separates the SQL layer, which parses and plans queries, from storage engines, which store and retrieve data. InnoDB is the default and the right choice for almost every table: it supports transactions, row-level locking, crash recovery and foreign keys. MyISAM, the older default, lacks transactions and uses table-level locks, so it is rarely used in new projects. Other engines, such as MEMORY for temporary data, exist for special cases. You can check a table's engine with SHOW TABLE STATUS.

Users, permissions and security basics

CREATE USER 'app_user'@'%' IDENTIFIED BY 'use-a-strong-generated-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_user'@'%';
SHOW GRANTS FOR 'app_user'@'%';

Give each application its own user with only the privileges it needs, rather than connecting as root. Restrict which hosts can connect, keep MySQL off the public internet when possible, require TLS for remote connections, and keep the server updated. Always use parameterised queries in your code to avoid SQL injection.

Backups and replication

For small databases, mysqldump creates a logical backup as SQL statements that can be restored on another server. Larger systems often use physical backup tools or managed cloud backups with point-in-time recovery, which rely on MySQL's binary log of changes. Replication copies changes from a primary server to replicas, which can serve read traffic or take over if the primary fails. Whatever method you choose, test restoring backups regularly; an untested backup is not a real backup.

Common mistakes

  • Using the utf8 character set instead of utf8mb4, which cannot store some characters such as emoji.
  • Missing indexes on columns used in joins and WHERE clauses.
  • Storing money in FLOAT instead of DECIMAL, causing rounding errors.
  • Running the application as the root user.
  • Never testing backup restores.

FAQs

Is MySQL free?

The Community Edition is free and open source. Oracle also sells commercial editions with extra features and support.

What is the difference between MySQL and SQL?

SQL is the language. MySQL is a database system that uses SQL.

MySQL vs PostgreSQL?

Both are excellent. MySQL is known for simplicity and wide hosting support; PostgreSQL for advanced features and standards compliance.

What is MariaDB?

A community fork of MySQL that is largely compatible with it.

Which GUI tools work with MySQL?

MySQL Workbench is the official free tool. DBeaver, phpMyAdmin, HeidiSQL, TablePlus and many IDE database plugins also work well. Use whichever lets you browse data, run queries and view execution plans comfortably. Learn more about safe transactions in ACID properties and procedures in the stored procedures tutorial.