Neil
Neil
Treat your password like your toothbrush. Don’t let anybody else use it, and get a new one every six months.

Manage MySQL with Ansible

- Advertisement -

Introduction

MySQL with Ansible is an open-source automation tool that automates provisioningconfiguration managementapplication deploymentorchestration, 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/DefaultsComments
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
- Advertisement -

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

Join us on Facebook

x