How to copy MySQL databases using Ansible

In this guide, you will learn how to copy a MySQL database from one remote server to another using Ansible as the controller. Database migration in the world of DevOps is a challenging job but with Ansible this is can be very accomplished.

Ansible provides a featureful MySQL module. With the help of the module, we could copy databases between two hosts, create regular backups, users, and many more database operations.

Assumptions:

• Ansible Controller can successfully connect to both Server A and Server B.
• Server A can SSH into Server B.
• Both servers have MySQL server working.

Database Migration

The diagram below shows the steps involved in a MySQL database migration. The Ansible Controller is where our playbook is executed. Will use the mysql_db module to create a dump of the database. Using rsync we will copy the database to the other server. After successfully copying the database will use the mysql_db module to import the database.

Let’s assume that we need to migrate the database “wordpress” which is hosted in the Production inventory to our development server called (neo_development).

---
  - hosts: production
    become: true
    tasks:
        
      - name: create a backup
        mysql_db:
          name: wordpress
          state: dump
          target: /var/www/html/wordpress.sql
          login_host: localhost
          login_user: root
          login_password: 123456
 
 
      - name: copy the database to the development server
        raw: rsync /var/www/html/wordpress.sql root@192.168.0.69:/home/vagrant/wordpress.sql
 
      - name: import the database
        mysql_db:
          name: wordpress
          state: import
          target: /home/vagrant/wordpress.sql
          login_host: 192.168.0.69
          login_user: root
          login_password: '123456'
        delegate_to: neo_development

In this case, we will set the host to be “production“, this is because we want to store the backup file on the production server, not on the Ansible Controller.

Store your credentials in Ansible vaults!

The raw module lets us execute a raw shell command. Using rsync will transfer the backup to the development server (neo_development). In the final step, we will import the database.

The important note in step 3 is “delegate_to“. Delegate_to lets us run the command on the remote host machine.

Below is the inventory file which we have used in our example to let Ansible identify production and development machines.

[production]
centos ansible_host=192.168.0.18 ansible_user=root
 
[development]
neo_development ansible_host=192.168.0.15 ansible_user=root