Try SpotterAdd or remove MySQL databases from a remote host
Authors: Ansible Core Team
Install with ansible-galaxy collection install community.general:==0.2.1
collections:
- name: community.general
version: 0.2.1Add or remove MySQL databases from a remote host.
- name: Create a new database with name 'bobdata'
mysql_db:
name: bobdata
state: present
- name: Create new databases with names 'foo' and 'bar'
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
mysql_db:
name: my_db
state: import
target: /tmp/dump.sql.bz2
- name: Restore database ignoring errors
mysql_db:
name: my_db
state: import
target: /tmp/dump.sql.bz2
force: yes
- name: Dump multiple databases
mysql_db:
state: dump
name: db_1,db_2
target: /tmp/dump.sql
- name: Dump multiple databases
mysql_db:
state: dump
name:
- db_1
- db_2
target: /tmp/dump.sql
- name: Dump all databases to hostname.sql
mysql_db:
state: dump
name: all
target: /tmp/dump.sql
- name: Dump all databases to hostname.sql including master data
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
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>
mysql_db:
state: dump
name: db_1
encoding: latin1
target: /tmp/dump.sql
- name: Delete database with name 'bobdata'
mysql_db:
name: bobdata
state: absent
- name: Make sure there is neither a database with name 'foo', nor one with name 'bar'
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
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
mysql_db:
check_implicit_admin: yes
login_user: bob
login_password: 123456
name: bobdata
state: present
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
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).
required: false
type: bool
version_added: 0.2.0
version_added_collection: community.general
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.
required: false
type: bool
version_added: 0.2.0
version_added_collection: community.general
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.
required: false
type: bool
version_added: 0.2.0
version_added_collection: community.general
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.
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).
required: false
type: int
version_added: 0.2.0
version_added_collection: community.general
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
required: false
type: list
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.
required: false
type: str
version_added: 0.2.0
version_added_collection: community.general
skip_lock_tables:
default: false
description:
- Skip locking tables for read. Used when I(state=dump), ignored otherwise.
required: false
type: bool
version_added: 0.2.0
version_added_collection: community.general
login_unix_socket:
description:
- The path to a Unix domain socket for local connections.
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.2.0
version_added_collection: community.general
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.2.0
version_added_collection: community.general
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.2.0
version_added_collection: community.general
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.2.0
version_added_collection: community.general
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.2.0 version_added_collection: community.general