Introduction
MySQL with Ansible is an open-source automation tool that automates provisioning, configuration management, application deployment, orchestration, and many other manual IT processes. Unlike more simplistic management tools, Ansible users (like system administrators, developers, and architects) can use Ansible automation to install software, automate daily tasks, provision infrastructure, improve security and compliance, patch systems, and share automation across the entire organization.
How MySQL with Ansible works
Ansible works by connecting to what you want automated and pushing programs that execute instructions that tasks would have done manually. These programs utilize Ansible modules that are written based on the specific expectations of the endpoint’s connectivity, interface, and commands. Ansible then executes these modules (over standard SSH by default) and removes them when finished (if applicable).
There are no additional servers, daemons, or databases required. Typically you’ll work with your favorite terminal program, a text editor, and a version control system to keep track of changes to your content.
Remote server requirement
- MySQLdb (Python 2. x)
- PyMySQL (Python 2.7 and Python 3. X), or
- MySQL (command-line binary)
- mysqldump (command-line binary)
Add or remove MySQL with Ansible databases
Required parameters
| Parameter | Choices/Defaults | Comments |
| config_file path | Default: “~/.my.cnf” | Specify a config file from which user and password are to be read. |
| connect_timeout integer | Default: 30 | The connection timeout when connecting to the MySQL server. |
| ignore_tables – added in 2.7 | Default: [] | A list of table names that will be ignored in the dump of the form database_name.table_name |
| login_host string | Default: “localhost” | Host running the database. |
| login_password string | The password used to authenticate with. | |
| login_port integer | Default: 3306 | Port of the MySQL server. Requires login_host to be defined as other than localhost if login_port is used. |
| login_unix_socket string | The path to a Unix domain socket for local connections. | |
| login_user string | The username used to authenticate with. | |
| quick boolean | Choices: no yes ← | The option used for dumping large tables |
| single_transaction boolean | Choices: no ← yes | Execute the dump in a single transaction |
| state – | Choices: present ← absent dump import | The database state |
- 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 the database dump file to the 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 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: Import file.sql similar to mysql -u <username> -p <password> < hostname.sql
mysql_db:
state: import
name: all
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 the word 'foo nor one with the name 'bar.'
mysql_db:
name:
- foo
- bar
state: absent
