Backing up a MySQL DB with phpMyAdmin

Posted on 2006-05-04
Last Modified: 2008-03-10
I am trying to move to a new web host and I need to move my old MySQL database to my new MySQL database.

What I'm doing:
From phpMyAdmin I choose the database from the drop down menu on the left.
I then choose the "Export" tab
I leave all the options as default (Export as SQL, Structure > Add AUTO_INCREMENT and Enclose table and field names with backquotes, Data > Use hexadecimal for binary fields, Export Type > Insert)
I then download the .sql file

On my new host I create a new database. I then select the database and click on the SQL tab.
I use the "Browse" button to choose the location of the .sql file.
I kept getting errors here until I changed the SQL export compatibility: to MySQL4 when exporting from the old database.

This seemed to work. The webpage that accesses the database is actually a knowledgbase. The knowledgebase loads fine and actually shows all the subcategories and knowledgebase article names. However when I click on a knowledgebase article to read it, I keep getting the following error:

Invalid query: Column 'id' cannot be null

I can't tell if this is something I'm doing wrong when backing up and restoring the database or if there is something else I'm missing. Any suggestions?
Question by:DVation191
    LVL 4

    Accepted Solution

    It appears that the database was restored incorrectly. The error probably means that your script makes a query with null id which can be from all sorts of incorrect data in your db.
    What version of MySQL does your old and new hosts use?
    To do anything it would be very helpful to see your .sql file...
    LVL 20

    Author Comment

    The error I was getting had to do with the different versions of MySQL I was using as you suggested. By fixing those lines I was able to migrate the database without error. Thanks.
    LVL 4

    Expert Comment

    You're welcome. You should see how minor differences in postgresql versions (i.e. 7.3.2 versus 7.3.5) screw the db up! Thank god (myself, more likely) that I'm no longer a system administrator.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now