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

kalpax · **Joined:** Wed Sep 28, 2005 6:33 pm **Posts:** 56

I've used these excellent instructions to migrate several sites form my old webhosting co to my new webshosting co. With the last site I'm having issues. Importing to the new db fails with something like a permissions issue:

#1044 - Access denied for user 'myacct'@'localhost' to database 'oldacctname_joom'

I dont have same user name for the db on each side, that's for sure, but I didn't when migrating the other sites either. I don't see why it's referencing the old db name at all. I'm using the Import tab on phpMyAdmin (my ver is set up slightly different than the walkthrough). The user I'm logged in as does have all perms on the new db. I've tried many things at this point incl exprt as txt from orig. Just don't see why this one is balking.

pyost · **Posted:** Fri Jul 14, 2006 6:10 pm

I will have to ask the same question someone already did, but didn't get an answer.

What should we do with links and images in our posts? When we switch, every link will point to the old one. Would a simple Find and Replace solve the problem when running it on the database?

visualbuffet · **Joined:** Fri Feb 10, 2006 4:36 am **Posts:** 124

pyost, content will have to be fixed manually.

Kalpax, I was having this issue, please check your configuration file carefully.. It sounds like a path error to me.

kalpax · **Joined:** Wed Sep 28, 2005 6:33 pm **Posts:** 56

I turned it over to my hosting company and they were able to work it out..."I had to remove the "CREATE DATABASE" statements in order to get it to import". Now, I don't get that, the export process was 'rote', should have produced sql just like the other sites that I moved. Is the sql script supposed to have "CREATE DATABASE" statements in it? I know the instructions tell one to create the db manually and assign a user all perms...I'd like to figure out why this one varied from the others, but maybe it's not going to be possible.

joonji · **Joined:** Fri Sep 02, 2005 5:10 am **Posts:** 17

Hey Guilliam and ALL,

Is there an updated thing on the steps you had because I've tried that recently and it didnt work, I get the same error as this dude here on this post:

http://forum.joomla.org/index.php/topic,71273.0.html

Anyway I've never had errors like that ever, well I kinda sort of got a way around with it right now I am just stumped anybody here can answer that.

Thanks all and cheers.

Junji
Cebu

nzbdex · **Joined:** Thu Jun 29, 2006 8:30 pm **Posts:** 17

I was wondering if there was a guide to copying the db to a godaddy site.

I get this error:

Quote:

CREATE TABLE `jos_acctexp` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`userid` int( 11 ) NOT NULL default '0',
`expiration` date NOT NULL default '0000-00-00',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =2;

MySQL said: Documentation
#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 COLLATE=latin1_general_ci AUTO_INCREMENT

howdwegethere · **Posted:** Sun Jul 23, 2006 5:18 am

I have remote site which I want to bring local for backup and to do some redevelopment work on. I've followed these instructions and eventually got it working, almost...

The backend admin section works fine and the front page loads okay, but none of the links off the front page work so I can't browse the site.

I get the error

Not Found
The requested URL /N2B/content/view/81/96/ was not found on this server.

The remote site has OpenSEF running, but I've tried turning it both on and off on the local copy but that didn't help.

Any ideas

infograf768 · **Posted:** Wed Jul 26, 2006 5:37 am

nzbdex wrote:

I was wondering if there was a guide to copying the db to a godaddy site.

I get this error:

Quote:

CREATE TABLE `jos_acctexp` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`userid` int( 11 ) NOT NULL default '0',
`expiration` date NOT NULL default '0000-00-00',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =2;

MySQL said: Documentation
#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 COLLATE=latin1_general_ci AUTO_INCREMENT

Take off with an editor all instances of "DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci" in the dump.

Set these parameters when creating the empty database with phpmyadmin

pyost · **Posted:** Fri Jul 28, 2006 9:12 am

visualbuffet wrote:

pyost, content will have to be fixed manually.

But why? I just checked my existing database with a lot of content, and found out that it would work fine. The web site is located at http://www.domain.net, so I exported the database and did a search on "www.domain.net". All the results notepad found actually are the ones that should be replaced if I were to switch to http://www.domain2.net. So what's the problem?

infograf768 · **Posted:** Fri Jul 28, 2006 9:25 am

Relative urls are always better to me. Makes things easier.
Gobal change of one url to the other is fine.

pyost · **Posted:** Sat Jul 29, 2006 2:54 pm

Of course, global URLs are the best, but still, I can't use that when putting images in an article, can I?

infograf768 · **Posted:** Sun Jul 30, 2006 5:36 am

Using relative path is easy with JCE 1.0.4. Just a parameter to change in the bot.
For images as for links to other articles or files to download. For anything which is in the images/ folder.

