Re: Moving / Exporting Joomla's mySql DB to another host or server via phpmyadmi

EnergySka · Post by **EnergySka** » Sun Jan 14, 2007 5:03 am

Okay - I found something that worked for me. I upgraded my xampp lite installation to version 1.5.5 which includes phpMyAdmin 2.9.1.1 and mySQL 5.027. I opened the local database in phpMyAdmin, selected the export tab, and choose the option SQL compatibility mode [See pic below] - selected MYSQL40, and created dump. In the empty remote database, I was able to import this file with no errors

. I am not sure why this worked (no collation references are in the dump though); but it did. Seems strange though since I was originally trying to go from mySQL 4.1.12 to 4.1.2.

Just changed configuration.php settings and am getting error when I try to open the host site..

Warning: main(usr/home/cmcweb/public_html/includes/version.php): failed to open stream: No such file or directory in /usr/home/cmcweb/public_html/includes/joomla.php on line 71
Fatal error: main(): Failed opening required 'usr/home/cmcsite/public_html/includes/version.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /usr/home/cmcsite/public_html/includes/joomla.php on line 71

I learned a few things today - I see there are some posts about configuration.php, but haven't a solution that works for me yet. hopefully I can iron out the kinks tomorrow -gotta sleep....
Thanks for all helpful posts/suggestions above!

Post by **infograf768** » Sun Jan 14, 2007 5:33 am

I think the version of MySql should at least be 4.1.12

Good to know it worked for you now.

joseph21 · Post by **joseph21** » Sun Jan 14, 2007 5:53 am

Glad it worked for you

[OFF TOPIC]
Now I'm having some battle againts JoomFish (gonna read the readme file more than 10 times again) x_X *Hoping there are some easier way to translate my site to foreign language.

hilu · Post by **hilu** » Sun Jan 21, 2007 9:38 pm

Hi there,
Sorry for being dumb.

I would love to try out this great tutorial but there is no phpmyadmin in my cpanel.
my host is lunarpages
Can anyone please tell me what to do in my case
Regards,

joseph21 · Post by **joseph21** » Mon Jan 22, 2007 2:19 am

Most cpanels have PhpMyAdmin... When you login to cpanel then click MySQL then it is there below the "PhpMyAdmin" link (no picture just word). Or maybe you could ask lunarpages for support whether how they are going to address this problem.

hilu · Post by **hilu** » Mon Jan 22, 2007 4:56 pm

Thank you Dear Joseph,
Thank you for your kind help.
I do have a phpmyadmin as you said...It was only me being too dumb not seeing the thing.
Thanks once again,
Regards

joseph21 · Post by **joseph21** » Tue Jan 23, 2007 6:23 am

Your welcome, no one is dumb! Asking questions is part of learning!

hilu · Post by **hilu** » Tue Jan 30, 2007 11:25 pm

Hi guilliam,
What a wonderful tutorial it is!!!
When I started out I didn't even know where to locate "phpmyadmin" .
Now I started playing around with the database

and everything is working...Wonderful tonight

.
Thank you for the wonderful gift of information.

I wanted to know a bit more on this : $:-\$
I have a live Joomla site and a separate live SMF forum which are not bridged- which need to be bridged.
I made a new site and a forum which is bridged using joomlahacks bridge.
Is it possible to move only the content parts of my live loomla and forum to the new bridged site?

If so kindly give me some pointers
REgards,

guilliam · Post by **guilliam** » Wed Jan 31, 2007 3:45 am

yes its very possible.

the joomlahacks integrattion requires jos_ tables to be in the same mysql db with the smf_ tables. little more info:

in your integrated joomla-smf site if you look into the db using phpmyadmin you will notice tables with prefixes of jos_ and smf_. the tables with jos_ is for joomla. the tables with prefix of smf_ is obviously for smf

now how to pull the joomla tables out from one db and insert it to an integrated joomla-smf db.

on the tutorial #4 which says:

[/quote]4. click on 'Select All', tick on SQL on the button below the 'Select All".[/qoute]

instead of clicking "select all" choose only the tables with prefix of "jos_" (joomla tables).

make sure you have ticked "add drop tables" and "add if not exist". export it.

import the dump to the db where you want to insert those tables into.

do the same thing with smf_ tables

--------

TAKE NOTE: i dont see it more efficient if you insert the content of joomla and smf into a new db. why not insert the smf_ tables in the existing joomla db so that they will be sitting in the same db?

- g

hilu · Post by **hilu** » Wed Jan 31, 2007 5:09 pm

Dear Gulliam,
thank you very much for your reply.
Forgive me for pestering you with doubts

