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

Discuss the FAQ's here, and get in touch with the FAQ Team.
Locked
User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Wed Nov 23, 2005 9:44 am

.

instructions on how to move the files can be read here:
cronlin wrote: To move your Mambo site from either one folder to another or one server to another, here are a few basics.


    * Copy over your files, either via ftp or SSH/command line
    * Backup and then restore your database - perhaps using phpmyadmin or SSH/command line
      (this step only needed if moving site from one server to another.
    * Edit your configuration.php file to reflect the new values. Specifically the following:
     
      $mosConfig_user = 'joomla_joomla';
      $mosConfig_password = 'password';
      $mosConfig_db = 'joomla_joomla';
      $mosConfig_absolute_path = '/home/joomla/public_html';
      $mosConfig_live_site = 'http://www.joomla.org';
      $mosConfig_cachepath = '/your absolute path/cache';
Note: You may also need to ensure you have configured your .htaccess file in the new location.
Of course with the release of Joomla! it will no longer be $mosConfig_ but $josConfig :D
or go directly to the thread: http://forum.joomla.org/index.php/topic,5556.0.html

- g
.
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

User avatar
Caracal
Joomla! Apprentice
Joomla! Apprentice
Posts: 32
Joined: Mon Sep 05, 2005 7:06 pm
Location: South Africa

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

Post by Caracal » Wed Nov 23, 2005 10:33 am

Thanks guilliam

When tried to find out more information, visuals can always make it so much easier. :D

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Wed Nov 23, 2005 10:42 am

Caracal wrote: Thanks guilliam

When tried to find out more information, visuals can always make it so much easier. :D
warmly welcome.. feel free to post if you encounter any problems in this walkthru.. will be happy to assist if i have the time :)

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

bprof
Joomla! Apprentice
Joomla! Apprentice
Posts: 17
Joined: Sat Nov 26, 2005 11:10 am

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

Post by bprof » Sat Nov 26, 2005 8:29 pm

Thanks alot for these very useful instructions.

I just have a question regarding this point:
1. should have phpMyAdmin in both the dev site(1st site) and live site(2nd site).
I have only one site which is my live site. Can I just back up my db using the instructions you wrote, and in case of failure I just upload this file using also the instruction?

User avatar
The One
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Wed Nov 02, 2005 6:22 pm

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

Post by The One » Tue Nov 29, 2005 9:45 pm

Now that I got this I just upload and install joomla?
Last edited by The One on Tue Nov 29, 2005 9:54 pm, edited 1 time in total.

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Wed Nov 30, 2005 8:19 am

The One wrote: Now that I got this I just upload and install joomla?
upload the joomla files. then insert the mysql db with the instruction above given.

- guilliam
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

User avatar
The One
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Wed Nov 02, 2005 6:22 pm

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

Post by The One » Wed Nov 30, 2005 2:00 pm

Tnx alot, worked perfect, that was a noob Q.

joomlearner
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Wed Nov 30, 2005 4:51 pm

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

Post by joomlearner » Wed Nov 30, 2005 6:31 pm

Hello,

Oh well thanks anyways.

Joomlearner
Last edited by joomlearner on Mon Dec 05, 2005 2:35 pm, edited 1 time in total.

kovox
Joomla! Apprentice
Joomla! Apprentice
Posts: 36
Joined: Thu Oct 27, 2005 9:46 pm

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

Post by kovox » Thu Dec 01, 2005 7:57 am

I was able to backup and then export the database into my new one.

When I log on to the admin backend, none of the information is there.

