Importing Mysql database using Ruby/Chef Recipe for Vagrant

Question

I am writing a chef script to automate setting dev environments. I can get a database created and grant privileges but I am trying to find out a way to import a mysql dump file into the database that has just been created.

My code for granting the access 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 would be able to do the following query #m.query("-u root -p root db_name < /project/db/import.sql")

but is just gives me an error.

I haven't done much Ruby so finding it hard to figure out. Anybody know how I can do this?

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

If it's a file path error, and you're using chef solo, try using the path specified within solo.rb, like:

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

As a general note, consider using the database cookbook for mysql user and database management tasks. Once you setup the necessary cookbook dependencies, you can put code like this into your main recipe's default.rb:

# 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

Haven't tested that last one because storing a database file within the chef directory really slows things down.

1 Comment

Michael Kropat Over a year ago

The source command is only implemented in the mysql CLI tool — not MySQL itself, so attempting to use it from Ruby results in a syntax error. The File.open().read solution works well for small .sql files, however it throws an error if the file is larger than 1MB.

davidstamm · Accepted Answer · 2011-05-20 04:09:46Z

3

You can create a backup from the MySQL command line client, but not from within a SQL query. You need to execute the command from the shell. I believe the execute resource might do the trick for you:

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

answered May 20, 2011 at 4:09

davidstamm

1511 silver badge6 bronze badges

3 Comments

Craig Ward Over a year ago

I think I now have the correct code in place but I think the problem is that in the command line the following works mysql -p -u root test_project < import.sql but you get a password prompt which needs filling in, then it executes the command. I am using the execute resource with the same command but even if I specify the password using -p it fails.

Rik Heywood Over a year ago

you can include the password on the command line. mysql -u root -pSecret test_project < import.sql will not prompt for a password (assuming you get your password right. You will have to include the password in the chef script, but for a dev vagrant vm config this shouldn't be a problem.

Roberto Aloi Over a year ago

An alternative is to use randomly generated passwords, as wordpress does for its chef recipe: github.com/opscode/cookbooks/tree/master/wordpress

Michael Kropat · Accepted Answer · 2015-07-20 23:49:05Z

I'm not really a Ruby guy, but I managed to get Chef to import a large .sql file by leveraging the mysql command line tool. Challenges I needed to solve:

Import a .sql file in the 100's of MB range (YMMV if you need GBs or TBs)
Idempotentcy — only run the import if the .sql file has changed
Not pass credentials to MySQL as command parameters (a security concern)

First I created a .my.cnf file template to pass the 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 an attribute that contains the MySQL root password)

Security Note: for simplicity I'm doing the import as the root user. If the .sql file to be imported is not from a trusted source, you will want to run mysql as a limited user and connect to MySQL with a limited db user that only has access to the database in question.

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

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 that will be restored.)

Collectives™ on Stack Overflow

Importing Mysql database using Ruby/Chef Recipe for Vagrant

3 Answers 3

1 Comment

3 Comments

templates/default/.my.cnf.erb

recipes/import-db.rb

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

templates/default/.my.cnf.erb

recipes/import-db.rb

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related