Installing and Setting Up MySQL

Concept

Setting up MySQL involves:

Installing MySQL Server (mysqld), which runs as a background service.

Installing one or more clients: the mysql command-line client (bundled with the server), MySQL Workbench (graphical), and MySQL Shell (mysqlsh, advanced scripting/administration).

Securing the installation (root password, remove anonymous users and test database).

Creating application users with limited privileges.

Basic configuration (my.cnf / my.ini).

The database engine process mysqld Stores data, executes SQL

Quick queries, scripts, servers without GUI Visual query editor, ER modeling, administration, migration MySQL Shell (mysqlsh)

Advanced client with SQL, JavaScript, and Python modes Dump/load utilities, InnoDB Cluster setup, upgrade checks, scripting

Driver libraries (Connector/J, Connector/Python, Connector/NET, Connector/C++, Connector/ODBC)

Why It Matters

A correct, secure installation prevents the most common beginner problems: "Access denied", "Can't connect to MySQL server", forgotten root passwords, and databases accidentally exposed to the internet.

Syntax

Commands you will use constantly (run in an operating-system terminal, not at the mysql> prompt):

mysql --version                     # client version
mysql -u root -p                    # log in as root, prompt for password
mysql -u appuser -p -h 127.0.0.1 -P 3306 eh_company   # user, host, port, database
mysqladmin -u root -p status        # server status summary
mysqladmin -u root -p version       # server version and uptime

Example

2.1 Installation on Windows Download the MySQL Community Server MSI installer for Windows from the official MySQL downloads site (dev.mysql.com). For MySQL 8.0, the all-in-one MySQL Installer can also install Workbench and Shell; for 8.4 LTS and later, each product has its own MSI installer.

Run the installer and choose Server (plus Workbench and Shell as separate downloads if needed).

In the configuration step:

Config type: *Development Computer* (small memory footprint) or *Server Computer*.

Port: 3306.

Authentication: caching_sha2_password (the strong default).

Set the root password and optionally create a user.

Configure MySQL as a Windows service (default name MySQL80 for 8.0 or MySQL84 for 8.4) and start it at system startup.

Add C:\Program Files\MySQL\MySQL Server 8.4\bin to the PATH so mysql works in any terminal.

2.2 Installation on Linux Ubuntu / Debian (For the latest Oracle builds, add the official MySQL APT repository first; distribution packages may lag behind.)

RHEL / Rocky / Oracle Linux / Fedora 2.3 Installation on macOS and with Docker

The -v volume keeps data when the container is recreated. Never use a weak password or publish port 3306 on a public server.

2.4 Securing the installation mysql_secure_installation interactively:

Enables the validate_password component (optional password-strength rules).

Sets or changes the root password.

Removes anonymous users.

Disallows remote root login.

Removes the test database.

Reloads privilege tables.

2.5 Connecting to MySQL -u / --user -p / --password Prompt for password (do not type the password after -p on shared machines — it appears in shell history and process lists) -h / --host

Server host name or IP (default localhost) -P / --port

TCP port (default 3306) — note: uppercase P -D or last argument Execute a statement and exit

DISABLED, PREFERRED (default), REQUIRED, VERIFY_CA, VERIFY_IDENTITY

On Linux/macOS, localhost means a Unix socket connection; 127.0.0.1 means TCP. They are different connection paths and can match different user accounts ('user'@'localhost' vs 'user'@'127.0.0.1').

Once connected:

2.6 Creating a user MySQL accounts are identified by user name and host: 'name'@'host'.

Since MySQL 8.0, GRANT no longer creates users implicitly; create the user first. FLUSH PRIVILEGES is not needed after CREATE USER/GRANT (it is only needed if you modify the grant tables directly, which you should not do).

2.7 Logging in Logging in without typing a password each time — use an option file (protected with file permissions) or the encrypted login path:

2.8 Changing passwords SET PASSWORD FOR 'user'@'host' = 'new'; also works; the old PASSWORD() function was removed in MySQL 8.0.

Forgotten root password (outline): stop the server, start it with --skip-grant-tables --skip-networking (so no one can connect remotely), connect, run FLUSH PRIVILEGES; then ALTER USER 'root'@'localhost' IDENTIFIED BY '...', and restart normally. (An alternative is --init-file containing the ALTER USER statement.)

2.9 Starting and stopping the MySQL service Enable at boot sudo systemctl start mysql sudo systemctl stop mysql sudo systemctl status mysql sudo systemctl enable mysql sudo systemctl start mysqld sudo systemctl stop mysqld sudo systemctl status mysqld sudo systemctl enable mysqld Windows (admin prompt) net start MySQL84 net stop MySQL84 sc query MySQL84 or Services app Service startup type = Automatic macOS (Homebrew) brew services start mysql brew services stop mysql brew services list automatic with brew services docker start mysql-lab docker stop mysql-lab --restart unless-stopped

Shutting down from SQL (requires the SHUTDOWN privilege):

or mysqladmin -u root -p shutdown. Always shut down cleanly; killing the process forces crash recovery on the next start.

2.10 Checking the MySQL version The client and server versions can differ; the server version is what matters for SQL features.

2.11 Basic MySQL configuration The server reads option files at startup:

/etc/my.cnf, /etc/mysql/my.cnf, /etc/mysql/mysql.conf.d/mysqld.cnf, ~/.my.cnf (client)

