Practical Implementation of MySQL Deployment with Ansible

1. Overview of Ansible

Ansible is an open-source automation tool for IT operations, created by Michael DeHaan in 2012 and acquired by Red Hat in 2015 for over $100 million. It is developed in Python and manages remote nodes via SSH protocol without the need to install any client agents on the controlled end (Agentless). This design makes it a lightweight and easy-to-deploy automation solution, particularly suitable for scenarios such as batch system configuration, application deployment, and task orchestration.

Core Features

Agentless Architecture: Directly manages nodes via SSH, reducing environmental complexity.

Declarative Language: Uses YAML format Playbooks to describe tasks, lowering the learning curve.

Modular Design: Provides over 3000 built-in modules (such as copy, service), supporting custom modules in Python.

Idempotency: The same task yields consistent results when executed multiple times, avoiding unintended changes.

2. Installation and Configuration

1. Installation Steps (Example for CentOS)

# Install EPEL repository
yum install epel-release -y
# Install Ansible
yum install ansible -y
# Verify installation
ansible --version

2. Key Configuration Host Inventory (Example configuration for /etc/ansible/hosts):

[web_servers]
192.168.95.11 ansible_ssh_port=22 ansible_ssh_user=root
192.168.95.12

[db_servers]
192.168.95.13

[all:vars]
ansible_ssh_private_key_file=/root/.ssh/id_rsa

SSH Passwordless Login Setup

ssh-keygen -t rsa -P ""
ssh-copy-id [email protected]  # Distribute public key to all nodes

Configuration File (/etc/ansible/ansible.cfg) Tuning Options

forks = 50             # Number of concurrent connections
host_key_checking = False  # Skip SSH fingerprint verification
log_path = /var/log/ansible.log

2. Playbook Writing Example

—

- name: Deploy Web Application
  hosts: web_servers
  vars:
    app_version: "2.0"
  tasks:
    - name: Install Nginx
      yum: 
        name: nginx 
        state: latest
    
    - name: Copy Configuration File
      copy:
        src: templates/nginx.conf.j2 
        dest: /etc/nginx/nginx.conf
        backup: yes
      notify: Restart Nginx  # Trigger handler
    
    - name: Start Service
      service:
        name: nginx
        state: started
        enabled: yes
  
  handlers:
    - name: Restart Nginx
      service:
        name: nginx
        state: restarted

This Playbook implements the full process of application deployment → configuration update → service start, and includes a rollback mechanism (backup) and service restart linkage.

3. Now, let’s start deploying MySQL 5.7

I will use a MySQL script to transfer to the target host and execute this script, which is relatively simple and efficient for configuration.

3.1. Create File

Playbook file (deploy_mysql.yml)

—

- name: Deploy MySQL 5.7 to Target Host
  hosts: target_server
  become: yes
  vars:
    src_dir: /usr/local/
  tasks:
    - name: Upload MySQL Installation Script
      copy:
        src: mysql5.7.sh
        dest: "{{ src_dir }}/mysql5.7.sh"
        mode: 0755
    - name: Upload MySQL Installation Package
      copy:
        src: mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
        dest: "{{ src_dir }}/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz"
    - name: Execute Installation Script
      command: "{{ src_dir }}/mysql5.7.sh"
      args:
        chdir: "{{ src_dir }}"
      async: 600
      poll: 0

3.2. Usage Method

Create Inventory File

Create hosts.ini file:

[target_server]
192.168.1.128

[target_server:vars]
ansible_user=your_username  # Replace with the username for the target host
ansible_ssh_private_key_file=~/.ssh/id_rsa  # Replace with the SSH key path
ansible_become_password=your_sudo_password  # If sudo password is required

3.3. Prepare Files Ensure the following files are in the same directory as the Playbook: mysql5.7.sh (MySQL installation script) mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz (MySQL binary package)

3.4. Run Playbook

ansible-playbook -i hosts.ini deploy_mysql.yml

3.5. Installation Script Example (mysql5.7.sh)

#!/bin/bash
set -e
SRC_DIR=/usr/local/src
MYSQL_DIR=/usr/local/mysql
DATA_DIR=/data/mysql
# Unpack installation package
tar zxvf $SRC_DIR/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
mv /usr/local/mysql-5.7.44-linux-glibc2.12-x86_64 $MYSQL_DIR
# Create MySQL user
if ! id mysql >/dev/null; then    useradd -M -s /sbin/nologin mysql
fi
# Create data directory
mkdir -p $DATA_DIR
chown -R mysql:mysql $DATA_DIR
# Initialize MySQL
$MYSQL_DIR/bin/mysqld --initialize-insecure --user=mysql --basedir=$MYSQL_DIR --datadir=$DATA_DIR
# Create configuration file
cat > /etc/my.cnf <<EOF
[mysqld]
basedir = $MYSQL_DIR
datadir = $DATA_DIR
port = 3306
socket = /tmp/mysql.sock
EOF
# Create systemd service
cat > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=$MYSQL_DIR/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
EOF
# Start MySQL
systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
echo "MySQL 5.7 installation completed!"

Notes Script Permissions: Ensure mysql5.7.sh has executable permissions (chmod +x mysql5.7.sh) Important Security Note: The initialization command –initialize-insecure will create a root account with an empty password. In a production environment, you must run mysql_secure_installation after installation. Custom Adjustments: Modify the DATA_DIR variable to specify the data storage location and add additional configuration parameters in my.cnf.

Conclusion: Adjust parameters according to actual conditions, configure passwordless access, target host settings, orchestration configurations, and finally run and wait for the installation to complete.