init-mysql-mac - MySQL Setup for macOS

Complete MySQL server installation, configuration, and management tool for macOS using Homebrew. Handles everything from fresh install to database/user creation with intelligent defaults.

When to Use This Skill

• User wants to install MySQL on macOS
• User mentions "setup MySQL on Mac" or "install MySQL Homebrew"
• User needs to create a MySQL database and user
• User wants to secure MySQL installation
• User asks about MySQL configuration on macOS
• User needs to manage MySQL service (start/stop/restart)

Core Capabilities

1. Installation

   • Install MySQL via Homebrew
   • Check existing installation
   • Handle reinstallation scenarios
   • Verify Homebrew availability

2. Security Configuration

   • Secure MySQL installation
   • Set root password
   • Remove anonymous users
   • Configure access restrictions
   • Generate secure passwords

3. Database & User Setup

   • Create database with proper charset
   • Create user with privileges
   • Configure connection settings
   • Generate configuration files

4. Service Management

   • Start/stop/restart MySQL service
   • Check service status
   • Auto-start on boot
   • Troubleshoot service issues

5. Configuration Generation

   • Create .env files
   • Generate database.yaml
   • Save connection strings
   • Document credentials securely

Installation Modes

Interactive Mode (Default)

• Prompts for all configuration
• User sets passwords manually
• Runs mysql_secure_installation
• Full control over settings

Auto Mode (--auto)

• No user prompts
• Auto-generates secure passwords
• Uses sensible defaults
• Ideal for quick setup or scripting

Instructions

1. Pre-Installation Checks

Before starting:

1. Check Homebrew:

   command -v brew || echo "Homebrew not installed"
   

   If not installed, guide user:

   /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
   

2. Check existing MySQL:

   brew list mysql 2>/dev/null
   mysql --version 2>/dev/null
   

3. Ask user about mode:

   • Interactive: Full control, set passwords manually
   • Auto: Quick setup, auto-generated passwords

2. Install MySQL

For fresh installation:

1. Install via Homebrew:

   brew install mysql
   

2. Start MySQL service:

   brew services start mysql
   

3. Wait for initialization (5 seconds):

   sleep 5
   

4. Verify service is running:

   brew services list | grep mysql | grep started
   

For existing installation:

1. Ask user:

   • Reinstall? (Stop service, uninstall, reinstall)
   • Skip to configuration?
   • Update existing installation?

2. If reinstalling:

   brew services stop mysql
   brew uninstall mysql
   brew install mysql
   

3. Secure MySQL Installation

Interactive Mode:

1. Run mysql_secure_installation:

   mysql_secure_installation
   

2. Guide user with recommended answers:

   • Set root password: YES (strong password)
   • Remove anonymous users: YES
   • Disallow root login remotely: YES
   • Remove test database: YES
   • Reload privilege tables: YES

3. Collect information:

   • Root password (user-provided)
   • Database name (default: textsql_db)
   • Database user (default: textsql_user)
   • User password (user-provided)
   • Host (default: localhost)
   • Port (default: 3306)

Auto Mode:

1. Generate secure passwords:

   # 16-character random password
   ROOT_PASSWORD=$(LC_ALL=C tr -dc 'A-Za-z0-9!@#$%^&*' < /dev/urandom | head -c 16)
   DB_PASSWORD=$(LC_ALL=C tr -dc 'A-Za-z0-9!@#$%^&*' < /dev/urandom | head -c 16)
   

2. Set root password (fresh install):

   mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '${ROOT_PASSWORD}';" 2>/dev/null
   

3. Use defaults:

   • Database: textsql_db
   • User: textsql_user
   • Host: localhost
   • Port: 3306

4. Create Database and User

Execute SQL commands:

-- Create database with UTF8MB4 charset
CREATE DATABASE IF NOT EXISTS ${DB_NAME}
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Create user (only from localhost for security)
CREATE USER IF NOT EXISTS '${DB_USER}'@'localhost'
  IDENTIFIED BY '${DB_PASSWORD}';

-- Grant all privileges on the specific database
GRANT ALL PRIVILEGES ON ${DB_NAME}.*
  TO '${DB_USER}'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify database exists
SHOW DATABASES;

Run via bash:

mysql -u root -p"${ROOT_PASSWORD}" <<EOF
CREATE DATABASE IF NOT EXISTS ${DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS '${DB_USER}'@'localhost' IDENTIFIED BY '${DB_PASSWORD}';
GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'localhost';
FLUSH PRIVILEGES;
SHOW DATABASES;
EOF

Verify success:

• Check exit code: $? -eq 0
• Test connection: mysql -u ${DB_USER} -p"${DB_PASSWORD}" ${DB_NAME} -e "SELECT 1"

5. Generate Configuration Files

Create database.yaml:

Location: ./config/database.yaml

Content:

# MySQL Database Configuration
# Auto-generated by Claude Code init-mysql-mac skill

database:
  type: mysql
  version: "8.0"
  host: ${DB_HOST}
  port: ${DB_PORT}
  user: ${DB_USER}
  password: ${DB_PASSWORD}
  database: ${DB_NAME}
  charset: utf8mb4

  # Connection pool settings (optional)
  pool_size: 5
  max_overflow: 10
  pool_timeout: 30
  pool_recycle: 3600

Create/Update .env:

Location: ./.env

Before creating:

1. Check if .env exists
2. If exists, backup: .env.backup.YYYYMMDD_HHMMSS
3. Create new .env

Content:

# =============================================================================
# Database Configuration
# =============================================================================
# Auto-generated by Claude Code init-mysql-mac skill on $(date)

DATABASE_URI=mysql+pymysql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}
DATABASE_VERSION=MySQL 8.0

