mysql 5.6.10 deb preseed

Trying to install mysql 5.6.10 from http://dev.mysql.com is a pain with puppet. One reason is that it doesn't seem to play nicely on ubuntu, in so much as the preseed options do not seem to be honored, so you have to manually set the root password! The horror!

There are also several files it installs that you definately do not want,  the random my.cnf in the /opt/ directories, for example. Take note that these will be included in your config when you start up mysql! So make sure to remove them before hand

The basic puppet (version 2 - though it will also work with v3) config I have for this is:

class mysql-5-6-10 {

  include accounts::users
  include mysql-5-6-10::files
  include mysql-5-6-10::packages
  include mysql-5-6-10::symlink
  include mysql-5-6-10::exec
  include mysql-5-6-10::service
  include mysql-5-6-10::postservice
  include mysql-5-6-10::tcollector
  include mysql-5-6-10::checkmk
  include stats

  Class['stats']->
  Class['mysql-5-6-10::files']->
  Class['mysql-5-6-10::packages']->
  Class['mysql-5-6-10::symlink']->
  Class['mysql-5-6-10::exec']->
  Class['mysql-5-6-10::service']->
  Class['mysql-5-6-10::postservice']->
  Class['mysql-5-6-10::tcollector']->
  Class['mysql-5-6-10::checkmk']

  Accounts::Virtual::User::Localuser   <| group == "dbas" |>
  Accounts::Virtual::Group::Localgroup <| title == "dbas" |>
  Accounts::Virtual::User::Localuser   <| group == "mysql"|>
  Accounts::Virtual::Group::Localgroup <| title == "mysql" |>

  nagios::checkmk::addtag { 'mysql': }

}

  #############################

  # Mysql 5.6 specific options
  # requires conf.d directory
  file { '/etc/mysql/conf.d':
    ensure => directory,
    mode   => 0775,
    owner  => root,
  }
  # add data dir
  file { '/var/lib/mysql':
    ensure => directory,
    mode   => 0755,
    owner  => mysql,
    group  => mysql,
  }
  # Add init script symlink:
  file { '/etc/init.d/mysql':
    source => 'puppet:///mysql-5-6-10/init.d/mysql',
    owner  => root,
    group  => root,
    mode   => 0755,
  }
  # needed for logs
  file {'/var/log/mysql':
    ensure => directory,
    owner  => 'mysql',
    group  => 'mysql',
    mode   => 0755,
  }
  # wipe the stuff that doesn't need to be there or it might conflict with what we're running
  # wipe the default config so it doesn't load ita and override our confi
  file { '/opt/mysql/server-5.6/support-files/my-default.cnf':
    ensure  => absent,
    #ensure => link,
    #target => '/etc/mysql/my.cnf',
  }
  file { '/opt/mysql/server-5.6/data':
    ensure => absent,
  }
class mysql-5-6-10::packages {

  $mysql_server_package = 'mysql'

  $generic_packages = [
    'libaio1',
  ]

  package { 'mysql-server-package-5-6-10':
    name         => $mysql_server_package,
    ensure       => installed,
    responsefile => '/etc/mysql/mysql_preseed',
  }

  package { 'mysql-support-packages-5-6-10':
    name    => $generic_packages,
    ensure  => installed,
    before  => Package['mysql-server-package-5-6-10'],
  }

}
class mysql-5-6-10::symlink {

