salt data to mysql

# salt data to mysql

---

By default the return values of the commands sent to the Salt minions are returned to the Salt master. By using a Salt returner, results data can be redirected to external data-stores for analysis and archival.

## 把客户端执行命令返回的值可以直接返回到文件，或者保存到数据库里.

First:
install software mysql, python-mysqldb,saltstack. Required python modules: MySQLdb second: create mysql, salt returner tool import to databases:

# yum install mysql-server mysql-devel MySQL-python
    # vim /etc/my.cnf
    # mysql_install_db --defaults-file=/etc/my.cnf
    # /etc/init.d/mysqld start
    # /usr/bin/mysqladmin -u root password 'salt'

Use the following mysql database schema:

CREATE DATABASE  `salt`
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
 
USE `salt`;
 
```mysql

--
-- Table structure for table `jids`
--
 
DROP TABLE IF EXISTS `jids`;
CREATE TABLE `jids` (
  `jid` varchar(255) NOT NULL,
  `load` mediumtext NOT NULL,
  UNIQUE KEY `jid` (`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX jid ON jids(jid) USING BTREE;
```
 
--
-- Table structure for table `salt_returns`
--

```mysql
DROP TABLE IF EXISTS `salt_returns`;
CREATE TABLE `salt_returns` (
  `fun` varchar(50) NOT NULL,
  `jid` varchar(255) NOT NULL,
  `return` mediumtext NOT NULL,
  `id` varchar(255) NOT NULL,
  `success` varchar(10) NOT NULL,
  `full_ret` mediumtext NOT NULL,
  `alter_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `id` (`id`),
  KEY `jid` (`jid`),
  KEY `fun` (`fun`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

```
 
 
--
-- Table structure for table `salt_events`
--
 
DROP TABLE IF EXISTS `salt_events`;
CREATE TABLE `salt_events` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`tag` varchar(255) NOT NULL,
`data` mediumtext NOT NULL,
`alter_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`master_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
    # mysql -uroot -p
    #  grant all privileges on salt.* to 'salt'@'%' identified by 'salt';
Edit salt-master:

# vim /etc/salt/master
    #mysql.host: ‘127.0.0.1’
  mysql.host: '1.81.235.230'
  mysql.user: 'salt'
  mysql.pass: 'salt'
  mysql.db: 'salt'
  mysql.port: 3306
  master_job_cache: mysql
 
Alternative configuration values can be used by prefacing the configuration with alternative.
    #  alternative.mysql.host: 'salt'
    #  alternative.mysql.user: 'salt'
    #  alternative.mysql.pass: 'salt'
    #  alternative.mysql.db: 'salt'
    #  alternative.mysql.port: 3306
    #  alternative.mysql.ssl_ca: '/etc/pki/mysql/certs/localhost.pem'
    #  alternative.mysql.ssl_cert: '/etc/pki/mysql/certs/localhost.crt'
    #  alternative.mysql.ssl_key: '/etc/pki/mysql/certs/localhost.key'
 
Define your salt returners python shell

# vim /srv/salt/_returners/mysql_return.py

```

from contextlib import contextmanager
import sys
import json
import logging
try:
  import MySQLdb
  HAS_MYSQL = True
except ImportError:
  HAS_MYSQL = False
log = logging.getLogger(__name__)
def __virtual__():
  if not HAS_MYSQL:
    return False
  return 'test_mysql'
def _get_options():
  '''
  Returns options used for the MySQL connection.
  '''
  defaults = {'host': '1.81.235.230',
        'user': 'salt',
        'pass': 'salt',
        'db': 'salt',
        'port': 3306}
  _options = {}
  for attr in defaults:
    _attr = __salt__['config.option']('mysql.{0}'.format(attr))
    if not _attr:
      log.debug('Using default for MySQL {0}'.format(attr))
      _options[attr] = defaults[attr]
      continue
    _options[attr] = _attr
  return _options
@contextmanager
def _get_serv(commit=False):
  '''
  Return a mysql cursor
  '''
  _options = _get_options()
  conn = MySQLdb.connect(host=_options['host'], user=_options['user'], passwd=_options['pass'], db=_options['db'], port=_options['port'])
  cursor = conn.cursor()
  try:
    yield cursor
  except MySQLdb.DatabaseError as err:
    error, = err.args
    sys.stderr.write(error.message)
    cursor.execute("ROLLBACK")
    raise err
  else:
    if commit:
      cursor.execute("COMMIT")
    else:
      cursor.execute("ROLLBACK")
  finally:
    conn.close()
def returner(ret):
  '''
  Return data to a mysql server
  '''
  with _get_serv(commit=True) as cur:
    sql = '''INSERT INTO `salt_returns`
        (`fun`, `jid`, `return`, `id`, `success`, `full_ret` )
        VALUES (%s, %s, %s, %s, %s, %s)'''
    cur.execute(sql, (ret['fun'], ret['jid'],
              str(ret['return']), ret['id'],
              ret['success'], json.dumps(ret)))
 
Required python modules: MySQLdb

# salt '*' saltutil.sync_returners
# salt '*' cmd.run 'uname -a'  --return mysql
# salt '*' cmd.run 'uptime' --return mysql
# salt '*' test.ping --return mysql
Run result.

test.ping	20160808190513047722	true	m2	1	{“fun_args”: [], “jid”: “20160808190513047722”, “return”: true, “retcode”: 0, “success”: true, “cmd”: “_return”, “_stamp”: “2016-08-08T11:05:13.672486”, “fun”: “test.ping”, “id”: “m2”}	2016-08-08 19:05:13
test.ping	20160808190513047722	true	m4	1	{“fun_args”: [], “jid”: “20160808190513047722”, “return”: true, “retcode”: 0, “success”: true, “cmd”: “_return”, “_stamp”: “2016-08-08T11:05:13.663032”, “fun”: “test.ping”, “id”: “m4”}	2016-08-08 19:05:13
```