I’m continuing with my plan to create a series of articles for learning Chef from a developer perspective.

Part #1 gave an intro to Chef, Chef Solo, Vagrant, and Virtualbox. I also created my first Ubunutu VM running Apache and serving up the default website.

Part #2 got into creating a cookbook of my own, and evolved it whilst introducing PHP into the mix.

In this article I’ll get MySQL installed and integrated with PHP, and tidy up my own recipe.

Adding a database into the mix

1. Getting MySQL

Download mysql cookbook from the Opscode github repo into your “cookbooks” subdirecctory:

mysql

[bash]git clone https://github.com/opscode-cookbooks/mysql.git
[/bash]

Since this will be a server install instead of a client one you’ll also need to get OpenSSL:

openssl

[bash]git clone https://github.com/opscode-cookbooks/openssl.git
[/bash]

Now use Chef Solo to configure it by including the recipe reference and the mysql password in the Vagrantfile I’ve been using in the previous articles;

Vagrantfile

[ruby highlight=”14-17,26″]Vagrant.configure("2") do |config|
config.vm.box = "precise32"
config.vm.box_url = "http://files.vagrantup.com/precise32.box"
config.vm.network :forwarded_port, guest: 80, host: 8080

config.vm.provision :shell, :inline => "apt-get clean; apt-get update"

config.vm.provision :chef_solo do |chef|

chef.json = {
"apache" => {
"default_site_enabled" => false
},
"mysql" => {
"server_root_password" => "blahblah",
"server_repl_password" => "blahblah",
"server_debian_password" => "blahblah"
},
"mysite" => {
"name" => "My AWESOME site",
"web_root" => "/var/www/mysite"
}
}

chef.cookbooks_path = ["cookbooks","site-cookbooks"]
chef.add_recipe "mysql::server"
chef.add_recipe "mysite"
end
end
[/ruby]

No need to explicitly reference OpenSSL; it’s in the “cookbooks” directory and since the mysql::server recipe references it it just gets pulled in.

If you run that now you’ll be able to ssh in and fool around with mysql using the user root and password as specified in the chef.json block.

[bash]vagrant ssh
[/bash]

and then

[bash]mysql -u root -p
[/bash]

and enter your password (“blahblah” in my case) to get into your mysql instance.

Now let’s make it do something. Using the mysql::ruby recipe it’s possible to orchestrate a lot of mysql functionality; this also relies on the build-essential cookbook, so download that into your “cookbooks” directory:

Build essential

[bash]git clone https://github.com/opscode-cookbooks/build-essential.git
[/bash]

To get some useful database abstraction methods we need the database cookbook:

Database

[bash]git clone https://github.com/opscode-cookbooks/database.git
[/bash]

The database cookbook gives a nice way of monkeying around with an RDBMS, making it possible to do funky things like:

[ruby]mysql_connection = {:host => "localhost", :username => ‘root’,
:password => node[‘mysql’][‘server_root_password’]}

mysql_database "#{node.mysite.database}" do
connection mysql_connection
action :create
end
[/ruby]

to create a database.

Add the following to the top of the mysite/recipes/default.rb file:

[ruby]include_recipe "mysql::ruby"

mysql_connection = {:host => "localhost", :username => ‘root’,
:password => node[‘mysql’][‘server_root_password’]}

mysql_database node[‘mysite’][‘database’] do
connection mysql_connection
action :create
end

mysql_database_user "root" do
connection mysql_connection
password node[‘mysql’][‘server_root_password’]
database_name node[‘mysite’][‘database’]
host ‘localhost’
privileges [:select,:update,:insert, :delete]
action [:create, :grant]
end

mysql_conn_args = "–user=root –password=#{node[‘mysql’][‘server_root_password’]}"

execute ‘insert-dummy-data’ do
command %Q{mysql #{mysql_conn_args} #{node[‘mysite’][‘database’]} <<EOF
CREATE TABLE transformers (name VARCHAR(32) PRIMARY KEY, type VARCHAR(32));
INSERT INTO transformers (name, type) VALUES (‘Hardhead’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Chromedome’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Brainstorm’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Highbrow’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Cerebros’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Fortress Maximus’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Chase’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Freeway’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Rollbar’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Searchlight’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Wideload’,’Throttlebot’);
EOF}
not_if "echo ‘SELECT count(name) FROM transformers’ | mysql #{mysql_conn_args} –skip-column-names #{node[‘mysite’][‘database’]} | grep ‘^3$’"
end
[/ruby]

and add in the new database variable in Vagrantfile:

[ruby highlight=”22″]Vagrant.configure("2") do |config|
config.vm.box = "precise32"
config.vm.box_url = "http://files.vagrantup.com/precise32.box"
config.vm.network :forwarded_port, guest: 80, host: 8080

config.vm.provision :shell, :inline => "apt-get clean; apt-get update"

config.vm.provision :chef_solo do |chef|

chef.json = {
"apache" => {
"default_site_enabled" => false
},
"mysql" => {
"server_root_password" => "blahblah",
"server_repl_password" => "blahblah",
"server_debian_password" => "blahblah"
},
"mysite" => {
"name" => "My AWESOME site",
"web_root" => "/var/www/mysite",
"database" => "great_cartoons"
}
}

chef.cookbooks_path = ["cookbooks","site-cookbooks"]
chef.add_recipe "mysql::server"
chef.add_recipe "mysite"
end
end
[/ruby]

Now we need a page to display that data, but we need to pass in the mysql password as a parameter. That means we need to use a template; create the file templates/default/robotsindisguise.php.erb with this content:

[php]<?php
$con = mysqli_connect("localhost","root", "<%= @pwd %>");
if (mysqli_connect_errno($con))
{
die(‘Could not connect: ‘ . mysqli_connect_error());
}

$sql = "SELECT * FROM great_cartoons.transformers";
$result = mysqli_query($con, $sql);

?>
<table>
<tr>
<th>Transformer Name</th>
<th>Type</th>
</tr>
<?php
while($row = mysqli_fetch_array($result, MYSQL_ASSOC))
{
?>
<tr>
<td><?php echo $row[‘name’]?></td>
<td><?php echo $row[‘type’]?></td>
</tr>
<?php
}//end while
?>
</tr>
</table>
<?php
mysqli_free_result($result);
mysqli_close($con);
?>
[/php]

That line at the top might look odd:

[php]$con = mysqli_connect("localhost","root", "<%= @pwd %>");
[/php]

But bear in mind that it’s an ERB (Extended RuBy) file so gets processed by the ruby parser to generate the resulting file; the PHP processor only kicks in once the file is requested from a browser.

As such, if you kick off a vagrant up now and (eventually) vagrant ssh in, open /var/www/robotsindisguise.php in nano/vi and you’ll see the line

[php]$con = mysqli_connect("localhost","root", "<%= @pwd %>");
[/php]

has become

[php]$con = mysqli_connect("localhost","root", "blahblahblah");
[/php]

browsing to http://localhost:8080/robotsindisguise.php should give something like this:

2. Tidy it up a bit

Right now we’ve got data access stuff in the default.rb recipe, so let’s move that lot out; I’ve created the file /recipes/data.rb with these contents:

data.rb
[ruby]include_recipe "mysql::ruby"

mysql_connection = {:host => "localhost", :username => ‘root’,
:password => node[‘mysql’][‘server_root_password’]}

mysql_database node[‘mysite’][‘database’] do
connection mysql_connection
action :create
end

mysql_database_user "root" do
connection mysql_connection
password node[‘mysql’][‘server_root_password’]
database_name node[‘mysite’][‘database’]
host ‘localhost’
privileges [:select,:update,:insert, :delete]
action [:create, :grant]
end

mysql_conn_args = "–user=root –password=#{node[‘mysql’][‘server_root_password’]}"

execute ‘insert-dummy-data’ do
command %Q{mysql #{mysql_conn_args} #{node[‘mysite’][‘database’]} <<EOF
CREATE TABLE transformers (name VARCHAR(32) PRIMARY KEY, type VARCHAR(32));
INSERT INTO transformers (name, type) VALUES (‘Hardhead’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Chromedome’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Brainstorm’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Highbrow’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Cerebros’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Fortress Maximus’,’Headmaster’);
INSERT INTO transformers (name, type) VALUES (‘Chase’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Freeway’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Rollbar’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Searchlight’,’Throttlebot’);
INSERT INTO transformers (name, type) VALUES (‘Wideload’,’Throttlebot’);
EOF}
not_if "echo ‘SELECT count(name) FROM transformers’ | mysql #{mysql_conn_args} –skip-column-names #{node[‘mysite’][‘database’]} | grep ‘^3$’"
end
[/ruby]

I’ve moved the php recipe references into recipes/webfiles.rb:

webfiles.rb
[ruby]include_recipe "php"
include_recipe "php::module_mysql"

# — Setup the website
# create the webroot
directory "#{node.mysite.web_root}" do
mode 0755
end

# copy in an index.html from mysite/files/default/index.html
cookbook_file "#{node.mysite.web_root}/index.html" do
source "index.html"
mode 0755
end

# copy in my usual favicon, just for the helluvit..
cookbook_file "#{node.mysite.web_root}/favicon.ico" do
source "favicon.ico"
mode 0755
end

# copy in the mysql demo php file
template "#{node.mysite.web_root}/robotsindisguise.php" do
source "robotsindisguise.php.erb"
variables ({
:pwd => node.mysql.server_root_password
})
mode 0755
end

# use a template to create a phpinfo page (just creating the file and passing in one variable)
template "#{node.mysite.web_root}/phpinfo.php" do
source "testpage.php.erb"
mode 0755
variables ({
:title => node.mysite.name
})
end
[/ruby]

So /receipes/default.rb now looks like this:

default.rb
[ruby]include_recipe "apache2"
include_recipe "apache2::mod_php5"

# call "web_app" from the apache recipe definition to set up a new website
web_app "mysite" do
# where the website will live
docroot "#{node.mysite.web_root}"

# apache virtualhost definition
template "mysite.conf.erb"
end

include_recipe "mysite::webfiles"
include_recipe "mysite::data"
[/ruby]

Summary

Over the past three articles we’ve automated the creation of a virtual environment via a series of code files, flat files, and template files, and a main script to pull it all together. The result is a full LAMP stack virtual machine. We also created a new website and pushed that on to the VM also.

All files used in this post can be found in the associated github repo.

Any comments or questions would be greatly appreciated, as would pull requests for improving my lame ruby and php skillz! (and lame css and html..)