Getting a MySQL Server Running and Usable From Scratch

The first hour with a new MySQL server decides a lot of what follows. A server installed carelessly, with a root account that anyone on the network can try, a data directory nobody can find and an application that connects as root, will cause trouble months later, and it will be harder to fix then. The same hour spent well gives a server that is secure by default, documented in a few lines and easy for the next person to understand. This guide walks through that hour: choosing a version, installing, securing, creating accounts, connecting, finding the files and meeting the common errors. It links to the detailed articles in the series for each step.

Decide what you are building

Before installing anything, answer four questions. Is this a development machine, a shared test server or production? A development database can live in a container and be thrown away, while production needs backups, monitoring and access control from the first day. Who needs to connect, and from where? A single application on the same host is a very different case from several services and analysts across a network. How much data and traffic do you expect? A few gigabytes with light traffic runs on a small virtual machine, and hundreds of gigabytes needs thought about memory and storage. And who will run it? If nobody on the team wants to be responsible for patching, backups and recovery, a managed cloud service may be the better start, with the trade-offs in running PostgreSQL on managed services, which applies in spirit to MySQL services as well. Write the answers in a sentence or two; they will guide the choices below.

Choose the version

For a new project, install MySQL 8.4, the current long term support release. It receives fixes for years without feature changes, which is what a database should do. Innovation releases such as 9.x offer new features sooner and need upgrades every few months, a choice for teams with a testing pipeline. MySQL 8.0 is at or near the end of its supported life and should not host new work. Managed services decide which versions they offer, so check the provider's list and its retirement dates before choosing. MariaDB is a separate product with differences that this series does not assume. The reasoning and the support calendar are in which MySQL version to choose for a new project.

Install it

There are two good routes. A native package installs the server as a service on the host, managed by the operating system. On macOS use Homebrew or the Oracle installer, on Ubuntu and Debian the MySQL APT repository, on Red Hat family systems the MySQL Yum repository, on Windows the MSI installer. Each puts the data in a conventional directory and registers the service, and the platform differences are covered in installing MySQL 8.4 on macOS, Linux and Windows. The second route is a container: the official mysql:8.4 image with a named volume for the data directory runs identically on every developer's machine and disappears cleanly when you are done, as shown in running MySQL in Docker with a data volume. Containers shine for development and testing. For a server that lives for years, a native install or a managed service is simpler to operate, unless your organization already runs containers well.

Secure it on day one

A fresh server needs five minutes of hardening, and it is much easier now than after an application depends on it. Set a long, generated password for the root account, using ALTER USER 'root'@'localhost' IDENTIFIED BY '...'. Run mysql_secure_installation, which removes anonymous accounts and the test database and disallows remote root login, covered in removing anonymous users and test databases. Keep the server from listening on the public internet: bind to localhost or a private address, and put a firewall in front. If remote clients must connect, require TLS and restrict the accounts by host. Keep the server patched with minor updates. These steps are cheap, and skipping them is how databases end up in breach reports.

Create the database and the accounts

Create the database with utf8mb4 and a clear name. Then create separate accounts for separate jobs: one for the application with SELECT, INSERT, UPDATE, DELETE on its database, one for schema migrations with the data definition privileges, one read-only for reporting, and a personal account for each administrator. Each account is a user and a host pattern, and the host pattern should be as narrow as the network allows. Never let an application use root. The statements, the role feature in MySQL 8 and the verification steps are in creating a database and a least privilege user in MySQL. Keep the statements in version control so that every environment is created the same way, and use generated passwords stored in a secret manager.

Connect with the command line client

The mysql client is the baseline tool. It connects through a Unix socket when the host is localhost and through TCP for any other name, and the two paths match different accounts and fail in different ways. Put repeated settings in an option file or a login path, keep passwords off the command line, request TLS with --ssl-mode when crossing a network and use an SSH tunnel for private servers. The options and the habits are in connecting to MySQL from the command line. Most "cannot connect" problems resolve by reading the error number: 2002 is a socket problem, 2003 a network problem, 1045 a credentials problem.

