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.
- 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.
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 [email protected]:/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
Ansible speeds up your daily manual tasks by automating it. Database migration can be automated and this reduces the chances of making errors. In some cases, there are post-migration scripts that execute certain MySQL operations on the imported databases.
If you are looking to find out best practices using Ansible, read this article.
I’m a passionate engineer based in London.
Currently, I’m working as a Cloud Consultant at Contino.
Aside my full time job, I either work on my own startup projects or you will see me in a HIIT class 🙂