May I explain my situation a bit more please :

In my live Joomla site the front end edit, polls etc. are corrupted.
When clicked on those button it gives error messages and the machine gets hanged.
I made a posting for this butcould not get around this.
That is why I am trying to build a fresh new site in the first instance.
At the moment I am confident (with the help of your tutorial of course) to build up a joomla-SMF site from scratch and transferring everything from one site to another.
But what I want is only the articles and postings in the live sites.
Templates and other customisations from the ( corrupted modules / compoenetns) live sites need to be discarded
What I want is a fresh joomla installation and only the articles/images/mp3 s coming to the new site
Kind of pick and choose what is good

Kindly give me some ideas how to do this
Regards,

guilliam · Post by **guilliam** » Wed Jan 31, 2007 5:53 pm

from your old or corrupted db, when you clicked on the button of "export" you will have a field which you can choose which tables are to include in the export. core joomla has 35 tables. in moving only the content items; TRY this tables to export:

jos_content
jos_categories
jos_content_frontpage
jos_content_rating
jos_core_acl_aro
jos_core_acl_aro_groups
jos_core_acl_aro_sections
jos_core_acl_groups_aro_map
jos_menu
jos_sections
jos_users
jos_usertypes

if you export-import this to the fresh one, your username and password will be of the old db you are using.

in the list above i have included the user types and users access levels. just in case you dont need the users and their access levels(like who can view pages) just select this in the list:

jos_content
jos_categories
jos_content_frontpage
jos_sections

4 tables only and insert that with the boxes selected for "add drop tables" and "add if not exist"

import the dump into your fresh joomla db.

MAKE SURE TO MAKE A BACK UP OF THE DB. WE HOLD NO RESPONSIBILITY WHEN CRAP HAPPENS

- g

hilu · Post by **hilu** » Wed Jan 31, 2007 7:21 pm

Dear Gulliam,
thank you very much for your reply.

MAKE SURE TO MAKE A BACK UP OF THE DB. WE HOLD NO RESPONSIBILITY WHEN CRAP HAPPENS
- g

I have made a subdirectory- test and a db -test in it to test all these things.
whatever I do is hopefully not affecting my live site- ( so goes my understanding $:-\$ )
I realised what you meant as soon as I messed up the fresh db. If I had backed up the same I could have undone the harm- But no worries it is all on a Guinea pig site
I have an ijoomla magazine component - is it enough to select those tables with jos_* ijoomla name on it to include the magazine structure?
I am into experimentation and shall come back as soon as I am finished
Regards,

hilu · Post by **hilu** » Wed Jan 31, 2007 8:31 pm

Dear Guilliam,
The pick and choose is working!!!

I could copy my articles into the fresh joomla site following your advice.
I am indebted to you ...I do not know how to thank you. Hosanna to you.
Here are the results of my experimentation:
I tried to import the following tables in an effort to copy the magazine structure
jos_magazine
jos_magazine articles
jos_magazine categories
jos_magazine_config
jos_magazine_sections
jos_magazine_users
But I am getting a "the page you are trying to access does not exist" message.
I will be able to do the magazine otherwise and this is not a big issue in my case.
The other problem observed is that the e are becoming different in the text as shown in the image. This may be because of some difference in the character encoding. If you could kindly throw some light on this aspect it was very helpful
Lastly as I imported the jos_menu table and the live site menu came up in the test site but I didn't want.
How do I know for sure which are the tables for which component/module/mambot etc. is there any mention of such info anywhere in our forums?
Thanking you once again.
Regards,

guilliam · Post by **guilliam** » Thu Feb 01, 2007 4:40 am

youre getting you are not authorized to veiw this page beacuse you probably missed importing the user acl's. the tables controlling access levels in joomla are those with the prefix of "jos_core_acl_TableNameHere"

about character changing due to encoding. you may check this "guide" on this thread i posted a year ago ehehhe:

http://forum.joomla.org/index.php/topic ... #msg183175

about the menu table, best way is to just recreate those menu items from the joomla backend. meaning dont edit it in the phpmyadmin; it will be hard for you to edit with phpmyadmin since it will needs increments etc etc. also the menu has to have menu permissions of who can view them. so its more complicated to edit it in mysql. better recreate your menus via the backend.

warmly welcome. pay it forward!

- g

asdc · Post by **asdc** » Fri Feb 02, 2007 8:39 am

