dotfiles

MySQL Installation & Configuration Guide

Prerequisites

• Terminal access
• macOS: Homebrew installed
• WSL/Ubuntu: Ubuntu 20.04+ on WSL2

---

Installation

macOS (Homebrew)

# Install MySQL 8.0
brew install mysql@8.0

# Link MySQL 8.0 to make it available in PATH
brew link mysql@8.0 --force

# Start MySQL service
brew services start mysql@8.0

WSL/Ubuntu (APT)

# Update package index
sudo apt update

# Install MySQL Server
sudo apt install mysql-server -y

# Start and enable MySQL service
sudo systemctl start mysql
sudo systemctl enable mysql

---

Initial Configuration

1. Check Default Root Setup

Connect to MySQL as root:

macOS:

mysql -u root  # No password by default

WSL/Ubuntu:

sudo mysql  # Uses socket authentication

Verify root configuration:

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
EXIT;

Expected output (macOS):

+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | caching_sha2_password |
+------+-----------+-----------------------+

Expected output (WSL/Ubuntu):

+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | auth_socket           |
+------+-----------+-----------------------+

2. Configure Root Authentication (WSL/Ubuntu only)

If root uses caching_sha2_password or mysql_native_password, configure it to use socket authentication. If already using auth_socket, skip this step.

Connect to MySQL:

sudo mysql -p

Set root to use socket authentication:

-- Check current root authentication
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

-- If not using auth_socket, set it now
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

FLUSH PRIVILEGES;
EXIT;

Verify root uses socket authentication:

# This should work (no password needed)
sudo mysql -e "SELECT USER(), CURRENT_USER();"

# This should NOT work
mysql -u root -p

3. Secure MySQL Installation

Run the secure installation wizard:

macOS:

mysql_secure_installation

WSL/Ubuntu:

sudo mysql_secure_installation

Answer the prompts:

• VALIDATE PASSWORD component: Your choice (both platforms)
• Enter password for user root: Your password if set (macOS only)
• Set root password:
  • macOS: Yes → Choose strong password
  • WSL/Ubuntu: No (using socket auth)
• Remove anonymous users: Yes
• Disallow root login remotely: Yes
• Remove test database: Yes
• Reload privilege tables: Yes

---

Create Application User

Connect as root:

macOS:

mysql -u root -p

WSL/Ubuntu:

sudo mysql

Create appuser with full privileges:

-- Create appuser with password authentication
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Password@123';
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'localhost' WITH GRANT OPTION;

-- Allow connections from any host (optional, development only)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'Password@123';
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

-- Verify users
SELECT user, host, plugin FROM mysql.user WHERE user IN ('root', 'appuser');
EXIT;

---

Configure MySQL Settings (Optional)

Edit Configuration File

macOS:

nano /opt/homebrew/etc/my.cnf

WSL/Ubuntu:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Recommended Settings

macOS (/opt/homebrew/etc/my.cnf):

innodb_buffer_pool_size=512M
innodb_log_file_size=128M
binlog_expire_logs_seconds=604800
max_binlog_size=512M

WSL/Ubuntu (/etc/mysql/mysql.conf.d/mysqld.cnf):

[mysqld]
bind-address=0.0.0.0
mysqlx-bind-address = 0.0.0.0
innodb_buffer_pool_size=512M
innodb_log_file_size=128M
max_binlog_size=512M

Restart MySQL

macOS:

brew services restart mysql@8.0

WSL/Ubuntu:

sudo systemctl restart mysql

---

Verify Installation

macOS:

# Test root
mysql -u root -p -e "SELECT USER(), CURRENT_USER();"

# Test appuser
mysql -u appuser -p -e "SELECT USER(), CURRENT_USER();"

WSL/Ubuntu:

# Test root (socket auth)
sudo mysql -e "SELECT USER(), CURRENT_USER();"

# Test appuser
mysql -u appuser -p -e "SELECT USER(), CURRENT_USER();"

---

MySQL Workbench (GUI Tool)

Installation

macOS:

brew install --cask mysqlworkbench

WSL/Ubuntu:

Download from: https://dev.mysql.com/downloads/workbench/

