Skip to content

Instantly share code, notes, and snippets.

@woowa-hsw0

woowa-hsw0/copy_database.sql

Created December 14, 2017 23:55
Show Gist options
  • Save woowa-hsw0/2d6bc58cd62773d0443f2401fe00f6e3 to your computer and use it in GitHub Desktop.
Save woowa-hsw0/2d6bc58cd62773d0443f2401fe00f6e3 to your computer and use it in GitHub Desktop.
Download ZIP
RENAME DATABASE / COPY DATABASE in MySQL
Raw
copy_database.sql
SELECT @src_schema := 'dbname', @dst_schema := 'dbname_bak';
SELECT concat(
'CREATE TABLE ',
'`', @dst_schema, '`', '.',
'`', TABLE_NAME, '`', ' LIKE ',
'`', @src_schema, '`', '.',
'`', TABLE_NAME, '`', ';'
) q
FROM information_schema.tables
WHERE table_schema = @src_schema;
SELECT concat(
'INSERT INTO ',
'`', @dst_schema, '`', '.',
'`', TABLE_NAME, '`', ' '
'SELECT * FROM ',
'`', @src_schema, '`', '.',
'`', TABLE_NAME, '`', ';'
) q
FROM information_schema.tables
WHERE table_schema = @src_schema;
Raw
rename_database.sql
SELECT @src_schema := 'dbname', @dst_schema := 'dbname2';
SELECT concat(
'RENAME TABLE ',
'`', @src_schema, '`', '.',
'`', TABLE_NAME, '`', ' TO ',
'`', @dst_schema, '`', '.',
'`', TABLE_NAME, '`', ';'
) q
FROM information_schema.tables
WHERE table_schema = @src_schema;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment