Executing MySQL load data once on chef using tags

Recently I have been trying to cut off expanses in one of the projects I ve been working. So we decided to move two of the t1.micro ec2 AWS instances to our dedicated server,which was already hosting VMS for other projects, as one KVM guest. First instance was the production MySQL DB server for the project while the other was the webserver(apache) hosting our php application. I thought it would be great to have the whole migration automated with chef in case we need to migrate later back to AWS or to another dedicated server.

Fortunately our php application was under git version control using different branches for development, QA and production. So the community cookbooks needed for the webserver migration were git, apache2, php and mysql. I only had to create a cookbook that would clone our php application from our git server,checkout specific branch or tag or hash, create the configuration file with the credentials used to connect to MySQL DB and configure apache.

To migrate our MySQL DB server we needed to install the mysql-server packages, change default mysql root password,create a database and create a user/password to use the database. What I d also like was to populate the database with the data of the production DB. I have been backing up the production DB every now and then (using a cron script) to AWS S3 so this was something that would help since I would be pulling the data from S3 bucket and then load them on MySQL. The filesize without being compressed was about 30MB while compressed was about 3MB. My custom chef cookbook depended on mysql, database and aws community cookbooks. In order to load the data I thought of 2 ways.

1) To create a another recipe on my cookbook let’s call it insert_db_data which I would have it on my runlist when I d bootstrap my node and then have it removed manually with knife node edit NODENAME command
2) To add a tag during bootstrap of the node. When the bootstrap process would finish the tag would be automatically removed so next time chef-client would run then this part of the code would not be executed.

The code of pulling the compressed database dump from aws, uncompress and load it on MySQL is

include_recipe 'aws'
 aws_creds = data_bag_item('credentials','aws')
 if aws_creds && aws_keys = aws_creds[node.chef_environment] 
  aws_s3_file "/tmp/production_latest.sql.bz2" do
   bucket "MyBucket/DB_backups"
   remote_path "production_latest.sql.bz2"
   aws_access_key_id "#{aws_keys['aws_access_key_id']}"
   aws_secret_access_key "#{aws_keys['aws_secret_access_key']}"
  end
 end
 execute "uncompress-dbprod" do
   cwd "/tmp/"
   command "unzip2 production_latest.sql.bz2" 
 end
 execute "load-DB-data" do
   command "mysql -u root -p'#{node['mysql']['server_root_password']}' #{db_name} < /tmp/production_latest.sql"
 end

If I have choosen method 1 then this would be the content of my insert_db_data.rb recipe file.I have choosen to make my life easier and went for method 2. The difficult part on that was how to add a tag on the node that was about to bootstrap. After searching around I found that this could be done using the the following while executing knife

 $ knife bootstrap XX.XX.XX.XX -x root --node-name NodeName \
--json-attributes "{\"tags\": [      "install_db_data\"    ]  }" \
-r "role[my_role]"

The modified code is highlighted below.

include_recipe 'aws'
if tagged?("install_db_data")
 aws_creds = data_bag_item('credentials','aws')
 if aws_creds && aws_keys = aws_creds[node.chef_environment] 
  aws_s3_file "/tmp/production_latest.sql.bz2" do
   bucket "MyBucket/DB_backups"
   remote_path "production_latest.sql.bz2"
   aws_access_key_id "#{aws_keys['aws_access_key_id']}"
   aws_secret_access_key "#{aws_keys['aws_secret_access_key']}"
  end
 end
 execute "uncompress-dbprod" do
   cwd "/tmp/"
   command "bunzip2 production_latest.sql.bz2"
 end
 execute "load-DB-data" do
   command "mysql -u root -p'#{node['mysql']['server_root_password']}' #{db_name} < /tmp/mobivox_production_latest.sql"
 end
 untag ("install_db_data")
end
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s