Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 75 ยท Getting Started with MySQL

Installing MySQL and Connecting

Install MySQL 8.4 with an installer or Docker, connect with the mysql client, MySQL Shell or Workbench, and understand users, hosts and connection options.

To follow the rest of this tutorial against a real server, you need three things: a running MySQL server, a client to talk to it, and an account that is allowed to connect. This lesson gets all three working. The commands here are read-only on this site, because a browser cannot start a server, so run them in your own terminal. Two small live examples at the end model how accounts and connection options work.

Ways to get a server

Pick one. They all give you the same mysqld.

The official installer or package. On Windows and macOS, the MySQL Community Server installer sets up the server as a background service. On Debian and Ubuntu you can use the distribution package or MySQL's own APT repository. Choose 8.4 LTS unless you have a reason not to. The installer asks for a password for the root account. Keep it safe, but do not use root for applications.

Docker. For a throwaway development server this is usually the quickest, and it keeps MySQL off your machine when you are done:

Terminal
docker run --name mysql-lab -d \
  -e MYSQL_ROOT_PASSWORD=change-me-now \
  -e MYSQL_DATABASE=lab \
  -p 3306:3306 \
  -v mysql-lab-data:/var/lib/mysql \
  mysql:8.4

The -v option keeps the data in a Docker volume so it survives container restarts. Without it, removing the container deletes your tables. The first start takes a few seconds while the server initializes the data directory. Watch it with docker logs mysql-lab until you see a line saying the server is ready for connections.

A bundled local stack. Tools such as Laravel Herd with DBngin, XAMPP or MAMP include MySQL or MariaDB. They are fine for learning, but check the version, because bundled copies are sometimes MariaDB or an older 8.0.

A managed service. Cloud providers sell MySQL as a service. You get a host name, a port and credentials, and connect with the same tools. Nothing else in this lesson changes.

The three main clients

Client What it is Best for
mysql The classic command-line client, installed with the server Quick queries, scripts, servers without a GUI
MySQL Shell (mysqlsh) A newer shell with SQL, JavaScript and Python modes Admin utilities, dump and load, InnoDB Cluster
MySQL Workbench A desktop application Browsing schemas, diagrams, long queries

You only need mysql for this tutorial. The next lesson, Using the mysql Command-Line Client, explores it in detail.

Connecting for the first time

If the server runs on your machine, connect as root:

Terminal
mysql -u root -p

-u names the user and -p with nothing after it makes the client prompt for the password, so it never appears in your shell history. Typing -pSecret directly works, but it exposes the password to anyone who can list processes.

With Docker, you can run the client inside the container instead of installing it:

Terminal
docker exec -it mysql-lab mysql -u root -p

A successful login prints a banner and gives you a mysql> prompt:

TEXT
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.3 MySQL Community Server - GPL

mysql>

The connection id identifies this session on the server. You will meet it again when you look for slow or stuck queries.

Connection options you will use often

Option Meaning
-h, --host Server host name or IP. The default is localhost.
-P, --port TCP port, default 3306. Capital P, unlike the password flag.
-u, --user Account name.
-p, --password Prompt for the password.
-D, --database Select a default database at login.
--protocol TCP, SOCKET, PIPE or MEMORY.
--ssl-mode PREFERRED by default. Use REQUIRED or VERIFY_IDENTITY for remote servers.

One classic surprise: with -h localhost, the client uses a Unix socket file, not TCP, so a server running on another port or inside Docker may seem unreachable. Use -h 127.0.0.1 to force TCP:

Terminal
mysql -h 127.0.0.1 -P 3306 -u root -p lab

Instead of retyping options, you can keep them in ~/.my.cnf. Protect that file with chmod 600, because it holds a password:

INI
[client]
user = lab_app
host = 127.0.0.1
database = lab

Leave the password out of the file, and the client keeps asking for it. A tool called mysql_config_editor can store credentials in an obscured login path if you prefer.

Users and hosts

A MySQL account is not just a name. It is a user name plus a host, written 'user'@'host'. The host part says where that user is allowed to connect from.

  • 'app'@'localhost' can connect only from the same machine.
  • 'app'@'10.0.0.%' can connect from any address in that range. % is a wildcard.
  • 'app'@'%' can connect from anywhere.

These are three different accounts, even though the name is the same, and each has its own password and privileges. When you connect, the server picks the most specific matching account, and that one decides what you may do.

Create a non-root account and a database for practice. CREATE USER and GRANT are covered fully in Users, Privileges and Roles; for now copy them as they are:

SQL
CREATE DATABASE IF NOT EXISTS lab CHARACTER SET utf8mb4;
CREATE USER 'lab_app'@'%' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON lab.* TO 'lab_app'@'%';

ALL PRIVILEGES ON lab.* gives full control of the lab database and nothing else. That is a good habit: every application gets an account that can touch only its own data.

Two important checks after logging in:

SQL
SELECT CURRENT_USER(), USER(), DATABASE();

USER() shows what you typed, and CURRENT_USER() shows the account the server actually matched, which is the one whose privileges apply. They can differ when a wildcard host matches.

Do not expose port 3306 to the internet. If you must connect from another machine, restrict the host part of the account, use TLS, and put a firewall or private network in front of the server. Securing MySQL covers this properly.

Live model 1: which account does a connection match?

Real MySQL stores accounts in system tables and sorts them so the most specific host wins. This live stand-in uses an ordinary table to show the idea. Hosts without a wildcard are more specific than hosts with one. The query picks, for a connection from 10.0.0.7, the best match:

SQL Stand-in: pick the most specific matching host

Press Run (or Ctrl + Enter) to see the result here.

The result is the row for 10.0.0.7, "exactly this machine". Remove that insert and the 10.0.0.% row would win instead. The stand-in uses LIKE, which treats % as a wildcard just like a MySQL host pattern. The real server's sorting rules are more detailed, but the principle is the same.

Live model 2: building the command line

Connection mistakes are mostly typos in options. This second stand-in keeps options in a table and assembles the command, so you can see which pieces are required and which have defaults:

SQL Stand-in: assemble a mysql command from options

Press Run (or Ctrl + Enter) to see the result here.

The result is mysql -h 127.0.0.1 -u lab_app -p -D lab. The port row is skipped because 3306 is the default. The -p flag has no value, which is why it prompts. In MySQL itself you would use CONCAT_WS(' ', ...) and GROUP_CONCAT(... SEPARATOR ' '); SQLite spells those with || and a separator argument.

If the connection fails

The error code tells you where to look:

  • ERROR 2002 (HY000): Can't connect to local MySQL server through socket means the server is not running, or the socket path is different. Start the service and check the path.
  • ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' means nothing is listening on that port, or a firewall blocks it.
  • ERROR 1045 (28000): Access denied for user means the server was reached, but the user, host or password does not match.
  • ERROR 1130 (HY000): Host is not allowed to connect means no account exists for your host.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does -p with no value at the end of mysql -u root -p matter?

    -p alone prompts for the password. Writing it after -p on the command line can expose it to process lists and history. The port flag is capital -P.
  2. 2. You run mysql -h localhost against a Docker container published on port 3307, and it cannot connect. What is a likely cause?

    With the host name localhost the client uses a Unix socket by default. Use -h 127.0.0.1 and -P 3307 to force TCP to the published port.
  3. 3. Which statement about 'app'@'localhost' and 'app'@'%' is true?

    An account is a user name plus a host pattern. The server picks the most specific match for each connection.
  4. 4. Which error number means the server answered but rejected the credentials?

    1045 is Access denied. 2002 and 2003 mean the client could not reach the server at all.

Summary

  • Run MySQL 8.4 LTS from an installer, a package, Docker or a managed service. Persist Docker data in a volume.
  • Use the mysql client first; MySQL Shell and Workbench are good additions later.
  • -p prompts for the password, -P sets the port, and -h localhost means a socket, not TCP.
  • An account is 'user'@'host'; the most specific host pattern wins, and CURRENT_USER() shows which one matched.
  • Give each application its own account limited to its own database, never root.

Next: Using the mysql Command-Line Client.