Move MySQL database from one host to another

©

©®

I have a MySQL Guestbook in PHP on a webhosting server and might be
moving to a new hosting provider.
How would I go about "getting" my database and moving it to the new
provider as the guestbook entries are important to me and I don't want
to lose what I already have.
Any tips/suggestions to help a MySQL/PHP newbie.
Thanks
PS - Tried PHPMyAdmin and am just getting errors when trying the import.
Hoping there is a simple (non-techy way to sort this out!)
My old provider has emailed me a zip of all the files. When I unzip the
backup folder, there are lots of files with .frm, .myd, .myi extensions.
The problem at the old hosting was I has one database which I used for:
guestbook, wordpress etc. I think there were 4 databases in that one
mysql DB created. I realise (by the prefix) that I can choose my
guestbook from the "multiple" database. My new host doesn't do support
(unless premium paid line) on db stuff other than creating etc. All the
import/export stuff seems that I am on my own. Help!!!!!!!
 
T

Tr.

©® said:
I have a MySQL Guestbook in PHP on a webhosting server and might be
moving to a new hosting provider.
How would I go about "getting" my database and moving it to the new
provider as the guestbook entries are important to me and I don't want
to lose what I already have.
Any tips/suggestions to help a MySQL/PHP newbie.
Thanks
PS - Tried PHPMyAdmin and am just getting errors when trying the import.
Hoping there is a simple (non-techy way to sort this out!)
My old provider has emailed me a zip of all the files. When I unzip the
backup folder, there are lots of files with .frm, .myd, .myi extensions.
The problem at the old hosting was I has one database which I used for:
guestbook, wordpress etc. I think there were 4 databases in that one
mysql DB created. I realise (by the prefix) that I can choose my
guestbook from the "multiple" database. My new host doesn't do support
(unless premium paid line) on db stuff other than creating etc. All the
import/export stuff seems that I am on my own. Help!!!!!!!
Click to expand...

Download xampp.
http://www.apachefriends.org/en/xampp.html
Install at disc root (C:\)
Mine is here: C:\Xampp\xampp

Copy the folder with all the .frm and similar files to
C:\Xampp\xampp\mysql
There will already be a couple of folders in that directory. One is
called cdcol - you can check you have your folder in the right place.

That creates the database.

Now run Xampp
From inside phpmyadmin, export the database as an .sql file

And then post back with the errors... but if the error is complaining
about 'DEFAULT CHARSET' then open the .sql file and do a search/replace
for DEFAULT CHARSET = LATIN1 and replace it with nothing.

That help?
 
©

©®

And then post back with the errors... but if the error is complaining
about 'DEFAULT CHARSET' then open the .sql file and do a
search/replace
for DEFAULT CHARSET = LATIN1 and replace it with nothing.
Click to expand...

I replaced:

CREATE DATABASE `guestbook` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci;
(the latin and swedish were there even though I never created the db).

with:
CREATE DATABASE `rocketdreams`;

I then got the error:
#1044 - Access denied for user: 'guestbooknew@%' to database
'guestbook'

Is there anything else I can edit in the SQL database?
Your input to change teh charset has helped the progress move on a bit.
Thanks
 
T

Tr.

©® said:
I replaced:

CREATE DATABASE `guestbook` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci;
(the latin and swedish were there even though I never created the db).

with:
CREATE DATABASE `rocketdreams`;

I then got the error:
#1044 - Access denied for user: 'guestbooknew@%' to database
'guestbook'
Click to expand...

That means that in the new database, user 'guestbooknew' does not have
ALL permissions on the db OR that the password is wrong. In a default
xampp setup, user is 'root' and there is no password.

..
 
©

©®

Er...excuse my ignorance, but how do I run PHPMyAdmin locally with
XAmpp?
Thanks very much.
 
T

Tr.

©® said:
Er...excuse my ignorance, but how do I run PHPMyAdmin locally with
XAmpp?
Thanks very much.
Click to expand...
At what stage are you?
Do you have the database files in a folder in /mysql?

phpmyadmin - http://localhost should get the Xampp splash screen. Choose
language. phpMyAdmin is an option on the right.

..
 
©

©®

Thanks for your patience.
So far this is what I got:

phpMyAdmin tried to connect to the MySQL server, and the server rejected
the connection. You should check the host, username and password in
config.inc.php and make sure that they correspond to the information
given by the administrator of the MySQL server.

Error
MySQL said:

#1045 - Access denied for user 'pma'@'localhost' (using password: NO)
 
