Page 1 of 2

[SOLVED] Setting Up Testing Database

Posted: Sun Oct 31, 2004 6:38 pm
by bradles
Hi All,

I am trying to go about creating a mysql database on my site and then exporting a copy of it to my local machine so I can test it to write a login script. I have tried the suggestions from the following link:
viewtopic.php?t=26340&postdays=0&postorder=asc&start=0

I have managed to export a copy of the database with one table to my local machine but it is sitting in my root folder where index.php resides.
I emailed my host to find out the path to the database so I can try and mimick that on my testing server and this is the response I got:
well although the path to the databases is /var/lib/mysql/databasename you do not have explict access to the above path. Using the phpmyadmin from your mysql feature, you can manage the mysql database and upload your database.
I don't know if I should actually post the folder mentioned in the quote above so if it is a security risk, please let me know and I will modify it for safety.

I have php and mysql servers set up and running fine on my local machine. Now I just need to know how to hook up this database and start testing it. Could someone give me a hand with this one please?

Brad.

Posted: Sun Oct 31, 2004 11:55 pm
by bradles
I have tried unsuccessfully to export the database that I created on the cpanel of my site.
For some reason it won't export the table I made with the database.

I setup a script to test connecting to the database below:

Code: Select all

<?php
// change below is your assigned mySQL username
$user = "root";

// change to the pw below is your assigned mySQL password
$pw = "myrootpassword";

// change to the database you have permission to connect to
$db = "bpk1_clients";


$dbh=mysql_connect ("localhost", $user, $pw) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db($db) or die("Could not select database");  

if ($dbh) echo "Success";
?>
This works ok when I use the root user/pass for mysql on my local machine. I set up the same user/pass on my local server for mysql as the user/pass for this database on the www. When I try to use the user and pass created with the database when it was created on the www I get the following error:
Warning: mysql_connect(): Access denied for user: 'bpk1_admin@localhost' (Using password: YES)
Can anyone see what I am doing wrong?

Brad

Posted: Mon Nov 01, 2004 2:47 am
by swdev
Have you granted the correct privileges to the created user on you local MySQL server?

Also, have you created the same database on your local MySQL server as you have on your webhosts server?

Checkout this link to the MySQL manual [mysql_man]GRANT[/mysql_man] for details on how to grant privileges

Hope this helps

Posted: Mon Nov 01, 2004 2:51 am
by phpScott
If I follow you correctly you are getting that error on your local machine.
Have you reset the permissions table after your created the user?

the other thing is that you can usually refrence your db as localhost and not need the direct folder path.

Posted: Mon Nov 01, 2004 3:49 am
by bradles
Scott,

Thanks for replying.
If I follow you correctly you are getting that error on your local machine.
Yes...I am testing it on my machine before I upload the changes to the database. At least that is the plan...if I can get it started :)
Have you reset the permissions table after your created the user?
I have set exactly the same permissions for this username as the root user...is that what you mean? Or do you mean I need to flush priveledges or something? I just tried flushing priveledges but it made no difference.
the other thing is that you can usually refrence your db as localhost and not need the direct folder path.
I had just figured that out but thanks for letting me know.

When I opened up the MySQL Control Center program on my local machine it sees the database but does not show any tables. I am positive that I exported structure and data from my www site after I made the database and user table - so I exported it again, meticulously and still have the same problem after trying to do this. In any case, I wouldn't think that this would matter as I am only trying to connect to the database.

Any more ideas?

Brad

Posted: Mon Nov 01, 2004 4:07 am
by phpScott
yes FLUSH PRIVILEGES;

Posted: Mon Nov 01, 2004 4:12 am
by bradles
Thanks Scott,

I tried flushing privileges but it didn't make any difference. I'm sure I'm missing something here.

Brad

Posted: Mon Nov 01, 2004 4:32 am
by bradles
I just installed phpmyAdmin on my local machine and selected the database.
It said "No tables found in database". How can that be? I exported the database and made sure structure and data were exported. Am I doing something wrong?

Brad.

