Jump to content

Quick Tutorial on how to back up and restore DB


Guest jmartinez

Recommended Posts

Guest jmartinez

NEW UPDATE: I have recently created a short video tutorial on both of these functions and they are located here. I will be creating another short video tutorial soon on how to switch your store from one domain or webhost to another.

The last few days I have been reading about a lot of people either changing webhosts, wanting to backup or restore their databases, or wanting to do a fresh install of CC and port over their database. Below is a quick and simple way to backup your database and restore your database with PHPMYADMIN. I hope this helps someone, somewhere. Sorry this is just a quick and dirty post didn't have much time to make it pretty. I will come back and edit it later when I have more time to show more in detail.

Here are the steps I took when switching from one webhost to another.

1: Did a full backup of my database like below

2: Did a full backup of my full site (I was just hopping webhosts not upgrading to a diff version of CC)

3: Installed CC at the new location

4: Backed up the new locations global.inc.php file (saved it to my site backup just over wrote the old one)

5: Uploaded my site backup to the new location

6: Restored my database like below

7: changed the home.inc.php CHMOD to 777

That was basically it, all my mods and data was now at the new location. Sure there were a few little issues I had to fix here and there, but nothing really big.

Backing Up Your Database

Step One:

Login to your PHPMYADMIN and select the CubeCart database then click on export.

Step-1.jpg

Step Two:

Under the export section select all tables. Then make sure the SQL option is selected.

Under the SQL Options section check all the same things shown under Structure and Data.

Check to save as file.

Click Go.

Step-2.jpg

Step Three:

Take note of the name of your file you are saving as. In this case madcityc_ccrt3.sql (sorry top arrow went crazy on me).

Make sure that Save to Disk is selected.

Then Click OK.

Thats it your Database is backed up!

Step-3.jpg

Restoring Your Database

Step One:

Select your database you are restoring to.

Click on the SQL tab.

Click on browse.

Step-4.jpg

Step Two:

Locate the file you saved and click open.

Step-5.jpg

Step Three:

Verify that you see the file listed then click Go.

Step-6.jpg

Step Four:

If sucessfull you will see this screen.

Step-7.jpg

Step Five:

To verify that your data is there I click on the CubeCart_inventory table on the left.

Then click on browse.

Step-8.jpg

Step Six:

You should then see your products...

(I started with a newly installed cart that is why I have only the test product listed.)

Step-9.jpg

Link to comment
Share on other sites

Guest degsey69

Very Good and informative tutorial which will help the newbie who is a little nervous about restoring their database backup for their shopping cart. :)

Link to comment
Share on other sites

Guest EverythingWeb

Nice tutorial!

Mod Note: Pinned into this topic. To be included in a 'main' pinned topic as a link, for when it becomes 'un-pinned' on its own.

Link to comment
Share on other sites

Guest jmartinez

Thank You for plain tutorial. It is useful for everyone who has CC

Thanks! Just hope it can help some people. I was going to clean it up and rewrite it so that it was formatted nicely but I have been really busy lately. Then I thought why not make a tut that starts with full backup and restore of CC and the DB. So I started to work on this. So here's the plug LOL...

.:COMING SOON TO A CUBECART FORUM NEAR YOU:.

A visual step by step guide to backing up and restoring your CubeCart and DB...

Link to comment
Share on other sites

Guest chantesse

I have backed up this way and the contents of the sql dump created look OK. How to to a test Restore? My host easily.co.uk seems to allow me to have 1 database name only - I don't fancy chancing my good data by restoring on top of it.

Has anyone tried editing the sql dump with a text editor & changing all the table name prefixes then re-importing (also would be useful if you wanted a test store to play with)?

Link to comment
Share on other sites

Guest jmartinez

I have backed up this way and the contents of the sql dump created look OK. How to to a test Restore? My host easily.co.uk seems to allow me to have 1 database name only - I don't fancy chancing my good data by restoring on top of it.

Has anyone tried editing the sql dump with a text editor & changing all the table name prefixes then re-importing (also would be useful if you wanted a test store to play with)?

Yes you can edit the dump file with either notepad or some other text editor. Also you can open the file with your favorite html/php editor. As for actually testing the information, I would say to import the data to a new sql database. If your host only allows you to have only one db then all I can say is.. what a terrible host solution. Most web hosts will provide for their basic package to have at least 2-3 database's. **URL Removed** This is what I offer for my most basic package. Just for reference. There is a way to create a test store on your local system though if a better host solution is not the answer for you. Take a look at this pinned topic on how to install CC on a thumb drive. Or install apache/php/mysql on your windows or linux box and test that way.

Moderator Note: URL Removed. Self-promotion. Thanks for the co-operation.

