Easily dump or load your entire MySql database using text files
One of my apps currently runs in several different environments: development, test, staging and production. Whenever I deploy to my staging environment, I like make sure that the deployment will be successful on production data. I don’t currently have any synchronisation strategy between staging and production, so I needed a way to quickly and easily replicate the entire production database into my staging database.
The reasoning behind this is that the deployment itself should be a test. Deployment to staging should effectively be a test for a production deployment. If my staging deployment succeeds, then it’s pretty safe to assume that the production deployment will also succeed.
MySql gives us a very handy command to dump the database into a text file: mysqldump. Similarly, it’s just as easy to load a database from a text file.
Dumping data
mysqldump -uEd -pSecret foo_db --skip-triggers --compact
--ignore-table=foo_db.schema_migrations
--no-create-info > data_file
In this command, our user is Ed with a password of Secret. The lack of spaces between the -u and -p switches and their values is intentional. We are targeting the foo_db database. The contents of all tables except the schema_migrations table will be dumped into the data_file text file.
We are also providing a couple of other options to exclude any triggers and to exclude SQL statement that will create the tables.
Loading data
mysql -uEd -pSecret foo_db < data_file
Not much explanation needed here. Load the contents of the data_file into the foo_db database.
Ok, how the hell do I use this…?
Well, I have a rake task for each of the above operations. When I deploy to my staging environment, I dump the entire production database into a text file, and then load that file into the staging database. Then the deployment itself begins, part of which is running migrations.
Here’s what my rake tasks look like:
namespace :db do
namespace :data do
desc "Dump data into sql script file: filename=[target filename]"
task :dump => 'environment' do
environment = (ENV.include?("RAILS_ENV")) ? (ENV["RAILS_ENV"]) : 'development'
ENV["RAILS_ENV"] = RAILS_ENV = environment
database = get_database(environment)
user = ENV['USER'] || 'root'
password = ENV['PASSWORD']
filename = ENV['FILENAME'] || environment
timestamp = ENV['TIMESTAMP'] ? bool(ENV['TIMESTAMP']) : true
file_suffix = DateTime.now.strftime('%Y-%m-%d-%H-%M-%S')
full_filename = filename + (timestamp ? "_#{file_suffix}" : '') + '.sql'
puts "Connecting to #{environment} environment..."
sh "mysqldump -u#{user} -p#{password} #{database} --skip-triggers --compact " +
"--ignore-table=#{database}.schema_migrations " +
"--no-create-info > #{RAILS_ROOT}/db/data/#{full_filename}"
puts "Successfully created #{full_filename} from #{environment} environment."
end
desc "Load data from sql script file: filename=[data file]"
task :load => 'environment' do
environment = (ENV.include?("RAILS_ENV")) ? (ENV["RAILS_ENV"]) : 'development'
ENV["RAILS_ENV"] = RAILS_ENV = environment
database = get_database(environment)
user = ENV['USER'] || 'root'
password = ENV['PASSWORD']
filename = ENV['FILENAME']
raise "Please specify a source file (FILENAME=1)" if filename.blank?
puts "Connecting to #{environment}..."
ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
puts "Truncating tables..."
ActiveRecord::Base.connection.execute('show tables').each do |table|
unless table.to_s == 'schema_migrations'
puts " Truncating #{table}"
ActiveRecord::Base.connection.execute("truncate table #{table.to_s}")
end
end
puts "Importing data from #{filename}..."
sh "mysql -u#{user} -p#{password} #{database} < #{RAILS_ROOT}/#{filename}"
puts "Completed loading #{filename} into #{environment} environment."
end
end
end
private
def get_database(environment)
case environment
when 'test'
'db_test'
when 'staging'
'db_staging'
when 'production'
'db'
else
'db_development'
end
end
def bool(s)
s.match(/(true|t|yes|y|1)$/i) != nil
end
Gotchas!
Obviously this strategy will not work with very large, high-traffic databases. It also make various assumptions about access right and privileges, but for my purposes it works. Be very careful with these commands though – there is no “Are you sure?” safety net when you load a data file. Use with care!
From Agile Developer…to Agile Person
One of the questions that comes up in interviews – without fail – is the one about where you see yourself in the near future. You know the one…
“So Ed, where do you see yourself in 3-5 years?”
Cue long, wordy answer littered with buzz words and important sounding job titles. Yawn.
It’s a standard-issue question, and one that until recently I’ve always been ready to answer, not just because I know it’s coming, but because I thought I knew where I wanted to be in 3-5 years.
Something changed though. I realized that I actually cannot answer that question anymore, not with anything a potential employee would probably want to hear anyway. I decided that rather than just be an Agile Developer, I wanted to be an Agile Person.
What the hell is an Agile Person?
I don’t know where I want to be in 3-5 years. I don’t know what job title I want, neither do I know what I want to be doing. I don’t have a detailed career spec for the next 3 years. What I do know is that as long as I’m doing interesting, challenging things at work, I’m happy for the long-term requirements of my career to evolve naturally, steered solely by the interesting things I’m doing now.
To me, this is what being an Agile Person is, and this is what I’m going to aspire to.
“So Ed, where do you see yourself in 3-5 years?”
Well, I have no idea. But, I if it’s challenging and interesting, I’ll be happy giving it my best shot.
Easy Remote Desktop connections in Ubuntu
SSH is easy, right? Thought so. However, establishing a remote desktop connection with an Ubuntu (or Linux!) machine is a little more tricky.
If you’re ever tried setting up VNC you’ll know how horrible it is to get going. You have to do all kinds of silly things with config files, not to mention making sure that the VNC server is running on the remote machine in order to accept in-coming connection requests to begin with. Awful.
So, instead of inflicting that kind of pain on yourself, just install the xrdp package…
sudo apt-get install xrdp
Then open your favourite Remote Desktop client and you should be able to connect immediately. As I’m connecting from my Ubuntu laptop, I just setup a Terminal Services connection.
It works beautifully.
Deploying with Capistrano and RVM: .rvmrc trust issues!
I’ve been having some problems with my deployments since I introduced a project-specific .rvmrc file. This file IS checked into version control, because I have several machines I develop on and want them to all be the same in terms of gemsets etc.
Capistrano was successfully completing my deployment but the Rails app was not starting. Passenger could not start the application because RVM had not been told to trust the new .rvmrc file. Eh…?!
As it turns out this is a fairly easy fix. I modified my deployment recipe to contain the following:
require 'capistrano/ext/multistage'
require 'bundler/capistrano'
namespace :rvm do
desc 'Trust rvmrc file'
task :trust_rvmrc do
run "rvm rvmrc trust #{current_release}"
end
end
after "deploy:update_code", "rvm:trust_rvmrc"
All this does is run a command to tell RVM that is should trust my rvm file in the new release directory. It’s important to use current_release and not current_path here because RVM needs the actual path – RVM won’t trust a file given a symlink path.
Force autotest to load all your application files
I was having a problem where autotest was not picking up changes to one of my models, and therefore not running my specs when I was saving changes. It was however picking up the filesystem event, which was odd. All I was getting was the following…
# Waiting since 2011-03-08 11:49:33 .. .. # Waiting since 2011-03-08 11:51:53 .. ..
To fix this, create an autotest folder in your application root. Then create a discover.rb file in the new autotest folder with the following content:
Autotest.add_discovery { "rails" }
Autotest.add_discovery { "rspec2" }
Autotest.add_hook(:initialize) do |at|
at.add_mapping(%r%^(models|controllers|routing|views|helpers|mailers|requests|lib)/.*rb$%) do |filename, _|
filename
end
end
What we’re doing here is adding an autotest hook which creates a new file mapping. This will ensure that all files in the folders mathcing our regular expression will be monitored by autotest, thereby firing the relevant specs when you maek an update to either the spec or the model/controller/view etc.
Happy testing.
Customising your sort order
Ever needed to order your records based on a specific ordering? Here’s how to do it…
Let’s say you have some records which track “state”. Values for “state” can be the following:
- pending
- submitted
- accepted
- rejected
Easy enough. However, what if you want to display the results with the following ordering:
- rejected
- pending
- submitted
- accepted
As it turns out, this is pretty easy. Here’s the sql statement to do just that:
select * from things
order by
case state
when 'rejected' then 1
when 'pending' then 2
when 'submitted' then 3
else 99
end
Simples.
Run your specs and features in different environments
By default your specs and cucumber feature both run in the test rails environemnt i.e. RAILS_ENV=test.
However, I need these two environments to be different. First I’ll show you how, then I’ll tell you why.
I want to have my feature run in a cucumber environment i.e. RAILS_ENV=cucumber. Simply edit the features/support/env.rb file and update the default environment value so that you file looks like this:
ENV['RAILS_ENV'] = 'cucumber' Rails.env = 'cucumber'
We still want our feature to hit the test database, so simply create a pointer in your database.yml file to do this:
test: &TEST adapter: ... database: .... host: ... cucumber: <<: *TEST
Lastly, make sure you have a config/environments/cucumber.rb file for your new cucumber environment. I normally just copy the test environment file.
Now when you run your features they will run in their own, separate environment. Groovy.
Optional: You may also want to modify your Gemfile by adding an additional :cucumber group. I’ve never done this and never run into any problems, but bear it in mind.
Why do I need this?
When I run my specs I use spork and autotest. This makes running specs lightning fast. However, you do need to make some tweaks to the test.rb environment file, namely turning class caching off. This is to make sure your specs always run with the latest saved versions of your models.
config.cache_classes = false
For cucumber, you can leave caching on. However, I use database_cleaner for my features with a truncation strategy. This makes the running of selenium features reliable because no transaction has to be rolled back after each scenario – the data is simply truncated.
Simples.
