Create MySQL Tables with Ansible

Question

I'm using ansible to manage a small mail server using ubuntu. I wanted to use ansible to create a database which I can do and also create users for the database(s) which I can do also. But I'm not sure how to create tables using ansible. I'm trying to create the following three MySQL tables using ansible:

1)

CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2)

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(106) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3)

CREATE TABLE `virtual_aliases` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `source` varchar(100) NOT NULL,
  `destination` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have searched and searched and even ask in #ansible and have stated that I can use the mysql_db module to complete the above task but I can't find any examples that will give me some type of direction on how to achieve the above in ansible.

Any and all help would be GREATLY appreciated!

Answer 1

With the mysql_db module you can import a MySQL file. So you can simply copy all 3 create statements into one singly text file and import it like this:

- mysql_db: name=my_db state=import target=/tmp/dump.sql.bz2

That example is taken from the above linked docs page, there are more tasks including one which shows you how to copy the file to the host before.