MySQL How to copy all data in tables from one huge database to another in the same server

Question

Consider that I have 2 databases with the exact same structure sitting on the same server one with data and the other is empty. The usual way to copy the data from the first one to the second is to export it on my local machine and then import it on the other one but what if the database size is over the server's limit (and I have no control over that limit), the only two options I know of are:

• To export bits and pieces of the first database over a number of times so I'd end up with 10 sql files for example each not exceeding the upload size limit and import those one by one.
• To write a query to copy a table from the first database to the second (i.e. INSERT INTO database2.table SELECT * FROM database1.table) Which is a better option if you have small number of tables because you'd have to write the same query for each table.

Is there a way to use the second method where I wouldn't have to write the tables' names? In theory I should be able to get the tables names from the information_schema database and work on those, but I'm not sure where they are stored and how that would work in a query.

Answer 1

You can query information schema for list of all tables in your source database and build the import commands.

SELECT
    CONCAT('INSERT INTO target_db.', TABLE_NAME, ' SELECT * FROM source_db.', TABLE_NAME, ';') restore_command
FROM information_schema.TABLES
WHERE
    TABLE_SCHEMA='source_db'
    AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME;

Then copy/paste the output in query window.