How to re-import database tables for the NextGen Gallery Plugin when moving your WordPress website to a new domain – so your photo galleries work again!
Sometimes a website’s content needs to be moved to a new domain. If there are pictures and plugins involved, the process gets a little more complicated. I wanted to move a blog with posts and photo galleries from one domain to a totally different subdomain. There are really good instructions on what to do when moving your WordPress website to a new domain, and instead of rewriting them, there are links below.
How to Move WordPress to a New Domain
How to Move a WordPress Blog or Website
The keys to getting the NextGen Galleries back in working order on a different domain depends on being able to find and download the files that contain information about the previous NextGen database files in phpMyAdmin, then modifying and uploading them to the new domain’s server.
As I imported the xml file along with the whole wp-content folder (so the plugins were also replicated) onto the new – different server, I beamed with premature satisfaction that things seemed to be working. Then, with horror, I realized that the Nextgen photo galleries weren’t loading on the new site. (The Nextgen Gallery is a really great plugin for WordPress that allows uploading and organization for photos, among other things.)
I knew that the photos from the old site were in the new website and that all the previously used plugins had been reinstalled. This is because as the previous wp-content folder was being uploaded to the new server via ftp, I saw the photo names from the Nextgen Gallery uploading. (Downloading and uploading with compression took over one hour. I’m not complaining. Overall, it saved a lot of time.)
While I took care to replicate the posts, content and plugins, the galleries were not being accessed and could not be found. So, not wanting to upload over 100 photos and type all the photo details again, I started searching for a way to make this happen. The best explanation that I found was at the WordPress forums, but here are points of clarification.
Here’s a step by step way to get the Nextgen Galleries plugin working by going into phpMyAdmin.
1. Log into the old server to access phpMyAdmin. Click on the correct database to find the wp_ngg_album, wp_ngg_gallery, and wp_ngg_pictures files. (My example is a bit wonky because I think I was messing around and did two installs, so your table info will likely not have the wp1.)
Here’s a screenshot of what PHPMyAdmin export values I used. (I had additional editing because my files had (wp1_ngg…) – a number one after wp, so I changed them all to wp – even for all of the picture files, but in the end, it worked!)
2. Export them one at a time by making sure that only the chosen one is highlighted (on my first try, by default, every file was highlighted) by looking over the export values, then click “Go” in the lower right hand corner of the page. This will allow you to name the file and save it to your computer. “Save as” a .txt file.
3. After downloading the files to your computer (see above, and before importing them to the new domain) open the files in a text editor to make sure that the correct, updated database name is in the file. Most people will only need to make sure that the new (destination) domain’s database be switched from the old one.
4. Now log into the new server’s PHPMyAdmin. (It’s assumed that WordPress has already been installed, so you’ve already created the new database, and the plugins have been added or replicated.)
5. Delete the wp_ngg_album, wp_ngg_gallery, and wp_ngg_pictures files, and use the import function to import your .txt files from your computer. (I had to delete them before importing them, but that probably had to do with which boxes I left check marked on the initial export.) This replicates the previous NextGen commands and tables so that all the previous photos and galleries can be accessed and display as before.
This worked for me. All the galleries were recognized and loaded just as in the previous domain. Hope that it works for you. Many thanks to Alex Rabe (NextGen Photo Gallery plugin) and Kevin Sylvestre (Slimbox display plugin) for creating and sharing the plugins.
You have just saved me hours of work and frustration. I can’t tell you how much I appreciate your easy-to-follow directions. I looked all over the place for this. Best,
Matt Klein
Excellent! Really glad to hear that it worked for you.
You are a life saver!!! Thank you so much for posting this. It worked perfectly!
I’m about to move a 2.9.2 website over to 3.0 RC1 and the only thing that got me worried was my NextGen database.
Thanks for the info!!
I’ll be interested to hear how the upgrade goes, although it is the WordPress 3.0 Release Candidate, and are you also moving to a different domain? I would think that NextGen would be integrated to upgrade and play nicely with 3.0, but we will soon find out! Back everything up!
Just an FYI – you can also recover off the internal WP 3.0 backup database if you check marked the extra tables when you backed up.
The NGG stuff is towards the bottom and you can copy/paste it to make it work, but the format is slightly different than the phpmyadmin text export. Basically some wording is different and you swap #s for -s.
I went into phpmyadmin and exported a blank table, then pieced a new one together, saved as .txt in BBEdit and re-imported it into the database table.
That solution worked like a charm…
The expose to the phpMyAdmin is a great tutorial on how the Nextgen plug-in is connected to your WordPress installation – I really appreciated this approach.
Happy to hear that it worked out for you.
Getting the files and adding the information for the new database tables helps Nextgen get pointed in the right direction.
If only life could be so simple!
Hi keyalea,
Worked great here with a couple tweaks. I moved 18 NextGen galleries with over 400 images from a standard WP install to a subdomain site on a WP (Multi-Site) installation on a new host, new database name, and new table prefixes (just replaced the “wp_” prefix in the SQL exports.
I moved everything and then viewed the gallery pages and no images showed. It took me awhile to figure out that I had to simply change the path to the images (in each gallery) to point to the right spot.
Thanks for getting me started on the NextGen migration path.
Fabulous – worked for me moving my WP install from root to a sub-folder and a fresh database – THANK YOU!
You’re welcome. Thanks also to WP and all who contribute.
Thanks for this. Sure hope someone (Alex?) will package this functionality in the near future. Seems to me it’s essential for developers.
Thank you SO MUCH for writing this. This saved me hours of time. It could not have been easier. I owe you a beer. And a pizza.
Thanks for this, saved me a ton of drudgework. Would definitely be cool for this functionality to be part of the plugin.
Glad that the post could help. It would be a nice functionality to have with the plugin… because it’s open source, perhaps someone could code it – or work with Alex? Get him a few beers!
http://alexrabe.de/wordpress-plugins/nextgen-gallery/
Brilliant….. So easy to follow – Thanks a million.
V
You are a true hero! Thank You!!
You’re welcome!
THANK YOU!!! Saved me literally hours of work.
it worked for me, now I have all my galleries and albums but…now I can’t add a new gallery or a new album (no errors, just nothing happens) Why!?
I’m not sure why you can’t add new galleries or albums. I was able to add new albums. It may be a conflict with your theme – perhaps? I have not run across this. You may want to head over to http://wordpress.org/support/ as this seems to be a different problem than just transferring the galleries and albums to a new domain.
Wow… this worked like a charm… with a bit of help. For some reason on my old server the files were named “ngg_gallery”…etc, while on the new server they were named “wp_ngg_gallery”… so i had to go through and rename them in all 3 files before re-uploading the .txt files to phpMyAdmin. Works great though!
thanks, thank, thanks!
ez
What is the best solution to move my nextgen galleries (1500 images) from one database to another database on two different hosting companies…I need to do this for a client. UGH
With your instructions I moved 27 galleries, from one site to another. There was a little bit of redirecting to the right gallery page, but that was about it. Thank you.
Eric and Tom, glad to hear that the post helped out. You’re welcome and thanks for letting me know.
Ed, are you moving the database from one server to a new server, or one database to two different servers with two different hosting companies? I think the advise is the same, except if the domain is the same, it doesn’t need to be changed.
I think I know what’s happening with elena (April 23)
Probably the plugins version are different. Use the same plugin version in the new domain/WP. I had the same problem, and I solve this using the installed version in older WP.
Thanks for this brilliant post!
Gustavo, thanks for commenting and for responding to elena.
It’s amazing and beautiful what happens when we all lend and hand and work together. Thanks!
Keya,
Thanks for the reply…Here is the situation…I have a client with a domain (example.com/blog) on a different host and database…I’m creating a new site on different host, different database, but same domain…What do I need to do to keep the images from still working on the new site? Any help would be amazing…Site is a day away from being complete…
Revised version
Ed, If the information is the same, why not simply import all the information from one server to the other? The links at the beginning of the article should help you do that. If the domain and the database are the same, they should work. You can look to see the database name and information from the previous site, and name it exactly the same, import in all the information, including the images, and they should work.
Still confused…
Here is the deal…the next gen images are at a site called example.com/blog on different host and different database…
I’m creating a new site on different host and different database…she wants her domain to be example.com…Right now all the posts and everything are set…the nextgen images are showing up still because her domain is still live…my fear is when we switch I will lost all post images and galleries?
I’m just not sure the best way to get all galleries over? Is this more clear.
Ed, At the beginning of this article, there are links to posts that explain the best way to move the content of a website to either another server, or to another server with a different domain, or to a different domain. It sounds like you are moving the content of one website over to another server. These instructions should help.
In regard to the database for the NextGen image gallery, if the same name and password is used to create the database on the different server – with the same domain, everything should be set up. The instructions in this post are for moving the website and NextGen galleries to another domain, perhaps on another server – it really doesn’t matter. In the case where the domain changed, this articles give information on how to change that. If it is the same domain, and if you set up the database similarly, (same name, same password) then the gallery should show up. Hope this helps.
You. Are. My. Hero.
Thank you, thank you, thank you.
Thanks for the guide, it worked perfectly and I was able to knock it out in 20 minutes.
You’re welcome! Glad to hear that it helped.
I followed the directions. Now I can see all the pictures when I go into the gallery in the dashboard. However, when I add a gallery to a page, on some galleries, only two out of 15 pictures show, on others, no pictures show. Any advice?
Thanks!
Without knowing or understanding how your theme works, if you’re able to see the pictures in the galleries, but not see them on the pages, it sounds like there is a problem with your theme displaying all of the pictures in the gallery. Are there similarities between the galleries that show all of the pictures and the pages that do not? Are you trying to add the same gallery to different pages, and on the different pages, all, some, or none of the pictures show – or are you trying to add different galleries to different pages? Was your internet connection uninterrupted while you were uploading? So that perhaps the thumbnails were created, while the actual picture wasn’t able to upload the full information? Sorry I can’t help. Can someone else?
Genius!!!! – It works!!!!
Your post is a real contribution! You should be proud and pleased… you saved me hours of works and I know I just one of many.
Thank you! Robert
You’re welcome! Glad that I could contribute.
Thank you!! It worked like a charm and saved me hours of editing
When you say “I had to delete them before importing them” do you mean “empty” or “drop” from the database in mysql ?
it throws me this error :
Error
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 1
STR: <?
SQL:
CREATE TABLE `wp_ngg_album` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`sortorder` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SQL query:
CREATE TABLE `wp_ngg_album` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `sortorder` longtext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
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 ‘
<!–
- phpMyAdmin XML Dump
- version 3.4.' at line 1
do i need to use .sql extension instead of .txt ???
please help
djmk
derek,
What that means is that on the new server for the new domain, the existing files had to be deleted before importing the ‘previous’ files. In moving the data from one server to the next, the commands need to be linked up to the current database.
I’m not sure how to help you based upon what you have provided. I can tell you that I saved them as .txt files before uploading them.
Does anyone else know how to help derek?
This worked very well. Saved lots of time. For whatever reason it failed to import 2 of the galleries – something to do with the mysql but easy to upload 2 galleries than 12…
These instructions saved MUCH time and THANK YOU for making it public!
Also, in the downloaded exports from the original database, I had to go in and make sure the each of the references to the new database structure had the correct prefix. (mysql inserted the old database prefix) *so make sure you don’t just change the database name reference, but also change the prefix on the database name/field structure. EX: “olddbprefix_ngg_gallery” to “newdbprefix_ngg_gallery”.
Also, the command to “delete” the “ngg” files (database structure in table) is “drop”.
This is referenced in Step 5. “5. Delete the wp_ngg_album, wp_ngg_gallery, and wp_ngg_pictures files,…” That might be better referenced as “Drop the tables referencing the Nextgen Gallery (_ngg__). The mysql import will re-add them.
**At least this was my experience
You’re welcome. Glad that it could help. Thank you for adding your information and experience!
Great instructions, thank you. But for some reason, I’m still not seeing the galleries or photos recognized when looking in the dashboard (or the live posts). It’s all there in PHPMyAdmin, and the content is there when I look via FTP — but I’m seeing nothing inside the dashboard.
Any ideas? Thanks!
Just adding to my own content — I had to take another step for some reason of uploading the image folders into the galleries via the dashboard. They didn’t reattach to the posts they’d been connected to, however, but they are back in the dashboard now.
Jeannie and Steve,
I’m wondering if something is different with the many and recent upgrades that have happened within both WordPress and the NextGen Gallery plugin.
Have the directions posted above worked for others?
Thank’s for this post, it works!
Marco,
That’s great to hear. Thanks for letting me know. I was starting to wonder if it still worked with the upgrades.
I had the same problem after moving a site this morning. I followed your steps but still my images would not show up. My solution was to SSH into the server as root, navigate to the wp-content directory and run the following commands:
chown -R apache gallerychmod -R 766 gallery/This changes the ownership of the directory to apache and sets the permissions for the directory recursively to 766. I’m not sure or not if I could have skipped your steps exporting the gallery tables, I suspect not but I’m not sure. Anyway, I hope this works and helps anyone who has tried the steps listen in the article and is still having problems.