Need to store an sql dump in other schema in MYSQL V5.1.11

Question

I have a situation in which I have taken a backup(sql scripyts containing the insert queries) of a table in one of the environment, and I want that backup to be restored in a different schema in in MYSQLClient Version 5.1.11. The problem is that the file is too big(200MB).

The backup file contains the "use schema_name" query, so if i try to restore that, It will be restored in the schema_name mentioned in that script.

Neither I can edit that file, since it is too big, that after edit it is not saved.

Note: the schema above means the DATABASE.

Please suggest me an approach.

Thanks.

Answer 1

If you really can't edit the dump file (don't know what OS you're using but if it is Linux then you could try using something like sed to replace the schema name or a Windows equivalent) then you could try:

1. Create the original schema in your new database
2. Run the dump file into the original schema in your new database
3. Create your new schema in your new database
4. Rename each table from your original schema to your new schema.
5. Drop the original schema

You can generate a script to move all tables in one schema to another schema using the following script:

To create a script to move all tables from 'FromSchema' to 'ToSchema':

select concat('RENAME TABLE ',table_schema,'.',table_name,' TO ','ToSchema','.',table_name,';')
from information_schema.tables t
where t.table_schema = 'FromSchema';

Good luck!