Installing MySQL and Connecting

Article · 2:00

Getting a server running, logging in from two clients, and the errors everyone meets on day one.

On Windows, the MySQL Installer bundles the server, Workbench and the command-line tools in one download. On macOS, Homebrew's brew install mysql is the shortest path. On Linux, your distribution packages it. In all three cases the server is a service, so it has to be started, and it has to be set to start on boot if you want it there tomorrow.

The first thing to do is secure it. mysql_secure_installation sets a root password, removes the anonymous accounts, and disables remote root login - three settings that have been the entry point for a lot of compromised servers.

Connect twice, deliberately. The command-line client is the one that will be available on a server over SSH when nothing else is, and it is the one every piece of documentation assumes. Workbench gives you a schema browser, a visual EXPLAIN and a data editor, and is where you will spend most of this course.

Three errors account for most first days. Access denied means the username, password or host is wrong - and note that MySQL treats user@localhost and user@'%' as different accounts. Can't connect to local server means the process is not running. Unknown database means you connected but did not choose a schema; USE fixes it.

-- Starting the server, by platform.
--   macOS      brew services start mysql
--   Linux      sudo systemctl start mysql && sudo systemctl enable mysql
--   Windows    net start MySQL80
--   Docker     docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret \
--                -p 3306:3306 -d mysql:8

-- Then, once, before anything else:
--   mysql_secure_installation

-- Connecting from the terminal. -p with no value PROMPTS for the password;
-- typing it on the command line puts it in your shell history.
--   mysql -u root -p
--   mysql -u root -p -h 127.0.0.1 -P 3306

-- Once connected, orient yourself.
SHOW DATABASES;

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE shop;          -- "Unknown database" usually means you skipped this

SELECT DATABASE();  -- confirms which one you are in
SHOW TABLES;