# Individual connection parameters
DB_HOST=${DB_HOST}
DB_PORT=${DB_PORT}
DB_USER=${DB_USER}
DB_PASSWORD=${DB_PASSWORD}
DB_NAME=${DB_NAME}

# =============================================================================
# Add your application-specific settings below
# =============================================================================

Create my.cnf (optional):

Location: ~/.my.cnf (for client convenience)

Content:

[client]
user=${DB_USER}
password=${DB_PASSWORD}
host=${DB_HOST}
port=${DB_PORT}
database=${DB_NAME}

Set permissions:

chmod 600 ~/.my.cnf

6. Post-Installation Summary

Display to user:

1. Installation summary:

   • Database Name
   • Database User
   • Host & Port
   • Service status

2. Auto mode: Show credentials:

   • ⚠️ Display generated passwords
   • Warn to save securely
   • Indicate where passwords are saved

3. Configuration files created:

   • config/database.yaml
   • .env
   • ~/.my.cnf (optional)

4. Useful commands:

   # Service management
   brew services start mysql
   brew services stop mysql
   brew services restart mysql
   brew services list | grep mysql
   
   # Connect to MySQL
   mysql -u ${DB_USER} -p ${DB_NAME}
   mysql -u root -p
   
   # Check MySQL status
   mysqladmin -u root -p status
   mysqladmin -u root -p variables
   

5. Next steps:

   • Test connection
   • Import schema/data
   • Configure application
   • Set up backups

Common Scenarios

Scenario 1: Fresh MySQL Installation

User: "Install MySQL on my Mac"

Actions:

1. Check Homebrew installed
2. Install MySQL: brew install mysql
3. Start service: brew services start mysql
4. Ask: Interactive or auto mode?
5. If interactive: Run mysql_secure_installation
6. If auto: Generate passwords, secure automatically
7. Create database and user
8. Generate config files
9. Display summary with credentials

Scenario 2: Create New Database on Existing MySQL

User: "Create a new MySQL database called myapp"

Actions:

1. Check MySQL is running
2. Ask for root password (or detect from config)
3. Ask for new user details
4. Create database and user
5. Update .env with new database info
6. Test connection
7. Display connection string

Scenario 3: Reset MySQL Installation

User: "Reinstall MySQL, I forgot the root password"

Actions:

1. Stop MySQL: brew services stop mysql
2. Uninstall: brew uninstall mysql
3. Remove data directory (ask user):

   rm -rf /usr/local/var/mysql
   # or for Apple Silicon
   rm -rf /opt/homebrew/var/mysql
   

4. Fresh install
5. Secure installation
6. Create database and user
7. Generate new configs

Scenario 4: Automated Setup for CI/CD

User: "Set up MySQL automatically for testing"

Actions:

1. Run in auto mode
2. Use test database name
3. Generate secure passwords
4. Save credentials to CI environment
5. Create minimal config
6. Verify connection
7. Return connection string

Scenario 5: Multiple Databases

User: "Create databases for dev, staging, and production"

Actions:

1. Check MySQL running
2. For each environment:
   • Create database: ${app}_dev, ${app}_staging, ${app}_prod
   • Create user with privileges
   • Generate separate .env files
3. Create .env.dev, .env.staging, .env.prod
4. Document which to use when

Service Management

Start MySQL:

brew services start mysql

# Verify started
brew services list | grep mysql
# Should show: started

Stop MySQL:

brew services stop mysql

Restart MySQL:

brew services restart mysql

Check Status:

# Service status
brew services list | grep mysql

# MySQL process
ps aux | grep mysql

# MySQL port
lsof -i :3306

Auto-start on boot:

# Enable (default with brew services start)
brew services start mysql

# Disable auto-start but keep running
brew services run mysql

Troubleshooting

MySQL Won't Start

Check logs:

# Homebrew logs
tail -f /usr/local/var/mysql/$(hostname).err
# or for Apple Silicon
tail -f /opt/homebrew/var/mysql/$(hostname).err

Common fixes:

1. Remove lock files:

   rm -f /usr/local/var/mysql/*.pid
   rm -f /tmp/mysql.sock*
   

2. Check permissions:

   sudo chown -R $(whoami) /usr/local/var/mysql
   # or for Apple Silicon
   sudo chown -R $(whoami) /opt/homebrew/var/mysql
   

3. Reinstall:

   brew services stop mysql
   brew uninstall mysql
   brew install mysql
   brew services start mysql
   

Can't Connect to MySQL

Error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket

Fix:

1. Check MySQL is running:

   brew services list | grep mysql
   

2. If not running, start:

   brew services start mysql
   sleep 5
   

3. Check socket file exists:

   ls -la /tmp/mysql.sock
   

Access Denied

Error: ERROR 1045 (28000): Access denied for user 'user'@'localhost'

Fix:

1. Verify username/password

2. Reset user password:

   mysql -u root -p
   ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
   FLUSH PRIVILEGES;
   

3. If root password forgotten:

   • Stop MySQL
   • Start in safe mode
   • Reset password
   • Restart normally

Port Already in Use

Error: Port 3306 already in use

Fix:

1. Check what's using port:

   lsof -i :3306
   

2. Kill process or change MySQL port:

   # Edit config
   nano /usr/local/etc/my.cnf
   # Add: port = 3307
   
   # Restart
   brew services restart mysql
   

Homebrew Not Found

Error: brew: command not found

Fix:

# Install Homebrew
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# For Apple Silicon, add to PATH
echo 'eval "$(/opt/homebrew/bin/brew shellenv)"' >> ~/.zprofile
eval "$(/opt/homebrew/bin/brew shellenv)"

Security Best Practices

1. Strong Passwords:

   • Minimum 16 characters
   • Mix of letters, numbers, symbols
   • Use password generator

2. Limit Access:

   • Users only from localhost (unless remote needed)
   • Specific database privileges only
   • No GRANT ALL unless necessary

3. Remove Defaults:

   • Delete test database
   • Remove anonymous users
   • Disable remote root login

4. Secure Files:

   • Protect .env: chmod 600 .env
   • Protect my.cnf: chmod 600 ~/.my.cnf
   • Don't commit passwords to git

5. Regular Updates:

   brew update
   brew upgrade mysql
   

6. Backup Regularly:

   mysqldump -u root -p ${DB_NAME} > backup_$(date +%Y%m%d).sql
   

Advanced Configuration

Custom my.cnf

Location: /usr/local/etc/my.cnf (Intel) or /opt/homebrew/etc/my.cnf (Apple Silicon)

Example:

[mysqld]
port = 3306
bind-address = 127.0.0.1
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Performance
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M

# Logging
log_error = /usr/local/var/mysql/error.log
slow_query_log = 1
slow_query_log_file = /usr/local/var/mysql/slow.log
long_query_time = 2

[client]
port = 3306
default-character-set = utf8mb4

Apply changes:

brew services restart mysql

Enable Remote Access

Warning: Only enable if needed, security risk!

1. Edit my.cnf:

   [mysqld]
   bind-address = 0.0.0.0
   

2. Create remote user:

   CREATE USER 'user'@'%' IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON database.* TO 'user'@'%';
   FLUSH PRIVILEGES;
   

3. Firewall (if needed):

   # macOS firewall allows by default
   # Check: System Settings > Network > Firewall
   

SSL/TLS Configuration

Generate certificates:

mysql_ssl_rsa_setup --datadir=/usr/local/var/mysql

Configure my.cnf:

[mysqld]
require_secure_transport = ON
ssl-ca = /path/to/ca.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem

Integration Examples

Python (pymysql/SQLAlchemy):

from sqlalchemy import create_engine

# From .env
DATABASE_URI = "mysql+pymysql://user:pass@localhost:3306/dbname"
engine = create_engine(DATABASE_URI)

Node.js (mysql2):

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

Go (go-sql-driver):

import "database/sql"
import _ "github.com/go-sql-driver/mysql"

db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")

Output Format

Success output:

==========================================
MySQL Server Setup for macOS
==========================================

✓ Homebrew is installed
✓ MySQL installed successfully
✓ MySQL service is running
✓ MySQL secured in AUTO mode
✓ Database and user created successfully
✓ Created config/database.yaml
✓ Created .env with database configuration

==========================================
Installation Complete!
==========================================

MySQL Server Setup Summary:
  Database Name: myapp_db
  Database User: myapp_user
  Host: localhost
  Port: 3306

Auto-generated Credentials (SAVE THESE!):
  MySQL Root Password: Xy9#mK2$pL4@nQ8v
  Database User Password: Bw7!tR5&hN3%jM9c

⚠ IMPORTANT: Save these credentials in a secure location!

Configuration Files:
  Database config: config/database.yaml
  Environment: .env

Useful Commands:
  Start MySQL:   brew services start mysql
  Stop MySQL:    brew services stop mysql
  Restart MySQL: brew services restart mysql
  MySQL CLI:     mysql -u myapp_user -p myapp_db
  Check status:  brew services list | grep mysql

Next Steps:
  1. Test the connection
  2. Import your database schema/data
  3. Configure your application to use the database

Setup complete! Happy developing! 🚀

Usage with Claude Code

Natural commands that trigger this skill:

• "Install MySQL on my Mac"
• "Set up MySQL database on macOS"
• "Create a new MySQL database called myapp"
• "Help me configure MySQL on Mac"
• "Reset my MySQL installation"
• "Generate MySQL config files"
• "Secure my MySQL installation"

Claude will handle all the details automatically!