Or install on Windows and connect to WSL MySQL using WSL IP address.

Connecting to Local MySQL

1. Open MySQL Workbench
2. Click ”+” next to “MySQL Connections”
3. Enter connection details:
   • Connection Name: Local MySQL Server
   • Hostname: 127.0.0.1 (or WSL IP for Windows connection)
   • Port: 3306
   • Username: appuser
   • Password: Click “Store in Keychain/Vault” and enter Password@123
4. Click “Test Connection”
5. Click “OK”

MySQL Workbench provides:

• Visual database designer and ERD tools
• Query editor with syntax highlighting
• Database administration interface
• Import/Export wizards
• Performance monitoring
• User management

---

File Locations Reference

Purpose	macOS	WSL/Ubuntu
Main configuration	/opt/homebrew/etc/my.cnf	/etc/mysql/mysql.conf.d/mysqld.cnf
Data directory	/opt/homebrew/var/mysql/	/var/lib/mysql/
Error log	/opt/homebrew/var/mysql/error.log	/var/log/mysql/error.log
Socket file	/tmp/mysql.sock	/var/run/mysqld/mysqld.sock
Binary logs	/opt/homebrew/var/mysql/mysql-bin.*	N/A (enable in config)
PID file	/opt/homebrew/var/mysql/*.pid	/var/run/mysqld/mysqld.pid

---

Common Commands

macOS

# Service management
brew services start mysql@8.0
brew services stop mysql@8.0
brew services restart mysql@8.0
brew services info mysql@8.0

# Connect
mysql -u root -p
mysql -u appuser -p

# Logs
tail -f /opt/homebrew/var/mysql/$(hostname).err

WSL/Ubuntu

# Service management
sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql

# Connect
sudo mysql  # root (socket auth)
mysql -u appuser -p

# Logs
sudo tail -f /var/log/mysql/error.log

Common to Both

# Connect to specific database
mysql -u appuser -p database_name

# Execute SQL from command line
mysql -u appuser -p -e "SHOW DATABASES;"

# Import SQL file
mysql -u appuser -p database_name < backup.sql

# Export database
mysqldump -u appuser -p database_name > backup.sql

---

WSL-Specific Notes

Accessing MySQL from Windows:

1. Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

   bind-address=0.0.0.0
   

2. Restart MySQL:

   sudo systemctl restart mysql
   

3. Find WSL IP:

   hostname -I
   

4. Connect from Windows using this IP and appuser credentials

---

Security Best Practices

⚠️ Important Security Notes:

1. Set a strong root password during mysql_secure_installation (macOS)
2. Change default password (Password@123) to a strong, unique password
3. Never commit passwords or configuration files with credentials to version control
4. Use environment variables for passwords in production applications
5. Restrict appuser@’%’ in production (only allow specific hosts)
6. Regularly update MySQL to get security patches
7. Enable SSL/TLS for remote connections in production
8. Regularly backup your databases

---

Troubleshooting

MySQL won’t start

macOS:

cat /opt/homebrew/var/mysql/error.log
lsof -i :3306
rm /tmp/mysql.sock
mysql.server start

WSL/Ubuntu:

sudo cat /var/log/mysql/error.log
sudo lsof -i :3306
sudo rm /var/run/mysqld/mysqld.sock
sudo systemctl restart mysql

Authentication issues

# Verify users (macOS)
mysql -u root -p -e "SELECT user, host, plugin FROM mysql.user;"

# Verify users (WSL/Ubuntu)
sudo mysql -e "SELECT user, host, plugin FROM mysql.user;"

# Reset appuser password
sudo mysql -e "ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewPassword123';"

Can’t connect from application

# Check if MySQL is listening
netstat -an | grep 3306

# Verify privileges
sudo mysql -e "SHOW GRANTS FOR 'appuser'@'localhost';"

# Check bind-address (should be 0.0.0.0 or commented out)
# macOS:
grep bind-address /opt/homebrew/etc/my.cnf

# WSL/Ubuntu:
grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf

---

Additional Resources

• MySQL Official Documentation
• MySQL Security Guide
• MySQL Performance Tuning
• MySQL Workbench Documentation