T

Tr.

©® said:
Thanks for your patience.
So far this is what I got:

phpMyAdmin tried to connect to the MySQL server, and the server rejected
the connection. You should check the host, username and password in
config.inc.php and make sure that they correspond to the information
given by the administrator of the MySQL server.

Error
MySQL said:

#1045 - Access denied for user 'pma'@'localhost' (using password: NO)
Click to expand...

On xampp?
Xampp 'just works' - what OS do you have?

P.
 
©

©®

On xampp?
Xampp 'just works' - what OS do you have?
Click to expand...

WinXP (Home)
DLed the Win version.
What settings do I need to modify?
Have Apache, MySQL and Filezilla running. Do I need Mercury? There's
also a button for settings as well as Admin buttons for each. Any NB
comfigs there I must change?
What about pw for user Root?
Thanks very much.
 
©

©®

I get the splash screen and choose English and it shows:

Welcome to XAMPP for Windows Version 1.5.3a
Congratulations:
You successfully installed XAMPP on this system!
Now you can start using Apache and Co. Firstly you should try »Status«
on the left navigation to make sure everything works fine.

For OpenSSL support please use the test certificate with
https://127.0.0.1 or https://localhost

So, I presume all is okay for XAmpp working, but get:
Error
MySQL said: Documentation
#1045 - Access denied for user 'pma'@'localhost' (using password: NO)

when I click on PHPMyAdmin.
Hope my info will help troubleshoot my predicament.
 
T

Tr.

©® said:
I get the splash screen and choose English and it shows:

Welcome to XAMPP for Windows Version 1.5.3a
Congratulations:
You successfully installed XAMPP on this system!
Now you can start using Apache and Co. Firstly you should try »Status«
on the left navigation to make sure everything works fine.

For OpenSSL support please use the test certificate with
https://127.0.0.1 or https://localhost

So, I presume all is okay for XAmpp working, but get:
Error
MySQL said: Documentation
#1045 - Access denied for user 'pma'@'localhost' (using password: NO)

when I click on PHPMyAdmin.
Hope my info will help troubleshoot my predicament.
Click to expand...

Where has this 'pma' come from?

Ignore the security page.

..
 
©

©®

Where has this 'pma' come from?
I have no idea. It was "there" when I ran XAmpp.
Ignore the security page.
Click to expand...
Perhaps I will try and uninstall and reinstall and see if that does
anything.
I'll keep trying until we succeed.
BTW - you on MSN Messenger or Skype? Might be quicker chatting and you
talking me through it than posting messages?
 
T

Tr.

©® said:
I have no idea. It was "there" when I ran XAmpp.

Perhaps I will try and uninstall and reinstall and see if that does
anything.
I'll keep trying until we succeed.
BTW - you on MSN Messenger or Skype? Might be quicker chatting and you
talking me through it than posting messages?
Click to expand...

I don't do IM :)
This might help with the WordPress bit and all db's are pretty much
handled the same in xampp: http://www.tamba2.org.uk/wordpress/xampp/

..
 
©

©®

It worked!
I uninstalled *twice* and then re-read your posts.
Installed in root (C:\XAmpp), and then only exported *data* and not
structure!
My new hosting people are using phpMyAdmin 2.7.0-pl2, whereas XAmpp is
using phpMyAdmin 2.8.1.
Anyway to update it or do I have to ask my host?
Will see if it is in my folder on the host and see if if I can do it!
*So happy* that my guestbook entries are back! Phew!!!
Thanks very much for all your help!
 
T

Tr.

©® said:
It worked!
Click to expand...
Cool !
I uninstalled *twice* and then re-read your posts.
Installed in root (C:\XAmpp), and then only exported *data* and not
structure!
My new hosting people are using phpMyAdmin 2.7.0-pl2, whereas XAmpp is
using phpMyAdmin 2.8.1.
Anyway to update it or do I have to ask my host?
Click to expand...
It shouldn't really matter to be honest.
Will see if it is in my folder on the host and see if if I can do it!
*So happy* that my guestbook entries are back! Phew!!!
Thanks very much for all your help!
Click to expand...
You are very welcome :)

..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Web Database 8
How Easy To Convert Access > MySQL? 13
Database connectivity using Mysql 2
Problem in importing from an MySql-database 2
Problem with Import or Linked Tables with MySql 1
export MySQL to Excel? 7
ASP.Net Membership & Roles Using MySQL- Basics Needed. 6

Top