When provisioning MySQL servers in automated environments like Vagrant, we often need to modify the bind-address parameter to allow remote connections. The default configuration (typically 127.0.0.1) only allows local connections, which is problematic for development environments.

While tools like mysqladmin are great for runtime configuration, they don't persist changes across restarts. The most reliable method is to modify my.cnf directly. Here's why other approaches fall short:

• Appending to the file risks having multiple conflicting bind-address directives
• Environment variables only affect the current session
• SET GLOBAL commands don't persist after restart

Here's a production-tested shell script snippet that safely modifies the MySQL configuration:

#!/bin/bash

MYSQL_CONF="/etc/mysql/my.cnf"
BACKUP_CONF="/etc/mysql/my.cnf.bak"

# Create backup
sudo cp "$MYSQL_CONF" "$BACKUP_CONF"

# Handle three possible scenarios:
# 1. bind-address exists and needs modification
# 2. bind-address doesn't exist and needs adding
# 3. File might use [mysqld] section with indentation

# First, comment out any existing bind-address
sudo sed -i '/^bind-address/s/^/#/' "$MYSQL_CONF"

# Then add our new binding after the [mysqld] section
sudo sed -i '/^$$mysqld$$/a bind-address = 0.0.0.0' "$MYSQL_CONF"

# Restart MySQL to apply changes
sudo systemctl restart mysql

For more complex configurations, Perl provides better pattern matching:

#!/usr/bin/perl

use strict;
use warnings;

my $file = '/etc/mysql/my.cnf';
my @content;

{
    local $/;
    open(my $fh, '<', $file) or die "Can't open $file: $!";
    @content = split(/\n/, <$fh>);
    close($fh);
}

my $found_mysqld = 0;
foreach my $line (@content) {
    if ($line =~ /^$$mysqld$$/) {
        $found_mysqld = 1;
        next;
    }
    if ($found_mysqld && $line =~ /^bind-address/) {
        $line = "# $line";  # Comment out existing
    }
}

if ($found_mysqld) {
    # Add our new binding after [mysqld]
    splice(@content, $found_mysqld + 1, 0, 'bind-address = 0.0.0.0');
    
    open(my $fh, '>', $file) or die "Can't write to $file: $!";
    print $fh join("\n", @content);
    close($fh);
    
    system('sudo systemctl restart mysql');
} else {
    die "[mysqld] section not found in $file";
}

Modern MySQL installations often split configuration across multiple files. Here's how to handle this scenario:

#!/bin/bash

# Search for active configuration files
MYSQL_CONF_FILES=$(find /etc/mysql -name '*.cnf')

for conf_file in $MYSQL_CONF_FILES; do
    # Skip if file doesn't contain [mysqld] section
    if ! grep -q '^$$mysqld$$' "$conf_file"; then
        continue
    fi
    
    # Process each relevant config file
    sudo sed -i '/^$$mysqld$$/a bind-address = 0.0.0.0' "$conf_file"
    sudo sed -i '/^bind-address/s/^/#/' "$conf_file"
done

sudo systemctl restart mysql

Always verify your changes:

# Check running configuration
mysql -e "SHOW VARIABLES LIKE 'bind_address';"

# Verify listening ports
sudo netstat -tulnp | grep mysql

# Test remote connection (from another machine)
mysql -h [your-server-ip] -u root -p

When automating MySQL server configuration in provisioning scripts (like for Vagrant boxes), programmatically modifying the bind-address parameter presents unique challenges. Unlike runtime parameters, this setting requires file modification and service restart.

Common but problematic methods include:

# Risky: Appending without checking existing values
echo "bind-address = 0.0.0.0" >> /etc/mysql/my.cnf

# Dangerous: Blind overwrites
sed -i 's/bind-address.*/bind-address = 0.0.0.0/' /etc/mysql/my.cnf

These can cause configuration conflicts or file corruption.

Here's a robust method that handles various my.cnf formats:

#!/bin/bash
CONF_FILE="/etc/mysql/my.cnf"
TEMP_FILE=$(mktemp)

# Process the configuration
{
  # Preserve existing [mysqld] section with modified bind-address
  sed -n '/^$$mysqld$$/,/^\[/p' "$CONF_FILE" | \
    sed '/^bind-address/d;/^$$mysqld$$/a bind-address = 0.0.0.0'
  
  # Include non-[mysqld] sections verbatim
  sed '/^\[mysqld\]/,/^\[/d' "$CONF_FILE"
} > "$TEMP_FILE"

# Validate before applying
if mysql --defaults-file="$TEMP_FILE" --connect-expired-password -e "SELECT 1" &>/dev/null; then
  sudo mv "$TEMP_FILE" "$CONF_FILE"
  sudo systemctl restart mysql
else
  echo "Configuration test failed" >&2
  rm "$TEMP_FILE"
  exit 1
fi

For different environments:

# Ubuntu/Debian with includedir
find /etc/mysql/conf.d/ -name '*.cnf' -exec sed -i '/bind-address/d' {} +
echo "[mysqld]\nbind-address = 0.0.0.0" | sudo tee /etc/mysql/conf.d/bind.cnf

# RHEL/CentOS with override directory
if [ -d /etc/my.cnf.d/ ]; then
  echo "!includedir /etc/my.cnf.d" | sudo tee -a /etc/my.cnf
  echo "[mysqld]\nbind-address = 0.0.0.0" | sudo tee /etc/my.cnf.d/bind.cnf
fi

When opening MySQL to remote connections:

# Automate firewall rules
sudo ufw allow 3306/tcp comment 'MySQL remote access'

# Set up restricted user
mysql -e "CREATE USER 'remote_user'@'%' IDENTIFIED BY 'complex-password'"
mysql -e "GRANT SELECT,INSERT ON db.* TO 'remote_user'@'%'"
mysql -e "FLUSH PRIVILEGES"

Include these checks in your provisioning script:

# Verify binding
ss -tulnp | grep 3306
# Expected: tcp LISTEN 0 70 0.0.0.0:3306

# Test remote connection (from host)
mysql -h [VM_IP] -u remote_user -p -e "SELECT @@bind_address"