1

I need to take a mysqldump of specific tables across all databases in a cluster. In this case the cluster has more than 300+ databases. And I need to take the dump of table1 and table2 from each of the 300+ databases.

Then I need to restore the dump to another cluster, to a single database having table1 and table2. Essentially, the data from the tables in the 300+ databases above needs to be consolidated to the tables in a single database in this cluster.

Example:
Cluster A has information about a company's employees in a table called Employees which is spread out across databases that represent the company's locations, like database Los Angeles, Austin, New York etc...

I need to take a dump of all the Employee tables across the location databases.

Then in Cluster B, there will be a database called USA in which there will be a table called Employees which will have a union of all the Employee information from the above dump.

1 Answer 1

1

mysqldump cannot do what you ask. It's meant for backups, and whatever transforms data is not a backup.

But all you have to do is to create a view, and then backup the contents of the view.

The following SQL creates the view:

SELECT
    @view_definition := GROUP_CONCAT('(SELECT * FROM ', TABLE_SCHEMA, '.employee)' SEPARATOR ' UNION ') AS view_definition
    FROM information_schema.TABLES
    WHERE TABLE_NAME = 'employee';
SET @view_definition := CONCAT('CREATE VIEW usa.employee AS ', @view_definition);
SELECT @view_definition;
PREPARE stmt_create_view FROM @view_definition;
EXECUTE stmt_create_view;
DEALLOCATE PREPARE stmt_create_view;

mysqldump doesn't backup views. But you can backup an employee table structure with SHOW CREATE TABLE and the view contents into a CSV file with SELECT INTO OUTFILE.

To create the table into cluster B, run the CREATE TABLE statement and load the CSV file contents with LOAD DATA INFILE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.