MySQL on Localhost

MySQL is the world's most popular open-source relational database. When running locally, MySQL listens on localhost:3306 by default. It's included in XAMPP, MAMP, WAMP, and can be installed standalone or via Docker.

Access MySQL: phpMyAdmin

Default: root / (blank) | Port: 3306 | GUI: MySQL Workbench

Default Credentials

Stack	Username	Password	Host
XAMPP	root	(blank)	localhost
MAMP	root	root	localhost
WAMP	root	(blank)	localhost
Laragon	root	(blank)	localhost
Docker (official)	root	Set via env var	localhost
MySQL Installer	root	Set during install	localhost

Ways to Access MySQL

1. phpMyAdmin (Web Interface)

The easiest way for beginners — included with XAMPP, MAMP, WAMP:

http://localhost/phpmyadmin
http://localhost:8080/phpmyadmin (some configs)

2. MySQL Command Line

# Connect to MySQL
mysql -u root -p

# Connect with password inline (not recommended)
mysql -u root -pYourPassword

# Connect to specific database
mysql -u root -p mydatabase

# Execute SQL file
mysql -u root -p mydatabase < backup.sql

3. MySQL Workbench (GUI)

Official MySQL GUI tool — download from mysql.com:

Download MySQL Workbench
Click + to add new connection
Connection Name: Local MySQL
Hostname: 127.0.0.1
Port: 3306
Username: root
Click Test Connection

4. Code Connection

PHP (mysqli)

$conn = new mysqli("localhost", "root", "", "mydatabase");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

PHP (PDO)

try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Node.js

const mysql = require('mysql2');
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'mydatabase'
});
connection.connect();

Python

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="mydatabase"
)

Common MySQL Commands

# Show all databases
SHOW DATABASES;

# Create database
CREATE DATABASE mydatabase;

# Use database
USE mydatabase;

# Show tables
SHOW TABLES;

# Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# Insert data
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

# Select data
SELECT * FROM users;

# Create user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Start/Stop MySQL

XAMPP

Use XAMPP Control Panel → Start/Stop MySQL

Windows (Standalone)

# Start MySQL service
net start mysql

# Stop MySQL service
net stop mysql

Mac (Homebrew)

brew services start mysql
brew services stop mysql
brew services restart mysql

Linux

sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql

MySQL Configuration

OS/Stack	Config File Location
XAMPP (Windows)	C:\xampp\mysql\bin\my.ini
XAMPP (Mac)	/Applications/XAMPP/xamppfiles/etc/my.cnf
MAMP	/Applications/MAMP/conf/my.cnf
Linux	/etc/mysql/my.cnf or /etc/my.cnf
Windows	C:\ProgramData\MySQL\MySQL Server X.X\my.ini

Troubleshooting

Problem	Solution
Can't connect to MySQL server	Start MySQL service in XAMPP/MAMP
Access denied for user 'root'	Check password, try blank password
Port 3306 already in use	Stop other MySQL instance or change port
MySQL shutdown unexpectedly	Check error log, often port conflict
Connection refused	MySQL not running or firewall blocking
Can't create database	Check user permissions

Reset Root Password

# Stop MySQL
# Start MySQL with skip-grant-tables
mysqld --skip-grant-tables

# In another terminal
mysql -u root

# Reset password
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

# Restart MySQL normally

Backup & Restore

# Backup database
mysqldump -u root -p mydatabase > backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_backup.sql

# Restore database
mysql -u root -p mydatabase < backup.sql