Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving database to new machine

    I'm trying to relocate my site to a new host and I'm having a lot of trouble moving the database.

    I did the export for the old host/database with phpmyadmin. It saved to a txt/sql file with no problems.

    When I try to import it into the new host/database I get all kinds of syntax errors.

    Then I realized, after purchasing the new host that:

    Old host/db = MySQL version 5.0.24a
    New host/db = 4.0.27

    Is there any way to convert a 5.0 db to 4.0?

  • #2
    ess
    ess is offline
    Regular Coder
    Join Date
    Oct 2006
    Location
    United Kingdom
    Posts
    866
    Thanks
    7
    Thanked 30 Times in 29 Posts
    If you still have access to your old host, then you can make use of the following command using PhpMyAdmin

    Code:
    mysqldump --compatible=mysql40
    This should dump out the database structure including the data in the database.

    cheers,
    Ess

  • #3
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Error

    SQL-query :

    mysqldump * - compatible = mysql40

    MySQL said:


    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 'mysqldump * - compatible = mysql40' at line 1
    Sorry I'm a bit of a n00b at this stuff. I can point and click, but don't know any of the code. I didn't put that star in there, I copy/pasted exactly what you wrote in your post.


  • #4
    ess
    ess is offline
    Regular Coder
    Join Date
    Oct 2006
    Location
    United Kingdom
    Posts
    866
    Thanks
    7
    Thanked 30 Times in 29 Posts
    Here is a couple of websites that explain how might be able to dump out a single database, or all-database from a given MySql server instance.

    http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

    http://www.linuxcommand.org/man_pages/mysqldump1.html

    Here is a good article that explains different ways to back MySql database
    http://codex.wordpress.org/Backing_Up_Your_Database

    You can also have a go at using webyog. You can download the community edition from the following URL free of charge.
    http://www.webyog.com/en/downloads.php

    If you do download webyog, you can easily connect to a remote MySql database, and be able to export the database as a text file. You should note that you can disable MySql5 features when you are performing the export operation if you are to use webyog.

    Note: I would advise you to install MySql4 on your local machine so that you can do all the testing etc. before dumping your database on the server.

    Good luck.
    Ess

  • #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,044
    Thanks
    2
    Thanked 316 Times in 308 Posts
    Don't most phpmyadmin versions have a dropdown selector on the export screen where you can pick the output file version?

    Also, the mysql migration toolkit might be able to help you read your Version 5 file and import it into a version 4 server - http://mysql.com/products/tools/migration-toolkit

    The mysql administrator program might also let you restore a version 5 file to a version 4 server (I have not tried this with the administrator program, but it would not hurt to try it.) The mysql administrator is part of the same package with the toolkit at the link already posted.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #6
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    That webyog program worked like a charm.

    I still had to take out the "DEFAULT CHARSET=latin1", but it was quick/easy to do with notepad.

    Thanks for the help!

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Why would you move to a host with mysql 4.0.x?

    it is out of date, two major code revisions ago. MySQL 4.1 was the production standard in October of 2004.

    You are giving these people your money right?

    I mean a car is a car, do you want to buy a DeSoto or Edsel or something more modern? Assuming you aren't a collector I mean?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •