A shell script to install MySQL server on Ubuntu VPS with specified user and password.

mysql_installer.sh

#!/bin/bash

# Check if MySQL is already installed
if ! command -v mysql &> /dev/null; then
  echo "MySQL is not installed. Installing MySQL Server..."
  apt update
  apt install -y mysql-server
else
  echo "MySQL is already installed."
fi

# Run MySQL secure installation (optional step to improve security)
echo "Running MySQL secure installation..."
mysql_secure_installation

# Read user input for MySQL username and password
read -p "Enter the MySQL username you want to create: " MYSQL_USER
read -sp "Enter the password for $MYSQL_USER: " MYSQL_PASSWORD
echo

# Log in to MySQL and create the user with full privileges
echo "Creating MySQL user and granting privileges..."
mysql -e "
CREATE USER IF NOT EXISTS '$MYSQL_USER'@'%' IDENTIFIED BY '$MYSQL_PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO '$MYSQL_USER'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
"

# Allow remote connections by updating the bind address
echo "Configuring MySQL to allow remote connections..."
sed -i "s/^bind-address\s*=.*/bind-address = 0.0.0.0/" /etc/mysql/mysql.conf.d/mysqld.cnf

# Restart MySQL service to apply changes
echo "Restarting MySQL service..."
systemctl restart mysql

# Confirm changes
echo "MySQL setup complete. User '$MYSQL_USER' with remote access has been created."

Explanation of the Script

1. Check MySQL Installation: It first checks if MySQL is installed. If not, it installs it.
2. Secure Installation: The script runs mysql_secure_installation to help secure the MySQL setup.
3. User Input for Username and Password: Prompts the user for the username and password for the new MySQL user.
4. Create User and Grant Privileges: Creates the new user with full privileges to all databases and allows connections from any host ('%').
5. Enable Remote Connections: Modifies MySQL’s bind-address to 0.0.0.0, allowing connections from any IP address.
6. Restart MySQL: Restarts MySQL to apply the changes.

Usage

1. Save the script as mysql_installer.sh.
2. Make it executable:

   chmod +x mysql_installer.sh

3. Run the script:

   sudo ./mysql_installer.sh

This script will install MySQL, create a user with full remote access, and configure MySQL to accept remote connections.