CODEWITHSUNDEEP
mysqlsqlruby-on-railsdatabaseruby-on-rails-3
Leem.fin
Leem.fin

Reputation: 42672

copy tables between databases

I am using MySQL v5.1.

I am developing a Rails app. and writing a ruby script to copy database. So far, I have got an array of table names, the number of tables is 2090. I need to create all the tables in a new database, my code looks like:

#"table_names" is fetched by execute 'show tables' SQL commands

 table_names.each { |tbl_name|
     ActiveRecord::Base.connection.execute("CREATE TABLE #{new_db_name}.#{tbl_name} LIKE #{old_db_name}.#{tbl_name}")

  }

This code works, but it took a long time to complete, because the code has to execute the CREATE TABLE command one by one and there are 2090 tables to create.

I am wondering is there any way to have bulk creating of tables (like bulk inserting of data) in SQL to save the time? If not, how can I improve the speed of creating the tables? That's copy all 2090 tables from one database to another.

P.S. I don't want to hard code all 2090 table names in SQL file.

Upvotes: 0

Views: 1603

Answers (2)

Duke Hall
Duke Hall

Reputation: 582

Sounds like what you're looking for is a SchemaCompare tool as opposed to a DataCompare tool. This is built into Visual Studio for SQL. This tool will do that: http://toadformysql.com/index.jspa

Upvotes: 0

Marc B
Marc B

Reputation: 360872

Simplest method in mysql is to do a mysqldump of the database in question, then restore it to the new database, e.g:

mysql_dump -pPASSWORD -uUSERNAME name_of_db > name_of_db.sql
mysql -pPASSWORD -uUSERNAME name_of_db < name_of_db.sql

the dump file will contain all the necessary DDL/DML queries to recreate the database, plus disabling foreign keys and whatnot so that the dump can be loaded without causing any foreign key problems while the restored DB is in a halfway state.

Upvotes: 4

Related Questions