saltstack自动化运维系列④之saltstack的命令返回结果mysql数据库写入

saltstack自动化运维系列④之saltstack的命令返回结果mysql数据库写入
salt的返回值写入mysql数据库：
可参考：
https://docs.saltstack.com/en/latest/ref/returners/all/salt.returners.mysql.html#module-salt.returners.mysql

在mysql数据库中创建数据库、表

CREATE DATABASE `salt`DEFAULT CHARACTER SET utf8DEFAULT COLLATE utf8_general_ci;USE `salt`;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;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;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> show tables;+----------------+| Tables_in_salt |+----------------+| jids || salt_events || salt_returns |mysql> grant all on salt.* to salt@'%' identified by 'salt';

安装python插件

# yum install MySQL-python -y

将以下配置分别配置到服务端和客户端：
/etc/salt/master
/etc/salt/minion

mysql.host: '192.168.3.19'mysql.user: 'salt'mysql.pass: 'salt'mysql.db: 'salt'mysql.port: 3306

执行命令后面跟 --return mysql 可以将执行的结果和命令记录到mysql数据库中

# salt '*' test.ping --return mysqlnode2.chinasoft.com:Truemini1:True[root@mini1 ~]# salt '*' cmd.run 'date' --return mysqlnode2.chinasoft.com:Fri Apr 7 16:25:53 CST 2017mini1:Fri Apr 7 16:25:53 CST 2017[root@mini1 ~]# salt '*' cmd.run 'df -Th' --return mysqlmini1:Filesystem Type Size Used Avail Use% Mounted on/dev/mapper/vg0-root ext4 25G 1.6G 23G 7% /tmpfs tmpfs 495M 16K 495M 1% /dev/shm/dev/sda1 ext4 291M 39M 238M 14% /boot/dev/mapper/vg0-usr ext4 20G 2.8G 16G 15% /usr/dev/mapper/vg0-var ext4 9.7G 1011M 8.2G 11% /varnode2.chinasoft.com:Filesystem Type Size Used Avail Use% Mounted on/dev/mapper/vg_node2-root ext4 29G 1020M 27G 4% /tmpfs tmpfs 935M 12K 935M 1% /dev/shm/dev/sda1 ext4 485M 39M 421M 9% /boot/dev/mapper/vg_node2-data ext4 29G 252M 28G 1% /data/dev/mapper/vg_node2-usr ext4 9.7G 2.0G 7.2G 22% /usr/dev/mapper/vg_node2-web ext4 25G 172M 23G 1% /web[root@mini1 ~]# salt '*' cmd.run 'free -m' --return mysqlnode2.chinasoft.com:total used free shared buffers cachedMem: 1869 1760 108 0 167 456-/+ buffers/cache: 1136 733Swap: 3999 35 3964mini1:total used free shared buffers cachedMem: 988 922 65 0 3 51-/+ buffers/cache: 868 120Swap: 3999 316 3683

 

可以看到表 salt_returns 中的数据

mysql> select * from salt_returns\G*************************** 1. row ***************************fun: test.pingjid: 20170407162451855497return: trueid: node2.chinasoft.comsuccess: 1full_ret: {"fun_args": [], "jid": "20170407162451855497", "return": true, "retcode": 0, "success": true, "fun": "test.ping", "id": "node2.chinasoft.com"}alter_time: 2017-04-07 16:24:52*************************** 2. row ***************************fun: cmd.runjid: 20170407162553259109return: "Fri Apr 7 16:25:53 CST 2017"id: node2.chinasoft.comsuccess: 1full_ret: {"fun_args": ["date"], "jid": "20170407162553259109", "return": "Fri Apr 7 16:25:53 CST 2017", "retcode": 0, "success": true, "fun": "cmd.run", "id": "node2.chinasoft.com"}alter_time: 2017-04-07 16:25:53*************************** 3. row ***************************fun: cmd.runjid: 20170407162605846972return: "Filesystem Type Size Used Avail Use% Mounted on\n/dev/mapper/vg_node2-root ext4 29G 1020M 27G 4% /\ntmpfs tmpfs 935M 12K 935M 1% /dev/shm\n/dev/sda1 ext4 485M 39M 421M 9% /boot\n/dev/mapper/vg_node2-data ext4 29G 252M 28G 1% /data\n/dev/mapper/vg_node2-usr ext4 9.7G 2.0G 7.2G 22% /usr\n/dev/mapper/vg_node2-web ext4 25G 172M 23G 1% /web"id: node2.chinasoft.comsuccess: 1full_ret: {"fun_args": ["df -Th"], "jid": "20170407162605846972", "return": "Filesystem Type Size Used Avail Use% Mounted on\n/dev/mapper/vg_node2-root ext4 29G 1020M 27G 4% /\ntmpfs tmpfs 935M 12K 935M 1% /dev/shm\n/dev/sda1 ext4 485M 39M 421M 9% /boot\n/dev/mapper/vg_node2-data ext4 29G 252M 28G 1% /data\n/dev/mapper/vg_node2-usr ext4 9.7G 2.0G 7.2G 22% /usr\n/dev/mapper/vg_node2-web ext4 25G 172M 23G 1% /web", "retcode": 0, "success": true, "fun": "cmd.run", "id": "node2.chinasoft.com"}alter_time: 2017-04-07 16:26:06*************************** 4. row ***************************fun: cmd.runjid: 20170407162611429976return: " total used free shared buffers cached\nMem: 1869 1760 108 0 167 456\n-/+ buffers/cache: 1136 733\nSwap: 3999 35 3964"id: node2.chinasoft.comsuccess: 1full_ret: {"fun_args": ["free -m"], "jid": "20170407162611429976", "return": " total used free shared buffers cached\nMem: 1869 1760 108 0 167 456\n-/+ buffers/cache: 1136 733\nSwap: 3999 35 3964", "retcode": 0, "success": true, "fun": "cmd.run", "id": "node2.chinasoft.com"}alter_time: 2017-04-07 16:26:114 rows in set (0.00 sec)

至此saltstack命令行结果返回mysql数据库配置完毕，很多时候可以通过查询数据库得知哪些客户端没有返回我们想要的结果，可以方便的查询并进行二次处理