Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mysqldump - server to server - all tables?

Posted on 2007-11-23
7
Medium Priority
?
1,596 Views
Last Modified: 2008-09-08
mysqldump tool is a new one to me, i have been reading the many many MANY uses for it, but one in particular i wish to use, to dump an entire DB to a new DB server.....

I have attemped to use the below string, but nothing happens, so as usual, i blame myself :) for likely mssing something , or miss understanding something

==============================
mysqldump --opt -h 1.1.1.1 -u root  | mysql --host=2.2.2.2 --user=root -C
==============================

now since i want it to copy ALL tables, do i need to specify anything after -C ?, moving the data from server 1.1.1.1. to server 2.2.2.2

i do it in a mysql command prompt and i press enter and all i get is a new line

->

and nothing happens, neither DB has password on it so i didnt include the -p part.
0
Comment
Question by:Mathiau
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20342398
you should include the database name you want to copy on both sides:
mysqldump --opt -h 1.1.1.1 -u root dbname | mysql --host=2.2.2.2 --user=root -C -D dbname

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20342399
of course, does this return anything:
mysqldump --opt -h 1.1.1.1 -u root dbname

if yes, does this also work:
mysqldump --opt -h 1.1.1.1 -u root dbname > yourfile.sql

if yes, does this work:
mysql --host=2.2.2.2 --user=root -C -D dbname < yourfile.sql


0
 
LVL 2

Author Comment

by:Mathiau
ID: 20343330
What if i want to copy all of the databases with in the data/ folder? or is that not possible? i need to go one by one ?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 2

Author Comment

by:Mathiau
ID: 20343332
trying those queries right now also.
0
 
LVL 2

Author Comment

by:Mathiau
ID: 20343354
i am running the query on another system  (i am tyig to backup 1.1.1.1 , i am running the query from system 2.2.2.2 using query browser and also trying command prompt)

mysqldump --opt -h 1.1.1.1 -u root tournaments

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump --opt -h 1.1.1.1 -u root tournaments' at line 1
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22423226
PAQed with points refunded (50)

Computer101
EE Admin
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month6 days, 10 hours left to enroll

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question