C:\ProgramData\MySQL\MySQL Server 8.4\my.ini /opt/homebrew/etc/my.cnf (Apple silicon) or /usr/local/etc/my.cnf Find which files your server reads:

Example my.cnf:

Changing settings at runtime:

Restart the service after editing option files for non-dynamic settings.

2.12 Connecting applications to MySQL Every application uses a connector (driver) and a connection string with host, port, database, user, and password. Store credentials in environment variables or a secrets manager, never in source code.

Module 35 covers connectors for more languages, connection pooling, and error handling.

mysql --version
mysql -u root -p

sudo apt update
sudo apt install mysql-server          # installs server and client
sudo systemctl status mysql            # service name on Ubuntu/Debian: mysql
sudo mysql_secure_installation         # secure the installation (see 2.4)
sudo mysql                             # on Ubuntu, root initially uses auth_socket: log in via sudo

sudo dnf install mysql-server          # or install from the official MySQL Yum repository
sudo systemctl enable --now mysqld     # service name on RHEL family: mysqld
sudo grep 'temporary password' /var/log/mysqld.log   # Oracle's RPMs generate a temporary root password
mysql -u root -p                       # log in with it, then change it immediately

# macOS with Homebrew
brew install mysql
brew services start mysql
mysql_secure_installation

# Docker (any OS) — quickest way to get a disposable MySQL for learning/testing
docker run --name mysql-lab \
  -e MYSQL_ROOT_PASSWORD=ChangeMe_Str0ng! \
  -p 3306:3306 \
  -v mysql_lab_data:/var/lib/mysql \
  -d mysql:8.4

docker exec -it mysql-lab mysql -u root -p   # connect using the client inside the container

mysql -u root -p                                   # local connection (socket on Linux/macOS, TCP/pipe on Windows)
mysql -u root -p -h 127.0.0.1 -P 3306              # force TCP to localhost
mysql -u appuser -p -h db.example.internal eh_company   # remote host and default database
mysql -u appuser -p --ssl-mode=REQUIRED -h db.example.internal   # require an encrypted connection

SELECT CURRENT_USER(), USER(), VERSION(), @@hostname, @@port;
STATUS;           -- client command: connection details (also \s)

-- Log in as root first: mysql -u root -p
CREATE DATABASE eh_company CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'eh_app'@'localhost' IDENTIFIED BY 'Str0ng!AppPassw0rd';
CREATE USER 'eh_app'@'10.0.0.%'  IDENTIFIED BY 'Str0ng!AppPassw0rd';   -- any host in 10.0.0.x
CREATE USER 'eh_readonly'@'%'    IDENTIFIED BY 'An0ther!Str0ngOne';    -- any host (use with care)

GRANT SELECT, INSERT, UPDATE, DELETE ON eh_company.* TO 'eh_app'@'localhost';
GRANT SELECT ON eh_company.* TO 'eh_readonly'@'%';

SHOW GRANTS FOR 'eh_app'@'localhost';

mysql -u eh_app -p eh_company
# Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.x MySQL Community Server - GPL

mysql>

mysql_config_editor set --login-path=ehapp --host=localhost --user=eh_app --password
mysql --login-path=ehapp eh_company

Expected Result

After installation and login:

(Your exact version number will differ.)

SELECT VERSION() AS server_version, CURRENT_USER() AS logged_in_as, @@port AS port;

+----------------+-------------------+------+
| server_version | logged_in_as      | port |
+----------------+-------------------+------+
| 8.4.3          | eh_app@localhost  | 3306 |
+----------------+-------------------+------+

Common Mistakes

Running mysql -u root -p inside the mysql> prompt (it is an operating-system command).

Typing -p password with a space: MySQL treats password as the database name. Use -p alone (prompt) or -pPassword (no space, not recommended).

Using -p for port — the port option is uppercase -P.

Forgetting that 'user'@'localhost' and 'user'@'%' are different accounts.

Exposing port 3306 to the internet with bind-address = 0.0.0.0 and weak passwords.

Using root for applications.

Old client libraries failing with "authentication plugin 'caching_sha2_password' cannot be loaded" — upgrade the connector instead of downgrading security. (In MySQL 8.4, the old mysql_native_password plugin is not even loaded by default.)

Editing the wrong my.cnf file and wondering why settings do not apply.

Best Practices

Run mysql_secure_installation on every new server.

Create one account per application, with only the privileges it needs.

Bind MySQL to private interfaces and use firewalls; require TLS for remote connections.

Store credentials in option files with restricted permissions, mysql_config_editor, or a secrets manager.

Use SET PERSIST or option files for durable configuration and keep configs in version control.

For learning and CI testing, use Docker containers with volumes.

Interview Relevance

Medium: "How do you connect to MySQL from the command line?", "Where is the configuration file?", "Default port?", "How do you create a user and grant privileges?", "How do you reset a forgotten root password?", "What is the difference between localhost and 127.0.0.1 in MySQL?".

Practice Questions

Install MySQL using your OS installer or Docker, then check both the client and server versions.

Create a database eh_lab and a user lab_user@localhost who has full rights only on eh_lab.

Log in as lab_user and try SHOW DATABASES;. Which databases are visible and why?

Change lab_user's password and force it to expire after 60 days.

Find your server's option file location and the current value of innodb_buffer_pool_size.

Stop and start the MySQL service using your OS commands.

Write a 10-line program in any language that connects to eh_company and prints the number of customers.