hi ... still the same i trying i get this ..
-->Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 02, 2007 at 04:37 PM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `cicinor_smktelokkerang`
--
-- --------------------------------------------------------
--
-- Table structure for table `jos_banner`
--
CREATE TABLE `jos_banner` (
`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
) TYPE = MYISAM AUTO_INCREMENT =1;

MySQL said: Documentation
#1050 - Table 'jos_banner' already exists

--> i'm still tryin n try again but hv the same error .. anybody can help me ..

guilliam · Post by **guilliam** » Sat Feb 03, 2007 6:58 am

asdc wrote: hi ... still the same i trying i get this ..
-->Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 02, 2007 at 04:37 PM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `cicinor_smktelokkerang`
--
-- --------------------------------------------------------
--
-- Table structure for table `jos_banner`
--
CREATE TABLE `jos_banner` (
`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
) TYPE = MYISAM AUTO_INCREMENT =1;

MySQL said: Documentation
#1050 - Table 'jos_banner' already exists

--> i'm still tryin n try again but hv the same error .. anybody can help me ..

in phpmyadmin go to the DB for your 2nd site. you need to drop all tables and contents in that db first(be sure that you know what dropping of table means deleting all data).

- g

kspades · Post by **kspades** » Thu Feb 08, 2007 2:52 am

Hey Joomla Experts
Ok, I've been using this CMS for years and have developed many websites with it. Most people host with me however this one client wants to host on their own box. I've developed the site on my ISP and now trying to move it to their ISP (godaddy) I backed up the SQL and moved the files and edited hte configuration.php file. The site appears to be fine after the move, I can browse the website itself however I cant login to the backend...no one can. Where did I go wrong? The jos_users table does exists on the new db and I can see the users and encrypted passwords in the table

guilliam · Post by **guilliam** » Thu Feb 08, 2007 5:45 am

can you login in the frontend?

- g

fistersn · Post by **fistersn** » Sat Feb 10, 2007 11:25 pm

Hello.

I have moved my Joomla page from a server to a new server, but the frontpage is totaly blank.
I can login at the administrator page. What can be wrong?

My homepage is http://sulkesligaen.org

guilliam · Post by **guilliam** » Sun Feb 11, 2007 5:43 pm

fistersn wrote: Hello.

I have moved my Joomla page from a server to a new server, but the frontpage is totaly blank.
I can login at the administrator page. What can be wrong?

My homepage is http://sulkesligaen.org

now tells me that page cant be found.

- g

rgoetz · Post by **rgoetz** » Sun Feb 11, 2007 6:14 pm

Well I hope someone has the answer to this. I followed the instructions for moving my db and site. But this is what I get. Everything is there, but in the backend, everything I created and ll new modules come up with a blank screen. I also get the notice that some of my modules aren't registered. The strange this is that the components work fine.

Thanks for your help. Rhonda

guilliam · Post by **guilliam** » Mon Feb 12, 2007 6:56 am

rgoetz wrote: Well I hope someone has the answer to this. I followed the instructions for moving my db and site. But this is what I get. Everything is there, but in the backend, everything I created and ll new modules come up with a blank screen. I also get the notice that some of my modules aren't registered. The strange this is that the components work fine.

Thanks for your help. Rhonda

does the FILES of the respective modules are there in the new/2nd host?

- g

rgoetz · Post by **rgoetz** » Mon Feb 12, 2007 10:39 am

Yes, apparently the server decided to change ownership to httpd:httpd instead of what it should be. That is causing all kinds of headaches when I upload files. Rhonda

guilliam · Post by **guilliam** » Mon Feb 12, 2007 7:00 pm

rgoetz wrote: Yes, apparently the server decided to change ownership to httpd:httpd instead of what it should be. That is causing all kinds of headaches when I upload files. Rhonda

it isnt an issue of mysql transfer but of the file transfer then? why not manually copy the files to your local then upload to the new host?

- g

Frederikke · Post by **Frederikke** » Tue Feb 20, 2007 8:22 pm

I have a question in regards to moving a site to a new host where there is no domain name pointing to the site yet.

My webhost has given me a temporary path where I can reach my site: http://203.98.79.129/~drjones/

But I can't seem to get it to work. I have put the above adress in as my live site in configuration.php but keep getting a 404 error site not found (my database was restored via phpMyAdmin without issues).

I have checked that the database name, username and passwords are correct. Any ideas as to what could be wrong?

I tried the script where you should be able to find the path to your live site, but I couldn't get that to work either - probably because I haven't placed it correctly.

I am on a host using cpanel

part of config.php file:

$mosConfig_absolute_path = '/home/drjones/public_html';
$mosConfig_live_site = 'http://203.98.79.129/~drjones/';
$mosConfig_sitename = 'drjones.com.au';
$mosConfig_allowUserRegistration = '1';
$mosConfig_back_button = '1';
$mosConfig_cachepath = '/home/drjones/public_html/cache';
$mosConfig_cachetime = '900';
$mosConfig_caching = '0';
$mosConfig_db = 'drjones_jones';
$mosConfig_dbprefix = 'jos_';