I dont know what I am doing wrong? :(


what i did:

1. downloaded 1.0.4 joomla
2. installed joomla on server
3. made backup of old database
4. exported to new database

help! lol i've been trying to learn how to do this for months now. I dunno what I am doing wrong :(

ForestDweller
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Sun Dec 11, 2005 9:48 pm

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

Post by ForestDweller » Mon Dec 12, 2005 3:13 am

I followed these instructions and received this result at the end of the last step:
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 '�' at line 1
I thought maybe I had made a mistake somewhere along the line so I went back and did it again and got the same result.
>:(
What am I doing wrong??

User avatar
veroeven
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Fri Dec 09, 2005 4:44 pm

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

Post by veroeven » Tue Dec 13, 2005 8:21 pm

I'm trying to get my site sync'ed for the first time.

Here's what I've done and where I'm at:

1. I've got a fresh install of Joomla on a subdomain of my main site, so I can show it to a client.

2. I've imported the SQL Db.

3. I've uploaded the site's files from JSAS.

4. I've configured the configuration.php file with guilliam's instructions.

However, I am still getting: 

"This site is temporarily unavailable.
Please notify the System Administrator"

when I bring up the site.


Can anyone point me in the right area to troubleshoot? Have I given enough info for you to? If not let me know.

Thanks!

Tony

User avatar
Tonie
Joomla! Master
Joomla! Master
Posts: 16584
Joined: Thu Aug 18, 2005 7:13 am

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

Post by Tonie » Tue Dec 13, 2005 8:43 pm

Which number does it show with the message?

#1 = no mysql support in php
#2 = cant connect to mysql
#3 = mysql ok but cant connect to database
#4 = give up and have a beer

User avatar
veroeven
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Fri Dec 09, 2005 4:44 pm

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

Post by veroeven » Tue Dec 13, 2005 9:01 pm

Thanks for the prompt reply Tonie.


I'd like to say number 4, but alas, it is number 2. Now I know what that code is for!

I looked and found a minor typo that I fixed and resaved the config.php. but I am still getting the same error code.

I've got the $mosConfig_db = 'joomla_joomla'; as " $mosConfig_db = 'username-databaseprefix_Joomladatabasename';

So far so good?

User avatar
Tonie
Joomla! Master
Joomla! Master
Posts: 16584
Joined: Thu Aug 18, 2005 7:13 am

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

Post by Tonie » Tue Dec 13, 2005 9:21 pm


User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Tue Dec 13, 2005 9:42 pm

@ForestDweller,

sorry for the delay in replying,.. my email notification in the forums is not working.

to answer your question,.. youre getting that error based on the difference of mysql version. can check what version are you using from the site1 and the version on site2?

will give you further instruction if its really a version incompatibility.. its just a way of dumping the mysql from site1.

- guilliam
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

duukertje
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Wed Dec 14, 2005 6:05 am

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

Post by duukertje » Sat Dec 17, 2005 4:59 pm

I have the same problem:

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=4' at line 19 20404.0

How can I find out which version of MySQL I or my webhoster is using?

Do I need version 5? It seems Joomla has probleems with this version.

duukertje
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Wed Dec 14, 2005 6:05 am

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

Post by duukertje » Sat Dec 17, 2005 5:13 pm

I found which MySQL-versions are being used (you see them when one starts phpmyadmin :P). On my local JAS MySQL 4.1.14 and on my on line website  (B-One net) version 4.0.24 is used.

Lumis
Joomla! Apprentice
Joomla! Apprentice
Posts: 23
Joined: Tue Dec 20, 2005 3:32 am
Location: Perth, Western Australia
Contact:

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

Post by Lumis » Tue Dec 20, 2005 3:50 am

Bear with me, I will waffle on for my first post - hello everybody  :P

Have just been thru this nightmare - I suggest you DO NOT use cpanel to backup an SQL database - as it often does not restore when you want it to, and it also adds strange code to the backup file. You must use phpMyAdmin as suggested above.

For example you will find it adds \ to all your hyperlinks - i.e. img src="www.mysite.com/image.jpg"  becomes img src=\"www.mysite.com/image.jpg\"

This can often be fixed by a find/replace in notepad of find: \" and replace: "
Also any single quote ' will also be altered to read \' - this is harder to replace, I just went thru and replaced it with nothing - in other words deleted it, so now some of my content now shows as youll instead of you'll

Also, because the cpanel backup function does not use the back quote ` (note this is the key next to number 1 on keyboard) - try restore the fixed database using phpMyAdmin, and everytime you get a 1064 error in syntax - you must manually fix it. I know from past experience that it does not like terms like "fulltext", "introtext" and "option"
When you get this 1064 error, read where it states the error occurs, then fix it by adding a ` backquote before and after the word.

This is very confusing, so I have placed examples below:

1064 error because of MySQL conflict (MySQL uses this word and so does not realise it should be a table)

DROP TABLE IF EXISTS mos_components;
CREATE TABLE mos_components (
  id int(11) NOT NULL auto_increment,
  name varchar(50) NOT NULL default '',
  link varchar(255) NOT NULL default '',
  menuid int(11) unsigned NOT NULL default '0',
  parent int(11) unsigned NOT NULL default '0',
  admin_menu_link varchar(255) NOT NULL default '',
  admin_menu_alt varchar(255) NOT NULL default '',
  option varchar(50) NOT NULL default '',
  ordering int(11) NOT NULL default '0',
  admin_menu_img varchar(255) NOT NULL default '',
  iscore tinyint(4) NOT NULL default '0',
  params text NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

must be made into

DROP TABLE IF EXISTS mos_components;
CREATE TABLE mos_components (
  id int(11) NOT NULL auto_increment,
  name varchar(50) NOT NULL default '',
  link varchar(255) NOT NULL default '',
  menuid int(11) unsigned NOT NULL default '0',
  parent int(11) unsigned NOT NULL default '0',
  admin_menu_link varchar(255) NOT NULL default '',
  admin_menu_alt varchar(255) NOT NULL default '',
  `option` varchar(50) NOT NULL default '',
  ordering int(11) NOT NULL default '0',
  admin_menu_img varchar(255) NOT NULL default '',
  iscore tinyint(4) NOT NULL default '0',
  params text NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

as well as the insert table bit below which may contain

INSERT INTO mos_components (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore, params) VALUES (1,'Banners','',0,0,'','Banner Management','com_banners',0,'js/ThemeOffice/component.png',0,'');

so change into
INSERT INTO mos_components (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, `option`, ordering, admin_menu_img, iscore, params) VALUES (1,'Banners','',0,0,'','Banner Management','com_banners',0,'js/ThemeOffice/component.png',0,'');

For hyperlinks change this:


into this


and for single quotes change this:
isn\'t

into this:
isnt
Maybe you could try to use a ` backquote around the ' like isn`'`t but I wasnt prepared to try as I had one humongous database to fix and this was the least of my worries.

Once I had found all the \", \' and words that MySQL doesnt like in tables like 'option', then I could use phpMyAdmin to restore fixed database.

Thats my story...........

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Tue Dec 20, 2005 10:36 am

thank you @Lumis, your first post in the official support forums has been a very meaningful one indeed. hope to see you more around.

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

Lumis
Joomla! Apprentice
Joomla! Apprentice
Posts: 23
Joined: Tue Dec 20, 2005 3:32 am
Location: Perth, Western Australia
Contact:

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

Post by Lumis » Tue Dec 20, 2005 11:17 am

You're most welcome  ;D
I wouldn't wish my experiences on my worst enemy  >:(

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Tue Dec 20, 2005 11:22 am

Lumis wrote: You're most welcome  ;D
I wouldn't wish my experiences on my worst enemy  >:(
OT: i wonder what your "worst enemy" is. :)

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

Lumis
Joomla! Apprentice
Joomla! Apprentice
Posts: 23
Joined: Tue Dec 20, 2005 3:32 am
Location: Perth, Western Australia
Contact:

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

Post by Lumis » Tue Dec 20, 2005 11:36 am

guilliam wrote:
Lumis wrote: You're most welcome  ;D
I wouldn't wish my experiences on my worst enemy  >:(
OT: i wonder what your "worst enemy" is. :)

- g
At the moment, one of my damned business clients that used CPanel to backup his SQL databases then deleted the original database when the backup would not restore properly....

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Tue Dec 20, 2005 11:43 am

i get you. ehehhehe. okies.. thanks again.

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

Drakosha
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Tue Jan 03, 2006 10:49 am

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

Post by Drakosha » Tue Jan 03, 2006 11:22 am

Hi All,

I have one problem after moving site to the new host.

Once I did all steps required I got my site up on new host. I can login into administrator, see all content, manage joomla asetting etc.
I can also see home page of the site. But when I click to the any other link in menu I'm getting standard IE error
"Page cannot be found."
In address bar I have text like:
http://127.0.0.1/content/view/19/28/

In Apache error log I have message:
[Tue Jan 03 13:06:14 2006] [error] [client 127.0.0.1] File does not exist: c:/program files/easyphp1-8/www/content/view/19/28/

any ideas?

Thanks.

Drakosha
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Tue Jan 03, 2006 10:49 am

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

Post by Drakosha » Tue Jan 03, 2006 4:27 pm

answer to my post above:
problem was related to .htaccess and Search Engine Friendly URLs option. :)
resolved.

timeforsmilin
Joomla! Apprentice
Joomla! Apprentice
Posts: 26
Joined: Tue Dec 20, 2005 5:15 am
Location: Gibsons, BC Canada
Contact:

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

Post by timeforsmilin » Sat Jan 07, 2006 5:03 pm

Hello forum,

I had great difficulty restoring my db from a compressed file.  I tried sql, then csv, and always had syntax errors on the restore.  Since I only had 17 tables from Open-Realty, I exported a non-compressed sql file, and that imported perfectly using the technique shown above.  Thanks G!!
Real Estate Auctioneer

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Sat Jan 07, 2006 5:37 pm

timeforsmilin wrote: Hello forum,

I had great difficulty restoring my db from a compressed file.  I tried sql, then csv, and always had syntax errors on the restore.  Since I only had 17 tables from Open-Realty, I exported a non-compressed sql file, and that imported perfectly using the technique shown above.  Thanks G!!
warmly welcome! PAY IT FORWARD! :)

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

