SQL Backups to another server

[durban]

Hello all, first post here so hopefully I won't upset anyone.

Anyway to the point...

I am trying to run mysqldump on one of my servers to dump a database and pipe it to another site that I own.

The problem is however that while both the sites are on the same server, they are hosted using virtual host and the server owner has enforced that all sql usernames must begin with user_

So for example if I had the domain horse.com and my admin user was horse, then my sql username would have to be horse_something.

Also the databases are prefixed with horse_

Now if I want to backup to another site how do I go about sending the user and password so that I can have access to upload the backup to the other server?

For example if I do....

mysqldump --opt horse_database -uhorse --phorsepass ¦ mysql -h cows.com -C cows_database

will that work? I presume not.

How therefore can I allow the horse user to load the data onto the cows.com site?

I cannot manually amend the permissions on the cows server as again I have to create users with the prefix cows_

Sorry if this doesn't make sense (or if the above command aint to clever!)

If anyone can give me a quick and easy solution it would be very appreciated.

Many thanks, and take care.

Mark

[ExpLarry]

mysqldump --opt horse_database -uhorse --phorsepass ¦ mysql -h cows.com -C cows_database

will that work? I presume not.

It should work. I can't quite work out from your description where the mysql databases are located; if they're on the same physical server running under the same MySQL server instance, this should work:

mysqldump -d horse_database -uhorse -phorsepass ¦ mysql -d cow_database -ucow -pcowpass

(assuming cow_database is created and able to accept the input from mysqldump).

[ExpLarry]

Hello all, first post here so hopefully I won't upset anyone.

oh yes, and welcome :-) though I'm not sure if I'm qualified yet, I haven't been long here myself ;-)

[durban]

Hi mate, thanks for the response.

Ok I will try and explain a little better (although I will fail :) )

Both domains exist on the same server, although they are seperate (ie they are using apache virtual hosting) so in essence they are two seperate sites.

I think Apache VH uses the same SQL system but ring fences the actual databases so they don't know they exist to each other if you see what I mean.

I think you are probably right with the user and passwords on the away system and so I will try your way and let you know :)

I appreciate the quick response :)

Mark

[ExpLarry]

Both domains exist on the same server, although they are seperate (ie they are using apache virtual hosting) so in essence they are two seperate sites.

I think Apache VH uses the same SQL system but ring fences the actual databases so they don't know they exist to each other if you see what I mean.

I wouldn't think Apache has any influence on the database setup (might be wrong though, these ISP people come up with the most devlish ideas).

Just to clarify, where would you issue the mysql commands? On the server itself, or from your own computer? Also, are cows.com and horses.com on the same IP?

[sierra]

I'm a newbie, and am in need of some help. I hear this is the best site to ask for advice, so here I am. :)

I am moving from shared hosting to VPS. I have all of my files on the VPS now, except for my MySQL database. I have SSH access for both of my accounts.

Now, I know how to backup my database, but what I am clueless about is how do I then TRANSFER that database to my new server. I don't understand how I can be logged in to two different servers at once, nor do I have any clue what the command line should be for transferring my db. I have rarely had to use SSH, so I'm far from an expert where that's concerned. My new server host says that they will do it for me, but I would like to know how to do this myself. Not only that, if I know how to do it myself, I can do it at a time that's convenient for me.

Any help would be apprecaited. Thanks!

[vkaryl]

Just dealt with this myself, with NO ssh access, not that I would be particularly capable with it had it been available. BUT I have phpMyAdmin on both old and new servers....

So I downloaded a gzipped db file from the old server to my machine, then uploaded it to the new server. This worked just fine for the "small" db. The "large" one required a LOT of further dinking around, and regardless that my host has been a priceless princess, this time we couldn't get together on this so I had to figure it out myself. (Probably has no relevance to this topic, so won't go into detail, UNLESS you've got tables in your db which are larger than 5 MB....)

If you have phpMyAdmin on both servers, you should be good to go....

[sierra]

Unfortunately, my db is quite big...almost 400MB. Because of it's size, phpmyadmin isn't an option. :( I will be using phpmyadmin for a smaller db, but will need SSH for the big one. I can get as far as backing up the db, but I don't know how to transfer it. I'm having a really difficult time finding instructions on the net telling me how to do this.

Thanks for your response! :)

[vkaryl]

Hi Sierra, and welcome to WebmasterWorld! Yeh - 400 MB would be something you'd have to do with ssh. Y'know, I used to see instructions for the command line stuff on the fora at phpBB's community - because a lot of times people have to move their databases for their fora, and many of them are whacking great beasites.

You might check that out.

Also, you might post a request for the command line statements in the Linux, Unix, and *nix-like Systems forum here. They might be able to help you with this.

[ExpLarry]

Unfortunately, my db is quite big...almost 400MB. Because of it's size, phpmyadmin isn't an option. :( I will be using phpmyadmin for a smaller db, but will need SSH for the big one. I can get as far as backing up the db, but I don't know how to transfer it. I'm having a really difficult time finding instructions on the net telling me how to do this.

There's a free Windows program called "WinSCP" which will enable you to copy data over an SSH connection to and from your local computer.

The *nix command line program is plain "scp". Use it like this:

scp myfile [e-mail]:/target/directory/

(copies "myfile" from server you are logged onto to directory "/target/directory/" on the remote server).
[edit]added example[/edit]

[durban]

Hi again.

In answer to the previous questions.

I am currently using a cron job to do the backup and move.

The problem is as follows....

The backup side works ok, however it then issues a command on the secondary side saying that the userid [e-mail] is not valid.

Its hard to explain, the useride should be [e-mail] but it seems to want to override this with [e-mail]
(where servername is the server which holds the actual domain via virtual hosting eg- www.servername.com/~horses is set up in the nameserver to be where www.horses.com points to!)

I put it in quotes and it still overrides it, replacing horses (which is the name of the domain) with the servername (ie the server its running on).

I then removed the quotes and it says something like [e-mail]@servername.com invalid!

How do I pass it a fully qualified userid. I think this is my last issue :)

As for moving a whole server, I think cron is the way to go. It can be done in the background, timed for whenever you like. I am moving a 160mb database. The actual backup takes about 1 minute (whereas doing it in phpmysql takes about 20 mins!)

You can then use cron to reload it to the new server (which means you have to manually move the file across via ftp) or you can use (I presume) what I am attempting to do and set up a cron job that moves the backup to the other server and then loads it up as the database.

However as can be seen I am currently having issues doing this on a virtual server setup (ie www.horse.com is actuall just some space on 'servername.com'

ie www.horses.com is pointed to servername.com/~horses

hope this makes sense.