Create MySQL Tables with Ansible

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:

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

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;

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

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.

Advertisement

Answer