Skip to content

Instantly share code, notes, and snippets.

@rpbouman
Created April 10, 2016 19:18
Show Gist options
  • Save rpbouman/91be391f5ec24f376195b615be4cb1cb to your computer and use it in GitHub Desktop.
Save rpbouman/91be391f5ec24f376195b615be4cb1cb to your computer and use it in GitHub Desktop.
select concat(
'mysqldump'
, ' --user=wcm_dwh'
, ' --password=wcm_dwh'
, ' --no-create-info'
, ' --where="'
, case p2.partition_method
when 'hash' then
concat(
p2.partition_expression, ' % '
, (select count(*)
from information_schema.partitions p3
where p3.table_schema = p2.table_schema
and p3.table_name = p2.table_name)
, ' = ', (p2.partition_ordinal_position - 1)
)
when 'list' then
concat(
p2.partition_expression
, ' in (', p2.partition_description, ')'
)
when 'range' then
concat(
coalesce(
concat(
p2.partition_expression
, ' >= '
, p1.partition_description
, ' and '
)
, ''
)
, concat(
p2.partition_expression
, ' < '
, p2.partition_description
)
)
end
, '" ', p2.table_schema
, ' ', p2.table_name
, ' > '
, p2.table_schema, '.', p2.table_name
, '.', p2.partition_name, '.sql'
)
from information_schema.partitions p1
right join information_schema.partitions p2
on p1.table_schema = p2.table_schema
and p1.table_name = p2.table_name
and p1.partition_ordinal_position + 1 = p2.partition_ordinal_position
where p2.table_schema = schema()
and p2.partition_method in ('hash', 'list', 'range')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment