mysql_db - Add or remove MySQL databases from a remote host.
Synopsis
- Add or remove MySQL databases from a remote host.
Requirements
The below requirements are needed on the host that executes this module.
- MySQLdb (Python 2.x)
- PyMySQL (Python 2.7 and Python 3.X), or
- mysql (command line binary)
- mysqldump (command line binary)
Parameters
| Parameter | Choices/Defaults | Comments |
|---|---|---|
| collation | Default:
None
|
Collation mode (sorting). This only applies to new table/databases and does not update existing ones, this is a limitation of MySQL.
|
| config_file
(added in 2.0)
|
Default:
~/.my.cnf
|
Specify a config file from which user and password are to be read.
|
| connect_timeout
(added in 2.1)
|
Default:
30
|
The connection timeout when connecting to the MySQL server.
|
| encoding | Default:
None
|
Encoding mode to use, examples include
utf8 or
latin1_swedish_ci
|
| login_host | Default:
localhost
|
Host running the database.
|
| login_password | Default:
None
|
The password used to authenticate with.
|
| login_port | Default:
3306
|
Port of the MySQL server. Requires
login_host be defined as other then localhost if login_port is used.
|
| login_unix_socket | Default:
None
|
The path to a Unix domain socket for local connections.
|
| login_user | Default:
None
|
The username used to authenticate with.
|
| name
required
|
Default:
None
|
name of the database to add or remove
name=all May only be provided if
state is
dump or
import.
if name=all Works like --all-databases option for mysqldump (Added in 2.0)
aliases: db |
| quick
(added in 2.1)
|
Default:
yes
|
Option used for dumping large tables
|
| single_transaction
(added in 2.1)
|
Default:
no
|
Execute the dump in a single transaction
|
| ssl_ca
(added in 2.0)
|
Default:
None
|
The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server.
|
| ssl_cert
(added in 2.0)
|
Default:
None
|
The path to a client public key certificate.
|
| ssl_key
(added in 2.0)
|
Default:
None
|
The path to the client private key.
|
| state |
|
The database state
|
| target |
Location, on the remote host, of the dump file to read from or write to. Uncompressed SQL files (
.sql) as well as bzip2 (
.bz2), gzip (
.gz) and xz (Added in 2.0) compressed files are supported.
|
Notes
Note
- Requires the PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) package on the remote host, as well as mysql and mysqldump binaries.
- This module is not idempotent when state is
import, and will import the dump file each time if run more than once. - Requires the PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) Python package on the remote host. For Ubuntu, this is as easy as apt-get install python-pymysql. (See apt.) For CentOS/Fedora, this is as easy as yum install python2-PyMySQL. (See yum.)
- Both
login_passwordandlogin_userare required when you are passing credentials. If none are present, the module will attempt to read the credentials from~/.my.cnf, and finally fall back to using the MySQL default login of ‘root’ with no password.
Examples
- name: Create a new database with name 'bobdata'
mysql_db:
name: bobdata
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: Dump all databases to hostname.sql
mysql_db:
state: dump
name: all
target: /tmp/{{ inventory_hostname }}.sql
- name: Import file.sql similar to mysql -u <username> -p <password> < hostname.sql
mysql_db:
state: import
name: all
target: /tmp/{{ inventory_hostname }}.sql
Status
This module is flagged as preview which means that it is not guaranteed to have a backwards compatible interface.
Author
- Ansible Core Team
Hint
If you notice any issues in this documentation you can edit this document to improve it.
© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.5/modules/mysql_db_module.html
