I'm working on a legacy platform that we're automating the deploy. It will require multiple iterations until it gets to the point that we use in production. Right now, I'm in the step of setting up and configuring the basic MySQL 5.7.
Environment
- Ubuntu 22.04
- AWS
At this stage, it's a script Bash Script that we run locally and it configures everything in the remote server. Later, we'll convert to GitHub actions and this strategy has worked in the past for us, so let's stick to the initial architecture.
This is the working proof-of-concept that I ran manually in the server and worked perfectly:
# Firewall configuration.
sudo ufw allow 3306/tcp;
sudo wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb;
echo "mysql-apt-config mysql-apt-config/select-server select mysql-5.7" | sudo debconf-set-selections;
echo "mysql-apt-config mysql-apt-config/select-product select Ok" | sudo debconf-set-selections;
# Install MySQL APT configuration package
sudo DEBIAN_FRONTEND=noninteractive dpkg -i mysql-apt-config_0.8.12-1_all.deb;
# Download and add the key directly using the key ID
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C;
# Add the MySQL repository
echo "deb http://repo.mysql.com/apt/ubuntu bionic mysql-5.7" | sudo tee /etc/apt/sources.list.d/mysql.list;
sudo apt-get update;
# Set root password non-interactively
export CONFIG_SERVER_MYSQL_ROOT_PASSWORD='exEm!@123'; # This will be set in the environment variable that I'll show later.
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password password '${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}'";
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password_again password '${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}'";
# Install MySQL 5.7
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-community-server=5.7* mysql-client=5.7*;
# Stop MySQL
sudo systemctl stop mysql;
# Created MySQL directory and set permissions
sudo mkdir -p /var/run/mysqld;
sudo chown mysql:mysql /var/run/mysqld;
sudo chmod 777 /var/run/mysqld;
# Start MySQL in safe mode
sudo mysqld_safe --skip-grant-tables --skip-networking &
# Wait for MySQL to start
sleep 5;
# Execute MySQL commands
mysql --user=root << EOF
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}';
FLUSH PRIVILEGES;
EOF
# Stop MySQL in safe mode
sudo pkill mysqld;
sleep 5;
# Start MySQL normally
sudo systemctl start mysql;
# Check if the password worked:
mysql -u root -p'sistemA!@2131' -e "SELECT 1;";
# Check verion:
mysql -u root -p"${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}" -e "SELECT VERSION();";
So far, this is how I converted to run locally in the automated script:
# Here is where the .env variables get loaded.
source "$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/single-server-load-env.sh";
BACKEND_HOST_SERVER_SH_SCRIPT='
# Firewall configuration. \
sudo ufw allow 3306/tcp; \
# Update package lists \
sudo wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb; \
# Set non-interactive mode. \
echo "mysql-apt-config mysql-apt-config/select-server select mysql-5.7" | sudo debconf-set-selections; \
echo "mysql-apt-config mysql-apt-config/select-product select Ok" | sudo debconf-set-selections; \
# Install MySQL APT configuration package. \
sudo DEBIAN_FRONTEND=noninteractive dpkg -i mysql-apt-config_0.8.12-1_all.deb; \
# Download and add the key directly using the key ID. \
# Note: The key is deprecated and may not work somewhere in the future, so be sure to always check if it iss up to date. \
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C; \
# Add the MySQL repository. \
echo "deb http://repo.mysql.com/apt/ubuntu bionic mysql-5.7" | sudo tee /etc/apt/sources.list.d/mysql.list; \
# Update package lists. \
sudo apt-get update; \
# Set root password non-interactively. \
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password password '"$CONFIG_SERVER_MYSQL_ROOT_PASSWORD"'"; \
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password_again password '"$CONFIG_SERVER_MYSQL_ROOT_PASSWORD"'"; \
# Install MySQL 5.7. \
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-community-server=5.7* mysql-client=5.7*; \
# Stop MySQL \
sudo systemctl stop mysql; \
# Created MySQL directory and set permissions. \
sudo mkdir -p /var/run/mysqld; \
sudo chown mysql:mysql /var/run/mysqld; \
sudo chmod 777 /var/run/mysqld; \
# Start MySQL in safe mode and wait for MySQL to start. \
sudo mysqld_safe --skip-grant-tables --skip-networking &
sleep 5; \
# This next block is probably where I am having syntax issues:
# Execute MySQL commands. \
mysql --user=root << EOF
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}';
FLUSH PRIVILEGES;
EOF
# Did not work
# Also tried to escape some single quotes but did not work
# Execute MySQL commands. \
mysql --user=root -e "FLUSH PRIVILEGES;"; \
mysql --user=root -e "UPDATE mysql.user SET authentication_plugin='mysql_native_password', Password=PASSWORD('${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}') WHERE User='root';"; \
mysql --user=root -e "FLUSH PRIVILEGES;"; \
# Did not work
# Execute MySQL commands. \
mysql --user=root -e "FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}'; FLUSH PRIVILEGES;"; \
# Did not work
# Stop MySQL in safe mode. \
sudo pkill mysqld; \
sleep 5; \
# Start MySQL normally. \
sudo systemctl start mysql; \
# This block I am also having problems.
# Check if the password worked: \
mysql -u root -p'"$CONFIG_SERVER_MYSQL_ROOT_PASSWORD"' -e "SELECT 1;";
# This one too.
# Check verion: \
mysql -u root -p"'"$CONFIG_SERVER_MYSQL_ROOT_PASSWORD"'" -e "SELECT VERSION();";
';
# Write the private key to a temporary file
echo -e "$CONFIG_SERVER_PUBLIC_KEY" > id_rsa_server_private_key_temp.pem;
chmod 600 id_rsa_server_private_key_temp.pem;
# Execute the script on the server
ssh -v -t -t -i id_rsa_server_private_key_temp.pem \
-o ConnectTimeout=300 \
-o StrictHostKeyChecking=no \
ubuntu@"$CONFIG_SERVER_BACKEND_IP" "$BACKEND_HOST_SERVER_SH_SCRIPT";
# Remove the temporary private key
rm id_rsa_server_private_key_temp.pem;
echo "Status check: Basic MySQL 5.7 installation completed.";
Terminal log errors look something like this:
# More informatio, but mostly successful.
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.8) ...
NEEDRESTART-VER: 3.5
NEEDRESTART-KCUR: 6.5.0-1022-aws
NEEDRESTART-KEXP: 6.5.0-1022-aws
NEEDRESTART-KSTA: 1
2025-01-17T23:50:05.205820Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2025-01-17T23:50:05.233249Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded
2025-01-17T23:50:11.399290Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: client_input_channel_req: channel 0 rtype [email protected] reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to 123.123.123.1 closed.
Transferred: sent 6380, received 26264 bytes, in 39.7 seconds
Bytes per second: sent 160.7, received 661.7
debug1: Exit status 1
Status check: Basic MySQL 5.7 installation completed.
Important Notes:
- The environment variables
CONFIG_SERVER_MYSQL_ROOT_PASSWORD
should be able to hold complex passwords, such as:exEm!@123
- in the .env file, the environment variable is defined as CONFIG_SERVER_MYSQL_ROOT_PASSWORD='exEm!@123' (with single quotes)
I've done tons of automated steps like this. Here's an example of working steps:
# Here is where the .env variables get loaded.
source "$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/single-server-load-env.sh";
# Step: Create directory for the application (host server)
BACKEND_HOST_SERVER_SH_SCRIPT=' \
# Create application directory \
sudo mkdir -p /var/www/'"$APP_URL"'; \
sudo mkdir -p /var/www/'"$APP_URL"'/public; \
# Set ownership to root \
sudo chown root:root /var/www/'"$APP_URL"'; \
# Set directory permissions to 755 \
sudo chmod 755 /var/www/'"$APP_URL"'; \
# echo "Application directory created and permissions set.";
';
# Write the private key to a temporary file
echo -e "$CONFIG_SERVER_PUBLIC_KEY" > id_rsa_server_private_key_temp.pem;
chmod 600 id_rsa_server_private_key_temp.pem;
# Execute the SFTP configuration script on the server
ssh -v -t -t -i id_rsa_server_private_key_temp.pem \
-o ConnectTimeout=300 \
-o StrictHostKeyChecking=no \
ubuntu@"$CONFIG_SERVER_BACKEND_IP" "$BACKEND_HOST_SERVER_SH_SCRIPT";
# Remove the temporary private key
rm id_rsa_server_private_key_temp.pem;
echo "Status check: Application directory created and configured successfully!";
(Just showing a simple one to get a sense of how the syntax for incorporating the .env variables work perfectly)
Any ideas I can try out to fix the syntax?