Moving MySql database to hosting server 2

[GeertVc]

Hi all,

Maybe this is not the appropriate place to ask, but I give it a try...
I'm having a MySql database running fine on my local machine (WinXp Pro), where Apache2 is running as a local server.
My database application (PHP) is doing everything it needs to do on my local server.
The next step is to move my database to my hosting site. I see that my hosting site (siteflip.com) is using cPanel as an interface to my web space. I can create new MySql databases and via phpMyAdmin I can create new tables and content.

However, I'm looking for a way to get rid of these things. I would like to put my MySql files (so, tables and records, the whole content of my database), which are on my local PC (in the 'datadir' directory), directly on the site.

Does anyone know if this is possible and so, how it can be done?

I can hardly imagine that I have to create the database and tables directly via phpMyAdmin on my hosting web site. There should be another way, I guess (...I hope... )).

Anyone any idea?

Thanks in advance for the response.

GeertVc.

 

[sleipnir214]

This sounds like an excellent question for your hosting provider.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GeertVc]

Thanks for your suggestion. I did already what you propose (my first step), but my hosting provider (SiteFlip.com) is only providing the infrastructure. They don't provide technical support for application specific issues, among others mysql databases. If you would like to take the time, click on the link below

http://www.siteflip.com/policy.html

Look at chapter 8.
Sounds strange, but it's their policy...

I have to use cPanel to access everything on my site.

However, I read in the MySql documentation there's a command called mysqldump that 'converts' a database in a SQL format. Is this perhaps the way to upload my database?

Any more suggestions?

Thnx,

Geert

 

[sleipnir214]

In order to do what you propose, you would have to transfer the files to your hosting provider. Do they provide that functionality?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GeertVc]

Hi again,

What I just tried, is the following.

1. I made a test database (with simply one table and some 100 records).
2. I used mysqldump -u root -p --opt Jokes > c:\jokes.sql (Jokes is the name of my database).
3. I went to my cPanel interface, selected MySQL database, created a Jokes table and then selected the SQL tab and pointed to my jokes.sql file.

Result: my tables were created and also filled with the content of my local database.

Is this the way to go?

Rgds,
Geert

 

[sleipnir214]

If it worked, sure.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[tshot]

well i had opted another approach. I created a php script where in i created the tables. then ran that program on ISP system. It created the relevant file

case Control:
	$crea_query = $cre_string
	. "("
	."LastPaymentDate DATE,\n"
	."CumLastPayIN BIGINTU NSIGNED,\n"
	."LastPayIn BIGINT(20) UNSIGNED,\n"
	."CurrentPayIn BIGINT(20) UNSIGNED\n"
	.")" ;

part of my code. I would love to know some better procedures




----------------
ur feedback is a very welcome desire

 

[carbonbased]

In case you still need some help with this...


Part A: Exporting a Database
1. Log-in to phpMyAdmin. (It can be accessed from Your CPannel>MySQL Databases>phpMyAdmin)
2. Click on your old database you want to export, then wait for the right frame to load.
3. Click on the EXPORT tab at the top of the right frame.
4. Select Data & Structure, hit Select All (For whole database), check Save as File, then hit GO!

Part B: Importing a Database
1. Log-in to phpMyAdmin. (It can be accessed from Your CPannel>MySQL Databases>phpMyAdmin)
2. Click on the new database, that you want to import your old one into.
3. Click the SQL tab at the top of the right frame.
4. At the bottom, it'll say "Or Location of the textfile". Hit browse, and select the file you exported.
5. Hit GO, and wait for everything to upload.


Looks like you've already got Part dealt with, now just run Part B and you should be all set.
Credit goes to thexcist@mailforce.net

 

[GeertVc]

Carbonbased,

What you describe, is exactly what I did (at least, to import a database).

But I find it still a pity you can't create a database from scratch. What I've learned so far, is that you have to create a database first. Nothing more, nothing less. There even need not to be tables present!

So, I still can't create a database locally, test it and then upload it from scratch. I first have to go to the web, create only the database and then stuff it with the output of mysqldump (or some other program). Inserting tables in an empty database (or even dropping them if they are already present before performing the upload) is working fine.

But I'm missing the possibility to also create the database itself from the sql script.

What I tried is the following:

I have a database on my host.
I create a completely new database with other tables locally on my PC.
I try to 'cheat' phpMySql by pressing the SQL tab for the 'dummy' database.
I select the sql script that contains the command for database creation (next to the commands to create and stuff tables).
I press the 'Go' button.

Soon after, I get an error message, saying the following:

Error

SQL-query :

--
--
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/gevawebs_jokesdb

MySQL said:


Access denied for user: 'gevawebs@localhost' to database 'gevawebs_jokesdb'


Don't know exactly what the last line means, but I guess that means I again have to create the empty database and only then call the script to create and populate the tables.

And that's just one step too less for me...

If I would know exactly the host remote host and if I was allowed to remotely create databases, then I could use the following command:

mysqldump --opt database | mysql --host=remote-host -C database

That would be really nice for me!

Maybe you can advice on this one?

Rgds,

Geert

===================================================
A mind is like a parachute: it works best when open

 

[carbonbased]

Geert,

I'm not sure exactly where you're going wrong but try creating the db first, then import your existing tables. Your situation may be a bit different but the (now deprecated) function for creating a new db is

mysql_create_db('new_db_name');

In context the script could look something like this:

<?
$linkID = mysql_connect('localhost', 'root', 'sesame');
mysql_create_db('new_db', $linkID);
mysql_select_db('new_db');
$query = &quot;CREATE TABLE new_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, new_col VARCHAR(25))&quot;;
$result = mysql_query($query);
$axe = mysql_drop_db('new_db');
?>

Command line queries will be different.

I've just moved an entire database by Exporting out of PHPMyAdmin and I seemed to have some trouble Importing the entire db as a whole but it could have been due to server latency. I took the pro-active/impatient approach and Imported a few tables at a time and voila.

My suggestion is if you have access to PHPMyAdmin, just create your database there and import your .sql file.

http://www.mysql.com/doc/en/index.html

 

[GeertVc]

Thanks Carbonbased,

This is currently indeed the way to go. I first create an empty database on the hosting server and then I import the sql file (inclusive table creation and table population) created by the mysqldump feature of MySql.

It also goes fast, so this should not be a big issue anymore.

Thanks anyhow for your valuable input.

Rgds,

Geert

 

[nicholaspaul]

I too am using Cpanel to access the server. The way I got my databases uploaded was to make a dump (export from CocoaMySQL) , go to the BACKUP linkin Cpanel and RESTORE the dump file.

Hours and weeks of searching and I found it
If you need further help with this, let me know. nicholas@nburmandesign.com