mysql -hrdshost -uuser -ppassword -Dourdb < ${ETL_SCRIPT}
STEP 08 : Make sure all the data is in the target database
You can do that
If there were no foreign key constraints, mydb should be empty and ourdb should have all the tables.
If there were foreign key constraints, make sure mydb and ourdb have the same number of tables and the same number of rows. Make sure all triggers present by running
SELECT COUNT(1) trigger_count,table_schema
FROM information_schema.triggers
GROUP By table_schema;
STEP 09 : Drop the Old Database Manually
mysql> DROP DATABASE mydb;
I did not include dropping the database in the script, just in case. :-)
Best Answer
You can pull this off using mysqldump. Here the catch: You cannot ship the data because there may be a cost associated with shipping the data.
For this example, let's says you want to rename
mydb
toourdb
STEP 01 : Create the new database
STEP 02 : Get schema without the triggers
STEP 03 : Get the triggers
STEP 04 : Generate script to do
INSERT ... SELECT
across all tablesIF THERE ARE FOREIGN KEY CONSTRAINTS
IF THERE ARE NO FOREIGN KEY CONSTRAINTS
If there are no foreign key constraints, this should go fast.
STEP 05 : Combine the files into a single script
STEP 06 : Review the script
or
STEP 07 : Run the script
STEP 08 : Make sure all the data is in the target database
You can do that
If there were no foreign key constraints, mydb should be empty and ourdb should have all the tables.
If there were foreign key constraints, make sure
mydb
andourdb
have the same number of tables and the same number of rows. Make sure all triggers present by runningSTEP 09 : Drop the Old Database Manually
I did not include dropping the database in the script, just in case. :-)
GIVE IT A TRY !!!