Make the client safer

Before you spend hours in a production console, configure the client. --safe-updates makes the server refuse UPDATE and DELETE statements without a key-based WHERE, and caps accidental huge selects. A pager keeps long results readable, \G prints wide rows vertically, and a prompt that shows the host and database tells you where you are before you press Enter. Put them in the [mysql] section of your option file, as described in using mysql client options like safe updates and pager. Wrap manual data changes in transactions and check the row count before committing. These habits cost nothing and prevent the incidents that are remembered for years.

Know where things live

Most mysteries about a MySQL server are answered by knowing where its files are. The server tells you: SELECT @@datadir, @@socket, @@log_error. The configuration comes from layered option files, read in a fixed order with later settings winning, plus the mysqld-auto.cnf file written by SET PERSIST. The data directory holds the InnoDB tablespaces, redo and undo logs, one folder per database and the binary logs if enabled. Defaults differ between Debian, Red Hat, macOS, Windows and Docker. Never copy or delete files in the data directory while the server runs. The layout and the order of option files are explained in where MySQL keeps its config and data files.

The three first errors

Three errors bring most newcomers to a search engine. ERROR 1045 Access denied for user 'root'@'localhost' means the server was reached and refused the credentials: check the password, whether root uses socket authentication, which host the server saw and whether the client supports the authentication plugin. The checklist is in fixing access denied for user root at localhost. ERROR 2002 Can't connect to local MySQL server through socket means the client looked for a socket file that is missing or unreachable: check that the server runs, find the real socket path and use TCP with 127.0.0.1 if needed, see fixing can not connect to local MySQL server through socket. And the lost root password, which you reset with an init file as described in resetting a lost MySQL root password. Know these three and you will recover from the majority of first-day problems.

Character sets, time zones and modes

A few server settings deserve a decision early, because changing them later is painful. Use utf8mb4 as the server and database character set, and a collation that matches your comparison needs. Decide how time is stored: set the server time zone deliberately (UTC is the safest default for servers) and choose between DATETIME and TIMESTAMP with the trade-offs in DATETIME versus TIMESTAMP in MySQL. Keep the default strict sql_mode, which makes the server reject invalid data instead of silently truncating it; relaxing it to satisfy a sloppy application only hides bugs. These settings go in the configuration file, under version control, with a comment on why each is set.

Think about backups from the start

A server without a tested backup is a risk that grows with every row. Decide the method early: logical dumps with mysqldump or the MySQL Shell utilities for small and medium databases, physical backups with Percona XtraBackup for large ones, binary logs for point-in-time recovery. Schedule them, send them off the machine, and restore one into a scratch server to prove that it works. The details are in the backup series, starting with MySQL backups you can count on. Even on a development machine, a script that dumps the schema and seed data into the repository saves hours when the volume is lost.

Monitor the basics

Even a small server benefits from a few checks. Watch that it is up and accepting connections, that disk space is not running out, that the error log does not fill with warnings, that the number of connections stays below max_connections and that backups ran. Later you will add query performance, replication lag and buffer pool statistics, covered in the tuning and performance series. A cron job that mails on failure is better than nothing, and a metrics stack is better still. Enable the slow query log with a sensible threshold early, see turning on the slow query log safely, because it costs little and gives you evidence when someone says the database is slow.

A first-hour checklist

When you set up a server, run through a short list. Version chosen and recorded. Installed from a supported source. Root password changed and anonymous accounts gone. The server bound to the right interface, with a firewall. Database created with utf8mb4. Application, migration and reporting accounts created with narrow grants and host patterns. TLS configured for remote connections. Client defaults set for safety. Paths of the configuration, data and logs written down. Time zone and sql_mode set deliberately. Backup job running, and one restore tested. Slow query log on. Monitoring and an alert for disk space and for the service being down. A runbook page with the commands for the three first errors. Keep the checklist in the repository and update it after each setup, and the next server takes ten minutes instead of an afternoon.

