Creating MySQL Databases With Ansible

Image Courtesy Of Pexels.com

Ansible does a lovely job of deploying MySQL to a server with little fuss, there are one or two issues you might experience but hopefully we’ll be able to iron them out for you in the follow page. If you are new to Ansible, this won’t get too difficult, but we will use a Docker Image running Ansible that we discussed in our previous post “Create Your Own Docker Image Running Ansible”.

The following steps we’ll use to set up our MySQL database will be as follows:

1. Create a hosts file, although not needed in small examples, usually something we do.
2. Create the playbook. This is generally where the magic happens, and will run through the file line by line.
3. Create the SQL file. Not needed, but allows us to create some basic data to implement into the database, to then test later.
4. Test the changes we have made.

So lets not delay and get stuck straight into setting this up.

1.Create A Hosts File

Although not really needed, we prefer to run the playbook using a hosts file which means we can test on our localhost and then change the IP address if we wish to push it to a different environment. We only have one entry in our hosts file below, but feel free to add it to your current hosts file if you have one:

touch hosts
[mysql]
127.0.0.1

2.Create The MySQL Playbook

This is the part that does all the heavy lifting. It installs the applications needed, configures them to look the way we want, creates a test database and then adds some basic data. We need somewhere to house all of this, so start by running the command below to create the file named db-server-playbook.yml:

touch db-server-playbook.yml

Open the new file you have created with a text editor and start by setting up our playbook with the hosts we are going to deploy this to as we have on line 2 , referring specifically to our hosts file we created earlier. We next set up a variable for the mysql_root_password in lines 4 and 5. This is only for demonstration purposes, and in the real world, you would use something like ansible-vault or a command line variable instead of presenting a password in plan text:

NOTE: Don’t add the line numbers at the start of each line as they are simply there to help explain the code.

 1 ---   
 2 - hosts: mysql   
 3   
 4   vars:   
 5     mysql_root_password: password   
 6

Once our variables are in place, we need to start defining out tasks. Below we start with installing mysql using apt to install onto a Ubuntu Docker Image. We could also use the package module or the yum module if we were using a Centos Image:

 7   tasks:   
 8     - name: install mysql     
 9       apt: name=mysql update_cache=yes cache_valid_time=3600 state=present

Next, we set up tasks to make sure the mysql service is running and is enabled to run on startup:

10     - name: start up the mysql service  
11       shell: "service mysql start"  
12     - name: ensure mysql is enabled to run on startup  
13       service: name=mysql state=started enabled=true

We can now configure the password for all the root accounts on the mysql installation. Although mysql is only being installed on one host(Docker Image) it is also referred to in a number of different names including localhost and 127.0.0.1. Each needs to be set up with a mysql_user module and privileges set as we have below using the with_item directive:

14     - name: update mysql root password for all root accounts  
15       mysql_user:  
16         name: root  
17         host: "{{ item }}"  
18         password: "{{ mysql_root_password }}"  
19         login_user: root  
20         login_password: "{{ mysql_root_password }}"  
21         check_implicit_admin: yes  
22         priv: "*.*:ALL,GRANT"  
23       with_items:  
24       - "{{ ansible_hostname }}"  
25       - 127.0.0.1  
26       - ::1  
27       - localhost

Once MySQL has been configured, we can now use the mysql_db module to create a test database in line 32, and then in line 35, import some basic data into the new database from an SQL file that we will create shortly:

28     - name: create a new database  
29       mysql_db: name=testdb state=present login_user=root login_password="{{ mysql_root_password }}"  
30     - name: add sample data to database  
31       copy: src=dump.sql dest=/tmp/dump.sql  
32     - name: insert sample data into database  
33       mysql_db: name=testdb state=import target=/tmp/dump.sql login_user=root login_password="{{ mysql_root_password }}"

Save the playbook and then we can set up our SQL file to create our test data to be imported into the database.

3.Create The SQL File

This is being used by the playbook and will set up some basic information in the database. The table is simple but hopefully gives you an idea on how this can work and be expanded further to automate the creation of tables, schemas and set up your data.

Start by creating a file called dump.sql:

touch dump.sql

Open the file with your text editor and add in the information below to create our new table called test and add in three INSERT rows into the table we have created:

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')

4.Run The Ansible Playbook

If all has been set up correctly, you should now be able to run the playbook from the command line using the ansible-playbook command. Run the command below to run the playbook from the directory it was created in:

ansible-playbook -i hosts db-server-playbook.yml

This is a small playbook with only one package being installed and the database data from the SQL file is also relatively small, so our playbook should complete quickly and be ready for testing.

This is a small playbook with only one package being installed and the database data from the SQL file is also relatively small, so our playbook should complete quickly and be ready for testing.

5.Test The Changes We Have Implemented

If all goes well we should be able to perform some basic testing from the host, to log into MySQL, switch to the testdb database, show all the tables in the database, and finally output all the data in the test table we set up with Ansible:

mysql -u root -h localhost -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 17 
Server version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu)  
mysql> use testdb; 
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_testdb | 
+------------------+ 
| test             | 
+------------------+ 
1 row in set (0.00 sec)  
mysql> select * from test; 
+--------------------+ 
| message            | 
+--------------------+ 
| Ansible To Do List | 
| Get ready          | 
| Ansible is fun     | 
+--------------------+ 
3 rows in set (0.00 sec)

And that’s about it…Not much to it but as you can see with a few lines of Ansible we can have a working database up and running in a matter of minutes.

Check out my new book “Practical Ansible: Configuration Management from Start to Finish” for more information on working with Ansible to streamline your configuration management process.

Found this post useful? Kindly tap the clap button! :)

Vince Sesto is a DevOps Engineer, Endurance Athlete and Author.

As a DevOps Engineer I specialise in Linux, Cloud Services and Open Source Applications. Particularly interested in developing my skills in DevOps, Continuous Integration, Security, Reporting/Observability and Python Development.