Migating From Xampp

[konfused]

I have developed a database using XAMPP. Please would you explain how I migrate the database and its php pages to a remote commercial host.

The steps I think I already understand are:

1. Use phpMyAdmin to export an SQL file of my database and tables.

2. Use my FTP client to transfer all the php and html files from htdocs to the public_html folder in the host.

3. Use the host's control panel to access phpMyAdmin and then import the SQL file containing my database and tables.

 

Its the next bit that I find diffcult to understand:

 

Databases created directly on the host using phpMyAdmin are prefixed with my control panel username. How do I cope with this as my XAMPP database is not prefixed with my CP username.

 

My mysqli_connect.php file contains the details relevant to the database created with XAMPP. Which of those details have to be changed in order to access the database on the host. Can I assume that the user, password and localhost can remain unchanged?

[benjamin.morgan]

Okay, when you start the phpmyadmin on the server you should create your database, you should have your username and password set.

 

After you import you will probably have to change the database name (most hosts add a random prefix in front of the database name and username and maybe password. Once you create the database, user that accesses it and import the files, then you will have the info for the live site and you can just change it in the mysqli_connect.php file accordingly.

[HartleySan]

Yes, pretty much what benjamin said.

Also, you can open your SQL file directly in a text editor, and then copy and paste the necessary SQL statements into the SQL tab text area in phpMyAdmin to import the necessary information.

 

Basically, I would do the following:

1) Do everything you already mentioned, except

 

2) When importing the DB, instead of just importing the SQL file as is, first manually create the DB on the hosted server side (different hosting services have different interfaces for doing this), assigning a user to the DB you just created, and from the SQL tab of the DB you just created in phpMyAdmin, copy and paste all the SQL statements except for the one that actually creates the DB. (It should be obvious which statement I'm talking about).

 

3) Once you're sure that your entire DB has been successfully imported, note the new DB name (with the prefix that the hosting service might have added), and the name and password of the user you assigned to that DB. These are three of the four pieces of information you need for the mysqli_connect function. More than likely, the host name is still "localhost".

 

If you designed your site well, then hopefully, you'll only have to change the login credentials once for the mysqli_connect file, and everything else will work fine for your site after using an FTP client to copy all your files over to the hosted server.

 

Please let us know whether that works or not.

Thanks.

[konfused]

Many thanks Benjamin and HartleySan. I am not quite ready to migrate the database yet, but I needed reassurance about this daunting procedure. It does not seem quite so mysterious now thanks to you. Why oh why do all the MySQL manuals and web tutorials make such a secret of migrating a database? The hosts don't have documentation on the process either..

[Antonio Conte]

It should all be pretty simple. Don't worry about database info as you can simply change config files in your scripts. The trick is to export all tables found in each database instead of exporting the databases themselves. That way, the name of the database won't really be an issue. Most host won't even allow you to import huge portions of data at the time, so important tables should be easier than importing databases, really.

 

I have moved to other hosts a couple of times. It feels very scary, but I can assure you it'll work just fine.

[konfused]

Many thanks Antonio Conte for confirming my own latest thoughts on this.

 

I think this would also apply to backing up a database, in other words don't bother creating a dump for the whole database, just dump the tables, do you agree? Restoring a lost database then simply means creating an empty database then importing the tables with phpMyAdmin.

[HartleySan]

Yes, that's essentially it.

Back up the tables in the DB, create a new DB, and then import the tables into the new DB.

[konfused]

Thanks HartleySan

One more query, sorry to be pest. All manuals state that MySQL looks for the php files in htcocs by default. That works fine on my XAMPP based server. When exported to a host there is no htdocs folder, everything goes in the public_html folder. Does this mean that the host has configured its MySQL to look in public_html instead?

[Antonio Conte]

That is not linked to MySQL or even PHP, but to Apache - the web hosting software. There's no difference functionally between "htdocs", "public_html" or "www". Remember that there is not link between MySQL to PHP, but the other way around. The only thing you need to worry about is finding the PHP files through Apache (by a normal URL, no magic there) with your browser. When the files are executed, you just make sure you are connected to the new MySQL database.

 

Don't overthink this. As an example, create a file called "test.php". Place code for connecting to MySQL in there. Upload to "public_html" with FTP software. Run it with *your-domain.com/test.php" and it works like on localhost. No magic there.

[HartleySan]

Like Antonio said, there is no magic or anything special to do.

If you create a config file with the link to the DB connection (as Larry recommends), then when you move everything to the live host, you should only have to change the path to the DB connection script in the config file (and maybe not even there, depending on your config).