Copy mysql database from mysql command line

Question

How to copy database1to database2mysql from the command line?

I know that mysqldumpis one option, or I can do

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

But I will not do it manually for each table name. Is it possible?

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

Answer 1

First create a 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 to do this in the mysql shell.

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

Answer 2

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

SQL:

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

, MySQL , , , .

• CREATE PROCEDURE
• PREPARE EXECUTE
• CURSORS

Answer 3

Mysqldump can also be used from the mysql command line.

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

Query:

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

mysql -psecret -uroot -hlocalhost <Test.sql