Creacte Mysql Database copy

Question

I need copy full database in MySql but cant to do this.
I try
mysqldump db_name | mysql new_db_name

Its work, but DB so big and this comand need 5Gb+ in max_allowed_packed
Maybe you know best solution?
Task: Full copy Database with data
Thanks!

JYOTI RAJAI · Answer

You can do it in two ways :

1 Backup & Restore :

Take backup using mysqldump & restore backup with new name.

2 Manual Create Table in New Database :

create schema new_schema;

select concat("create table new_schema.`",table_name,"` as select * from `",table_schema,"`.`",table_name,"`;")
from
information_schema.tables 
where table_schema='old_schema';

/* Execute the above statement output simultaneously*/

-- i.e
create table new_schema.`XXX` as select * from `njindiainvest`.`XXX`;
create table new_schema.`YYY` as select * from `njindiainvest`.`YYY`;
create table new_schema.`ZZZ` as select * from `njindiainvest`.`ZZZ`;

Creacte Mysql Database copy

One Answer

Add your own answers!

Ask a Question