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
: Theapt
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 MySQLmysql_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 MySQLmysql_db
: A Special Ansible module which manages DBs in MySQLshell
: Extracts the temporary root password from MySQL’s log file for use in themysql_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.