Install MySQL on Windows

Before you start

You need a server you own, because practice websites never let you create a user, take a backup or read an error log.

What you need

• Windows 11, 64-bit. MySQL for Windows is 64-bit only; check with winver.
• An administrator account. The MSI, the service and the firewall rule need one.
• The Visual C++ 2019 (v14) Redistributable. Without it the service dies with a missing VCRUNTIME140.dll; step 1 installs it.
• About a gigabyte of disk.

Which edition, which line

Community Server is free and this course uses it; Enterprise Edition is paid. An LTS line keeps five years of premier support and removes nothing inside it; an Innovation release lives only until the next one.

Install the line your workplace runs, or 9.7 LTS if you do not know it. Only the version digits in paths change.

Where to download

Each product is its own download now:

• Server: dev.mysql.com/downloads/mysql/. Pick your version, then the "Windows (x86, 64-bit), MSI Installer" row: mysql-9.7.2-winx64.msi.
• MySQL Shell: dev.mysql.com/downloads/shell/, currently 26.7.1.
• MySQL Workbench: dev.mysql.com/downloads/workbench/, currently 26.7.0.

Oracle asks you to log in. Look below the buttons for "No thanks, just start my download."

Step by step: the MSI and MySQL Configurator

The MSI copies files into C:\Program Files\MySQL\MySQL Server 9.7\. MySQL Configurator, launched at the end of the MSI, initializes the data directory, writes my.ini, registers the service and sets the root password; until it runs, nothing listens on 3306. Steps show 9.7; swap in 8.4.

1. Install the Visual C++ Redistributable.

   Run Microsoft's vc_redist.x64.exe, or let winget do it. "Already installed" is fine.

   # One-time prerequisite for the server and for Workbench
   winget install -e --id Microsoft.VCRedist.2015+.x64

2. Download the server MSI.

   Pick your version and the MSI row, and skip the login prompt.

3. Run the MSI.

   Accept the license, pick Typical, and approve the UAC prompt. On the last page leave "Run MySQL Configurator" checked.

4. Configurator: Type and Networking.

   Choose Development, the lightest on memory. Leave TCP/IP on Port 3306 and X Protocol Port 33060, and untick the Windows Firewall box.

5. Configurator: Accounts and Roles.

   Type a strong root password, the only required field, and store it in your password manager. Skip "Add User".

6. Configurator: Windows Service.

   The Windows Service Name defaults to MySQL plus the version digits: MySQL97 or MySQL84. Write it down; keep the other defaults.

7. Configurator: File Permissions and Sample Databases.

   Accept the default permissions and tick sakila and world; chapter 6 uses them.

8. Configurator: Apply Configuration.

   Click Execute, watch the checklist turn green, and click Finish. my.ini and data now sit in hidden C:\ProgramData\MySQL\MySQL Server 9.7\; programs in C:\Program Files\MySQL\MySQL Server 9.7\bin\.

   # Is the service there and running? (use the name from the previous step)
   Get-Service MySQL97
   # Status   Name      DisplayName
   # ------   ----      -----------
   # Running  MySQL97   MySQL97

9. Put the bin folder on your PATH.

   Nothing adds it for you, so mysql is unknown until you do. Run the lines below, then open a new terminal.

   $bin = 'C:\Program Files\MySQL\MySQL Server 9.7\bin'
   $cur = [Environment]::GetEnvironmentVariable('Path', 'User')
   [Environment]::SetEnvironmentVariable('Path', "$cur;$bin", 'User')
   # setx PATH "..." also works, but it truncates the value at 1024
   # characters and can silently chop a long PATH. Prefer the lines above.

10. Connect.

    -u root names the account, and -p with no value makes the client prompt for the password.

    mysql -u root -p
    # Enter password: ********
    # Welcome to the MySQL monitor. ...
    # mysql>

    SELECT VERSION();
    SHOW DATABASES;
    exit

    VERSION()
    9.7.2

    SHOW DATABASES lists the system schemas, plus sakila and world if you ticked them.

The same install with winget

winget knows these packages, but Oracle.MySQL lags at 8.4.9 and runs Configurator silently, so you never see the pages above. Take the MSI path the first time.

winget install -e --id Oracle.MySQL
winget install -e --id Oracle.MySQLWorkbench
# Oracle.MySQLShell is stale (8.4.5): download Shell 26.7.1 from dev.mysql.com instead

Your first ten minutes

Four things a DBA does on every new instance: stop using root, load data, find the logs, learn the on/off switch.

