1

On Mysql i want to import and export database in single command, without intermediate sql file.

i was use to this methodology in Postgres which is below. Postgres Server to Server Database data transfer I want import database direct from other server database using single command.

I want something like this in Mysql

Community
  • 1
  • 1
Piyush Ghediya
  • 1,754
  • 1
  • 14
  • 17
  • 2
    This is a possible duplicate: http://stackoverflow.com/questions/8754607/i-want-to-copy-table-contained-from-one-database-and-insert-onto-another-databas – etalon11 Feb 11 '16 at 09:39
  • (same table) [http://stackoverflow.com/questions/8754607/i-want-to-copy-table-contained-from-one-database-and-insert-onto-another-databas] This is Copy table data from same database. here i am asking import from another database. – Piyush Ghediya Feb 11 '16 at 09:43

3 Answers3

3

Go to source server and use below command for mysql-

mysqldump -R -uroot -proot_pass source_db | mysql -h remote_server_ip -uroot -proot_pass target_db

If you are executing the command from any third server then-

mysqldump -R -h source_server_ip -uroot -proot_pass source_db | mysql -h remote_server_ip -uroot -proot_pass target_db

If want to migrate only some tables-

mysqldump -uroot -proot_pass source_db table1 table2 table3 | mysql -h remote_server_ip -uroot -proot_pass target_db

Note: from which server you are execuring command should have permission to execute command on other servers also.

update Finally you can use below command to copy stored routines also-

mysqldump -R -uroot -proot_pass source_db | mysql -h remote_server_ip -uroot -proot_pass target_db

Note: target db should exist on target server.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • To remove target_db is doesn't make any sense, in case of postgres its require both server's host db-name and password as well. – Piyush Ghediya Feb 11 '16 at 10:11
  • are u using postgras or mysql....if using mysql then try 3rd command with only one table and share error to see if this is syntax issue or some permission issue. – Zafar Malik Feb 11 '16 at 10:13
  • i have idea of postgres but i want to apply this on mysql, for 3rd command its copying tables with data, i want to copying whole database, with tables, stored procedure, triggers etc.. with mysql. and with execution of above command no errors no warnings. just termination. – Piyush Ghediya Feb 11 '16 at 10:15
  • check final command for everything from one db source server to another server for mysql if you are getting any issue then share error details. – Zafar Malik Feb 11 '16 at 10:17
  • finally its working with below command with your little changes mysqldump -R -h destination_host_ip -uroot -proot_pass dest_db_name | mysql -h source_host_ip -uroot -proot_pass source_db_name – Piyush Ghediya Feb 11 '16 at 10:27
  • Update this command in your Answer mysqldump -R -h destination_host_ip -uroot -proot_pass dest_db_name | mysql -h source_host_ip -uroot -proot_pass source_db_name – Piyush Ghediya Feb 11 '16 at 10:29
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103187/discussion-between-piyush-ghediya-and-zafar-malik). – Piyush Ghediya Feb 11 '16 at 10:40
  • Please check I have changed 2nd command which worked for you, but backup will be always from source server and restore on target server, so I keep it as it is even in practical server ips will be used here. – Zafar Malik Feb 11 '16 at 11:42
  • There is no change in my first given command and now you have accepted answer and I have included stored routines as per your requirement which will not be by new accepted answer..further its your choice...For connection lost issue better will be first take dump from source server then move that dump to target server and restore it by this you can avoid load and locking on source server....it is (simultaneously dump and restore) is good for small data....further this is for your information you can use --single-transaction to avoid locking on source server but after reading its documentation. – Zafar Malik Feb 12 '16 at 09:22
  • its nothing like this but previous one working without issue. in below suggetion i am facing issue of connection lost. even though i am in same server. – Piyush Ghediya Feb 12 '16 at 09:47
  • There can be many ways like execute command from source server and restore on remote server or execute command on target server and use source server remotely or execute command from 3rd server and use both source and target remotely but as i updated my earlier comment that it is fine if database size is not large ie. in few MBs and your LAN network is good to transfer data other wise better solution is first take backup from source and move backup in archival format and then restore on target server. – Zafar Malik Feb 12 '16 at 10:06
0

just try with below command for copy source server to target server.

mysqldump -uroot -proot_pass source_db | mysql -h remote_server_ip -uroot -proot_pass target_db
0

Using MySQL Workbench 6.3

Reference site: https://dev.mysql.com/doc/workbench/en/wb-migration-wizard-data-migration-setup.html

Recommend to do the migration task list to check errors.

Select the Create a batch file to copy the data at another time, you can customize the batch file.

Demz
  • 1
  • 2