Skip to content

mysql

Mysql tasks

This module provides tasks for working with mysql databases.

database(database, present=True, collate=None, charset=None, user=None, user_hostname='localhost', user_privileges='ALL', mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)

Add/remove MySQL databases.

  • name: the name of the database
  • present: whether the database should exist or not
  • collate: the collate to use when creating the database
  • charset: the charset to use when creating the database
  • user: MySQL user to grant privileges on this database to
  • user_hostname: the hostname of the MySQL user to grant
  • user_privileges: privileges to grant to any specified user
  • mysql_*: global module arguments, see above

Collate/charset: these will only be applied if the database does not exist - ie pyinfra will not attempt to alter the existing databases collate/character sets.

Example:

.. code:: python

mysql.database(
    name="Create the pyinfra_stuff database",
    database="pyinfra_stuff",
    user="pyinfra",
    user_privileges=["SELECT", "INSERT"],
    charset="utf8",
)

dump(dest, database=None, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)

Dump a MySQL database into a .sql file. Requires mysqldump.

  • dest: name of the file to dump the SQL to
  • database: name of the database to dump
  • mysql_*: global module arguments, see above

Example:

.. code:: python

mysql.dump(
    name="Dump the pyinfra_stuff database",
    dest="/tmp/pyinfra_stuff.dump",
    database="pyinfra_stuff",
)

load(src, database=None, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)

Load .sql file into a database.

  • src: the filename to read from
  • database: name of the database to import into
  • mysql_*: global module arguments, see above

Example:

.. code:: python

mysql.load(
    name="Import the pyinfra_stuff dump into pyinfra_stuff_copy",
    src="/tmp/pyinfra_stuff.dump",
    database="pyinfra_stuff_copy",
)

privileges(user, privileges, user_hostname='localhost', database='*', table='*', flush=True, with_grant_option=False, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)

Add/remove MySQL privileges for a user, either global, database or table specific.

  • user: name of the user to manage privileges for
  • privileges: list of privileges the user should have (see also: with_grant_option argument)
  • user_hostname: the hostname of the user
  • database: name of the database to grant privileges to (defaults to all)
  • table: name of the table to grant privileges to (defaults to all)
  • flush: whether to flush (and update) the privileges table after any changes
  • with_grant_option: whether the grant option privilege should be set
  • mysql_*: global module arguments, see above

sql(sql, database=None, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)

Execute arbitrary SQL against MySQL.

  • sql: SQL command(s) to execute
  • database: optional database to open the connection with
  • mysql_*: global module arguments, see above

user(user, present=True, user_hostname='localhost', password=None, privileges=None, require=None, require_cipher=False, require_issuer=False, require_subject=False, max_connections=None, max_queries_per_hour=None, max_updates_per_hour=None, max_connections_per_hour=None, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)

Add/remove/update MySQL users.

  • user: the name of the user
  • present: whether the user should exist or not
  • user_hostname: the hostname of the user
  • password: the password of the user (if created)
  • privileges: the global privileges for this user
  • mysql_*: global module arguments, see above
Hostname

this + name makes the username - so changing this will create a new user, rather than update users with the same name.

Password

will only be applied if the user does not exist - ie pyinfra cannot detect if the current password doesn't match the one provided, so won't attempt to change it.

Example:

.. code:: python

mysql.user(
    name="Create the pyinfra@localhost MySQL user",
    user="pyinfra",
    password="somepassword",
)

# Create a user with resource limits
mysql.user(
    name="Create the pyinfra@localhost MySQL user",
    user="pyinfra",
    max_connections=50,
    max_updates_per_hour=10,
)

# Create a user that requires SSL for connections
mysql.user(
    name="Create the pyinfra@localhost MySQL user",
    user="pyinfra",
    password="somepassword",
    require="SSL",
)

# Create a user that requires a specific certificate
mysql.user(
    name="Create the pyinfra@localhost MySQL user",
    user="pyinfra",
    password="somepassword",
    require="X509",
    require_issuer="/C=SE/ST=Stockholm...",
    require_cipher="EDH-RSA-DES-CBC3-SHA",
)