================================================== ===========
With the release of 3.5.1 there is a new settings import/export feature built into the theme options
================================================== ===========

Here's what I do to test theme on "development" site and then backup/copy results to other site. Requires using of PhpMyAdmin (which most hosting providers have):
First - make sure you have same versions of theme installed on both sites.
Note: you can still use this sequence to do intermediate backups of theme settings.

* Goto PhpMyAdmin
* Execute this query (in SQL tab):
SELECT option_name, option_value FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';
* At the botton of resulted window click "Export" icon/link
* uncheck the option 'Structure' the option
* Check [x] Save as File
* Press [Go] button at the lower right corner. Save file. This will save all settings of Atahualpa theme in wp_options.sql file
* Go to your other blog, run PhpMyAdmin there
* Execute this query in SQL Tab to clear up possible pre-existing setting for the theme (thanks juddledad for tip):
DELETE FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';
* Click "Import" tab
* Navigate to your wp_options.sql
* Press [Go] button at the lower right corner
* Observe "Import has been successfully finished ..."
* Done - reload/view your site with new imported settings.

Enjoy,

Gleb Esman,
Wordpress Membership site plugin:
http://www.MemberWing.com

Yes, that should work. Thanks, Gleb.

Not to confuse matters, but would it be easier to create a function which compiles the entire theme with options and outputs it as a single stylesheet etc. In other words one that strips off the ability to customize further via the admin interface, but allows you to simply port over the theme.

Thus, if you wanted to edit further on your mirror site, you could just re-export and copy over.

Just an idea, and I do not claim to understand whether that would, in fact, be easier from a development perspective or not.

Hello Gleb:

My site is share-hosted at GoDaddy. I've checked my directory and /subs and did not see the PhpMyAdmin program anywhere. I've submitted a "support ticket" inquiring about where/how I get PhpMyAdmin.

In the meantime, can you suggest where I'd best go to learn more about using PhpMyAdmin -- the how's and why's.

And I want I want to second Flynn's THANK YOU!!

Spepheard Tim, mySQL usually come together with PHPmyAdmin. I have never learned or used PHPmyAdmin before, its interface is quite intuitive for first time user.

My way is

1. Export WP_Options from the old server.

2. Activate WP in the new server.

3. Go to PHPmyAdmin in the new server to delete (drop) WP_Options.

4. Import WP_Options SQL file from old server to new server.

It works for me. Just to make sure to change upload unix path in the DB to your new server path.

Originally Posted by gesman

Here's what I do to test theme on "development" site and then backup/copy results to other site. Requires using of PhpMyAdmin (which most hosting providers have):
First - make sure you have same versions of theme installed on both sites.
Note: you can still use this sequence to do intermediate backups of theme settings.

* Goto PhpMyAdmin
* Execute this query (in SQL tab):
SELECT option_name, option_value FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';
* At the botton of resulted window click "Export" icon/link
* Check [x] Save as File
* Press [Go] button at the lower right corner. Save file. This will save all settings of Atahualpa theme in wp_options.sql file
* Go to your other blog, run PhpMyAdmin there
* Click "Import" tab
* Navigate to your wp_options.sql
* Press [Go] button at the lower right corner
* Observe "Import has been successfully finished ..."
* Done - reload/view your site with new imported settings.

Enjoy,

Gleb Esman,
MemberWing.com

That worked like a charm! Exactly what I was looking for. Thanks!

Originally Posted by gesman

Here's what I do to test theme on "development" site and then backup/copy results to other site.[snip]

A big thank you for this information: saved me LOTS of time
Gill

I have a question, i export a wp_options.sql...... before IMPORT this table, do i have to drop the 'wp_options' table alredy in the data base??
i already try of many ways and doesn't work!!!!!!!!
help!!

One thing to remember.

Before you IMPORT the options to the new database, you should DELETE any existing Atahualpa options from that database. So before the IMPORT step in post (1) you should navigate to the correct database, go to the SQL screen and then issue

DELETE FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';

The reason for this is that many options are kept in wp_options, not just Atahualpa's (this is also why you don't just copy the entire wp_options table). The key to each row in the table is automatically generated. So if you have Atahualpa options in the table, after the IMPORT you will have duplicate options, or worse, you might have had XX in the new DB, gotten rid of it in the old db, but when you copy the options, it will still be in the new db.

By deleting the Atahualpa options before doing the IMPORT, you make sure this issue will not come up. And you will avoid a possible weird 'I never set that option, but it's showing up??" problem.

Originally Posted by juggledad

One thing to remember.

Before you IMPORT the options to the new database, you should DELETE any existing Atahualpa options from that database. So before the IMPORT step in post (1) you should navigate to the correct database, go to the SQL screen and then issue

DELETE FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';

The reason for this is that many options are kept in wp_options, not just Atahualpa's (this is also why you don't just copy the entire wp_options table). The key to each row in the table is automatically generated. So if you have Atahualpa options in the table, after the IMPORT you will have duplicate options, or worse, you might have had XX in the new DB, gotten rid of it in the old db, but when you copy the options, it will still be in the new db.