Quote:

Gobal change of one url to the other is fine.

means you can achieve this easily by editing the dump.

pyost · **Posted:** Sun Jul 30, 2006 10:09 am

infograf768 wrote:

means you can achieve this easily by editing the dump.

Thanks, that's all I needed to hear!

NoXstar · **Joined:** Sat Aug 05, 2006 9:26 am **Posts:** 12

I can't seem to figure it out. I just get the same problem over and over again.

I'm gussing that my mysql is different from the servers that I use, but how to fix this?

infograf768 · **Posted:** Mon Aug 07, 2006 8:31 am

I would delete all the collate latin1_general_ci as well as all the charset=latin1 in the dump before query.
This should be set for the entire db.

NoXstar · **Joined:** Sat Aug 05, 2006 9:26 am **Posts:** 12

Is it okay that i ask, how do you do that? Do i fix that in phpmyadmin or do i fix it by hand, going over the entire dump...

infograf768 · **Posted:** Mon Aug 07, 2006 9:14 am

Open the dump with a text editor and mass replace it. Find/Replace all instances.

NoXstar · **Joined:** Sat Aug 05, 2006 9:26 am **Posts:** 12

By the way, thanks for trying to help me. But after doing just what you said, phpmyadmin found a new problem.

NoXstar · **Joined:** Sat Aug 05, 2006 9:26 am **Posts:** 12

I have found the solution to the problem.

The mysql was different versions from website 1 and website 2. So when i exported the database, i had to check a export button saying: Mysql 40, since it was that version i'm using on website 2.

Thanks for helping me, infograf768

infograf768 · **Posted:** Mon Aug 07, 2006 9:41 am

Also, you had left a collate...

Good to know it works for you now.

pyost · **Posted:** Mon Aug 07, 2006 11:32 am

pyost wrote:

I will have to ask the same question someone already did, but didn't get an answer.

What should we do with links and images in our posts? When we switch, every link will point to the old one. Would a simple Find and Replace solve the problem when running it on the database?

I have just found out how stupid I had been! If you are changing the domain name, there is no need to worry about images and links in content items. When you create a link that points to a page on your web site, it containt the site URL, right? Well, Joomla! is so smart that it created relative URLs in the database in those cases. The same goes for images.

So, if you created an image with "www.domain.com/images/smth.jpg" as the source, in the database it will be stored as "

I love Joomla!

Lili2 · **Joined:** Mon Aug 21, 2006 9:19 pm **Posts:** 7

doctorlife wrote:

8 ) In the box for "Structure" check the boxes for "Add DROP TABLE" AND "Add IF NOT EXISTS".

i did everyting through myphpadmin and was only getting errors when importing my db, and getting error 2 on my webpage.

after going back into my old db and exporting it again and checking these two options off, i was able to import it in with phpadmin and have it work,
THANK YOU

jnews · **Joined:** Fri Jul 28, 2006 2:55 pm **Posts:** 7

Quick queston:

I moved a site using the instructions posted on this site, as I have for many other sites previously. I carefully fixed all pathing in the config.php, and went to hit the site.

http://www.domain.net gives me a 500 error

http://www.domain.net/administrator brings me to the Site is down for maintenance, error code 2.

I had to change the name of the Db and the user name in order to move it, but fixed those as well in config.

So my question is this... Does changing the db name or user name in someway effect the secret word or password? Can't think of another reason I wouldn't be able to connect to Mysql (error code 2).

Any thoughts?

rickei · **Joined:** Tue Mar 28, 2006 9:57 pm **Posts:** 4

I'm getting the #2 error. this is the 5th time I have moved a Joomla site, and this is the first time I have had any errors.

my problem is the site was developed on a server with MySQL - 4.0.27-standard

and I am moving it to a site with MySQL - 4.1.18-standard

I have tried every solution in this tread, and have no other errors other than the
This site is temporarily unavailable.
Please notify the System Administrator
2

any other solutions or ideas????

rickei

joomlien · **Joined:** Thu Aug 03, 2006 6:42 am **Posts:** 9

Hi there!

To make a local copy of my online site I have followed all the steps:
- export then import the database
- copy all the files
- modify the configuration file

When I try to use the local site, the admin back end works fine, and everything seems alright (content, etc.).

However, the front end (127.0.0.1) doesn't work: the front page is displayed but without all the rest of the page. If I do "View page source", the first lines are:

Only the contentpane is displayed. Yet, the template is selected like it should be in the back end...

Do you know what is going on here?

Thanks a lot for your help, it's much appreciated!!!

Joomla! Discussion Forums

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

Quick reply

Who is online