Switching hosts want to transfer my database

I'm considering switching to a new hosting provider, and I would like to transfer my database f开发者_StackOverflow中文版or my production site to the new hosting provider. I'm using mysql. What are the steps I would need to take to transfer my db?

Appreciate any help.

Thank you,

Brian

---

Assuming a relatively simple app (PHP, something like that), one app server, one db server, then briefly:

On the new host, create the necessary accounts on the database that you're using on the old host's database.

Copy the app code over.

"Lock" your app on the old host so no data changes can occur (if this is feasible.)

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html is your friend. Dump schema and data, and capture it to a file. Here is the command I used to dump the database exampledb that has the login of example:

mysqldump --add-drop-table -u example -p exampledb > output.sql

(The --add-drop-table makes it easier to re-run the script if you need to later. But it does create a script that will destroy your database, so careful how you run it.)

Now copy (maybe using scp) the output.sql file to your new host.

On the new host, run mysql to build the database with the schema and data from the old host. I use a command like this one, assuming user "example" and a database name of "exampledb":

mysql -u example -p exampledb < output.sql

(Be careful to run this ONLY ON THE NEW HOST. It will obliterate your database.)

The nice thing is, you've got a blank slate of a new machine. You can keep trying different things on that machine without breaking anything.

Turn on the app on new host. Test. If it's been a while, you may need to make changes to get your code up to a newer version of the language. (I did in my case. But maybe you were better about keeping your code up to date.)

Shut down app on old host.

Point DNS/router/whatever to new host.

What'd I miss? (Just went through this moving my silly website to a new machine.)

---

It's pretty simple, especially for just a single database?

mysqldump followed by a mysqlimport.

MySQL Dump

Generating the .sql file is all you need, because that will contain all of the table information such as CREATE INDEXES, which when you then run through all of your inserts, will add the indexes.

If you struggle with command lines, may I suggest using Navicat Lite. It is free, and is the best GUI that I've seen on the market.

Navicat Lite