MySQL 使用ansible将数据插入mysql表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28188508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert data into mysql tables using ansible
提问by ankit tyagi
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.
应该有一些体面的方法来使用 ansible 来处理 mysql 数据库，例如将数据插入表中或在 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 replicationmysql_user- Adds or removes a user from a MySQL database.mysql_variables- Manage MySQL global variables
mysql_db- 从远程主机添加或删除 MySQL 数据库。mysql_replication(E) - 管理 MySQL 复制mysql_user- 在 MySQL 数据库中添加或删除用户。mysql_variables- 管理 MySQL 全局变量
My use case scenario is, I've installed mysql-serveron 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.
我的用例场景是，我已经mysql-server在 ubuntu上安装并成功创建了数据库，现在我必须将数据插入表中，并想知道是否有办法通过 ansible 实现它。
回答by Michal Gasek
Solution 1:
解决方案1：
I think you missed import functionality of mysql_dbmodule. You can load both schema and data with it using importas parameter to state and giving it a file to load in target
我认为您错过了mysql_db模块的导入功能。您可以使用它import作为参数来加载架构和数据，并为其提供一个要加载的文件target
Example from Ansible docs:
Ansible 文档中的示例：
# 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:
解决方案2：
If mysql_db does not give you all options that you need and flexibility you can just use mysqlprogram in combination with shell.
如果 mysql_db 没有为您提供所需的所有选项和灵活性，您可以将mysqlprogram 与shell.
- name: Import DB
shell: mysql db_name < dump.sql
Above loads dump.sqlfile into database db_name. See mysqlprogram manual for more options: man mysql
以上将dump.sql文件加载到数据库中db_name。有关mysql更多选项，请参阅程序手册：man mysql
Solution 3:
解决方案3：
mysqlimportutility with commandmodule:
mysqlimport带command模块的实用程序：
- name: Import DB
command: mysqlimport [options] db_name textfile1 [textfile2 ...]
See: mysqlimport docs
请参阅：mysqlimport 文档
回答by Mxx
You can use mysql_dbmodule. 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.
您可以使用mysql_db模块。其state:参数值为present, absent, dump, import。如果设置为import，则您可以使用target:参数来指示要从中读取的转储文件在远程主机上的位置。支持未压缩的 SQL 文件 (.sql) 以及 bzip2 (.bz2) 和 gzip (.gz) 压缩文件。
# 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
