[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Backing up a MySQL DB with phpMyAdmin

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?
  • 2
1 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...
DVation191Author Commented:
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.
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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now