Network exposure and firewalls

The most common serious mistake with a new MySQL server is leaving it reachable from places it should not be. The server listens on port 3306, and bind_address decides which interfaces accept connections: 127.0.0.1 means only the local machine, * means all. For a single-host setup, keep it on loopback. For a server shared with other machines, bind to the private address and open the firewall for the specific clients only. Cloud security groups, ufw, firewalld and the Windows firewall all do this, and a rule that allows 0.0.0.0/0 on 3306 is the line to look for in any review. Docker's published ports bypass some host firewalls, so publish to 127.0.0.1 explicitly. If people need access from laptops, give them an SSH tunnel or a VPN, not an open port. Scanners find open MySQL ports within hours, and a weak password on one is enough.

First settings worth changing

The default configuration is conservative. A few settings are worth setting at the start. Set innodb_buffer_pool_size to a value that fits your memory, about half to three quarters of RAM on a dedicated database host, less when the machine runs other services; the reasoning is in how big should innodb_buffer_pool_size be. Set max_connections to a number you can afford in memory, not an arbitrary large value. Turn on the slow query log. Set character_set_server to utf8mb4. Set the time zone. Enable binary logging if you will need point-in-time recovery or replication, and set an expiry for old binary logs, otherwise they fill the disk. Leave the rest alone until you have measurements, and write each change with its reason as a comment in the configuration file.

Time zones and clocks

Time handling trips up many beginners. MySQL stores TIMESTAMP values in UTC and converts them to the session time zone on the way in and out, while DATETIME stores what you give it with no conversion. The server has a global time zone setting, and each session can override it, so the same stored value can display differently to two clients. Set the server to UTC, load the time zone tables if you want named zones (mysql_tzinfo_to_sql on Linux), and have applications set the session zone explicitly. Keep the operating system clock synchronized with NTP, since replication, certificates and log analysis depend on it. The choice of column type has consequences for ranges and conversions, covered in DATETIME versus TIMESTAMP in MySQL.

Patching and upgrades

Treat updates as routine work. Minor releases, such as 8.4.2 to 8.4.3, contain security and bug fixes and are safe to apply with a restart. Subscribe to Oracle's security advisories, which come quarterly, and apply them within a defined time. Package managers do most of the work, and containers need a new image tag. Test the update on a copy first if the server is important, and take a backup before. Major changes, such as the move to the next LTS release, need more care: read the upgrade notes, test the application, plan the downtime and keep a rollback path. Replicas are upgraded before the source. A calendar entry for the end-of-support date of your version prevents the surprise of running unpatched software. The version policy is explained in which MySQL version to choose for a new project.

Logging that helps

Logs are how you learn what a server did. The error log is on by default and is the first place to look for any startup problem or crash. The slow query log, off by default, records statements slower than long_query_time; a threshold of one second or less with log_slow_extra gives useful detail. The general log records every statement and is only for short debugging, since it is large and can contain passwords. The binary log records changes for recovery and replication. Rotate logs with logrotate or the server's own options, watch their size, and protect them, as they can contain sensitive data. Where logs go in containers, on managed services and under systemd differs, so find out early.

Development versus production

A development database and a production database should be as similar as possible in version, character set, SQL mode and schema, and as different as possible in size, access and data. Developers need a quick way to create a fresh copy: a container, a dump of the schema and generated sample data, not a copy of production data, which brings privacy risk. Production needs access control, backups, monitoring and a change process. Keep the configuration files in the same repository and differ only in a small set of values. A common failure is the application that works on a laptop with permissive settings and fails in production with strict ones; run the test suite against a server with the production sql_mode and character set.

Frameworks and drivers

