what is the best way to switch my database from one host to another?

I would like to migrate the database for my WordPress site from one host to another. I have had problems with this particular action in the past. I tried saving the database using php myadmin then importing it into a fresh database I set up on the new host but it would not import because the mysql versions were not the same.

Do you have any suggestions on this?

Thank you.

Scott Aucutt

  • Kasia Swiderska

    Hi Scott,

    Can you tell me if the error while importing backup to new database looked like this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax (...)? Or it was something else?
    What is the version of WordPress on "old" hosting?
    From my experience database drop from PhpMyAdmin is the fastest way to move database. I always drop to bgzip archive (some severs have problems with zip) and then import it to new database. Of course very big archives could be problematic.
    Do you want to move only database or whole WordPress installation?

    kind regards,
    Kasia

  • Scott

    I am moving the entire WordPress site. I have already moved all of the WordPress files and content files to the a directory. That WordPress install is linked to a mysql database on a different server. I would like to export the database from the current location, import it to a new mysql database and then link that new database to the WordPress installation by changing the wp-config.php file.

    I am not sure if that will even work. The main goal is to get that WordPress site completely off of the old host so I can cancel that hosting account. So if you have a suggestion of a better way to do this please let me know. This site is a blog with many years of archives, images and stories which all have to stay intact. So it is important to not lose any of the old blogs.

    I am including three images for reference. One is the mysql information for the current site. The other image is the error message I get when I try to import the exported database into the new db. The third is info on the new database. One thing of note: The old database is on a server running MySQL v 5.5 and the one I am importing to is running 5.1.73. I don’t know if that is the problem. Please help.

    Thank you,

    Scott Aucutt

  • Kasia Swiderska

    Hi Scott,

    I am not sure if that will even work. The main goal is to get that WordPress site completely off of the old host so I can cancel that hosting account.

    This should absolutely work - if you don't change domain with your hosting, this is a good way for moving WordPress.
    So the error you see while importing data base is not because versions of MySQL are different, but because your import file contains an SQL query that attempts to create a database for the wrong username.
    Open your exported sql file and see if there is on the beginning of the file something like this
    CREATE DATABASE YourDataBaseName;
    if yes, comment it by adding two dashes before, like this:
    -- CREATE DATABASE YourDataBaseName;
    after saving the file, you should be able to import your database. But be careful to not mess with charset of your sql file (don't open it in Windows Notepad).
    If this will not help, we can try to use Snapshot to generate backup from your old hosting, and then extract only sql files to import.

    Please let me know how it went,
    kind regards,
    Kasia

  • Scott

    Kasia,

    Commenting out that line did not help. I still get an error (see first image). I tried commenting out the line after that and same result because when I did it the first time it seemed as if the next line was causing problems but I just got a different error (see second image).

    I'll see if I can use snapshot and extract the sql files. I'm not sure why this would make a difference because it is the same sql file, is it not?

    Another question on this: I used a text application to edit the mysql file called TextWrangler. Is this acceptable? I guess I'm wondering if the application I use to edit the file makes any difference?

  • Kasia Swiderska

    Hi Scott,

    I'll see if I can use snapshot and extract the sql files. I'm not sure why this would make a difference because it is the same sql file, is it not?

    When you extract snapshot archive, you will see that there is a lots of sql files. Every table in separate sql. Without any "CREATE DATABASE" and other stuff.

    TextWrangler is OK.

    About that error - when you are in PhpMyAdmin do you first select into which database import files?

    kind regards,
    Kasia

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.