13

There should be some decent way to work with mysql databases using ansible like inserting data into tables or any command to run on mysql db.

I know there are modules to create db, manage replications, user and variables:

  • mysql_db - Add or remove MySQL databases from a remote host.
  • mysql_replication (E) - Manage MySQL replication
  • mysql_user - Adds or removes a user from a MySQL database.
  • mysql_variables - Manage MySQL global variables

My use case scenario is, I've installed mysql-server on ubuntu and created the database successfully and now I have to insert data into the tables and wondering if there is a way to achieve it via ansible.

the
  • 21,007
  • 11
  • 68
  • 101
ankit tyagi
  • 778
  • 2
  • 16
  • 27

2 Answers2

29

Solution 1:

I think you missed import functionality of mysql_db module. You can load both schema and data with it using import as parameter to state and giving it a file to load in target

Example from Ansible docs:

# Copy database dump file to remote host and restore it to database 'my_db'
- copy: src=dump.sql.bz2 dest=/tmp
- mysql_db: name=my_db state=import target=/tmp/dump.sql.bz2

Solution 2:

If mysql_db does not give you all options that you need and flexibility you can just use mysql program in combination with shell.

- name: Import DB 
  shell: mysql db_name < dump.sql

Above loads dump.sql file into database db_name. See mysql program manual for more options: man mysql

Solution 3:

mysqlimport utility with command module:

- name: Import DB 
  command: mysqlimport [options] db_name textfile1 [textfile2 ...]

See: mysqlimport docs

Michal Gasek
  • 6,173
  • 1
  • 18
  • 20
  • 1
    Thanks both! appreciate your response! import parameter in mysql_db works well. I also have to update existing .sql in our deployments so working on that now. Thanks again:) – ankit tyagi Jan 29 '15 at 11:10
  • There are some handy tips in the following thread to help avoid importing the same database each time you run a playbook: https://groups.google.com/forum/#!msg/ansible-project/XrQ08TdtcRk/I4s7rF7GkyMJ – Matt V. Sep 24 '15 at 17:37
7

You can use mysql_db module. Its state: parameter has values of present, absent, dump, import. If it is set to import, then you can use target: parameter to indicate location, on the remote host, of the dump file to read from. Uncompressed SQL files (.sql) as well as bzip2 (.bz2) and gzip (.gz) compressed files are supported.

# Copy database dump file to remote host and restore it to database 'my_db'
- copy: src=dump.sql.bz2 dest=/tmp
- mysql_db: name=my_db state=import target=/tmp/dump.sql.bz2
Mxx
  • 8,979
  • 4
  • 27
  • 37
  • Danger: This overwrites any existing database (if the import sql has drops). See https://groups.google.com/forum/#!msg/ansible-project/XrQ08TdtcRk/I4s7rF7GkyMJ – Greg Bell Sep 19 '16 at 03:57