Import data into mySQL?

October 17, 2005 6:14 PM

I need help importing mySQL data from one server to another.

I have a 15mb file I exported from the source DB, but I don't know how to get it into the new one. I'm moving to a Dreamhost account, if that helps, and have phpMyAdmin at my disposal. It's certainly not intuitive -- can someone help me out?

posted by o2b to Computers & Internet (7 answers total)

The data dump from MySQL is just a series of queries which, when run, will recreate the data in a new database. View the datadump in a text editor, you'll see. Don't edit it unless you know what you're doing, though.

In phpMyAdmin, click your database name, click "SQL", and look for "Or Location of the text file:". Choose your file there, and it will be uploaded to the MySQL machine and then the commands in said file will be executed.

If there's a maximum size for the uploaded file listed that is less than your 15Mb file (it defaults to 2Mb, I believe), ask Dreamhost to increase the upload size for phpMyAdmin.
posted by jellicle at 6:25 PM on October 17, 2005

dang... I feel like I need to learn phpMyAdmin just so I can offer specific advice. To be honest if you have access to the MySQL port on the server then MySQL Administrator is the way to go for all your server admin needs, including backup/restore. Works well for cloning a database from one server to the next very cleanly.

The idea is pretty simple though. You need to dump a .sql file of the entire structure of your database and all the data that is in these tables. Basically it amounts to a couple 1000's lines of SQL. Then on the other end you "execute" that script. I'm pretty sure phpMyAdmin can at least execute the .sql script, if you can figure out a way to create it.
posted by nickerbocker at 6:29 PM on October 17, 2005

I had some suggestions, but on preview, they amount to what jellicle said. If your SQL dump does not include the 'CREATE DATABASE' command, you'll need to create it in the GUI before running your SQL.

If you have trouble getting Dreamhost to increase the upload limit, and you still have access to your old database, you could try running the export table-by-table.
posted by expialidocious at 6:37 PM on October 17, 2005

If you are comfortable at the command line, on the source server:

mysqldump --user=username --password=password dbname > MyDatabaseBackup.sql

You may have already done this part, since you mentioned you have the backup.

Then, move the .sql file to the target server, and do this:

mysql --user=username --password=password < MyDatabaseBackup.sql

This should work if username has permission to create new databases. If not, you'll have to edit the top of the .sql file to say something like use nameofyourdatabaseontargetserver; and delete the line which refers to the creation of the database.

A tiny bit daunting if you don't know much SQL and aren't familiar with the MySQL command line tools and unix shell tools, but if you do have any familiarity with those, it's easy enough.
posted by weston at 6:50 PM on October 17, 2005 [1 favorite]

What everyone above has said should work.

However, sometimes you run into a problem where a dump from one DB won't import into another because they had different values of --max-allowed-packet. When you create these dumps it's advantageous to use the "multiple insert" syntax so that you insert as many records with each command as possible. This leads to very long queries, however. If the server you're trying to import to has a smaller value of --max-allowed-packet compared to the version of mysqldump that created the file, it will error.

You also run into a similar problem if you're trying to use a PHP-based tool like phpMyAdmin to import a large dump. On shared hosts you sometimes run into problems with the query taking too long or the upload size being too large.

The solution to both of the above is this script.
posted by Rhomboid at 7:09 PM on October 17, 2005

Best answer: You don't have to use PHPMyAdmin. Just upload the SQL dump to your Dreamhost account using FTP, and log in using a shell account (if you don't have one, set one up).

Then just invoke the MySQL command line:

bam:~> mysql -u [user] -p -h [server] [database]

then at the prompt:

mysql> source ~/dump.sql

(Note: I have not actually tried this, so I don't know if there's a max filesize involved here.)
posted by neckro23 at 9:18 PM on October 17, 2005

Response by poster: Thanks all, I seem to have muddled through.
posted by o2b at 8:43 AM on October 18, 2005