使用Ansible将数据插入mysql表 [英] Insert data into mysql tables using ansible

问题描述

应该使用某种合适的方法来使用mysql数据库来处理mysql数据库，例如将数据插入表中或在mysql db上运行的任何命令.

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-从远程主机添加或删除MySQL数据库.
• mysql_replication(E)-管理MySQL复制
• mysql_user-在MySQL数据库中添加或删除用户.
• mysql_variables-管理MySQL全局变量

• 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

我的用例场景是，我已经在ubuntu上安装了mysql-server并成功创建了数据库，现在我不得不将数据插入表中，并且想知道是否有办法通过ansible实现它.

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.

推荐答案

解决方案1:

我认为您错过了mysql_db模块的导入功能.您可以使用import作为参数来加载模式和数据，以声明状态并为其提供文件以在target

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

Ansible文档中的示例

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

解决方案2:

如果mysql_db没有为您提供所需的所有选项和灵活性，则可以将mysql程序与shell结合使用.

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

以上将dump.sql文件加载到数据库db_name中.有关更多选项，请参见mysql程序手册: man mysql

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

解决方案3:

mysqlimport实用程序和command模块:

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

请参阅: mysqlimport文档

这篇关于使用Ansible将数据插入mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！