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

Answer 1

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