The fastest way to Copy Schema or Table in MySQL DB

Question

I have a schema (size 500 GB) in my MySQL DB and I want to make a copy of this schema to another schema in the same DB.

May I ask what is the fastest way of copying all the tables in that schema? (I am thinking about using create table select * from statement)

There are more than 100 tables in the schema, among which the biggest one is around 250GB...
All tables in InnoDB engine
All table has the index built (one or multiple columns)
The MySQL version is commercial 8.0.13
MySQL server sits in AWS REHL EC2

Thank you

If you ask it you seems to be newbie in that. If so I recommend you to perform backup of source database (excluding CREATE DATABASE and USE statements by command-line switches, but remembering database options) and then restore it with new schema name (with manual creating new database and switching to it). Use std MySQL programs for that. It is not the fastest method, but it is safe. Do not forget to include stored procedures into the backup if they are used, and check/edit their (and functions/triggers) code if database name is hard-coded in their source texts. — Akina, Jan 29 '19 at 6:21
I want to make a copy of this schema to another schema in the same DB. Strange phrase - in MySQL the terms "database" and "schema" are synonims. So " schema in ... DB" means "DB in DB", which cannot exist. — Akina, Jan 29 '19 at 8:08
Agree. I was always using the safest method however as the DB grows, the amount of time cost becomes more and more important. — Jupyterous, Jan 30 '19 at 4:26
Ambiguous terms: DB and schema. MySQL has servers, Databases, and Tables. Please use those terms. (Meanwhile, DB could mean 'server' or 'database') — Rick James, Jan 30 '19 at 18:37
8.0.3 is pre-release; I strongly recommend upgrading to at least 8.0.11, the first GA version. — Rick James, Jan 30 '19 at 18:38

jithin giri · Accepted Answer · 2019-01-29 06:40:41Z

0

Use mysqldump for backup, it is not the fastest but it is safe especially if you are having a 250GB table

mysqldump -u <username> -p <databasename> > dump.sql

If you have procedures add --routines

mysqldump -u <username> -p --routines <databasename> > dump.sql

make sure you have enough space in your ec2 for backup, also don't backup or restore from a remote machine, it will slow down the process

After completing backup create a new database and restore to it

mysql -u <username> -p <databasename> < dump.sql

answered Jan 29 '19 at 6:40

jithin giri

1614 bronze badges

Add --no-create-db - the author wants to alter DB name. – Akina Jan 29 '19 at 7:11
Thanks. This could be one alternative then. – Jupyterous Jan 30 '19 at 4:25
But does AWS let you execute commands such as mysqldump? – Rick James Jan 30 '19 at 18:40
yes. I think there is no restriction to execute any commands in EC2 MySQL – jithin giri Jan 31 '19 at 4:24

add a comment |

1 Answer 1