Setting Up SaltStack Job Cache with MySQL Backend

The SaltStack Job Cache functionality allows you to persist all Salt operations in a MySQL database, providing a centralized storage for job execution data.

Prerequisites

Begin by installing the MySQL Python connector on your Salt master node:

yum install MySQL-python

Configuring Salt Master

Edit the Salt master configuration file to enable MySQL job caching:

vim /etc/salt/master

Add the following configuration at the end of the file:

master_job_cache: mysql
mysql.host: 'your_mysql_server_ip'
mysql.user: 'salt_user'
mysql.pass: 'secure_password'
mysql.db: 'salt_cache'
mysql.port: 3306

Database Setup

Install MariaDB server and initialize it:

yum install mariadb mariadb-server -y
systemctl start mariadb
mysql_secure_installation

Create the databaes and required tablles:

CREATE DATABASE `salt_cache`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

USE `salt_cache`;

-- Table structure for job IDs
DROP TABLE IF EXISTS `job_ids`;
CREATE TABLE `job_ids` (
  `job_id` varchar(255) NOT NULL,
  `load_data` mediumtext NOT NULL,
  UNIQUE KEY `job_id` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX job_idx ON job_ids(job_id) USING BTREE;

-- Table structure for job returns
DROP TABLE IF EXISTS `job_returns`;
CREATE TABLE `job_returns` (
  `function` varchar(50) NOT NULL,
  `job_id` varchar(255) NOT NULL,
  `return_data` mediumtext NOT NULL,
  `minion_id` varchar(255) NOT NULL,
  `status` varchar(10) NOT NULL,
  `full_return` mediumtext NOT NULL,
  `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `minion_id` (`minion_id`),
  KEY `job_id` (`job_id`),
  KEY `function` (`function`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Table structure for salt events
DROP TABLE IF EXISTS `salt_events`;
CREATE TABLE `salt_events` (
  `event_id` BIGINT NOT NULL AUTO_INCREMENT,
  `event_tag` varchar(255) NOT NULL,
  `event_data` mediumtext NOT NULL,
  `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `master_id` varchar(255) NOT NULL,
  PRIMARY KEY (`event_id`),
  KEY `event_tag` (`event_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

User Permissions

Grant the necessary permissions to the Salt user:

grant all on salt_cache.* to salt_user@'your_salt_master_ip' identified by 'secure_password';

Testing the Connection

Verify the MySQL connection from the Salt master:

mysql -h your_mysql_server_ip -u salt_user -psecure_password

Restarting Salt Master

After configuration, restart the Salt master service to apply changes:

systemctl restart salt-master

Execute some Salt commands and then check the MySQL tables to verify data is being stored:

SELECT * FROM job_returns;

Reference

For additional information, see the SaltStack documentation: Salt MySQL Returner Documentation