how to create a large database with Ansible

Question

Suppose you have a sql-file database.sql, which creates the database schema, the table within it and some initial filling. Normally, I can use ansible to make this database by:

---

- name: copy database.sql to server
  template: src=database.sql dest=/tmp/database.sql owner=root group=root

- name: sql the thing into MySQL
  command: mysql "-u root -p{{ mysql_root_password }} < /tmp/database.sql"

- name: remove the sql file from the server
  command: rm -f /tmp/database.sql

and this does exactly what it says. But when database.sql is large (perhaps 2 TByte) you really don't want the copying action first. Are there ways to refer to database.sql as a file on the ansible-master server (where we push it from) such that you can do a mysql -u root@master -p... < "local file" such that the copy action isn't needed anymore ?

Answer 1

Whatever you do, data must flow from where it is to the database server. Note that in your example you should have used copy instead of template : templates are being parsed by Jinja, and parsing big files is very costly.

That being said, you might want to use local_action and remotely feed your mysql server :

- name: feed database.sql to server
  local_action: shell mysql -u root -p{{ mysql_root_password }} -h {{ ansible_default_ipv4 }} < /tmp/database.sql

This would run the command locally and should work if :

• your DB dump is in /tmp/database.sql on the localmachine
• /tmp/database.sql contains database creation statements
• you can remotely connect to your mysql server (hint: check rights + bind_address)

To make things a bit cleaner, you could use the mysql_db module :

- name: feed database.sql to server
  local_action: mysql_db login_user=root login_password={{ mysql_root_password }} login_host={{ ansible_default_ipv4 }} name={{ db_name }} state=import target=/tmp/database.sql