By deleting the Atahualpa options before doing the IMPORT, you make sure this issue will not come up. And you will avoid a possible weird 'I never set that option, but it's showing up??" problem.

I followed these steps, but it's still creating duplicates when I try to import.

Now, I'm using Wordpress_mu, which could have something to do with it, but there are separate versions of wp_options (one for the whole domain, and one each for the any subdomain created) and neither of them will work.

The first time I deleted bfta_ata entries it worked, but they don't appear to have actually deleted. Is there a way I can tell? Has the newer version of Atahualpa changed it's name from "bfta_ata"? I could see that being a simple answer.

And just for info, the first duplicate conflict that arises is "'1-0-_transient_random_seed' for key 1". Is that a wordpress, or a BFTA entry?

Okay, so I've gotten it to work, and I can see all of the BFTA entries in my database, nothing changed in the theme.

hmmm.... thanks for the detailed steps, I kinda thought it would have worked, like overwriting fields in the existing wp_options. Mine didnt work, getting an error. Guess I can go ahead and manually insert these but its time consuming.

Importing settings-----------
filename: wp_options.sql (no archive)
charset: utf8
allow interrupt of import... etc..: unchecked and checked, tried both
SQL compatability: tried NONE and TRADITIONAL

also, the original database name (thats blacked out) is different from the new one.... matter?

----- COMPLETED, my steps---:

Importing was the first problem because of the CREATE command (which is my default) but was easy to bypass by checking Add IF NOT EXISTS [x].

Next was the column count because of the error I was getting simply trying to insert a single line manually:

INSERT INTO `wp_options` VALUES('bfa_ata_show_xhtml_tags', 'Yes');

[i]would produce this:[/i] 

#1136 - Column count doesn't match value count at row 1

my export command is now

SELECT option_id, blog_id, option_name, option_value, autoload FROM `wp_options` WHERE
 option_name REGEXP  '^bfa_ata_';

and make sure the IF NOT EXISTS is checked.
Importing went smoothly after that.

what if you have 2 wordpress installations on the same server? I was able to export the settings from the one installation I wanted (i think), but don't have the choice of which settings to delete when I run the command.

the two wordpress installs should be in two different mySQL databases. You want to make sure you have selected the correct database.

Thanks. Because I have secured the MySQL table by changing its prefix from "wp_" to something else, I had to change that in the statement.

Sorry, this doesn't work for me. I get to the import step, then an error:

Error

SQL query:

--
-- Database: `hap0926209220695`
--
-- --------------------------------------------------------
--
-- Table structure for table `wp_options`
--
CREATE TABLE `wp_options` (
`option_id` bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
`blog_id` int( 11 ) NOT NULL default '0',
`option_name` varchar( 64 ) NOT NULL default '',
`option_value` longtext NOT NULL ,
`autoload` varchar( 20 ) NOT NULL default 'yes',
PRIMARY KEY ( `option_id` , `blog_id` , `option_name` ) ,
KEY `option_name` ( `option_name` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =706;

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

And then if I delete the "CREATE TABLE" guff, I get another error:

#1136 - Column count doesn't match value count at row 1

Also, I believe there's a missing step in the instructions. Before you run the SQL code, you need to select the wp_options section in the drop-down in the left column.

if you execute the sql query

SELECT option_name, option_value FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';

It can only export about 250 lines. It can not export the database since it is only refering to one table. You must have overridden the output and told it to export the entire table.

Also, you DO NOT want to delete the wp_options table, it contains many other options. This is why you need to issue the

DELETE FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';

so you won't create duplicate rows.

I was able to export just fine and did the empty table thingy but importing caused an error. I'm wondering if perhaps the two versions of MySQL are different? on my local MAMP installation I with with MySQL 5, it's looking like maybe the GoDaddy server is on 3???

thanks for any help you can provide and for writing up these instructions in the first place!

Allison Sheridan
NosillaCast Podcast at http://podfeet.com
A technology geek podcast with an EVER so slight Macintosh bias!
Follow me at http://twitter.com/podfeet

Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 25, 2009 at 06:11 PM
-- Server version: 5.1.37
-- PHP Version: 5.2.11
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

MySQL said: Documentation
#1193 - Unknown system variable 'SQL_MODE'

1) it looks like the version of phpMyAdmin you have is version 3.2.0.1
2) Why are you dumping the DB and not exporting it?
3) if that is an export as oppose to a dump, try deleting that line

Hi, i'm new here... but i find a solution :

in phpmyadmin, just before export sql file, check this 2 options :

" full insertions" "extented insertions" (in french "extensions complètes" et "insertions étendues")

after that, and for me, it's OK !

in fact, i have 2 versions of phpmyadmin, on 2 differents providers, and in one of them these options is not checked...

but now it's good !

best regards !

Originally Posted by juggledad

