Chef Experiments - Create SSH config

The objective here is to create  a simple sshd cookbook for Red Hat/CentOS configuration.

Create the cookbook

knife cookbook create sshd


Create the default recipe.
Options like sshd port , banner etc will be pulled from a data bag called base_config.   The template file for SSHD configuration would be sshd.erb.

File : cookbooks/sshd/recipes/default.rb

sshd_config = data_bag_item('base_config', 'sshd')
template "/etc/ssh/sshd_config" do
    source "sshd.erb"
    mode "0644"
    variables(
    :sshd_port => sshd_config['port'],
    :x11_forwarding => sshd_config['x11_forwarding'],
    :banner => sshd_config['banner'],
    :permit_root => sshd_config['permit_root']
)
end

template "/etc/issue.net" do
    source "issue.net.erb"
end

service "sshd" do
    action [ :restart ]
end

Templates sshd.erb looks like this

File : cookbooks/sshd/template/sshd.erb

Port <%= @sshd_port %>

Protocol 2

#other SSHD config has been omitted for the sake of the blog post

PermitRootLogin <%= @permit_root %>

X11Forwarding <%= @x11_forwarding %>

Banner <%= @banner %>

#other SSHD config has been omitted for the sake of the blog post

In issue.net.erb we are are reading from motd and adding a little blurb after that

File : cookbooks/sshd/template/issue.net.erb

<%= File.read("/etc/motd") %>

*****************************
Use of the Site by unauthorized users is prohibited and 
unauthorized users will be prosecuted to the fullest 
extent of the law.
*****************************

Data bag

Create the data bag

File :data_bags/base)config/config.json 

{  
  "id": "sshd",  
  "port": "2222",  
  "x11_forwarding": "no",  
  "banner":"/etc/issue.net",  
  "permit_root": "no"  
}  

Finishing up

knife data bag create base_config

knife data bag from file base_config data_bags/base_config/config.json 

knife cookbook upload sshd

Then add the recipe to a role or node run list and run the chef-client

Chef Experiments - Create host files

I am in the process of experimenting with Chef and here's one of them

knife create cookbook host_file_update


Then create the recipe

recipes/default.rb

hosts = search(:node, "*:*")
template "/etc/hosts" do
  source "hosts.erb"
  owner "root"
  group "root"
  mode 0644
  variables(
    :hosts => hosts,
    :hostname => node[:hostname],
    :fqdn => node[:fqdn]
  )
end

And then the template file hosts.erb referenced above

templates/default/hosts.erb 

127.0.0.1   localhost

<% @hosts.each do |node| %>
<%= node['ipaddress'] %> <%= node['hostname'] %> <%= node['fqdn'] %>
<% end %>

Pretty useful, if you want to populate this automatically as and when you add servers.  One of the next things to try is see if we can make Chef pick the additional IPs (e.g service net in Rackspace cloud) and create separate entries for it

Mysql Information Schema

Re-publishing from my wiki.

• Show tables that use Barracuda disk format

select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND ( ROW_FORMAT='Compressed' OR ROW_FORMAT='Dynamic'); 

• Show me all tables that are InnoDB 

SELECT `table_schema`, `table_name` FROM `information_schema`.`TABLES` WHERE `Engine`='Innodb' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

• Show me all tables that are MyISAM 

SELECT `table_schema`, `table_name` FROM `information_schema`.`TABLES` WHERE `Engine`='MyISAM' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

• Print Queries to aid in conversion FROM MyISAM to InnoDB 

 use `information_schema`; SELECT CONCAT("ALTER TABLE `" , `TABLE_SCHEMA`, "`.`", `table_name`, "` Engine=Innodb;") AS "" FROM `information_schema`.`TABLES` WHERE `Engine`='MyISAM' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

You can save the above in a file and run this
mysql --batch < input.sql > out.sql 

• Show me a count of tables grouped by engine type 

SELECT `Engine`, count(*) as Total FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' GROUP BY `Engine`;

• Show me the datasize and index size of all tables grouped by engine type

SELECT `Engine`, COUNT(ENGINE), sum(data_length)/(1024*1024*1024) as 'Datasize-GB', sum(index_length)/(1024*1024*1024) as 'Indexsize-GB' FROM `information_schema`.`TABLES` GROUP BY `Engine`; 

• Show me the top 10 tables by size outside of information_schema and mysql 

SELECT TABLE_SCHEMA, TABLE_NAME,data_length/1024*1024 FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' ORDER BY `data_length` DESC LIMIT 10; 

• Tables without indexes

USE `information_schema`; SELECT CONCAT(TABLES.table_schema,".",TABLES.table_name) as name, `TABLES`.`TABLE_TYPE`,`TABLE_ROWS` FROM `TABLES` LEFT JOIN `TABLE_CONSTRAINTS` ON `TABLES`.`table_schema` = `TABLE_CONSTRAINTS`.`table_schema` AND `TABLES`.`table_name` = `TABLE_CONSTRAINTS`.`table_name` AND `TABLE_CONSTRAINTS`.`constraint_type` = 'PRIMARY KEY' WHERE `TABLE_CONSTRAINTS`.`constraint_name` IS NULL; Check for redundant indexes SELECT * FROM ( SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME`, GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`) AS columns FROM `information_schema`.`STATISTICS` WHERE `TABLE_SCHEMA` NOT IN ('mysql', 'INFORMATION_SCHEMA') AND NON_UNIQUE = 1 AND INDEX_TYPE='BTREE' GROUP BY `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME` ) AS i1 INNER JOIN ( SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME`, GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`) AS columns FROM `information_schema`.`STATISTICS` WHERE INDEX_TYPE='BTREE' GROUP BY `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME` ) AS i2 USING (`TABLE_SCHEMA`, `TABLE_NAME`) WHERE i1.columns != i2.columns AND LOCATE(CONCAT(i1.columns, ','), i2.columns) = 1 

• List character sets 

 SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `CHARACTER_SET_NAME`, `TABLE_COLLATION` FROM `INFORMATION_SCHEMA`.`TABLES` INNER JOIN `INFORMATION_SCHEMA`.`COLLATION_CHARACTER_SET_APPLICABILITY` ON (`TABLES`.`TABLE_COLLATION` = `COLLATION_CHARACTER_SET_APPLICABILITY`.`COLLATION_NAME`) WHERE `TABLES`.`TABLE_SCHEMA` !='information_schema' AND `TABLES`.`TABLE_SCHEMA` !='mysql' ;

• List average row length and index length 

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `AVG_ROW_LENGTH`, `DATA_LENGTH`, `INDEX_LENGTH` FROM `TABLES` ORDER BY `AVG_ROW_LENGTH` DESC LIMIT 15; 

• Oldest tables with respect to update times 

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `UPDATE_TIME` FROM `TABLES` WHERE `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 10; 

• Tables with foreign keys 

SELECT * FROM `table_constraints` WHERE `constraint_type` = 'FOREIGN KEY' ; 

• List of indexes and their total count 

SELECT `INDEX_TYPE`, count(*) as NUM FROM `STATISTICS` group by `INFORMATION_SCHEMA`.`INDEX_TYPE`; 

• Get a summary of privileges 

SELECT * from `INFORMATION_SCHEMA`.`USER_PRIVILEGES`;