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> ...
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
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:
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
mysqldumpout of the question?