Edited by jmartinez
Link to comment
Share on other sites

Excellent tutorial. I learned a great deal of useful info. I have a related question.

For background, please see: http://www.cubecart.com/site/forums/index....showtopic=17524

No one has responded to me there but perhaps you can guide me?

After posting the above, I've finished the edits of the CSV file of the store_inventory table and I want to import it back into the database and overwrite the table that is there. But I have not seen anything that explains how to import that edited table.

To complicate matters, when I tried to save my work, the "save" dialog delivered ominous warnings that I was about to lose formatting or data or whatever but, in typical Mocrosoft fashion gave no useful information on exactly what would be lost or any of getting around the problem. So I saved it as an Excel file with the extension .XLS.

Anyway, can you advise me how to get this table back into the CC data base?

TIA

The save dialog that

Link to comment
Share on other sites

Guest jmartinez

Excellent tutorial. I learned a great deal of useful info. I have a related question.

For background, please see: http://www.cubecart.com/site/forums/index....showtopic=17524

No one has responded to me there but perhaps you can guide me?

After posting the above, I've finished the edits of the CSV file of the store_inventory table and I want to import it back into the database and overwrite the table that is there. But I have not seen anything that explains how to import that edited table.

To complicate matters, when I tried to save my work, the "save" dialog delivered ominous warnings that I was about to lose formatting or data or whatever but, in typical Mocrosoft fashion gave no useful information on exactly what would be lost or any of getting around the problem. So I saved it as an Excel file with the extension .XLS.

Anyway, can you advise me how to get this table back into the CC data base?

TIA

The save dialog that

Ok when updateing your SQL files via PHPMYADMIN by uploading a .CSV file you go to the table you are attempting to update. Then select SQL, at the bottom of the page you should see (Insert data from a text file into the table).

Click it and then browse for your .CSV file

Check Replace table data with file (if you want it to)

Set Fields terminated by to a comma

Leave the rest and then Submit

This will upload your .CSV file to your database. As always make sure your original data is backed up before attempting this.

Link to comment
Share on other sites

Thanks for the prompt response. I am lost. The problem is my newbie status in learning myPHPadmin, combined with a limited ability to follow anything but explicit instructions.

The late comedian Jonathan Winters used to tell about asking driving directions from a farmer who said, "Go down the road a ways and turn left where the old school house used to be." I have the same problem.

May I impose further on your generosity and ask for clarification?

You say:

"go to the table you are attempting to update. "

What, exactly do you mean by "go to the table you are attempting to update"

I opened the db and I'm looking at a list of tables. Across the top the column labels are:

Table Action RecordsTip Type Size Overhead

I scroll down and see the table CubeCart_inventory. I put a check mark in the box. Is that what I should do? Or does "go to" mean something else?

You say, "Then select SQL, at the bottom of the page you should see (Insert data from a text file into the table)."

There is no SQL at the bottom of this page and nothing at the bottom that says "(Insert data from a text file into the table)."

So at this point onward, I'm only guessing and I've learned that computers are even dumber than me, having even less ability make assumptions.

May I ask for some step by steps, please? You help is greatly appreciated.

Link to comment
Share on other sites

Guest jmartinez

My apologies, ever hear the old saying "Never assume it only makes an ass out of u and me.." I should have been more explicate. Here we go..

Once you open PHPMYADMIN you have the option to select your database via the drop down on the left. Select your database.

It will refresh and show you a list of tables for that database underneath it. Click on the table that you want to update. In this scenario we will use the table CubeCart_Inventory.

After you have clicked on the table named CubeCart_Inventory it will refresh and show you the structure of the table. If you look at the top of the page you will see several tabs Browse, Structure, SQL, Search, Insert, Export, Operations, Empty and Drop.

Click on the SQL tab. The page with then refresh again and show you several options. At the bottom of that page you will see a text link that says "Insert data from a text file into the table". Click this link.

The page will refresh again and you will see the options to browse for your file. Click browse and find your .CSV file. Check Replace table data with file (if you want it to), Set Fields terminated by to a comma, Leave the rest and then Submit. This will upload your .CSV file to your database.

I hope this makes more since..

Link to comment
Share on other sites

Thanks for your patience and empathy. At a quck scan, the new info looks like the kind of dumbing down that's my level.

It's after 12:30 here and I have an early appotiment in Manhattan.o I'll have to go to bed now and try those instructions when I return.

Again, thanks . . .

Link to comment
Share on other sites

  • 3 weeks later...
Guest wolfberry

Excellent tutorial! I am one of those newbies who prefers visual learning especially when we haven't a clue of what we are getting into, and sometimes reading too much would eventually caused more confusion.

