6

How do I copy database1 to database2 from the mysql command line?

I know that mysqldump is one option, or I can do 

drop table if exists table2; 
create table table2 like table1;
insert into table2 select * from table1;

But, i won't want to do this manually for each table name. Is this possible?

The key here is "from the mysql command line" mysql> ...

d-_-b
  • 21,536
  • 40
  • 150
  • 256
  • Just to be sure: does it "have" to be using the mysql command line? Is using `mysqldump` out of the question? – Mariano D'Ascanio Jul 11 '14 at 04:44
  • Yes, and thank you for asking. mysql command line is what i'm looking for, not a bash terminal – d-_-b Jul 11 '14 at 04:45
  • Possible duplicate of [Duplicate Entire MySQL Database](https://stackoverflow.com/questions/1887964/duplicate-entire-mysql-database) – ofnowhere Jun 24 '17 at 09:13

3 Answers3

8

First create the duplicate database:

CREATE DATABASE database2;

Make sure the user and permissions are all in place and:

 mysqldump -u admin -p database1| mysql -u backup -pPassword database2;

You can also refer to the following link for executing this on mysql shell.

http://dev.mysql.com/doc/refman/5.5/en/mysqldump-copying-to-other-server.html

d-_-b
  • 21,536
  • 40
  • 150
  • 256
abhinsit
  • 3,214
  • 4
  • 21
  • 26
1

In a stored procedure, loop over the results of

SELECT table_name FROM information_schema.tables WHERE table_schema = 'sourceDB';

At each iteration, prepare and execute a dynamic SQL statement:

-- for each @tableName in the query above
CREATE TABLE targetDB.@tableName LIKE sourceDB.@tableName;
INSERT INTO targetDB.@tableName SELECT * FROM sourceDB.@tableName;

Sorry, the MySQL syntax for stored procedure being a serious pain in the neck, I am too lazy to write the full code right now.

Resources:

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
0

Mysqldump can be used from mysql command line also.

Using: system (\!): Execute a system shell command.

Query:

system mysqldump -psecret -uroot -hlocalhost test > test.sql

system mysql -psecret -uroot -hlocalhost < test.sql

Aryas
  • 21
  • 4