  #requires sym linking
  file {'/usr/bin/innochecksum': ensure => link,  target => '/opt/mysql/server-5.6/bin/innochecksum',}
  file {'/usr/bin/msql2mysql': ensure => link,  target => '/opt/mysql/server-5.6/bin/msql2mysql',}
  file {'/usr/bin/myisamchk': ensure => link,  target => '/opt/mysql/server-5.6/bin/myisamchk',}
  file {'/usr/bin/myisam_ftdump': ensure => link,  target => '/opt/mysql/server-5.6/bin/myisam_ftdump',}
  file {'/usr/bin/myisamlog': ensure => link,  target => '/opt/mysql/server-5.6/bin/myisamlog',}
  file {'/usr/bin/myisampack': ensure => link,  target => '/opt/mysql/server-5.6/bin/myisampack',}
  file {'/usr/bin/my_print_defaults': ensure => link,  target => '/opt/mysql/server-5.6/bin/my_print_defaults',}
  file {'/usr/bin/mysql': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql',}
  file {'/usr/bin/mysqlaccess': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlaccess',}
  file {'/usr/bin/mysqlaccess.conf': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlaccess.conf',}
  file {'/usr/bin/mysqladmin': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqladmin',}
  file {'/usr/bin/mysqlbinlog': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlbinlog',}
  file {'/usr/bin/mysqlbug': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlbug',}
  file {'/usr/bin/mysqlcheck': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlcheck',}
  file {'/usr/bin/mysql_client_test': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_client_test',}
  file {'/usr/bin/mysql_client_test_embedded': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_client_test_embedded',}
  file {'/usr/bin/mysql_config': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_config',}
  file {'/usr/bin/mysql_config_editor': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_config_editor',}
  file {'/usr/bin/mysql_convert_table_format': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_convert_table_format',}
  file {'/usr/bin/mysqld': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqld',}
  file {'/usr/bin/mysqld-debug': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqld-debug',}
  file {'/usr/bin/mysqld_multi': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqld_multi',}
  file {'/usr/bin/mysqld_safe': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqld_safe',}
  file {'/usr/bin/mysqldump': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqldump',}
  file {'/usr/bin/mysqldumpslow': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqldumpslow',}
  file {'/usr/bin/mysql_embedded': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_embedded',}
  file {'/usr/bin/mysql_find_rows': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_find_rows',}
  file {'/usr/bin/mysql_fix_extensions': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_fix_extensions',}
  file {'/usr/bin/mysqlhotcopy': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlhotcopy',}
  file {'/usr/bin/mysqlimport': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlimport',}
  file {'/usr/bin/mysql_plugin': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_plugin',}
  file {'/usr/bin/mysql_secure_installation': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_secure_installation',}
  file {'/usr/bin/mysql_setpermission': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_setpermission',}
  file {'/usr/bin/mysqlshow': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlshow',}
  file {'/usr/bin/mysqlslap': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqlslap',}
  file {'/usr/bin/mysqltest': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqltest',}
  file {'/usr/bin/mysqltest_embedded': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysqltest_embedded',}
  file {'/usr/bin/mysql_tzinfo_to_sql': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_tzinfo_to_sql',}
  file {'/usr/bin/mysql_upgrade': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_upgrade',}
  file {'/usr/bin/mysql_waitpid': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_waitpid',}
  file {'/usr/bin/mysql_zap': ensure => link,  target => '/opt/mysql/server-5.6/bin/mysql_zap',}
  file {'/usr/bin/perror': ensure => link,  target => '/opt/mysql/server-5.6/bin/perror',}
  file {'/usr/bin/replace': ensure => link,  target => '/opt/mysql/server-5.6/bin/replace',}
  file {'/usr/bin/resolveip': ensure => link,  target => '/opt/mysql/server-5.6/bin/resolveip',}
  file {'/usr/bin/resolve_stack_dump': ensure => link,  target => '/opt/mysql/server-5.6/bin/resolve_stack_dump',}



}
class mysql-5-6-10::exec {

  # this only needs to be run once
  exec {'mysql_create_datadir-5-6-10':
    command     => "/opt/mysql/server-5.6/scripts/mysql_install_db --basedir=/opt/mysql/server-5.6/ --datadir=/var/lib/mysql/ --user=mysql ",
    refreshonly => true,
    subscribe   => File['/usr/bin/mysql'],
  }

  exec {'mysql_updatercd':
    command     => "/usr/sbin/update-rc.d mysql start 40 2 3 4 5 . stop 40 0 1 6 .",
    refreshonly => true,
    subscribe   => Exec['mysql_create_datadir-5-6-10'],
  }


}

class mysql-5-6-10::service {

  service { 'mysql':
    ensure => running,
    hasstatus => true,
    hasrestart => true,
  }

}

class mysql-5-6-10::postservice {
  $mysql_root_password = hiera('mysql_root_password')

  # Set root password cos  pre-seed doesn't work
  exec {'mysql_setrootpasswd':
    command => "/usr/bin/mysqladmin -uroot password $mysql_root_password",
    creates => "/etc/mysql/.mysql_setroot",
  }


  #this only needs to be run once - done at livedata_local setup
  exec {'mysql_create_tables-5-6-10':
    command     => "/usr/bin/mysql < /etc/mysql/LiveData_Local.sql ",
    refreshonly => true,
    subscribe   => Exec['mysql_setrootpasswd'],
  }


}

This is pretty self explainatory. 

We use hiera to store passwords, and there is a refresh on the exec, as we only want to run the command once.

We also setup the database and user accounts with the 'mysql_create_tables' exec

KVM domain lists (list of guests on KVM)

As we're moving towards virtualisation in our datacentre, we decided to go with KVM.

Sadly, there was no web interface to view them, so I created one in python. This runs in the background.

#!/usr/bin/env python
#
# Starts up webserver and runs forever
# lists domains running and defined
# pks - 20130405
# pks - add console

import re, os, sys, string, optparse
import xml.etree.ElementTree as ElementTree
import libvirt
import socket
import commands
from BaseHTTPServer import BaseHTTPRequestHandler,HTTPServer

Port_Number=80

conn = libvirt.openReadOnly(None) 
assert conn, 'Failed to open connection'

names = conn.listDefinedDomains()
domains = map(conn.lookupByName, names)

ids = conn.listDomainsID()
running = map(conn.lookupByID, ids)

columns = 3

states = {
    libvirt.VIR_DOMAIN_NOSTATE: 'no state',
    libvirt.VIR_DOMAIN_RUNNING: 'running',
    libvirt.VIR_DOMAIN_BLOCKED: 'blocked on resource',
    libvirt.VIR_DOMAIN_PAUSED: 'paused by user',
    libvirt.VIR_DOMAIN_SHUTDOWN: 'being shut down',
    libvirt.VIR_DOMAIN_SHUTOFF: 'shut off',
    libvirt.VIR_DOMAIN_CRASHED: 'crashed',
}
def info(dom):
    [state, maxmem, mem, ncpu, cputime] = dom.info()
    return '%s is %s,' % (dom.name(), states.get(state, state))

class myHandler(BaseHTTPRequestHandler):
  def do_GET(self):
    self.send_response(200)
    self.send_header('Content-type','text/html')
    self.end_headers()
    print 'Defined domains:'
    self.wfile.write ('<b>Defined domains:</b>')
    self.wfile.write ('</br>')
    for row in map(None, *[iter(domains)] * columns):
      for domain in row:
        if domain:
          print info(domain),
          print
          self.wfile.write (info(domain))
          self.wfile.write ('</br>')
    print 'Running domains:'
    self.wfile.write ('<b>Running domains:</b>')
    self.wfile.write ('</br>')
    for row in map(None, *[iter(running)] * columns):
      for domain in row:
        if domain:
          server=info(domain).partition(" ")
          execstring="/usr/local/bin/vm.sh vnc %s" % server[0]
          output=commands.getstatusoutput(execstring)
          vnc=output[1].split(" ")
          vncport=vnc[5]
          print info(domain)
          print vncport


          nic=socket.gethostbyname(socket.gethostname())
          self.wfile.write ("<a href=\"vnc://%s:%s\"> %s </a>" % (nic, vncport ,info(domain)))
          self.wfile.write ('</br>')

try:
  server = HTTPServer(('',Port_Number), myHandler)
  print "Started HTTP server on port", Port_Number
  server.serve_forever()


except KeyboardInterrupt:
  print 'CTRL+C received. Shutting down webserver'
  server.socket.close()
 

All you have to do is surf to port 80 (or modify the script so that  it points to your port of choice) then you could run from your central monitoring machine etc a script which grabs all the KVM hosts info and display it in one whole page.

An example would be this in PHP, where out servers on on the subnet 10.3.117.101 to 114. It also highlights all servers with the name 'testweb'

<html>
<head>

</head>
<body>
<h1>LN1</h1>
<table border=1>
<tr>
<?
    for ($i=1 ; $i <15 ; $i++)
    {
        echo "<td>";
        echo "<a href=\"http://10.3.117.".($i+100)."\">KVM".($i)."</a>";
        echo "</td><td>";
        $ip=100+$i;
        #$test=exec("curl -s http://10.3.117.$ip  | awk 'gsub(/ *<[^>]*> */,\" \")'");
        $test=exec("curl -s http://10.3.117.$ip");
        $test=str_replace("Running domains:","<b>Running domains:</b>",$test);
        $test=str_replace("Defined domains:","<b>Defined domains:</b>",$test);
        $test=str_replace("</br>","",$test);
        #$test=str_replace("testweb","<font color=6666ff>testweb</font>",$test);
        $test=str_replace("is running","",$test);
        echo $test;
        echo "</td></tr><tr>";
        echo "\n\r";
    }

?>
</tr>
</table>
</body>
</html>

AWS IAM policies for S3 and redshift

So we've got a user who needs access to a particular S3 bucket, and also to our redshift instance. Creating policies via IAM is a PITA.

The easiest method is to use the AWS policy generator here.
Sadly, this doesn't quite work, as if you custom build a policy for example to access just a redshift instance, you must also grant a whole host of other (seemingly) unrelated permissions. 

I wanted this particular user to be able to restore a snapshot and create a snapshot. I thought, it required only this:

"redshift:RestoreFromClusterSnapshot",
"redshift:CreateClusterSnapshot",

 
On a particular resource, But nope, you actually need all of this:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": [
        "redshift:*",
        "ec2:DescribeAccountAttributes",
        "ec2:DescribeAvailabilityZones",
        "ec2:DescribeSecurityGroups",
        "ec2:DescribeSubnets",
        "ec2:DescribeVpcs",
        "ec2:DescribeInternetGateways",
        "sns:Get*",
        "sns:List*",
        "cloudwatch:Describe*",
        "cloudwatch:Get*",
        "cloudwatch:List*",
        "cloudwatch:PutMetricAlarm",
        "cloudwatch:EnableAlarmActions",
        "cloudwatch:DisableAlarmActions"
      ],
      "Sid": "Stmtxxxxx",
      "Resource": [
        "*"
      ],
      "Effect": "Allow"
    }
  ]
}

Now to give a user access to an S3 bucket, you will need the below, which gives access to the test bucket.

{
 "Version": "2008-10-17",
 "Id": "Policyxxxxx",
 "Statement": [
  {
   "Sid": "Stmtxxxx",
   "Effect": "Allow",
   "Principal": {
    "AWS": "arn:aws:iam::99999:user/user1"
   },
   "Action": "s3:*",
   "Resource": "arn:aws:s3:::Redshift_dev"
  },
  {
   "Sid": "",
   "Effect": "Allow",
   "Principal": {
    "AWS": "arn:aws:iam::99999:user/user2"
   },
   "Action": "s3:*",
   "Resource": "arn:aws:s3:::Redshift_dev"
  }
 ]
}

This gives users1 and user2 access to only the bucket called 'redshift_dev', and you apply these permissions on the bucket itself via Properties -> Permissions tabs.

I have also found this JSON Lint very handy for checking the policies I write.

 

Accessing Windows 2012/2008 shares with sambaclient and NT_STATUS_LOGON_FAILURE

Ok
so we migrated from a Windows 2008 server to Windows 2012, and our automated build system hudson could no longer copy it's files over to the new server.

using smbclient to create a directory on the fileserver, didn't work after we migrated.

smbclient //server01.corp/dfs_shared/ -U username%password

The old file server could authenticate us, with both the hudson account and our domain admin accounts. The new file server couldn't.

we checked:
firewall rules (hardware)
firewall rules (windows)
local policy (Net lanman auto type -v1/v2)

Eventually,

I figured out this worked.

smbclient //server01.corp/dfs_shared/ -U domain\\username%password

The reason being that we were now authenticating against a file server ONLY. The previous server was also a domain controller (which has no concept of a local account). As the new server was a file server only, when it was authing, it was checking against it's local account, which obviously, doesn't exist.