the two wordpress installs should be in two different mySQL databases. You want to make sure you have selected the correct database.

Hi! I have a single wordpress installation, which I have cloned six times. I have gone in and changed the prefixes in the wp-config files so they don't interfere with each other.

So then, it seems as if I should be able to modify these instructions somehow so they will work for multiple cloned blogs within a single wp installation. Any suggestions?

OK, I have solved my own problem. Below I have modified the original instructions for a cloned installation, where I have taken the original wordpress folder, duplicated it in File Manager, renamed it the new blog name, opened the wp-config file, and changed the prefix from "wp_" to "newprefix_". I want to export the atahualpa settings for the original "wp_" blog and import them into the new "newprefix_" blog that is in the same WordPress installation. These instructions are only for someone who is in the exact same situation that I have described above. Otherwise it will not work. I have put my modifications in ALL CAPS below.

INSTRUCTIONS FOR CLONED INSTALL
* Goto PhpMyAdmin
SELECT CORRECT WP INSTALLATION TO BOTH COPY AND INSTALL, BE SURE YOU KNOW ALL CORRECT PREFIXES PUT INTO CONFIG FILES; CAN SEE THEM IF YOU BROWSE THE TABLES
* Execute this query (in SQL tab):
SELECT option_name, option_value FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';
* At the botton of resulted window click "Export" icon/link
* Check [x] Save as File
* Press [Go] button at the lower right corner. Save file. This will save all settings of Atahualpa theme in wp_options.sql file
IN FINDER, DUPLICATE THE FILE FOR BACKUP AND RENAME A COPY WITH NEW PREFIX
THEN OPEN FILE INTO EDITOR AND SEARCH AND REPLACE "wp_" TO "newprefix_"; WHEN I DID THIS THERE WERE 4 OCCURRENCES
* Go to your SAME blog, run PhpMyAdmin there
* Execute this query in SQL Tab to clear up possible pre-existing setting for the theme (thanks juddledad for tip):
NOTE: CHANGE "newprefix_" PREFIX TO CORRECT ONE FOR BLOG TO IMPORT SETTINGS
DELETE FROM `newprefix_options` WHERE option_name REGEXP '^bfa_ata_';
NOTE: I FIRST CLICKED ON CORRECT TABLE NAME TO IMPORT DATA INTO ALTHOUGH I DOUBT IT MADE A DIFFERENCE
* Click "Import" tab
NOTE: CHANGE "newprefix_" PREFIX BELOW TO CORRECT ONE FOR BLOG TO IMPORT SETTINGS
* Navigate to your newprefix_options.sql
* Press [Go] button at the lower right corner
* Observe "Import has been successfully finished ..."
* Done - reload/view your site with new imported settings.

Originally Posted by gesman

Here's what I do to test theme on "development" site and then backup/copy results to other site. Requires using of PhpMyAdmin (which most hosting providers have):
First - make sure you have same versions of theme installed on both sites.
Note: you can still use this sequence to do intermediate backups of theme settings.

* Goto PhpMyAdmin
* Execute this query (in SQL tab):
SELECT option_name, option_value FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';
* At the botton of resulted window click "Export" icon/link
* Check [x] Save as File
* Press [Go] button at the lower right corner. Save file. This will save all settings of Atahualpa theme in wp_options.sql file
* Go to your other blog, run PhpMyAdmin there
* Execute this query in SQL Tab to clear up possible pre-existing setting for the theme (thanks juddledad for tip):
DELETE FROM `wp_options` WHERE option_name REGEXP '^bfa_ata_';
* Click "Import" tab
* Navigate to your wp_options.sql
* Press [Go] button at the lower right corner
* Observe "Import has been successfully finished ..."
* Done - reload/view your site with new imported settings.

Enjoy,

Gleb Esman,
Wordpress Membership site plugin:
http://www.MemberWing.com

Thanks, Gleb. This did the trick. I made a number of changes to my settings and honestly couldn't remember them all. You saved me from countless butt-numbing, eye-crossing hours of copying and pasting.

Originally Posted by araneum

Spepheard Tim, mySQL usually come together with PHPmyAdmin. I have never learned or used PHPmyAdmin before, its interface is quite intuitive for first time user.

My way is

1. Export WP_Options from the old server.

2. Activate WP in the new server.

3. Go to PHPmyAdmin in the new server to delete (drop) WP_Options.

4. Import WP_Options SQL file from old server to new server.

It works for me. Just to make sure to change upload unix path in the DB to your new server path.

I'm trying to do this, but having problems due to the database prefix being mismatch between developing server and the clients webhotel. On my developing server I'm always setting up separate databases for each project. Unfortunately the there is only the ability to make one database on the webhotel, this is a problem as the client needs a trilangual site and therefore I'm working with 3 different prefix:

Danish site: wp_DK_
English site: wp_UK_
German site: wp_DE_

Now how do I change the the wp_options from development server to wp_UK_options?

Anyone got a suggestion, as this would really save me time in setting up the 3 sites.