Posted: Mon Nov 01, 2004 5:43 am
by swdev
You have said several times that you have exported the data from the database on the server, and that when using phpMyAdmin or SQL Control Center, you can connect to the database on your local machine, but you don't see any tables.

Is this correct?

If you don't see any tables (using phpmyAdmin) then you have not imported the table structure or data into your local MySQL server.

what is in the file that you have expoted from the server? Are there SQL statements to create the database, create the tables and load the data? or is it a binary file?

If the file consists of a sequence of SQL commands, then you can import this data and table structure from the command line by doing somethin like

Code: Select all

cmd_prompt mysql -u<username> -p<password>
mysql> source  <filenname>
where
<username> is the username you use to log onto the MySQL server
<password> is the password for this user
cmd_prompt is your operating system's command prompt
mysql> is the MySQL promot
<filename> is the name of the file you have downloaded from the server

Hope this helps

Posted: Mon Nov 01, 2004 5:44 am
by phpScott
have you gone to the command line and checked to make sure the db's and tables are there?
http://dev.mysql.com/doc/mysql/en/mysql.html
ex
c:\mysql\ mysql -uusername -p

it will then ask you for your password
enter it;
then do

show databases;

to see if your db is there.

hopefully it is then do

use yourDBname

let me know what happens.

Posted: Mon Nov 01, 2004 6:32 am
by bradles
phpScott wrote:have you gone to the command line and checked to make sure the db's and tables are there?
http://dev.mysql.com/doc/mysql/en/mysql.html
ex
c:\mysql\ mysql -uusername -p
Scott,
When I made the database on the www I made the username/pass something like the following:
username: bpk1_admin
password: 'admin password'
I actually made the username 'admin' but my host appends the bpk1 prefix in order to keep it unique I guess on their localhost.

I mimicked this username and password in the users table of my local mysql so that I didn't have to use the 'root' user/pass. I did this so I could make changes on my local site and just upload the new tables to the www database. I also made sure permissions were the same as the 'root' username.
One thing I did notice is that the passwords that were already in the users table of my local mysql seemed to be encrypted (16 characters jumbled) and the password that I added for the new username wasn't encryted at all.

Then I tried your suggestion: went to the c:\mysql\bin and tried loggin onto mysql with:
username: bpk1_admin
password: 'admin password'

It spat back:

Code: Select all

ERROR 1045: Access denied for user 'bpk1_admin@localhost' <Using password: YES>
So it looks like I am not setting up the user right in the users table of the local mysql database.

Thanks for the tip on testing for that. I at least know where the problem lies...but where do I go from here?

Brad.

Posted: Mon Nov 01, 2004 6:49 am
by bradles
OK.

I managed to fix the problem of connecting to my local mysql. I needed to use:

Code: Select all

PASSWORD('password here')
in order for it to encrypt the password.

Problem solved on connecting to mysql.

Now for some reason it cannot select the database...even though I have ensured that the database exists and it has one table with one record in it.

I am using the following php below:

Code: Select all

// change to the database you have permission to connect to
$db = "bpk1_clients";

$dbh=mysql_connect ("localhost", $user, $pw) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db($db) or die("Could not select database");
Brad

Posted: Mon Nov 01, 2004 7:02 am
by phpScott
can the corrected user access from the command line?

if not then instead of just die you can do still do the mysql_err() function to see what the might be the problem.
you did flush the privileges ?

Posted: Mon Nov 01, 2004 7:17 am
by bradles
Hi Scott,
phpScott wrote:you did flush the privileges ?
Yeah...I flushed the privileges on the connection to mysql on my localhost and the connection works fine now.

I used 'show databases;' after logging in through the command line and my database isn't in the list of two databases that are there. The funny thing is that when I view databases in the MySQL Control Center there is 5 databases including mine. I'm wondering if this is something to do with the path setup in my MySQL ini file or something.

Brad

Posted: Mon Nov 01, 2004 7:28 am
by bradles
OK SUCCESS!!! Thanks so much for your help.

In the process of trying to figure the problem out I deleted the original user/pass and added the new one with the encrypted password...but I didn't set the privileges up.

I reset the privileges and now it all works.

Thankyou so much for your help with this. I really do appreciate it.

Cheers.

Brad.