I want to change my webhost soon so I thought of practising how to download & upload from host to host.

I was so happy when I found your tutorial and followed the instructions for the backup of mysql database and I think it is successful as I did not encounter any problems on this section.

Unfortunately,after I restored the database in the new webhost this msg came up "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=72' at line 15 "

CREATE TABLE IF NOT EXISTS `addrecipe` (

`recipeid` int( 3 ) NOT NULL AUTO_INCREMENT ,

`reccat` text,

`recsubcat` varchar( 255 ) default NULL ,

`rectitle` text,

`recing` text,

`rectext` text,

`recpic` varchar( 255 ) default NULL ,

`recactivation` int( 3 ) default '0',

`recaddedby` varchar( 255 ) default NULL ,

`recrating` int( 3 ) default '0',

PRIMARY KEY ( `recipeid` ) ,

UNIQUE KEY `recipeid` ( `recipeid` ) ,

KEY `recipeid_2` ( `recipeid` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =72

Can anyone help me what the above is 'talking' about and how to rectify this problem?

Also, what does it mean 'No Privileges' under mysql side in myphpadmin, when I already granted all privileges to the user from mysql?

Thank you for your time.

Link to comment
Share on other sites

Guest jmartinez

Excellent tutorial! I am one of those newbies who prefers visual learning especially when we haven't a clue of what we are getting into, and sometimes reading too much would eventually caused more confusion.

I want to change my webhost soon so I thought of practising how to download & upload from host to host.

I was so happy when I found your tutorial and followed the instructions for the backup of mysql database and I think it is successful as I did not encounter any problems on this section.

Unfortunately,after I restored the database in the new webhost this msg came up "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=72' at line 15 "

CREATE TABLE IF NOT EXISTS `addrecipe` (

`recipeid` int( 3 ) NOT NULL AUTO_INCREMENT ,

`reccat` text,

`recsubcat` varchar( 255 ) default NULL ,

`rectitle` text,

`recing` text,

`rectext` text,

`recpic` varchar( 255 ) default NULL ,

`recactivation` int( 3 ) default '0',

`recaddedby` varchar( 255 ) default NULL ,

`recrating` int( 3 ) default '0',

PRIMARY KEY ( `recipeid` ) ,

UNIQUE KEY `recipeid` ( `recipeid` ) ,

KEY `recipeid_2` ( `recipeid` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =72

Can anyone help me what the above is 'talking' about and how to rectify this problem?

Also, what does it mean 'No Privileges' under mysql side in myphpadmin, when I already granted all privileges to the user from mysql?

Thank you for your time.

Does not sound like you have the database created yet. You must first create the database before you can import the tables. If your new webhost offers the ability to do a script install of CubeCart then do that first and then import the tables to the database that CubeCart creates.

Link to comment
Share on other sites

  • 1 month later...
Guest chantesse

In Step 7 of the Backup/Restore Tutorial, I see:

...7: changed the home.inc.php CHMOD to 777...

Am I right in thinking that home.inc.php should normally have CHMOD to 644 for security, per the installation instructions for Cubecart?

Link to comment
Share on other sites

  • 2 weeks later...
Guest AnnieK

Thank you sooooo much for this post....it has come in SUPER handy! I didnt even know where my freaking MyPHPadmin was located in my C panel...but once I found it...this was the SIMPLIST online tut I have done! It worked like a charm!! The only thing "extra" I really had to do was upload my thumbnails again..but other than that...PERFECT....

Thanks so much...as a newbie, I certainly appreciate it...

And to all you NEWBIES out there needing to do this...IT WORKS!!

Thanks again for your time and efforts in posting this for us all!!

Annie :blink:

Link to comment
Share on other sites

  • 2 weeks later...
Guest fridaydreamer

I don't know why I can't figure it out, it's probably unbelievably obvious, but can someone post a simple tuturial on how to back up my full CC site?? When I tried to just download everything, it wouldn't work due to some inability to open the .php files. Please tell me there is a simple straighforward method to back up my site!

Shaunie

Link to comment
Share on other sites

  • 2 weeks later...
Guest dr666

cheers for this

ive backed up now.

unsure if it worked yet as i aint changeing or anything.

however im confident that if/when the time arrives it will all be good

:-)

Link to comment
Share on other sites

  • 1 month later...

fast question regarding backup and restoral of CC database...

Can a database from cc v2.05 be "restored" to a newer version of CC (v3.x)

Thanks !

SS

Link to comment
Share on other sites

  • 2 weeks later...
Guest thimk

I succeeded once with these directions, used to change domains.

Saved localhost.sql after updating settings.

Developed problems in store, so deleted files and SQL-db, uploaded prior file

versions. Tried to restore localhost.sql and got rejected with

"#1044 - Access denied for user 'xyz'@'localhost' to database 'xyz_db'"

where the user should be xyz_id.

In MySQL, created db xyz_db for user xyz_id. Tried import and got

"'xyz_db'; database exists". The sql file correctly shows Database: `xyz_db`.

Renaming localhost.sql to xyz_db.sql or xyz_id.sql did not change anything.

I see no way to force import to use xyx_id instead of xyz for user-id.

My ISP puts owner's "name" in front of entered db and id, making xyz_db and xyz_id

at time of db and user name creations.

Can anyone help me, please???

Link to comment
Share on other sites

STILL TESTING.

I did an export as described, thru phpmyadmin, downloading

ocp_cube.sql, ocp_cube.sql.gz, ocp_cube.sql.zip.

With db still present, phpmyadmin re-imports ocp_cube.sql to ocp_cube for proper user name.

I then deleted db, representing crash or corrupted db removal, and tried same import. Got

"MySQL said: Documentation

#1046 - No database selected"

Tried import on ocp_cube.sql.gz. ONE time it succeeded, but renamed db as ocp_cube3, which is NOT acceptable.

Again dropped db, and import gets "#1046 - No database selected" for

ocp_cube.sql, ocp_cube.sql.gz, ocp_cube.sql.zip, and first renamed as ocp_cube.txt.

FAILURE.

Posting to phpMyAdmin forum at

https://sourceforge.net/forum/forum.php?thr...;forum_id=72909

has gotten no replies.

Now what?

By the way, import of *.gz would not allow edits of anything, as my PC programs can unpack *.gz but not pack them.

Link to comment
Share on other sites

Just tried changing the names within the downloaded sql file to the same name as the database thats already on the site Im trying to import to and I get this..

Error

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:

----BEGIN CUT----

eNplUElSwzAQvOsVc8jRm4yJgzgJR5WkKhuWCcdEXoq48IatLH5bDjyJLyCFChw4TNVUT093z7Aw

XIUEAgyBC3O2JIDBBXeIEY8UfkEo2HACg1lKoPsoGtF2WWsVeWw1+8Y4gmt5PriOM7QdbGMfsE8c

l9w5kPRxLbeyBHZuYIAWPX+eE/Asx3J9s5OiSkWbohcewoobQCdsGRmwYcrzNa9gxmBoOWi9oERZ

jPQWWk/XmmCsuNbxLJU3rw5nNKfLCYGsMvOuNkej+wdThddmX5dP0wSVc9HTtFSqCoXxoWyQgo9Z

2+V19auuoL2UDbHt0+mkjyt7oZesKpN6qGtad5JAUSei2KtWQ5OsylohtVKUlxkBenhTTzCuPwEh

AWOCHaALTeZZq2xv1rdv6Mn1tj/Y+wmkayykiEWnlHdxLWTc1u/bJGkl3l0ZQchoxGBMI/pEOftH

evwGXkOIJA==

----END CUT----

----BEGIN RAW----

ERROR: C1 C2 LEN: 1 2 261

STR: »

CVS: $Id: sqlparser.lib.php,v 2.47 2006/01/17 17:02:30 cybot_tm Exp $

MySQL: 4.0.27-standard

USR OS, AGENT, VER: Win IE 6.0

PMA: 2.8.0.2

PHP VER,OS: 4.4.2 Linux

LANG: en-iso-8859-1

SQL: -- phpMyAdmin SQL Dump

-- version 2.8.0.2

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Aug 17, 2006 at 11:10 AM

-- Server version: 4.0.27

-- PHP Version: 4.4.2

--

-- Database: `boatbrok_ccrt1`

--

CREATE DATABASE `boatbrok_ccrt1`;

----END RAW----

SQL query:

-- phpMyAdmin SQL Dump -- version 2.8.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 17, 2006 at 11:10 AM -- Server version: 4.0.27 -- PHP Version: 4.4.2 -- -- Database: `boatbrok_ccrt1` -- CREATE DATABASE `boatbrok_ccrt1`;

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 'CREATE DATABASE `boatbrok_ccrt1`' at line 1

Link to comment
Share on other sites

  • 2 weeks later...
Guest jmartinez

Sounds to me like you guys are attempting to recreate the database with the old database settings on either a new webhost or new domain. In order to do that successfully you will need to install CubeCart at the new location and then log in to your PHPMyAdmin interface and restore the tables of your original database overwriting the current tables at your new location. As an FYI I just created a quick backup and restore video tutorial, where you can visually see and hear how to perform a backup and restore. You can find that here. I will also be creating a video tutorial on how to switch your store to a different domain name or webhost within the next day or so.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...