Sunday, 9 November 2008

Uploading MySQL database with dynamic site from the local machine to the remote server

In the previous part of this tutorial I´ve shown you how to upload the site to the server.

In this part I will show you how to upload your MySQL database to the remote server and how to modify your database connection file.

On your local computer opent the phpMyAdmin or any other MySQL management application and export the database you want to upload to the server.

Exporting MySQL database using phpMyAdmin

If you haven´t got phpMyAdmin installed on your machine you can learn how to do it by following one of my tutorials which explains it step by step.

Login to the phpMyAdmin on your local machine and one the left hand side click the name of the database you want to export (Fig. 01), in my case it will be my_database.

Fig. 01

You are now presented with the list of options and tables related to your selected database. My database consists of 3 tables (Fig. 02):

  • tbl_admin
  • tbl_clients
  • tbl_projects

Fig. 02

To export your database, click on the Export tab in the top navigation (Fig. 03).

Fig. 03

You´ll be taken to the Export section. Click on Select All link to select all of the tables from your database (unless you want to export just some of them) then select the Save as file checkbox and click the Go button (Fig. 04).

Fig. 04

Save the file to your chosen location (Fig. 05).

Fig. 05

You can now close the phpMyAdmin on your local machine. Next step is to create database on your remote server.

Creating database on the remote server

Now, this part really depends on what control panel your hosting company provided you with. In this section I will be using the eXtend control panel to manage my hosting and database.

Once you´re logged in to your hosting eXtend control panel, under Web Tools section click on the MySQL Databases button (Fig. 06).

Fig. 06

You´ll be taken to the new page where you can create new database. Create new database providing Username, Password and if necessary (what isn´t in my case) Database name (Fig. 07). Make a note of these as we will be using them later on to modify the connection file to your database.

Fig. 07

Once you´re ready click the Create button to create the new database (Fig. 08).

Fig. 08

Click on the Manage Now button to open the phpMyAdmin on on the remote server (Fig. 09).

Fig. 09

In the phpMyAdmin click on the name of your new databae on the left hand side (Fig. 10).

Fig. 10

With the database opened click on the Import tab in the top navigation (Fig. 11).

Fig. 11

Click on the Browse button, select the database file (.sql) which you have exported at the beginning of this tutorial and click Open (Fig. 12).

Fig. 12

Now simply click the Go button to import your database to the remote server (Fig. 13).

Fig. 13

Once your database has been imported you will see the confirmation message together with the SQL query which has been used to process our request (Fig. 14).

Fig. 14

Our database has been successfully imported so you can now close the phpMyAdmin.

Next step will be to modify the connection file which connects our website to the database.

Modifying connection file

If you website has been developed using Dreamweaver and its built in tools then simply go to Connections folder and open the file with the name of the connection (in my case it´s conndb.php) (Fig. 15).

Fig. 15

In the source view you will see that the code has generated a few variables called:

  • $hostname_conndb
  • $database_conndb
  • $username_conndb
  • $password_conndb

They will have different names depending on the name you´ve given to your connection (Fig. 16).

Fig. 16

Change the values of the $database_conndb, $username_conndb and $password_conndb to the values you´ve defined when you created your database on the remote server (server will most of the time add some syntax to it so check it again to make sure you have the right information) (Fig. 17).

Fig. 17

You can now save the file and upload it to the server using FTP application.

Fig. 18

Your site is now connected to the MySQL database and ready to use.

Created by Web Design Tutorials :: Sebastian Sulinski.


Patty Ayers said...

This tutorial was really helpful to me this morning. Thanks very much!

Banjoplucker said...

It is also possible to check the naming convention on your hosting site, beforehand, and setup the same database user, password and database name when you build your site on your local computer's test server.

Very Important Fish said...

Thank you for the blog! It's gotten me quite a way. However, when I try to go to my site online, I get this error:
Warning: mysql_pconnect() [function.mysql-pconnect]: Can't connect to local MySQL server through socket '/usr/local/mysql-5.0/data/mysql.sock' (2) in /home/content/v/v/i/vviff/html/tetondems/Connections/tcdp_data.php on line 9

Fatal error: Can't connect to local MySQL server through socket '/usr/local/mysql-5.0/data/mysql.sock' (2) in /home/content/v/v/i/vviff/html/tetondems/Connections/tcdp_data.php on line 9

Can you help me resolve this?
Thank you
Reed said...

yes i must say that this one is really helpful for me thanks a lot....

Anonymous said...
This comment has been removed by a blog administrator.
ianthompson said...

Hi thanks for the tutorial. Could you tell me how safe the database is from hackers.

Chimezie Mbagwu said...

Thanks soooooooooo much for this tutorial. May God do for you what you cannot do for yourself - God Bless you man.

sandeep kumar said...

Web Expert India are a popular and reputed website design and development company in India.Our Delhi based young professionals are highly dedicated and concentrate on availing quality services to our precious clients. We have a good clientage from all over India.
VIEW MORE Web Designing Company in india, Delhi