User avatar
deputyjones
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 218
Joined: Sun Jan 01, 2006 5:15 pm
Location: Grand Rapids, MI
Contact:

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

Post by deputyjones » Thu Jan 19, 2006 5:39 am

Thanks so much for this post. Once again my Joomla questions have been answered in an easy and straight forward way! I just started using Joomla and have been hard pressed to find questions that have not previously been answered somewhere on this board (usually by guilliam). Thanks especially to guilliam and also to all the other Joomla experts!
Google knows all

User avatar
guilliam
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4181
Joined: Thu Aug 18, 2005 10:27 am
Location: Sunny City Cebu, Philippines!
Contact:

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

Post by guilliam » Thu Jan 19, 2006 11:06 am

@deputyjones, warmly welcome. PAY IT FORWARD! :)

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.

http://www.joomlaconsultancy.net

User avatar
raymond
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 245
Joined: Tue Jan 24, 2006 3:24 pm
Location: Philippines
Contact:

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

Post by raymond » Wed Jan 25, 2006 1:30 pm

I don't know if anyone else has come up with a problem like this but it I have... it seems that phpMyAdmin is limiting uploads of these SQL files to 51Mb. I've already got a HUGE database on my local PC which is bound to get even bigger. So my question goes like this...

Is there any way around this 51Mb barrier? Also, is there a way where I don't have to rely on my browser to import the SQL file? say, using FTP or Cpanel's file explorer? Any replies on this will go a long way toward helping me.  :)
http://raymond.santosestrella.net
Santos Estrella Personal Site
http://www.thecorpusjuris.com
The online repository of Philippine law, jurisprudence, administrative issuances and legal research tools.


Locked

Return to “FAQ Discussion Board”