Thank's

Frederikke

guilliam · Post by **guilliam** » Tue Feb 20, 2007 9:28 pm

Frederikke wrote: I have a question in regards to moving a site to a new host where there is no domain name pointing to the site yet.

My webhost has given me a temporary path where I can reach my site: http://203.98.79.129/~drjones/

But I can't seem to get it to work. I have put the above adress in as my live site in configuration.php but keep getting a 404 error site not found (my database was restored via phpMyAdmin without issues).

I have checked that the database name, username and passwords are correct. Any ideas as to what could be wrong?

I tried the script where you should be able to find the path to your live site, but I couldn't get that to work either - probably because I haven't placed it correctly.

I am on a host using cpanel

part of config.php file:

$mosConfig_absolute_path = '/home/drjones/public_html';
$mosConfig_live_site = 'http://203.98.79.129/~drjones/';
$mosConfig_sitename = 'drjones.com.au';
$mosConfig_allowUserRegistration = '1';
$mosConfig_back_button = '1';
$mosConfig_cachepath = '/home/drjones/public_html/cache';
$mosConfig_cachetime = '900';
$mosConfig_caching = '0';
$mosConfig_db = 'drjones_jones';
$mosConfig_dbprefix = 'jos_';

Thank's

Frederikke

try uploading an index.html via ftp to the siteroot and call it in your browser as:

http://203.98.79.129/~drjones/index.html

if you see yuor index.html in the browser then youre in for a ride. if NOT; ask your host again about the temporary url they gave if it still works. most hosts only allows a certain duration of time that temporary url is allowed to be viewed by public.

let us now what transpires.

- g

howdwegethere · Post by **howdwegethere** » Wed Feb 21, 2007 2:21 am

Frederikke wrote: Any ideas as to what could be wrong?

Thank's

Frederikke

Frederikke

Try your .htaccess and 3rd party SEF config. ??

Adam

PROXIMO · Post by **PROXIMO** » Wed Feb 21, 2007 5:15 am

hi guys I hope this thread is still open. I have the same problem I am going to transfer all of my mysql database to an other hosting database but I keep getting this same error. I did chose the utcf8 char set but when I put all of the database to my new hosting server mysql it says;
Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 19, 2007 at 07:11 AM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `aydinkoy_aydinkoyu`
--
-- --------------------------------------------------------
--
-- Table structure for table `jos_banner`
--
CREATE TABLE `jos_banner` (

`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default '0',
`type` varchar( 10 ) NOT NULL default 'banner',
`name` varchar( 50 ) NOT NULL default '',
`imptotal` int( 11 ) NOT NULL default '0',
`impmade` int( 11 ) NOT NULL default '0',
`clicks` int( 11 ) NOT NULL default '0',
`imageurl` varchar( 100 ) NOT NULL default '',
`clickurl` varchar( 200 ) NOT NULL default '',
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default '0',
`checked_out` tinyint( 1 ) NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =3;

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=3' at line 19

so I dont know what to do, can you give me a hand please? thanks very much

guilliam · Post by **guilliam** » Wed Feb 21, 2007 5:27 am

have you tried the method of copying the dump in pure text then copying it to clipboard and run it on the mysql of the new site?

- g

PROXIMO · Post by **PROXIMO** » Wed Feb 21, 2007 5:36 am

OMG what are you ? I am sorry I was very very suprised, I mean I just asked that question and you gave me an reply wow, I am impresed.Very nice well back to the topic
I was looking your other post about the one
Quote from: guilliam on January 23, 2006, 01:39:01 PM
hold.. let me give oyu a better and almost fail safe way to dump it without having those version issues in mysql.

1. on your site 1 open phpmyadmin and open the structure of the db.. then click export WITHOUT saving it to a file.. meaning just pure text. keep it open.

2. on the live site(site2), opne phpmyadmin and open a CLEAN db, i mean a new db without tables.. or if you want to use the existing db.. DROP all data on that db together with the tables of course. keep that open as well(site 2's new/clean db)

3. from the site one which generated a dump of "text" version select all and copy(so it will be in your clipboard.

4. in your site 2 which is a clean db,.. run that dump that you currently have in your clip board.

sorry.. my sys hd just crashed.. using a mobile pc in replying so i cant give you a guided solution with screenies.

I did this too, but didnt worked for me. I dont know why. I believe that I just did the way you old us.

The Joomla! Forum™