Path for MySQL FTP transfer

[SirTalksalot]

HI,

I'm just going through the instructions here http://www.vbulletin.com/docs/html/main/moving_servers_transfer

For the path, does it have to be the absolute path? If so are there any tools I can use to find what the path is?

[Wayne Luke]

You can type 'cwd' in the command line of a Unix/Linux based OS to return the current working directory. In Windows you would type 'cd' to get the current working directory.

[SirTalksalot]

Is this in SSH?

I just get "Bash: cwd: command not found

[SirTalksalot]

Also, my host is telling me that your commands are wrong and that I should type:

"myShell:~ > mysql -h dbxx.perfora.net -u dboxxxxxxxx -p dbxxxxxxxx <
dump_file.sql"

Is this correct?

[Colin F]

Yes, if your mysql server is not on localhost, that's correct.

[SirTalksalot]

I urgently need to sort this out if anyone can help?

[Colin F]

start in the directory the backup file is in, and type ls.
If you see the backup file then you don't have to use a directory for the PUT.

[SirTalksalot]

is says there's no such directory, do I need to create text file called dump_file.sql or whatever?

[SirTalksalot]

start in the directory the backup file is in, and type ls.
If you see the backup file then you don't have to use a directory for the PUT.

Do I do this in the SSH? I'm using putty. I really wish there was an idiots guide to doing a Server transfer, much of what people are trying to tell me just doesn't make any sense.

[Colin F]

is says there's no such directory, do I need to create text file called dump_file.sql or whatever?

You need to make the backup first, as described in the first step, with the adjusted query your host provided.

Do I do this in the SSH? I'm using putty. I really wish there was an idiots guide to doing a Server transfer, much of what people are trying to tell me just doesn't make any sense.

Yes.

[slappy]

When you log in with a SSL client, you usually are in the root directory. When you type the ls command (that's "L" not an "I", but yours may just be a typo) it "lists" the contents of a directory.

The "myShell:~ > mysql -h dbxx.perfora.net -u dboxxxxxxxx -p dbxxxxxxxx <
dump_file.sql" should create a file called "dump_file.sql" in your root directory. Have you created that file yet?

Regards,

[SirTalksalot]

When I do that though I just get this message.

bash: dump_file.sql: No such file or directory

[slappy]

When you log in to the server with an SSL client, you are at the COMMAND prompt. To create a database backup, you have to get to the MYSQL prompt. Have you done that first?

Regards,

[SirTalksalot]

When you log in with a SSL client, you usually are in the root directory. When you type the ls command (that's "L" not an "I", but yours may just be a typo) it "lists" the contents of a directory.

The "myShell:~ > mysql -h dbxx.perfora.net -u dboxxxxxxxx -p dbxxxxxxxx <
dump_file.sql" should create a file called "dump_file.sql" in your root directory. Have you created that file yet?

Regards,

I do that and I get the above error message, it creates a file, but that file is empty.

[SirTalksalot]

When you log in to the server with an SSL client, you are at the COMMAND prompt. To create a database backup, you have to get to the MYSQL prompt. Have you done that first?

Regards,

I don't think so, the manual made no reference to this, how do I do that?

[slappy]

First you have to know your mysql username and password. Then, from the command prompt you have to know the path to your mysql/bin folder. Once you have the path to your mysql/bin folder you "cd" (change directory" to you mysql/bin/ folder and issue the command:

mysql -uMysqlusername -pMysqlpassword and you should get a prompt which looks like:

mysql>

THIS is where you have to issue the backup command.

Regards,

[SirTalksalot]

How do I find the path to the mysql folder?

Thank you for the help BTW, really appreciating it.

[slappy]

Worst case is you have to ask your service provider, if you did not set up your forum yourself. There are many placed it could have been put. it depends on where the installer of the server OS decided to install it.

Do you have access to your service provider's staff to get the correct path to the mysql/bin/ folder?

Regards,

[SirTalksalot]

I do yes, but it's 1and1 and their responce time is about 24 hours on average, there's nothing in their manuals (hardly surprising). What would be the educated guess for the path?

I do have another question while I'm here, when I do the dump as my host suggested, will it be dumped to my root folder or my MYSQL folder?

[slappy]

If you use the < dump_file.sql command, it should be in the root directory.

I'm checking if there is a simple "find" command you can run which will tell you where the heck it is. Using the correct path is essential in making this work and there are alot of possibilities.

I'll stay with you for a bit and report back if I find an instruction which should work.

Your mysqlusername and mysqlpassword are in you config file in your /includes folder.

Regards,

[SirTalksalot]

Thank you slappy, I'm really appreciating your help. :)

[slappy]

OK. From your command prompt if you type:

whereis mysql

you should get the PATH to your mysql.

Regards,

[SirTalksalot]

Getting there, I typed that and got this back


/usr/bin/mysql /etc/mysql /usr/lib/mysql /usr/include/mysql /usr/local/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz


I just tried


cd /usr/bin/mysql


and got back

bash: cd: usr/bin/mysql: No such file or directory

[slappy]

That's because "mysql" is a "file" inside the /usr/bin/ directory. :eek:

Try cd /usr/bin/

and then try

mysql -uMysqlusername -pMysql password and see if you get to the mysql prompt. You may also need your -hHost instruction, which you previously gave as:

-h dbxx.perfora.net

Regards,

[SirTalksalot]

OK!

I've finally got the mysql prompt, so now do I just proceed as the manual described by using


mysqldump --opt -Q -uUSERNAME -p DATABASENAME > /PATH/TO/DUMP.SQL

[slappy]

Yes, except your service provider told you to use the -h host instruction, so I would first try:

Code:

mysqldump --opt -Q -hdbxx.perfora.net -uUSERNAME -p DATABASENAME > DUMPNAME.SQL

It will prompt you for the Password.

The DUMPNAME.SQL file should then be at root.

You may want to gzip the file before you download it.

Regards,

[SirTalksalot]

DEAR GOD!

After all that, I'm just getting "Permission Denied!" now!. This is bordering on ridiculous!

[slappy]

SirTalksalot:

I want to correct one piece of information I gave you. You mysqlusername and mysqlpassword for mysqldump and mysql restore functions most probably are NOT the same as those in your vBulletin config file. Those are vBulletin's mysql access username and password.

Your are more likely to have a mysql username like "root"; "localhost"; or possibly even "dbxx.perfora.net" or something similar and your mysqlpassword would probbly be something other than the password in the vBulletin config file and be given to you by the Service Provider.

Just wanted to make sure I cleared that up. ;)

[EDIT] When they gave you "mysql -h dbxx.perfora.net -u dboxxxxxxxx -p dbxxxxxxxx < dump_file.sql"

Did they give you actual username and passwords to use?


Regards,

[SirTalksalot]

Thanks slappy, I'm pretty confident I've got all the correct names and passwords, which I've got from my control panel.

I've managed to do everything I'm supposed to now, I can get to the MySQL prompt, and I then attempt to do the dump it just gives me a blank prompt and I can't do anything like this


->


I just pressed control and c to copy the code, and it says 'aborted', does this mean that it's working and I should leave it until it says something?

[slappy]

Depending on the size of your database, it could take several minutes to make the backup file. It's somewhat unnevering to see the cursor just sitting there down below the mysql prompt, but if you wait, eventually it should finish and put you back at the mysql> prompt.

How big is your database?

Regards,

[SirTalksalot]

less then 10 meg, I'll give it another go and sit and wait this time.

[slappy]

You could probably back that up successfully from the admincp. ;)

Regards,

[SirTalksalot]

Aye probably, but this is just one of several forums I own, and this one is the one I'm using as a trial run. The others are considerably larger.

[slappy]

Any luck with the waiting? Are you getting any warning messages?

It's entirely possible that, although you got a mysql prompt, this might not be the operative mysql connection. One test is from the mysql prompt to do the following command:

SHOW DATABASES

if this doesn't show your database then you need to try one of the other mysql locations you found.

Code:
/usr/bin/mysql /etc/mysql /usr/lib/mysql /usr/include/mysql /usr/local/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz


/usr/local/mysql and/or /usr/share/mysql are good possible alternatives. My own server has at least six locations and the operative one is the fourth listed in the "whereis" result.

Regards,

[SirTalksalot]

NO luck yet, it's just sitting there at the moment. I'll try you other suggestion next.

[slappy]

That's way too long for a 10 meg database. Ours is not large and its 130meg.

You could try each of the locations in order and start with a SHOW DATABASES command, until you get to one that shows YOUR database.

Just use the cd PATH to each of them in turn and try SHOW DATABASE. That should be a quick way to finally identify which one it actually is that is running your socket.

[EDIT] One other thought. Did you look in the root directory to make sure it created nothing?

Regards,

[SirTalksalot]

is 'show database' or 'show databases' correct? I've tried both, and they return


bash: show: command not found

[Steve Machol]

You have to log into mysql to run 'show database'.

[SirTalksalot]

Ahh forget that last post, I wasn't in the MySQL prompt, when I do 'Show databases' I get the same dead prompt.

[slappy]

Then that is probably NOT the correct socket for your forums. Try cd to the next path and login to the mysql prompt and issue the SHOW DATABASE command again.

Regards,

[SirTalksalot]

You know, I've just noticed it doesn't actually matter what I type, I will always get the same dead prompt.

[slappy]

Which, again, suggests it is not the socket that is working. Our server, which I have nothing to do with setting up, had mysql installed in at least 5 different locations and I spend a good part of an evening one day, trying to identify the one that worked.

We also have a sandbox, where we test stuff out, it's on a completely different socked from our main forum and there is a loooooong path instruction needed to get to the operative /bin/ folder and then you either have to use a full path or the ./mysql instruction before each command to get it to recognize anything. I spent two full nights working on that problem until the guy who handles these issues generally, finally told be about the ./ path usage. :D

[EDIT] The mysql manual says the command is plural for SHOW DATABASES and is generally shown in all capitals, but might work in small case as well. You see only those databases for which you have some kind of privilege, if you do not have the global SHOW DATABASES privilege. You can also get this list using the mysqlshow command.

Regards,

[SirTalksalot]

Just got this from 1and1's tech support.


Thank you for contacting us. I just tested your mysql database dump and
even created one called test1and1.dump.
There might have been a issue earlier when you were trying to backup
your database.

GENIUS! Well that's all my problems solved then! DO you think they wonder why I'm trying to leave them?

[slappy]

But they never told you what the "issue" was. Well, at least you have your back up and now have more information about how it's supposed to work.

Regards,

[SirTalksalot]

No, they didn't actually back up the database I required for their test. But I've managed to muddle through in the end anyway, I tried a different user access and using just the root folder, and against all the odds this worked.