Search for a command to run...
Explore the power of Ansible through this guide on automating MySQL database setup making database management easier, faster, and error-free.
Let's learn together and serve the society, Make India Proud.
No comments yet. Be the first to comment.
Welcome to our series on Ansible Automation! We'll explore how Ansible, an open-source IT automation engine, simplifies complex tasks, increasing productivity and efficiency in IT operations.
Elevate Your Mastery: Dive into Ansible Projects ideas, From Beginner to Advanced, and Master the Art of Automation!
How Real Teams Deploy to Production: Kubernetes Deployment Strategies
Hello, this is Rakesh Kumar — your DevOps project practice trainer and your friend.I’m back again with a brilliant DevOps project that is not only production-ready but also highly valuable for real-world learning. This project is designed to take you...
Introduction If you are learning DevOps, you may be asking yourself: 👉 “I already know Bash scripting. Do I really need Python? Can’t I do the same work with Bash?” This is a common question, and the truth is: No, you don’t always need Python – Bas...
Project Idea – Build Your Own Mini Shell
Why every DevOps engineer needs Python in their toolkit
MySQL is a widely used open-source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and managing content in a database. It’s known for its quick processing, proven reliability, ease and flexibility of use.
Ansible, on the other hand, is an open-source software provisioning, configuration management, and application-deployment tool. It provides large productivity gains to a wide variety of automation challenges, including setting up databases.
When it comes to automating MySQL database setup and management, Ansible shines in several ways:
Simplicity: Ansible uses a simple syntax written in YAML called playbooks. These playbooks are easy to write, read, and understand, even for those not familiar with the Ansible tool.
Efficiency: Ansible allows you to automate the process of setting up and configuring MySQL databases, which can be a time-consuming and error-prone task if done manually.
Consistency: By using Ansible, you ensure that your MySQL databases are set up and configured consistently across all your environments.
Scalability: Ansible can easily scale to manage many databases, making it a great choice for large-scale deployments.
The combination of MySQL and Ansible provides a powerful toolset for managing databases efficiently and consistently. Whether you’re a database administrator looking to automate routine tasks or a developer wanting to ensure consistent database setup across multiple environments, Ansible and MySQL can make your life much easier. So lets create ansible playbook for configuring and automating MySQL database on your host machine.
[ansible@master ~]$ vim mysql.yml
---
- name: Configure MySQL database using Ansible automation
hosts: localhost
vars:
db_username: ram
db_name: wordpress
mysql_root_password: password
tasks:
- name: Install pip3
ansible.builtin.yum:
name: python3-pip
state: present
update_cache: yes
- name: Install Python MySQL module
ansible.builtin.pip:
name: PyMySQL
state: present
- name: Install MySQL
ansible.builtin.yum:
name: mysql
state: latest
notify: restart mysqld
- name: Create database user with password and all database privileges and 'WITH GRANT OPTION'
community.mysql.mysql_user:
name: "{{ db_username }}"
password: "{{ mysql_root_password }}"
priv: '*.*:ALL,GRANT'
state: present
- name: Create a new database "{{ db_name }}"
community.mysql.mysql_db:
login_user: "{{ db_username }}"
login_password: "{{ mysql_root_password }}"
name: "{{ db_name }}"
state: present
- name: Add sample database to the database
copy:
src: ./dump.sql
dest: /tmp/dump.sql
- name: insert sample database into database
community.mysql.mysql_db:
name: "{{ db_name }}"
state: import
target: /tmp/dump.sql
login_user: "{{ db_username }}"
login_password: "{{ mysql_root_password }}"
handlers:
- name: restart mysqld
ansible.builtin.service:
name: mysqld
state: restarted
enabled: yes
[ansible@master ~]$ vim dump.sql
CREATE TABLE IF NOT EXISTS test (
message varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO test(message) VALUES('Ansible To Do List');
INSERT INTO test(message) VALUES('Get ready');
INSERT INTO test(message) VALUES('Ansible is fun');
INSERT INTO test(message) VALUES('Learn Ansible');
INSERT INTO test(message) VALUES('Setup Ansible');
INSERT INTO test(message) VALUES('Test Ansible setup');
Let's check whether our MySQL Database is configured correctly or not. This is the output result.
[ansible@master ~]$ mysql -u ram -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.32 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
mysql> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_wordpress |
+---------------------+
| test |
+---------------------+
1 row in set (0.00 sec)
mysql> describe test
-> ;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| message | varchar(255) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select * from test;
+--------------------+
| message |
+--------------------+
| Ansible To Do List |
| Get ready |
| Ansible is fun |
| Learn Ansible |
| Setup Ansible |
| Test Ansible setup |
+--------------------+
6 rows in set (0.00 sec)
mysql>
Let's understand this playbook in the description step by step. This Ansible playbook is designed to configure a MySQL database on localhost. Here’s a breakdown of what each part does:
Variables: The playbook defines three variables: db_username, db_name, and mysql_root_password. These are used later in the playbook to set up the MySQL database.
Tasks: The playbook then performs a series of tasks:
Install pip3: This task installs pip3, a package manager for Python, using the ansible.builtin.yum module.
Install Python MySQL module: This task uses pip3 to install the PyMySQL module, which is a Python interface for MySQL.
Install MySQL: This task installs the latest version of MySQL using the ansible.builtin.yum module. If the installation is successful, it triggers a handler to restart the MySQL service.
Create database user: This task creates a new MySQL user with the username and password specified by the db_username and mysql_root_password variables. The user is granted all privileges on all databases.
Create a new database: This task creates a new MySQL database with the name specified by the db_name variable.
Add sample database to the database: This task copies a sample database file (dump.sql) to the /tmp directory on the target host.
Insert sample database into database: This task imports the sample database into the newly created database.
Handlers: The playbook includes a handler to restart the MySQL service. This handler is triggered if the MySQL installation task is successful.
This playbook provides a comprehensive example of how to automate the process of setting up a MySQL database using Ansible. It demonstrates the use of variables, tasks, and handlers in an Ansible playbook. It also shows how to use different Ansible modules to perform tasks such as installing packages, creating users and databases, and importing data.
If any issues are found in this code, please comment, so we can fix them as soon as possible. Thank you.
#Ansible #MySQL #DatabaseConfiguration #DevOps #Automation #InfrastructureAsCode #IAC #Playbook #RelationalDatabase #RDBMS #ConfigurationManagement #ITAutomation #CloudComputing #OpenSource #Tech