Mysql Operations

Manage MySQL databases, users and privileges.

Requires the mysql CLI executable on the target host(s).

All operations in this module take four optional arguments:
  • mysql_user: the username to connect to mysql to
  • mysql_password: the password for the connecting user
  • mysql_host: the hostname of the server to connect to
  • mysql_port: the port of the server to connect to

See the example/mysql.py

Facts used in these operations: mysql.MysqlDatabases, mysql.MysqlUserGrants, mysql.MysqlUsers.

mysql.database

Add/remove MySQL databases.

mysql.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,
)
  • 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:

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

mysql.dump

Stateless operation

This operation will always execute commands and is not idempotent.

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

mysql.dump(
    dest, database=None, mysql_user=None, mysql_password=None, mysql_host=None,
    mysql_port=None,
)
  • dest: name of the file to dump the SQL to
  • database: name of the database to dump
  • mysql_*: global module arguments, see above

Example:

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

mysql.load

Stateless operation

This operation will always execute commands and is not idempotent.

Load .sql file into a database.

mysql.load(src, database=None, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)
  • src: the filename to read from
  • database: name of the database to import into
  • mysql_*: global module arguments, see above

Example:

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

mysql.privileges

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

mysql.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,
)
  • 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

mysql.sql

Stateless operation

This operation will always execute commands and is not idempotent.

Execute arbitrary SQL against MySQL.

mysql.sql(sql, database=None, mysql_user=None, mysql_password=None, mysql_host=None, mysql_port=None)
  • sql: SQL command(s) to execute
  • database: optional database to open the connection with
  • mysql_*: global module arguments, see above

mysql.user

Add/remove/update MySQL users.

mysql.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,
)
  • 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:

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",
)