Scribbles for my bad memory

Working with multiple MySQL environments is a common scenario for developers. Whether you're connecting to development, staging, or production databases, you often need different connection parameters for each environment. While MySQL's configuration files help manage these connections, switching between them can be cumbersome. Let me introduce a simple but powerful bash function that will streamline this process.

The Problem

If you work with MySQL regularly, you likely have multiple connection profiles defined in your ~/.my.cnf file using the [group] syntax. To use a specific configuration group, you need to use the --defaults-group-suffix option:

mysql --defaults-group-suffix=dev
mysql --defaults-group-suffix=prod

This works, but it's verbose and typo-prone.

The Solution: A Smarter Bash Function

Here's a bash function that makes working with MySQL much more convenient:

m() {
    if [ $# -eq 0 ]; then
        # If no arguments provided, run mysql directly
        mysql
    elif [[ "$1" == -* ]]; then
        # If first argument starts with -, pass all arguments directly to mysql
        mysql "$@"
    else
        # Otherwise, treat first argument as suffix
        local suffix=$1
        shift
        mysql --defaults-group-suffix=$suffix "$@"
    fi
}

How It Works

This function provides three different behaviors depending on how you call it:

1. No arguments: Simply runs the MySQL client with default settings.

   m
   

2. First argument starts with a dash: Passes all arguments directly to MySQL, just like you're using the mysql command.

   m -e "SHOW DATABASES"
   

3. First argument doesn't start with a dash: Uses the first argument as the defaults-group-suffix and passes the remaining arguments to MySQL.

   m dev -e "SELECT * FROM users"
   

Practical Examples

Example 1: Connect to Different Environments

m dev    # Connect to development database
m prod   # Connect to production database
m stage  # Connect to staging database

Example 2: Run Commands Directly

m dev -e "SELECT COUNT(*) FROM orders"
m prod -N -e "SELECT id FROM customers LIMIT 5"  # -N removes column names

Example 3: Use MySQL Options Directly

m -N -e "SELECT VERSION()"  # Quick query without environment prefix
m -t -e "SHOW TABLES"  # Table format output

Setup Instructions

1. Add the function to your .bashrc or .zshrc file.
2. Set up your MySQL configuration groups in ~/.my.cnf:

[client]
# Default settings

[clientdev]
user=devuser
password=devpass
host=dev-db.example.com

[clientprod]
user=produser
password=prodpass
host=prod-db.example.com

[mysql]
database=YourDataBase

3. Source your shell configuration file or restart your terminal.

Benefits

• Brevity: No need to type connection parameters (-h, -u, -p) repeatedly, as they're stored in your config file.
• Flexibility: Pass any MySQL options like -N (skip column names) or -e (execute) alongside environment selection.
• Consistency: Maintain the standard MySQL command syntax for direct option usage.

This simple function has saved me countless keystrokes and made database work significantly smoother. It's especially handy for running quick queries with options like -e (execute) and -N (no headers) without having to type connection details every time. Give it a try in your workflow—small improvements like this add up to a much more efficient development experience!

Do you have any favorite shell functions that make your development life easier? Share them in the comments!