Installing MySQL using Ansible

To install MySQL using Ansible, you need to create a playbook that automates the process of installing and configuring MySQL on one or more target machines. Below are the steps to achieve this.

1. Prerequisites

Before proceeding, ensure that:

  • Ansible is installed on the control machine.
  • You have SSH access to the target machines (remote hosts).
  • You have a configured inventory file listing your target machines.

2. Create the Ansible Playbook

Let’s create a playbook that installs MySQL on Ubuntu. If you are using other distributions (like CentOS or RedHat), you will need to adjust the package manager accordingly (apt vs. yum).

2.1 For Ubuntu (Debian-based systems)

Create a playbook file called install_mysql.yml:

---
- name: Install MySQL on Ubuntu
  hosts: all
  become: yes  # Run with sudo privileges
  tasks:
    - name: Update apt cache
      apt:
        update_cache: yes
        cache_valid_time: 3600  # Cache validity time in seconds

    - name: Install MySQL server
      apt:
        name: 
          - mysql-server
        state: present

    - name: Ensure MySQL service is started
      service:
        name: mysql
        state: started
        enabled: yes

    - name: Install Python MySQL Support 
      apt:
        name: python3-mysqldb 
        state: present

    - name: Sets the root password
      mysql_user: user=root password="Secure_Password" host=localhost
      no_log: yes

    - name: Deletes anonymous MySQL server user
      mysql_user: user="" state="absent" login_user="root" login_password="Secure_Password"

    - name: Removes the MySQL test database
      mysql_db: db=test state=absent login_user="root" login_password="Secure_Password"

Explanation of the Playbook:

  • apt: The apt module is used to install MySQL on Ubuntu.
  • service: Ensures that the MySQL service is started and enabled to start on boot.
  • mysql_user: A special Ansible module which manages user accounts in MySQL
  • mysql_db: A Special Ansible module which manages DBs in MySQL

2.2 For CentOS/RHEL/Amazon Linux (RPM-based systems)

For CentOS or RHEL-based systems, we will need to use the yum or dnf package manager, depending on the system.

Here is the playbook for CentOS-based systems:

---
- name: Install MySQL on CentOS
  hosts: all
  become: yes  # Run with sudo privileges
  tasks:
    - name: Get MySQL repo yum
      get_url:
        headers:
          user-agent: curl/7.81.0          
        url: https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
        dest: /tmp/mysql84-community-release-el9-1.noarch.rpm
        mode: 755

    - name: Install MySQL repo (if needed)
      dnf:
        name: /tmp/mysql84-community-release-el9-1.noarch.rpm 
        state: present
        disable_gpg_check: true 

    - name: Install MySQL server
      dnf:
        name: mysql-server
        state: present

    - name: Start MySQL service
      service:
        name: mysqld
        state: started
        enabled: yes

    - name: Get temporary MySQL root password
      shell: grep 'temporary password' /var/log/mysqld.log | tail -1 | awk '{print $NF}'
      register: mysql_temp_password
      changed_when: false

    - name: Install MySQL support for python
      dnf:
        name: python3-PyMySQL
        state: present

    - name: Set new password from temporary password
      shell: mysql --user="root" --password="{{ mysql_temp_password.stdout }}" --connect-expired-password --execute="ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Secure_Password1';"
      no_log: yes
      ignore_errors: yes

    - name: Deletes anonymous MySQL server user
      mysql_user: user="" state="absent" login_user="root" login_password="Secure_Password1"

    - name: Removes the MySQL test database
      mysql_db: db=test state=absent login_user="root" login_password="Secure_Password1"

Explanation of the CentOS Playbook:

  • dnf: Installs the MySQL server package on CentOS.
  • service: Ensures that MySQL is started and enabled to run on boot.
  • mysql_user: A special Ansible module which manages user accounts in MySQL
  • mysql_db: A Special Ansible module which manages DBs in MySQL
  • shell: Extracts the temporary root password from MySQL’s log file for use in the mysql_secure_installation module.

3. Create an Inventory File

Create a file called inventory.ini to specify the target machines:

[mysql_servers]
192.168.1.100 ansible_user=your_ssh_user
192.168.1.101 ansible_user=your_ssh_user

Replace your_ssh_user with the actual SSH user and list the IP addresses or hostnames of your target servers.

4. Run the Playbook

To execute the playbook, use the following command from the control machine:

ansible-playbook -i inventory.ini install_mysql.yml

5. Verify the Installation

Once the playbook runs successfully, you can verify that MySQL is installed and running on the target servers.

To check the MySQL service:

systemctl status mysql   # For Ubuntu
systemctl status mysqld  # For CentOS/RHEL

You can also log in to MySQL to check the installation:

mysql -u root -p

Enter the root password set during the playbook execution.


6. Optional Configuration (Post Installation)

After MySQL is installed, you may want to configure:

  • Setting up databases and users
  • Configuring MySQL replication
  • Adjusting MySQL configuration (e.g., in my.cnf)

Here’s an example task to create a database and user:

    - name: Create a new database
      mysql_db:
        name: my_database
        state: present

    - name: Create a new MySQL user
      mysql_user:
        name: my_user
        password: my_password
        priv: 'my_database.*:ALL'
        state: present

This creates a database my_database and a user my_user with full privileges on the database.


Conclusion

By using Ansible to install MySQL, you automate the process of setting up MySQL on multiple servers, ensuring a consistent environment. This approach saves time, reduces the potential for errors, and provides an easy way to scale the installation to multiple machines.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top