Início / server / Perguntas / 1170270
Accepted
Jorge Mauricio
Asked: 2025-01-18 07:54:31 +0800 CST

Ubuntu 22.04 - Instalar MySQL 5.7 - Script de automação

  • 772

Estou trabalhando em uma plataforma legada que estamos automatizando a implantação. Ela exigirá várias iterações até chegar ao ponto em que usamos na produção. No momento, estou na etapa de instalação e configuração do MySQL 5.7 básico.

Ambiente

  • Ubuntu 22.04
  • AWS

Neste estágio, é um script Bash Script que rodamos localmente e ele configura tudo no servidor remoto. Mais tarde, converteremos para ações do GitHub e essa estratégia funcionou no passado para nós, então vamos nos ater à arquitetura inicial.

Esta é a prova de conceito funcional que executei manualmente no servidor e funcionou perfeitamente:

# 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();";

Até agora, foi assim que converti para execução local no script automatizado:

# 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.";

Os erros do log do terminal são mais ou menos assim:

# 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.

Notas importantes:

  • As variáveis ​​de ambiente CONFIG_SERVER_MYSQL_ROOT_PASSWORDdevem ser capazes de armazenar senhas complexas, como:exEm!@123
  • no arquivo .env, a variável de ambiente é definida como CONFIG_SERVER_MYSQL_ROOT_PASSWORD='exEm!@123' (com aspas simples)

Eu já fiz toneladas de passos automatizados como este. Aqui está um exemplo de passos de trabalho:

# 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!";

(Apenas mostrando uma simples para ter uma ideia de como a sintaxe para incorporar as variáveis ​​.env funciona perfeitamente)

Alguma ideia que eu possa tentar para corrigir a sintaxe?

linux

1 respostas

  1. Best Answer

    Consegui fazê-lo funcionar, mas não 100% como eu pretendia com o uso de variáveis ​​.env inline. Foi assim que ficou.

    # Here is where the .env variables get loaded.
# In the .env file, the variable is configured like this (attention to the single quotes): CONFIG_SERVER_MYSQL_ROOT_PASSWORD='exEm!@123'
source "$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/single-server-load-env.sh";

BACKEND_HOST_SERVER_SH_SCRIPT='
    # Firewall configuration. \
    sudo ufw allow 3306/tcp; \

    # Download MySQL APT configuration package. \
    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; \

    # Add MySQL key. \
    sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C; \

    # Add 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; \

    # Store password in a variable with proper escaping. \
    MYSQL_ROOT_PASSWORD='${CONFIG_SERVER_MYSQL_ROOT_PASSWORD}'; \

    # Debug.
    # echo "MySQL password (debug): $MYSQL_ROOT_PASSWORD"; \

    # Set root password non-interactively. \
    sudo debconf-set-selections <<< "mysql-server mysql-server/root_password password $MYSQL_ROOT_PASSWORD"; \
    sudo debconf-set-selections <<< "mysql-server mysql-server/root_password_again password $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; \

    # Configure mysqld directory. \
    sudo mkdir -p /var/run/mysqld; \
    sudo chown mysql:mysql /var/run/mysqld; \
    sudo chmod 777 /var/run/mysqld; \

    # Create a temporary file for MySQL commands. \
    cat > /tmp/mysql_setup.sql << EOF
FLUSH PRIVILEGES;
USE mysql;
UPDATE user SET authentication_string=PASSWORD('"'"'$MYSQL_ROOT_PASSWORD'"'"'), plugin="mysql_native_password" WHERE User="root";
FLUSH PRIVILEGES;
EOF

    # Debug - view the content of the SQL file. \
    # echo "SQL file contents (debug):"; \
    # cat /tmp/mysql_setup.sql; \

    # Start MySQL in safe mode. \
    sudo mysqld_safe --skip-grant-tables --skip-networking & \
    sleep 10; \

    # Execute MySQL commands from file. \
    mysql --user=root < /tmp/mysql_setup.sql; \

    # Clean up. \
    rm -f /tmp/mysql_setup.sql; \

    # Stop MySQL safe mode. \
    sudo pkill mysqld; \
    sleep 10; \

    # Start MySQL normally. \
    sudo systemctl start mysql; \
    sleep 5; \

    # Create a file to test MySQL connection. \
    cat > /tmp/mysql_test.sql << EOF
SELECT VERSION();
SELECT CURRENT_USER();
SELECT user,authentication_string,plugin from mysql.user where user="root";
EOF

    # Test the connection using the password. \
    echo "Testing MySQL connection..."; \
    mysql --user=root --password="$MYSQL_ROOT_PASSWORD" < /tmp/mysql_test.sql; \
    
    # Clean up test file. \
    rm -f /tmp/mysql_test.sql; \

    # Update MySQL configuration. \
    sudo mkdir -p /etc/mysql/mysql.conf.d; \
    sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf << EOF

# Allow root login with password.
[mysqld]
skip-grant-tables = 0
EOF

    # Restart MySQL one final time. \
    sudo systemctl restart mysql; \
    sleep 5; \

    # Final verification. \
    echo "Performing final verification..."; \
    mysql --user=root --password="$MYSQL_ROOT_PASSWORD" -e "SELECT 1 AS final_verification;"; \
';

# 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.";

    Se alguém tiver uma solução mais suave para a integração das variáveis ​​.env inline, eu preferiria.

    • 0

relate perguntas