Mysql – restore snapshots from another instance

amazon-rdsdatabase-restoreMySQL

I have 2 amazon rds instance (one for production, and another for development/testing). Now I want to restore one database from production instance to my development instance. I am taking snapshots on my production instance, but there is no options to restore snapshots on development instance from another instance.

Is there any way to do this without downloading snapshot?

Best Answer

Restoring from snapshot means creating a new RDS instance, so you will basically be replacing the old development instance with a new one.

First you have to either delete or rename the original development instance, otherwise it's name can't be reused for the new instance.

If you want an RDS instance with both the development schema and production schema, then you'll have to transfer the data using database-specific tools (e.g., mysqldump if you're using MySQL, data pump if using Oracle, etc).

Related Solutions

Mysql – Run MySQLDump without Locking Tables

Does the --lock-tables=false option work?

According to the man page, if you are dumping InnoDB tables you can use the --single-transaction option:

--lock-tables, -l

Lock all tables before dumping them. The tables are locked with READ
LOCAL to allow concurrent inserts in the case of MyISAM tables. For
transactional tables such as InnoDB and BDB, --single-transaction is
a much better option, because it does not need to lock the tables at
all.

For innodb DB:

mysqldump --single-transaction=TRUE -u username -p DB

Mysql – Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Best Answer

Related Solutions

Mysql – Run MySQLDump without Locking Tables

Mysql – Which MySQL data type to use for storing boolean values

Related Topic