Applications reach MySQL through drivers and frameworks that bring their own defaults. Check the connection string options: the character set, the time zone, whether TLS is requested, the connection pool size and the timeouts. Check that the driver supports the authentication plugin your server uses; recent versions of the mainstream drivers support caching_sha2_password. Set the pool size from the server's connection budget and not from the framework's default, and set a connection timeout so that an unavailable server fails fast. Prepared statements and parameter binding in the driver, not string concatenation, are the first defense against injection, covered in preventing SQL injection in MySQL applications. Test failover behavior if you run replicas: does the application reconnect after the server restarts?

A troubleshooting flow

When something does not work, move from the outside in. Is the host reachable (ping, DNS)? Is the port open (nc -vz host 3306)? Is the server running (systemctl status, mysqladmin ping)? Does the error log show a problem? Can you connect locally over the socket? Over TCP? With which account? What do SHOW GRANTS and mysql.user say? Is the plugin compatible? Is TLS required and configured? Each question narrows the layer, and the error number says where to begin: 2002 and 2003 are connectivity, 1045 is authentication, 1044 and 1142 are privileges, 1049 is an unknown database. Write the flow in your runbook with the commands for your environment. A calm checklist beats a panicked guess at 3 a.m.

Ownership and documentation

A database server needs an owner: a named person or team who decides on upgrades, grants access, watches alerts and answers questions. Write down in one page where the server lives, how it was installed, which version it runs, where the configuration is, who has access, how backups work and where, how to restore and how to reach the owner. Review the page twice a year. The most useful documentation is short and exact: commands that were tested, not descriptions of intentions. When someone leaves the team, the page and the secret manager are what keep the server operable. Treat undocumented servers as a risk to be fixed, whatever their age.

Storage and hardware basics

MySQL is more sensitive to disk than to almost anything else. Use SSD or NVMe storage for the data directory, and keep the redo log and binary logs on storage with predictable latency. A busy server on a slow disk spends its time waiting, and no setting repairs that. Leave headroom: a data volume that is 90 percent full leaves no space for index builds, temporary tables or ALTER TABLE operations that copy a table. Memory comes next, since a buffer pool that holds the working set turns most reads into memory hits. CPU matters for concurrent queries, and a few fast cores serve many small workloads better than many slow ones. In virtual machines and clouds, check the disk's IOPS and throughput limits, because credits and burst allowances can run out under sustained load. Plan growth: measure the size of the data directory monthly, and extend storage well before it fills.

A practical day-two list

After the first hour, the work continues for a few days. Add a monitoring agent or a metrics exporter, and dashboards for connections, queries per second, the buffer pool and replication. Set alerts for disk space, service availability and failed backups. Write a runbook entry for how to restore. Run a restore into a scratch server and time it. Review the user list and the grants. Check the slow query log after a day of real traffic and fix the worst query. Look at the error log for warnings that appear every minute and silence them by fixing the cause. Put the configuration in version control. Schedule the next review in a month. Small servers that receive this attention in the first week tend to stay healthy for years, and those that do not tend to be rescued in a hurry after an incident.

When a managed service is the better start

If your team lacks the time or the inclination to run a database, a managed MySQL service removes the installation, patching, backups and failover from your list and adds a bill. You still do the account design, the schema, the queries and the monitoring, and you lose some control: no host access, a restricted set of settings and privileges, and a provider-set upgrade schedule. For a small team building a product, that trade is usually right. For a team with operations experience and specific needs, a self-run server is fine. The decision is reversible with a dump and restore, so it need not be agonized over; pick one, document it and review in a year.

Where to go from here

The articles in this series cover each step: installing MySQL 8.4 on macOS, Linux and Windows, running MySQL in Docker with a data volume, creating a database and a least privilege user in MySQL, connecting to MySQL from the command line, fixing access denied for user root at localhost, fixing can not connect to local MySQL server through socket, resetting a lost MySQL root password, where MySQL keeps its config and data files, which MySQL version to choose for a new project and using mysql client options like safe updates and pager. The authority is the MySQL 8.4 reference manual and the installing and upgrading chapter, and the next topics are data types in picking MySQL data types with care and query writing in writing MySQL 8 queries that use modern features.