Import a Mysql database using Ruby / Chef Recipe for Vagrant

Question

I am writing a chef script to automate development environment settings. I can get the created database and grant privileges, but I'm trying to figure out a way to import the mysql dump file into the just created database.

My access code is

ruby_block "Execute grants" do block do require 'rubygems' Gem.clear_paths require 'mysql' m = Mysql.new('localhost', "root", node[:mysql][:server_root_password]) m.query("GRANT ALL ON *.* TO 'root'@'10.0.0.1' IDENTIFIED BY '#{node[:mysql][:server_root_password]}'") m.query('FLUSH PRIVILEGES') end end 

and I was hoping I could run the following query #m.query("-u root -p root db_name < /project/db/import.sql")

but just giving me an error.

I did not do much Ruby, so it was hard for me to understand. Does anyone know how I can do this?

Answer 1

If this is a file path error and you are using a chef solo, try using the path specified in solo.rb, for example:

 /tmp/chef-solo/site-cookbooks/path_to_file.sql 

As a general note, consider using > for user and mysql database tasks. After you configure the necessary dependencies of the cookbook, you can put such code in your main default.rb recipe:

 # externalize conection info in a ruby hash mysql_connection_info = { :host => "localhost", :username => 'root', :password => node['mysql']['server_root_password'] } # drop if exists, then create a mysql database named DB_NAME mysql_database 'DB_NAME' do connection mysql_connection_info action [:drop, :create] end # query a database from a sql script on disk mysql_database "DB_NAME" do connection mysql_connection_info sql { ::File.open("/tmp/chef-solo/site-cookbooks/main/path/to/sql_script.sql").read } action :query end #or import from a dump file mysql_database "DB_NAME" do connection mysql_connection_info sql "source /tmp/chef-solo/site-cookbooks/main/path/to/sql_dump.sql;" end 

I did not test the latter, because storing the database file in the chef directory really slows down.

See also: Import SQL file in mysql

Answer 2

You can back up from the MySQL command line client, but not from an SQL query. You need to execute the command from the shell. I believe the execute resource can do the trick for you:

http://wiki.opscode.com/display/chef/Resources#Resources-Execute

Answer 3

I'm not a Ruby guy, but I managed to get the chef to import a large .sql file using the mysql command-line tool. The tasks that I need to solve:

• Import .sql file into 100 MB ranges (YMMV if you need GB or TB)
• Idempotentcy - only import if .sql file .sql changed
• Do not pass MySQL credentials as command parameters (security issue)

First, I created a .my.cnf file .my.cnf for transferring credentials:

Templates / default /.my.cnf.erb

 [client] host=<%= @host %> user=<%= @user %> password="<%= @password %>" 

Then I added a resource to my recipe that would populate the template:

Recipes / Import-db.rb

 template '/root/.my.cnf' do mode 0600 variables({ :host => 'localhost', :user => 'root', :password => node[:your_cookbook][:db][:root_password], }) end 

(Where node[:your_cookbook][:db][:root_password] is the attribute containing the MySQL administrator password)

Safety note . For simplicity, I am importing as a root . If the .sql file you want to import is not from a reliable source, you want to start mysql as a limited user and connect to MySQL with a limited user db, which has access only to the corresponding database.

Finally, I added another resource to the recipe that actually imports:

 backup_sql = '/path/to/the/db-backup.sql' db_last_modified = "/etc/db-#{node[:your_cookbook][:db][:name]}.lastmodified" execute 'restore backup' do command "mysql #{node[:your_cookbook][:db][:name]} <'#{backup_sql}' && touch '#{db_last_modified}'" not_if { FileUtils.uptodate?(db_last_modified, [backup_sql]) } end 

(Where node[:your_cookbook][:db][:name] is the name of the MySQL database to be restored.)