Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    512
    Plugin Contributions
    0

    Default Import local backup into new database

    I need to import data from a local backup into a new database.

    I created a new MySQL database and new name for it. Unfortunately I did not enable drop table, when I backed up my old database.

    When I try to import my old data into a new database, it errors out and states that database exists already. (my old database is still up, but I do not want to delete or merge due to some changes)


    I have my local backup as a .sql file which I downloaded with phpmyadmin, with these settings: see jpeg

    and I also have a .txt file from a telnet/SSH dump but it also contains my old database name.

    any advice?
    Attached Images Attached Images  

  2. #2
    Join Date
    May 2007
    Location
    Florida
    Posts
    18
    Plugin Contributions
    0

    Default Re: Import local backup into new database

    Hi there,
    A few questions-
    is your new database (in which you are trying to IMPORT data) totally empty?
    Or are there tables in it? Specifically, did you install and unpack ZenCart's tables to this new database?

    In your screen shot it appears you did not check 'Save as file', so generally if you leave that unchecked it opens the file... then did you save it?

    as a test, in phpmyAdmin, create a totally new database. leave it empty. try to import your sql backup. do you still get an errror?

  3. #3
    Join Date
    Nov 2006
    Posts
    512
    Plugin Contributions
    0

    Default Re: Import local backup into new database

    I think this is my issue, with all my backups of my old database (dumped from phpmyadmin) with above settings (save as file was checked sorry) this line exists "create database"

    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.9.1.1
    -- http://www.phpmyadmin.net
    -- 
    -- Host: MySQL.XXXX.com
    -- Generation Time: Dec 07, 2007 at 09:34 AM
    -- Server version: 4.0.27
    -- PHP Version: 4.4.4
    -- 
    -- Database: `XXXXX-db101`
    -- 
    CREATE DATABASE `XXXXX-db101`;
    USE XXXXX-db101;
    
    -- --------------------------------------------------------
    
    -- 
    -- Table structure for table `address_book`
    
    etc..
    My new database db102 is empty, but when I try to import from my local file I get an error that that database exists already (db101).

    I must import from my saved file, as the server db101 is possibly messed up.

    A further mystery is why when I back up my database now, with the same settings the CREATE DATABASE `XXXXX-db101`;
    USE XXXXX-db101; is gone.

    I have in addition to the .sql file a text file which I dumped from telenet, which only has this in the header:

    Code:
    -- MySQL dump 9.11
    --
    -- Host: mysql.XXXXX.com    Database: XXXXX-db101
    -- ------------------------------------------------------
    -- Server version	4.0.27-log
    
    --
    -- Table structure for table `address_book`
    Should I import just the text file with telenet into the empty db102? am I stuck with the name of the old database?

    Or Simply change the name of my existing database db101 to TEMP.....create a new empty database with the same name db101..import from my saved file...download again with DROP table and hopefully not the "create database" part and upload into a new empty database db102 and restore the old database name from TEMP back to db101?

  4. #4
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: Import local backup into new database

    There's an FAQ on this topic:
    https://www.zen-cart.com/tutorials/i...hp?article=103

    Pay attention to the "NOTE" in step 3 ... if you haven't already selected your database before exporting, the create-database stuff will be inserted into your export ... which can cause confusion and permissions problems when importing later.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Nov 2006
    Posts
    512
    Plugin Contributions
    0

    Default Re: Import local backup into new database

    Quote Originally Posted by DrByte View Post
    There's an FAQ on this topic:
    https://www.zen-cart.com/tutorials/i...hp?article=103

    Pay attention to the "NOTE" in step 3 ... if you haven't already selected your database before exporting, the create-database stuff will be inserted into your export ... which can cause confusion and permissions problems when importing later.
    I guess that was my error. Now I am stuck with "create database" in the last back up of my site.

    I need to import this file into a new database, any suggestions on what to do?

    This is the only thing that I can think of:

    "Or Simply change the name of my existing database db101 to TEMP.....create a new empty database with the same name db101..import from my saved file...download again with DROP table and hopefully not the "create database" part and upload into a new empty database db102 and restore the old database name from TEMP back to db101?"

  6. #6
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: Import local backup into new database

    Most hosts don't allow you to create databases via direct SQL commands, because they control the security at a higher level.

    Thus, your best solution is to simply remove the "CREATE DATABASE xxxxxxx" and "USE xxxxxxxx" statements from your SQL backup file.
    Then import it to your new database.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  7. #7
    Join Date
    Jun 2007
    Posts
    145
    Plugin Contributions
    0

    Default Re: Import local backup into new database

    Using a decent text editor (e.g. Notepad++) remove the Create Database statement from the SQL file. You can download Notepad++ from http://notepad-plus.sourceforge.net - it's free.

    Create the new database by whatever means your host allows, open it and import the edited SQL file.

    There may also be a 'Set' statement right at the end of your SQL dump that generates an error. I just ignore it because the import will have done everything it needs to by then - unless you hit a new problem.
    We're now settled in at our final home - http://www.swiftbooks.co.uk - where you'll find an eclectic collection of used books. Still a lot of work to do, though!

  8. #8
    Join Date
    Nov 2006
    Posts
    512
    Plugin Contributions
    0

    Default Re: Import local backup into new database

    Thank you both for your help, I will remove this statement, with my text editor:

    Code:
    CREATE DATABASE `XXXXX-db101`;
    USE XXXXX-db101;
    then the header will look like this:

    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.9.1.1
    -- http://www.phpmyadmin.net
    -- 
    -- Host: MySQL.XXXX.com
    -- Generation Time: Dec 07, 2007 at 09:34 AM
    -- Server version: 4.0.27
    -- PHP Version: 4.4.4
    -- 
    -- Database: `XXXXX-db101`
    -- 
    
    
    -- --------------------------------------------------------
    
    -- 
    -- Table structure for table `address_book`

    Do I need to delete anything else? any of
    Code:
    --
    around the delted statement? or name of the old database
    Code:
     Database: `XXXXX-db101`
    Sorry to ask such newbie questions..and hitting everyone over the head with the obvious I just don't want to make any mistakes when it comes to the database...

    Oh and Merry Christmas!

  9. #9
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: Import local backup into new database

    Most of the time, two dashes starting a line in a SQL file are considered to be comments.
    So, you should be able to leave those alone safely.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

 

 

Similar Threads

  1. v153 Getting Database into new v.1.5.3 using Backup My Sql
    By NWCE in forum All Other Contributions/Addons
    Replies: 2
    Last Post: 5 Aug 2014, 02:24 PM
  2. import 1.3.8 database into new install 1.3.9h
    By vwebworld in forum Upgrading from 1.3.x to 1.3.9
    Replies: 4
    Last Post: 5 Aug 2011, 10:49 AM
  3. Import customer details only into database
    By mcpisik in forum General Questions
    Replies: 6
    Last Post: 28 Dec 2010, 05:48 PM
  4. Import my OSC database into Zen Cart
    By Yen_JS in forum General Questions
    Replies: 0
    Last Post: 27 Dec 2010, 08:03 PM
  5. Can I import a 1.3.7 Database into 1.3.8?
    By Big_Woody in forum Upgrading from 1.3.x to 1.3.9
    Replies: 1
    Last Post: 13 Feb 2010, 12:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR