Installing MySQL on Mac Mini with External Access: A Guide for the MoolMeow Project 🐬

Hello there! In this guide, we’ll walk through installing MySQL on a Mac Mini and configuring it for external access from a Kubernetes cluster for the MoolMeow project. I’ll also share common troubleshooting tips that we encountered along the way! 🛠️

⚠️ Warning: All database names, usernames, and passwords used in this guide are examples only. In a real environment, you should use different values for security reasons.

1. Installing MySQL with Homebrew 🍺

On Mac, we can easily install MySQL using Homebrew.

Installing Homebrew

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

# For zsh (default macOS shell)
echo 'eval "$(/opt/homebrew/bin/brew shellenv)"' >> ~/.zshrc
source ~/.zshrc

Installing and Starting MySQL

# Install MySQL
brew install mysql

# Start MySQL service
brew services start mysql

# Initial security setup
mysql_secure_installation

During the security setup, you’ll answer questions about:

• Enabling password complexity validation
• Setting a root password
• Removing anonymous users
• Disallowing root login remotely
• Removing test database
• Reloading privilege tables

2. Configuring MySQL for External Access 🌐

Finding MySQL Configuration File

For MySQL installed via Homebrew on Mac, the configuration file is typically located at:

# Check where MySQL is looking for config files
mysql --help | grep "Default options" -A 1

Usually, it’s at /opt/homebrew/etc/my.cnf.

Modifying the Configuration File

Edit the configuration file to allow external connections:

sudo nano /opt/homebrew/etc/my.cnf

Change the configuration content to:

# Default Homebrew MySQL server config
[mysqld]
# Allow connections from anywhere
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0

[client]
socket=/tmp/mysql.sock

Setting bind-address = 0.0.0.0 allows MySQL connections from all network interfaces. Adding socket=/tmp/mysql.sock in the [client] section resolves socket connection issues.

Restarting MySQL

After changing the configuration, restart MySQL:

brew services restart mysql

3. Resolving “Can’t connect to local MySQL server through socket” Error 🔧

When connecting to MySQL, you might encounter this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Here’s how to resolve it:

Method 1: Specify Socket Location

mysql -u root -p --socket=/tmp/mysql.sock

Method 2: Configure Socket in my.cnf

Add the socket location to the [client] section of your my.cnf file:

[client]
socket=/tmp/mysql.sock

This allows you to connect simply with mysql -u root -p.

Method 3: Check MySQL Logs

Check the MySQL logs to find the actual socket file location:

# Find MySQL process ID
ps aux | grep mysqld

# Check log file
tail -100 /opt/homebrew/var/mysql/*.err

Look for lines like socket: '/tmp/mysql.sock' to identify the actual socket location.

4. Creating Database and Users 🗄️

After connecting to MySQL, create a database and users for the MoolMeow project:

-- Create database (use a different name in your real environment)
CREATE DATABASE your_database_name;

-- Create user for local access (use different username and password)
CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'use_strong_password_here';
GRANT ALL PRIVILEGES ON your_database_name.* TO 'your_username'@'localhost';

-- Create user for Kubernetes nodes
CREATE USER 'your_username'@'192.168.100.%' IDENTIFIED BY 'use_strong_password_here';
GRANT ALL PRIVILEGES ON your_database_name.* TO 'your_username'@'192.168.100.%';

-- Apply changes
FLUSH PRIVILEGES;

Here, 192.168.100.% represents the IP range of your Kubernetes cluster. This setting only allows connections from those IPs.

5. Designing an Integrated Database Structure 📊

For the MoolMeow project, it’s efficient to use an integrated database structure for managing posts and marketplace items that apply to both Mool and Meow sections.

-- Posts table (customize table names for your environment)
CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    section ENUM('section1', 'section2') NOT NULL,  -- Distinguishing field (use your actual values)
    title VARCHAR(255) NOT NULL,
    content TEXT,
    author_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_section (section),
    INDEX idx_author (author_id)
);

-- Marketplace items table (customize table names for your environment)
CREATE TABLE marketplace_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    section ENUM('section1', 'section2') NOT NULL,  -- Distinguishing field (use your actual values)
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    seller_id BIGINT NOT NULL,
    status ENUM('available', 'sold', 'reserved') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_section (section),
    INDEX idx_seller (seller_id),
    INDEX idx_status (status)
);

This structure uses a section field to distinguish between Mool and Meow items.

6. Testing External MySQL Connection 🔄

After completing the setup, test the connection from another computer or Kubernetes node:

# From another computer
mysql -h [Mac Mini IP address] -u your_username -p -P 3306

If the connection succeeds, your external access configuration is working correctly.

7. Strengthening MySQL Access Control and Security 🔒

Some additional settings to enhance security when allowing external access:

1. Allow only specific IP addresses: As configured above, limit user connections to only the 192.168.100.% IP range.

2. Apply SSL/TLS encryption (optional):

   ALTER USER 'your_username'@'192.168.100.%' REQUIRE SSL;
   

3. Set resource limits (optional):

   ALTER USER 'your_username'@'192.168.100.%' WITH 
       MAX_QUERIES_PER_HOUR 10000
       MAX_CONNECTIONS_PER_HOUR 1000;
   

Conclusion 🎯

Now you’ve successfully installed MySQL on your Mac Mini and configured it for external access. With these settings, your Kubernetes cluster can securely connect to MySQL, and you can efficiently manage an integrated database for the MoolMeow project.

In the next guide, we’ll explore how to install and configure Redis, as well as how to set up the basic infrastructure on your k3s Kubernetes cluster. 🚀

Feel free to leave any questions or comments below! 💬