Spockproxy: Load data into each specific shard

Note – this post is a footnote for my MySQL users conference presentation Sharding Using Spockproxy: A Sharding-only Version of MySQL Proxy.

When you finished my last blog post you have a directory for each shard full of load files. The tables had been created so now we’re ready to load these files.

These two queries will generate load scripts, modify them as needed or write your own.

SELECT concat(‘ LOAD DATA INFILE \’
/db0′, ‘/’, st.table_name, ‘\’ INTO TABLE ‘, st.table_name, ‘;’) AS ”
FROM shard_table_directory st
WHERE status = ‘universal’;

and run this once for each shard (change the sr.database_id = 1 to each database_id).

SELECT concat(‘ LOAD DATA INFILE \’
/db’, database_id, ‘/’, st.table_name, sr.range_id, ‘\’ INTO TABLE ‘, st.table_name, ‘;’) AS ”
FROM shard_table_directory st,
shard_range_directory sr
WHERE status = ‘federated’ AND range_id < 30
and sr.database_id = 1;

Now you have one universal load script and one load script for each shard. Check these carefully and run each one on the appropriate database.

After this is done you have all the data loaded – you’re almost there. The last step is to make the universal data visible within each shard. Right now it should be replicated to each database server but it is in it’s own database. We want to create a view in each shard for each table in the local universal database on that server. Again I have a query to do most of the work for you:

First drop the old views (not needed the first time). Modify the <> and the <>

SELECT CONCAT(‘DROP VIEW ‘, TABLE_NAME, ‘;’) AS ‘–’ FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘<>’ AND TABLE_TYPE = ‘VIEW’ ;

and then create the news views.

SELECT CONCAT(‘CREATE VIEW ‘, t.TABLE_NAME, ‘ AS SELECT ‘, group_concat( COLUMN_NAME order by ORDINAL_POSITION), ‘ FROM <>.’, t.TABLE_NAME, ‘;’) AS ‘–’ FROM INFORMATION_SCHEMA.TABLES t, INFORMATION_SCHEMA.COLUMNS c WHERE t.TABLE_SCHEMA = ‘<>’ AND c.TABLE_SCHEMA = ‘<>’ and t.TABLE_NAME = c.TABLE_NAME GROUP BY t.TABLE_NAME;

Save and run the output from these on each shard.

Tags:

Comments are closed.