community / community.mysql / 3.9.0 / module / mysql_db Add or remove MySQL databases from a remote host Authors: Ansible Core Teamcommunity.mysql.mysql_db (3.9.0) — module
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections: - name: community.mysql version: 3.9.0
Add or remove MySQL databases from a remote host.
# If you encounter the "Please explicitly state intended protocol" error, # use the login_unix_socket argument - name: Create a new database with name 'bobdata' community.mysql.mysql_db: name: bobdata state: present login_unix_socket: /run/mysqld/mysqld.sock
- name: Create new databases with names 'foo' and 'bar' community.mysql.mysql_db: name: - foo - bar state: present
# Copy database dump file to remote host and restore it to database 'my_db' - name: Copy database dump file copy: src: dump.sql.bz2 dest: /tmp
- name: Restore database community.mysql.mysql_db: name: my_db state: import target: /tmp/dump.sql.bz2
- name: Restore database ignoring errors community.mysql.mysql_db: name: my_db state: import target: /tmp/dump.sql.bz2 force: true
- name: Dump multiple databases community.mysql.mysql_db: state: dump name: db_1,db_2 target: /tmp/dump.sql
- name: Dump multiple databases community.mysql.mysql_db: state: dump name: - db_1 - db_2 target: /tmp/dump.sql
- name: Dump all databases to hostname.sql community.mysql.mysql_db: state: dump name: all target: /tmp/dump.sql
- name: Dump all databases to hostname.sql including master data community.mysql.mysql_db: state: dump name: all target: /tmp/dump.sql master_data: 1
# Import of sql script with encoding option - name: > Import dump.sql with specific latin1 encoding, similar to mysql -u <username> --default-character-set=latin1 -p <password> < dump.sql community.mysql.mysql_db: state: import name: all encoding: latin1 target: /tmp/dump.sql
# Dump of database with encoding option - name: > Dump of Databse with specific latin1 encoding, similar to mysqldump -u <username> --default-character-set=latin1 -p <password> <database> community.mysql.mysql_db: state: dump name: db_1 encoding: latin1 target: /tmp/dump.sql
- name: Delete database with name 'bobdata' community.mysql.mysql_db: name: bobdata state: absent
- name: Make sure there is neither a database with name 'foo', nor one with name 'bar' community.mysql.mysql_db: name: - foo - bar state: absent
# Dump database with argument not directly supported by this module # using dump_extra_args parameter - name: Dump databases without including triggers community.mysql.mysql_db: state: dump name: foo target: /tmp/dump.sql dump_extra_args: --skip-triggers
- name: Try to create database as root/nopassword first. If not allowed, pass the credentials community.mysql.mysql_db: check_implicit_admin: true login_user: bob login_password: 123456 name: bobdata state: present
- name: Dump a database with compression and catch errors from mysqldump with bash pipefail community.mysql.mysql_db: state: dump name: foo target: /tmp/dump.sql.gz pipefail: true
name: aliases: - db description: - Name of the database to add or remove. - I(name=all) may only be provided if I(state) is C(dump) or C(import). - List of databases is provided with I(state=dump), I(state=present) and I(state=absent). - If I(name=all) it works like --all-databases option for mysqldump (Added in 2.0). elements: str required: true type: list chdir: description: - Changes the current working directory. - Can be useful, for example, when I(state=import) and a dump file contains relative paths. type: path version_added: 3.4.0 version_added_collection: community.mysql force: default: false description: - Continue dump or import even if we get an SQL error. - Used only when I(state) is C(dump) or C(import). type: bool version_added: 0.1.0 version_added_collection: community.mysql quick: default: true description: - Option used for dumping large tables. type: bool state: choices: - absent - dump - import - present default: present description: - The database state. type: str target: description: - Location, on the remote host, of the dump file to read from or write to. - Uncompressed SQL files (C(.sql)) as well as bzip2 (C(.bz2)), gzip (C(.gz)) and xz (Added in 2.0) compressed files are supported. type: path ca_cert: aliases: - ssl_ca description: - The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server. type: path encoding: default: '' description: - Encoding mode to use, examples include C(utf8) or C(latin1_swedish_ci), at creation of database, dump or importation of sql script. type: str hex_blob: default: false description: - Dump binary columns using hexadecimal notation. type: bool version_added: 0.1.0 version_added_collection: community.mysql pipefail: default: false description: - Use C(bash) instead of C(sh) and add C(-o pipefail) to catch errors from the mysql_dump command when I(state=import) and compression is used. - The default is C(no) to prevent issues on systems without bash as a default interpreter. - The default will change to C(yes) in community.mysql 4.0.0. type: bool version_added: 3.4.0 version_added_collection: community.mysql collation: default: '' description: - Collation mode (sorting). This only applies to new table/databases and does not update existing ones, this is a limitation of MySQL. type: str use_shell: default: false description: - Used to prevent C(Broken pipe) errors when the imported I(target) file is compressed. - If C(yes), the module will internally execute commands via a shell. - Used when I(state=import), ignored otherwise. type: bool version_added: 0.1.0 version_added_collection: community.mysql client_key: aliases: - ssl_key description: - The path to the client private key. type: path login_host: default: localhost description: - Host running the database. - In some cases for local connections the I(login_unix_socket=/path/to/mysqld/socket), that is usually C(/var/run/mysqld/mysqld.sock), needs to be used instead of I(login_host=localhost). type: str login_port: default: 3306 description: - Port of the MySQL server. Requires I(login_host) be defined as other than localhost if login_port is used. type: int login_user: description: - The username used to authenticate with. type: str client_cert: aliases: - ssl_cert description: - The path to a client public key certificate. type: path config_file: default: ~/.my.cnf description: - Specify a config file from which user and password are to be read. - The default config file, C(~/.my.cnf), if it exists, will be read, even if I(config_file) is not specified. - The default config file, C(~/.my.cnf), if it exists, must contain a C([client]) section as a MySQL connector requirement. - To prevent the default config file from being read, set I(config_file) to be an empty string. type: path master_data: choices: - 0 - 1 - 2 default: 0 description: - Option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. - C(0) to not include master data. - C(1) to generate a 'CHANGE MASTER TO' statement required on the slave to start the replication process. - C(2) to generate a commented 'CHANGE MASTER TO'. - Can be used when I(state=dump). type: int version_added: 0.1.0 version_added_collection: community.mysql ignore_tables: default: [] description: - A list of table names that will be ignored in the dump of the form database_name.table_name. elements: str type: list check_hostname: description: - Whether to validate the server host name when an SSL connection is required. Corresponds to MySQL CLIs C(--ssl) switch. - Setting this to C(false) disables hostname verification. Use with caution. - Requires pymysql >= 0.7.11. - This option has no effect on MySQLdb. type: bool version_added: 1.1.0 version_added_collection: community.mysql login_password: description: - The password used to authenticate with. type: str connect_timeout: default: 30 description: - The connection timeout when connecting to the MySQL server. type: int dump_extra_args: description: - Provide additional arguments for mysqldump. Used when I(state=dump) only, ignored otherwise. type: str version_added: 0.1.0 version_added_collection: community.mysql skip_lock_tables: default: false description: - Skip locking tables for read. Used when I(state=dump), ignored otherwise. type: bool version_added: 0.1.0 version_added_collection: community.mysql login_unix_socket: description: - The path to a Unix domain socket for local connections. - Use this parameter to avoid the C(Please explicitly state intended protocol) error. type: str single_transaction: default: false description: - Execute the dump in a single transaction. type: bool check_implicit_admin: default: false description: - Check if mysql allows login as root/nopassword before trying supplied credentials. - If success, passed I(login_user)/I(login_password) will be ignored. type: bool version_added: 0.1.0 version_added_collection: community.mysql restrict_config_file: default: false description: - Read only passed I(config_file). - When I(state) is C(dump) or C(import), by default the module passes I(config_file) parameter using C(--defaults-extra-file) command-line argument to C(mysql/mysqldump) utilities under the hood that read named option file in addition to usual option files. - If this behavior is undesirable, use C(yes) to read only named option file. type: bool version_added: 0.1.0 version_added_collection: community.mysql unsafe_login_password: default: false description: - If C(no), the module will safely use a shell-escaped version of the I(login_password) value. - It makes sense to use C(yes) only if there are special symbols in the value and errors C(Access denied) occur. - Used only when I(state) is C(import) or C(dump) and I(login_password) is passed, ignored otherwise. type: bool version_added: 0.1.0 version_added_collection: community.mysql config_overrides_defaults: default: false description: - If C(yes), connection parameters from I(config_file) will override the default values of I(login_host) and I(login_port) parameters. - Used when I(stat) is C(present) or C(absent), ignored otherwise. - It needs Python 3.5+ as the default interpreter on a target host. type: bool version_added: 0.1.0 version_added_collection: community.mysql
db: description: Database names in string format delimited by white space. returned: always sample: foo bar type: str db_list: description: List of database names. returned: always sample: - foo - bar type: list executed_commands: description: List of commands which tried to run. returned: if executed sample: - CREATE DATABASE acme type: list version_added: 0.1.0 version_added_collection: community.mysql