Remote Location to Local Location

[Viper114]

My employer has their current website built with PHP that uses a MySQL database that is located on a remote server, so the site reads the location, username, password and database name in order to work. I'm currently trying to get the website moved, and it appears we may have to have the MySQL database be placed locally on the same place as the website (we're moving to Amazon's EC2 service, and we'd rather not have two instances running two simulate what was once used). I'm not too familiar with MySQL beyond basic queries, so how would I go about setting up the database so I can place it on the same location as the website and set up a username/password to access it? I remember when trying a different hosting service as a possibility, they were able to do it somehow and give me the username/password to access it...

[thescientist]

if you have phpMyAdmin there should be an option to export and import databases.

[Viper114]

I think I installed it on this machine. I can double check and then read up on it on how to do it. Thanks for that.

[Viper114]

OK, so I found that phpMyAdmin is installed. Now I have the problem in that I can't access the database stored in the same location as the website.Beforehand, I used a .sh script to download the entire database that is found on the megasqlserver, and then placed it with the website (localhost). I then opened phpMyAdmin and then attempted to see if I could access the database so I can make the change as suggested, but I'm getting an access denied error, even though I'm using the same username and password that should be assigned to it on the megasqlserver. But I also notice phpMyAdmin doesn't really indicate WHERE it should look for a databse, only asking to log in, so maybe I just didn't place the database in the right place? Or could it be something else?

[Synook]

How did you "place it" on the local database server? You can't just copy the files over, you need to execute the exported SQL statements inside a database created on the local server. Also, permissions don't come with the database - your local server will have its own set of users.

[Viper114]

I just placed it in the same location as the web files, in /var/www. The database is an SQL file that was downloaded from the original megasqlserver that is running on the live site right now.But, I guess nothing's really that simple. What I should maybe do instead is load phpMyAdmin onto our live site and use it to export a copy that I can then try and use on this local machine. My knowledge with MySQL beyond queries is limited, though, so I will need to see what I can find.EDIT: And I just realized why I'm wrong. The file I have is the MySQL DUMP file, not a functional MySQL database. Durr, my bad. I'll look for a tutorial to see how I can turn the dump file into a database for it to be read.

[Viper114]

OK, so I've progressed a bit further. After needing to reset my machine's root MySQL password, I began reading up on how to import the dump file. So I first created a new database that is completely blank, and gave my user account full access. I then used the terminal to go into the folder where the dump file is located, and then I type:mysql -u uname -p pword dbname < dump.sqlHowever, when I do, all it does is throw up a big block of text on proper usage and useful switches and stuff. It doesn't actually import the dump into the new blank database. What am I doing wrong in this case?

[justsomeguy]

There should be no space between the -p and the password. I think the database name also needs a switch before it. The usage notes should tell all of this. If you're getting the usage screen that basically means your command isn't formatted right.

[Viper114]

Hmm, I was basically copying from what I found elsewhere. I'll double check, see what I can do.

[justsomeguy]

This is a PHP command I use to execute batch queries, so you can see where it replaces the data:

exec("mysql -h {$config['db_host']} -D {$config['db_name']} -u {$config['db_user']} -p{$config['db_pass']} < {$config['lms_root']}/sql/copy_records.sql");

Note there's no space between the -p switch and the password (in case the password starts with a space).

[Viper114]

That did it! By putting no space between the -p and the password, and also putting the -D switch before the database name, the command seemed to work. It sat there for a moment and then showed a brand new command line after. Now I can change the PHP files of the website on localhost to find the new database, and then I'll see if it worked.

[Viper114]

And it did. After pointing every file to the new database that I dumped the info into, the website now looks and acts as it should. Now I know how to do it. Thanks for the help!