ankit tyagi ankit tyagi - 7 months ago 214
MySQL Question

Insert data into mysql tables using ansible

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
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.


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