MySQL Database

ID #54

How do I export and move my database tables between servers or copy databases?

Applies to: Grid System

First, see this FAQ about how to create your MySQL database using OnSite.

To copy a database from one server at Modwest to another server at Modwest, you can use our experimental Database Migration Tool. Otherwise, or to perform the steps manually, continue:

The first step is to dump out the contents of the database running on the old server. You can do this by using the "mysqldump" command on the old server. This command will create all the sql necessary to recreate all the tables in your database, and is called a "dump". A dump can either be printed to your screen, or if there is a lot of data, the dump can be directed into to a file.  Note, that creating a "mysqldump" file does not remove your database content from the servers.  It is simply a copy of the database.

To dump your database from the old host, get a shell prompt there with telnet or ssh, and then type this all on 1 single line:

mysqldump -u DBUSER -p DBNAME > DBNAME.sql

substituting DBUSER with your MySQL username at the old host and DBNAME with your database name from the old host.

You will be prompted for your old host's MySQL password, which will not echo back at you as you type. After you provide the right password, you will have a file in your current directory called DBNAME.sql containing your entire database.

You'll need to get this file moved onto our system by either 1) FTP'ing it down to your own computer and then uploading it from there to your account on our system, or, 2) by moving the file directly from the old host to your account here with the 'ftp' or 'scp' command.

After you have the moved the dumpfile from the old host to your account here, follow these instructions on how to import the dump into your MySQL database running here.


Last update: 2010-09-29 16:24
Author: FAQ Admin
Revision: 1.2

Digg it! Share on Facebook Print this record Send FAQ to a friend Show this as PDF file
Please rate this FAQ:

Average rating: 3.17 (18 Votes)

completely useless 1 2 3 4 5 most valuable

You can comment this FAQ

Comment of Anonymous:
To FTP a dumpfile directly from the old host to here, type this at your old host's shell prompt after you have created the dumpfile:

ftp ftp.modwest.com

Enter your Modwest username at the first prompt.
Enter your Modwest password at the second prompt. (It will not echo back as you type).

Then you will be logged in and have a prompt like this:

230 User logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp>

Then type:

put dbname.sql

where 'dbname.sql' is the name of your dumpfile, and it will copy the file to your account here.

When you see:

226 Transfer complete.

It is finished and you can then follow the import FAQ:

http://www.modwest.com/help/kb.phtml?cat=6&qid=241

Added at: 2003-09-04 18:30

Comment of Anonymous:
To SCP a dumpfile directly from the old host to here, type this at your
old host's shell prompt after you have created the dumpfile:

scp dbname.sql myself@ftp.modwest.com:/dbname.sql

where "dbname.sql" is the name of the dumpfile you just created and "myself" is your Modwest hosting account username.

Enter your Modwest password at the prompt. (It will not echo back as you type).

Then you'll see a progress bar as the file is copied to your account here. Once it's done, then follow the import FAQ.
Added at: 2003-09-04 18:35

Comment of Anonymous:
To copy tables between 2 databases hosted at Modwest,

1. Make sure you have at least 2 databases here, both created using the Add Database section of your Control Panel

2. Use the Manage Databases link in your control panel and you'll see both databases.

3. Click the + sign next to the database name in the left window pane to expand it to see the individual tables.

4. Click a table name in the left pane

5. Click Operations in the right pane

6. Pull down the appropriate dropdown menus for "Move Table To" and then pull the dropdown for the new database name. Click "Go".

7. Do the same thing for every table in the original db that you want to copy.
Added at: 2004-04-01 18:51

Comment of Anonymous:
If you have a lot of data, you should avoid the 'extended insert' option when creating the dump, (http://dev.mysql.com/doc/mysql/en/mysqldump.html) because otherwise you could run into 'max_allowed_packet' errors when importing the dump.
Added at: 2004-11-11 15:27

Comment of Anonymous:
Tarantula From Inverted Software can do that for you. Download it and define the old and new servers. then connect to both of them, right click the database you need to move and select "copy". then just paste it into the new server.
Added at: 2007-06-04 11:10

Comment of Anonymous:
For databases on various servers you can specify the database server with the -h flag.

Two explicit examples

mysqldump -u DBUSER -h db.modwest.com -p DBNAME > DBNAME.sql
mysqldump -u DBUSER -h db1.modwest.com -p DBNAME > DBNAME.sql
Added at: 2007-08-15 12:45

Comment of How to Fix PHPBB Error: sessions is marked as crashed and should be repaired:
Hello, how can I export a specific column from a table? Is that possible on mysql?
Added at: 2011-07-28 19:08