Create a copy of the database in same server

Question

I am trying to create a copy of database in mysql using mysqldbcopy like :

mysqldbcopy --source=root:[email protected] --destination=root:[email protected] old_db:new_db

I am getting error saying cannot connect to the destination server (Error code 110). Source and destination is same server. I want to know which user and ip i need to put here, whether to put mysql username and password or server username and password. Please help me out in this.

Answer 1

do dump of your current database

mysqldump -p yourDatabase > file.sql

then create new database and restore file.sql

mysql -p -e "create database new_DB"

mysql -p new_DB < file.sql

Answer 2

Same database just on different schema names? Try this:

CREATE DATABASE new_db; CREATE TABLE new_db.tbl_name AS SELECT * FROM old_db.tbl_name;

If you have a bunch of tables in there you can just create a script to do the job.