Ask Your Question

How do you import a database with puppet?

asked 2013-10-23 12:42:43 -0500

spuder gravatar image

updated 2013-10-23 20:37:30 -0500

I have testing servers that need to have a schema imported into mysql

The best way I can think to do this is with an exec

exec {'import mysql':
  path  => '/usr/bin:/usr/sbin',
  command => 'mysql -u foo -p bar -D foobar < mysqlfun.sql',

The only way to preservice idempotentce would be to leave a breadcrumb, and include an 'unless' statement.

This seems clunky. Is there no better way to seed a database?

edit retag flag offensive close merge delete

3 Answers

Sort by ยป oldest newest most voted

answered 2013-10-27 06:40:44 -0500

doc75 gravatar image

If you are working with a MySql database, you can also use the puppetlabs-mysql module.

When you define a database, you have the ability to provide as input an sql script to populate it. You have a simple example on how to create a DB

You can add the following parameter to populate the database:

sql => '/path/to/sql_file.sql'

Do not forget to create this file first on the client side.

And you can also force the import of this file at each run using:

enforce_sql => true

Here is a "complete" example:

file {
    "temporary import file for xxxxxx DB ...
edit flag offensive delete link more

answered 2013-10-24 01:51:51 -0500

golja gravatar image

Depends how much time you would like to spend on this task and how often you will use this feature.

Definitely one way to do it is with exec using unless and breadcrumb file, but make it more generic with a define type and maybe pass parameters such as $user, $pass, $db, $schema_file. etc.

Another way to do it is to create a proper native type with ruby and add extra features like diffing the existing schema if there is one vs the new one, etc. It shouldn't be too hard, but it's definitely more time consuming.

edit flag offensive delete link more


surely I'm not the first person to try and do this am I? Seems like there should be a native type already written out there.

spuder gravatar imagespuder ( 2013-10-24 08:54:55 -0500 )edit

Nope I had a look and I could not find it. Probably you can ask a feature request in puppetlabs-mysql module.

golja gravatar imagegolja ( 2013-10-24 23:03:29 -0500 )edit

answered 2015-02-24 21:06:24 -0500

cpitman gravatar image

The ability to apply schema changes has come up fairly often, so I just published a puppet module (database_schema) that uses flybase or liquibase to populate a database as part of a puppet manifest. Since it uses migration tools, it can handle both initial database population as well as applying migration scripts to an existing database.

As en example, if you wanted to use flyway to apply simple sql migration scripts, it would look something like this:

include ::database_schema::flyway

database_schema::flyway_migration { 'Migrate TestDB':
  db_username   => root,
  db_password   => password,
  jdbc_url      => 'jdbc:mysql://localhost/testdb',
  schema_source => '/vagrant/tests/data/stage1'
edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower


Asked: 2013-10-23 12:42:43 -0500

Seen: 6,351 times

Last updated: Oct 27 '13