MySQL in practice
MySQL is an open-source relational database management system that stores data in tables of rows and columns and links them with keys.
It is the database layer of the LAMP stack, where the 4 main components are Linux, Apache, MySQL and PHP. Since its open-sourcing in 2000, MySQL has served as the default backend for web applications, and the MySQL 8.0 line added JSON support and a CTE engine on top of the InnoDB engine. In this practice, a single MySQL instance can carry 1,000 concurrent connections on modest hardware, and the 3306 TCP port is where the server listens by default.
This page works MySQL the way a practitioner uses it on a Linux host: what it is, how it sits inside a server-side web stack, and the commands and decisions that show up in real operations. For the surrounding configuration and hardening work, see Server-side web stacks and the Linux systems. When the schema is growing past what a single engine handles, the decision tree in Choosing between MySQL and PostgreSQL decides the direction. And for the day-to-day object work that most people do here, the MySQL Workbench walkthrough covers the visual side of the same job.
What MySQL actually is
MySQL is a client-server database in which the server, the mysqld process, holds the data and a client sends SQL over a socket or a network connection to read and write it. It is relational in the strict sense: every table is a set of rows, every row a set of columns, and a primary key plus foreign keys make the tables addressable and consistent. A web application such as a blog, a shop or a booking system maps cleanly onto this shape, and that mapping is why MySQL sits under so much of the web.
The server exposes the database through SQL, the query language, and through the storage engine it runs underneath. Since 5.5, InnoDB is the default engine, and it brings 3 properties that define modern MySQL: transactional atomicity, row-level locking, and foreign key enforcement. The older MyISAM engine is still shipped for read-heavy logs, but it has no transactions and locks whole tables, which is why a new schema should be built on InnoDB from the start. The database the server manages, the set of tables and indexes inside a schema, and the engine that stores them on disk are three distinct layers, and keeping those layers straight is the first step in working with MySQL without surprises.
How MySQL fits the server-side web stack
To see where MySQL belongs, place it in the LAMP stack: Apache (or a similar web server) hands a request to PHP, and PHP talks to MySQL over a connector to fetch or write the rows the page needs. In that chain, MySQL is the persistence layer, the part that remembers. The web server and the language layer are stateless, so the database is the only component that must survive a restart with its data intact, which is the whole reason the persistence layer gets its own server and its own disk policy.
On a production box the split usually runs like this: the web server and the PHP runtime sit on the front, MySQL runs on the same host or on a dedicated database host, and the two talk over localhost or a private network. If traffic grows, the first move is almost always to separate the database onto its own machine so that queries stop competing with page rendering for CPU and memory. That separation is the seam that turns a single LAMP box into a small stack, and it is the seam most server-side web stacks and the Linux systems work eventually cross.
Three connector paths show up in practice, and choosing one depends on the language on the front:
- PHP talks to MySQL through the PDO or mysqli extension, which wraps a native MySQL client library.
- Java applications use a JDBC connector, typically the MySQL Connector/J, to open the same 3306 connection.
- Node.js and other runtimes reach the database through a driver such as the mysql2 package, and each of these speaks the MySQL protocol to the server.
The connector is thin, and the work happens on the server, so a slow query is a server problem even when the client is on a different machine. That is why the optimizer, the part of mysqld that chooses which indexes to read and in what order, is where most performance work lands.
How to create and work with a database
To create a database in MySQL, run CREATE DATABASE with a name, then switch to it and build the tables. The sequence a practitioner repeats is short, and it is the shape of almost every schema that starts on a fresh server. First, start a client session, then declare the database, and only then write the tables that live inside it.
The commands that matter on a daily basis are a small set, and each one has a single job:
- CREATE DATABASE names a new schema and sets its character set.
- USE selects which schema the following statements target.
- CREATE TABLE defines the columns, the types, and the primary key.
- INSERT writes new rows, and SELECT reads them back with a WHERE clause.
- ALTER TABLE adds, removes, or changes a column after the table exists.
A working sales schema shows the shape. A customers table holds an id, a name, and a created date; an orders table holds an id, a customer id as a foreign key, a total, and a timestamp; and an order_items table joins the two with a line price and a quantity. A sample sales database like that is a good first exercise because it forces the full set of relationships: a customer has many orders, and an order has many items. Building it by hand, rather than importing a dump, is what makes the table layout stick. When the data model is this small, the optimizer has little to decide, and the queries read fast for the simple reason that the indexes line up with the joins.
MySQL is free to download and to run, and the community edition is the build that most projects ship. The cost model is where the licensing question lands, and it is worth stating plainly. The Community Server is distributed under the GNU General Public License and is free, while the commercial editions, such as the Standard and Enterprise editions, are paid and add features like the query performance schema and support contracts. A startup that wants zero database license cost runs the community edition, and a team that wants a vendor to pick up the phone runs a commercial one. The installer for Windows bundles both, and the download for Linux is the same community server that the LAMP stack uses.
Advantages and disadvantages
The advantages of MySQL are the reasons it won the default slot in server-side web development, and the disadvantages are the reasons it loses that slot in specific workloads. Listing them side by side makes the trade clear.
| Aspect | Advantage | Disadvantage |
|---|---|---|
| Engine | InnoDB gives transactions and row locking | Whole-table scans on a missing index are slow |
| Scale | Reads scale well behind a cache | Writes on a single node need a replica to grow |
| Cost | The community edition is free and easy to install | Commercial support costs money |
| Ecosystem | Connectors exist for every major language | Some advanced features sit behind the paid editions |
| Operations | Mature tooling and a large community | Upgrades between major versions need testing |
The pattern in that table is that MySQL is strong at the common case and expensive at the edge. A high-read, moderate-write application, which is the shape of most web backends, sits exactly in the strong zone. A workload that writes heavily on a single node, or that needs features only in the commercial edition, hits the edge, and that is when the comparison to a competitor stops being academic.
MySQL versus Microsoft SQL Server and PostgreSQL
Choosing between MySQL and PostgreSQL is a decision about the workload, not about which engine is better in the abstract. MySQL is the default for a LAMP stack and for teams that want the simplest path from install to running database, while PostgreSQL is the choice when the schema needs richer data types, full-text search, or stricter standards compliance. If the front is PHP and the requirement is a fast, free, well-documented backend, MySQL is the shorter path, and that comparison is the one that settles the most LAMP questions.
Microsoft SQL Server is a different category, because it is a commercial, Windows-rooted product. It runs on Windows and, since 2017, also on Linux, but it ships with a license cost and a tooling world that centers on SQL Server Management Studio rather than a command line. The practical split is that a shop already on Microsoft products, with the budget to match, often standardizes on SQL Server, and a shop running a Linux LAMP stack usually stays on MySQL to avoid the license and to keep the tooling in the same place. That is the real answer to the mysql vs microsoft sql server question: it is an ecosystem and a budget decision before it is a capability decision.
It is also worth separating the two terms that get blurred in search, SQL and MySQL. SQL is the query language, the standard syntax for selecting and writing rows, and MySQL is one of many products that implement it. A query that runs in MySQL, such as a SELECT with a JOIN, runs with the same shape in PostgreSQL, in SQLite, and in SQL Server, because they all speak SQL. Confusing the language for the product is the most common beginner error here, and the fix is to treat SQL as the what and MySQL as the where.
Backing up a MySQL database
To back up a MySQL database, use mysqldump to write a logical copy and keep it separate from the live server. A logical dump is a file of SQL statements that rebuild the schema and the rows, and it is portable across versions and across machines. The command that matters is mysqldump, run with the schema name, redirected to a file, and then tested by loading it into a clean database before it is trusted.
Three rules keep a backup from being a false comfort:
- Dump on a schedule, and verify that the last dump completed and is a file of the expected size.
- Store the dump off the database host, because the disk that fails is the one the backup is sitting on.
- Rehearse a restore at least once a quarter, because an untested backup is a guess about the future.
For a busy production database, a logical dump can take long enough that the rows change while it runs, so the dump is taken with a flag that locks the tables for the read or that uses the engine's snapshot. The goal is a consistent point in time, and InnoDB's transaction log is what makes that snapshot possible. A backup strategy is only as good as the restore that proves it, and the restore is the drill that should be on the operations calendar next to the upgrade test.
Worked that way, MySQL is the piece of the server-side stack that the whole page has been building toward: a relational database that takes the schema a web application needs, stores it in InnoDB, answers the queries the front sends over a connector, and stays recoverable because someone dumps it, moves it, and proves they can bring it back. Every command above, from CREATE DATABASE to the restore rehearsal, is the same job done at a different scale, and that is the shape of MySQL in practice.