Make yourself an account instead of using root

root@localhost is the master key in the safe (chapter 16 resets it). Connect as yourself so logs show who did what; chapter 14 explains the host part after the @.

-- Run these once, connected as root
CREATE USER 'yourname'@'localhost' IDENTIFIED BY 'a long passphrase';
GRANT ALL PRIVILEGES ON *.* TO 'yourname'@'localhost' WITH GRANT OPTION;

Then stop typing the password: mysql_config_editor stores a named login in %APPDATA%\MySQL\.mylogin.cnf, and every client accepts --login-path.

mysql_config_editor set --login-path=local --host=localhost --user=yourname --password
# from now on:
mysql --login-path=local

Load the shop database

Every example uses the "shop" dataset in assets/sample-shop.mysql.sql. PowerShell reserves <, so redirect inside cmd, or use the client's own source command with forward slashes.

cmd /c "mysql --login-path=local < C:\Users\you\dba\assets\sample-shop.mysql.sql"

-- ...or from inside the mysql client
source C:/Users/you/dba/assets/sample-shop.mysql.sql
SELECT name, city FROM shop.customers;

Four rows means server, client, account and dataset all work. PostgreSQL gets the same data in chapter 5.

Where everything lives

Programs sit in Program Files; my.ini, the Data folder and the logs sit in hidden ProgramData. Never guess the paths, because the server tells you them.

SELECT @@datadir, @@log_error;

Open that .err file first whenever the service refuses to start. The my.ini beside it holds the startup settings, in forward slashes.

[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 9.7/Data
log-error=YOURPC.err
lower_case_table_names=1
max_connections=151

You will edit it less often than you expect: SET PERSIST writes a setting to mysqld-auto.cnf, which survives restarts and overrides my.ini (chapter 16). lower_case_table_names is 1 on Windows, fixed at initialization.

Stop, start, restart

You restart after editing my.ini and after a patch release, and stopping disconnects every client. Three tools do the same job from an elevated window: services.msc, net stop and net start, and the PowerShell cmdlets.

# Elevated window. Use the service name Configurator showed you.
net stop MySQL97
net start MySQL97
Restart-Service MySQL97
Get-Service MySQL97 | Select-Object Status, StartType

Startup is not instant: InnoDB replays its redo log after an unclean shutdown, then the error log prints "ready for connections".

Install the clients: Workbench and MySQL Shell

You already have mysql.exe. Two more belong on day one; chapter 6 goes deep on both.

MySQL Shell 26.7.1

MySQL MySQL Shell (mysqlsh) runs SQL, JavaScript and Python, and hosts the DBA utilities: util.dumpInstance() for fast parallel backups (chapter 15) and util.checkForServerUpgrade(). Since 26.7 one Shell serves every server line, and it starts in SQL mode; \js and \sql switch language.

mysqlsh yourname@localhost
# MySQL  localhost:3306 ssl  SQL > SELECT COUNT(*) FROM shop.orders;

MySQL Workbench 26.7.0

MySQL Workbench is Oracle's graphical client: SQL editor, result grids, visual explain and administration screens. Install the MSI, then add a connection to localhost, port 3306, user yourname.

Workbench 26.7.0 is a brand new Electron application built on MySQL Shell and officially supports 8.4 LTS and newer. The old line ended with 8.0.47, now end of life.

Troubleshooting

Almost every install problem falls into one of these buckets, and the wording of the message is your clue.

# Who is on port 3306?
netstat -ano | findstr :3306
# Allow inbound 3306 if you unticked the firewall option in Configurator (elevated)
New-NetFirewallRule -DisplayName "MySQL 3306" -Direction Inbound -Protocol TCP -LocalPort 3306 -Action Allow

Uninstall and upgrade

A patch upgrade (9.7.2 to the next 9.7.x, one per quarter) keeps your data: run the newer MSI over the old install, then MySQL Configurator, which restarts the service. mysqld upgrades its own system tables at startup; mysql_upgrade was removed in 8.4. Back up first (chapter 15).

To uninstall, use MySQL Configurator's remove action, which deletes the service and offers to keep the data directory, then remove MySQL Server 9.7 in Settings > Apps. It leaves the data directory, my.ini, your saved logins, the PATH entry and the firewall rule for you to sweep up. A reinstall needs an empty Data folder.

Go deeper

• Installing MySQL on Microsoft Windows

  Prerequisites, default folders, silent msiexec options.

• MySQL Server Configuration with MySQL Configurator

  Every Configurator page and its defaults.