Transferring MySQL Database

I switched to Dreamhost and had wanted to transfer a MySQL database that was 12 gigs. I read this article:
http://wiki.dreamhost.com/index.php/Migrate_MySQL

but i didn't understand it. the import function on the administration page has a size cap of 7 gigs, and i'm not exactly sure on what to do to import it manually. right now, I have the database on my hard drive, and would like some guidance on how to upload or transfer the data onto the server. can anyone help?

This process is outlined in the K-base (somewhat). Here

Start by using a FTP program to upload a copy of your database dump. Just drop that file into he main directory that you see when you first log in. This is Behind the web-accessable area so it can't be downloaded by website visitors and saves a bit of extra typing.

Next you'll need to make sure that your user has shell access. By defult a user is set up as FTP only. Log into the Control Panel Click: Users> Users> Then click Edit across from the user name you want to log into Telnet with. Put a check next to "Enable ssh/telnet? " and click save changes. That user now has the access needed to log in.

Now, log into dreamhost via Telnet or SSH. Windows has a telnet program allready installed that will work Start> Programs > Accessories >Communication > Hyper Terminal. Or you can download a program called putty for a SSH connectio which is more secure.

To log in there, you'll need to know the machine you're hosted on here at dreamhost. You should have such information in your welcome E-mail I believe. For me it's Galage.dreamhost.com Then you log in with the user name / pass.

type in something to the effect of

mysqlimport -L -hHOSTNAME -uUSER -pPASSWORD DATABASE DUMPED_TEXTFILE

- OR -

mysql -hHOSTNAME -uUSER -pPASSWORD DATABASE < DUMPED_TEXTFILE

fill in hostname, user password, etc with the correct informaiion. Also please note that things are case sensitive.

-Matttail

I am following the same process that matttail outlined. However, I come across this error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Is the mysql server down?

You can find a great php script here:
http://www.ozerov.de/bigdump.php

It will import your DBs no matter of the size.

The problem is exporting...

can you connect to the database through phpmyadmin? If you can connect your database server should be up. If you can conect but are still getting that error at the command prompt then something isn't right with the command you are trying to pass.

-Matttail

i get the mysqld.sock error when using 'mysql'.

I was able to connect to phpmyadmin and do the import from there since the dump isn't very large.

If you are getting that error, then you missed the hostname option off and mysql is trying to connect to a server on the local machine.

Check you have the -h option set and there is no space between the h and the server name.

i entered the following:

[euro]$ mysql -hphpbboard.absurdtheater.org -uabsurd -p* absurd_board
< phpbb_db_backup.sql

and obtained the following error:

ERROR 1064 (00000) at line 121210: 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 '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
[euro]$ mysql -hphpbboard.absurdtheater.org -uabsurd -p(left out for obvious reasons) absurd_board
< phpbb_db_backup.s
bash: phpbb_db_backup.s: No such file or directory

how do i indicate what location the file is at? i have uploaded it to the root directory (behind the web accessible portion)

The file has to be in the location as where you are calling it from. That's the easiest thing to do. You might be able to use ~/domain.com/backup.sql . The ~ should take you to your home directory which is usually /home/username/.

I think the problem is that your sql file. It seems to be a html document rather than a sql file...
Can you open the file in notepad and paste the first few lines here for us?

#
# phpBB Backup Script
# Dump of tables for durik_absurdboard
#
# DATE : 24-05-2005 11:08:07 GMT
#
#
# TABLE: phpbb_auth_access
#
DROP TABLE IF EXISTS phpbb_auth_access;
CREATE TABLE phpbb_auth_access(
group_id mediumint(8) NOT NULL,
forum_id smallint(5) unsigned NOT NULL,
auth_view tinyint(1) NOT NULL,
auth_read tinyint(1) NOT NULL,
auth_post tinyint(1) NOT NULL,

That's fine....
have you tried using the paths I suggested? As log as you as in the same directory as the backup file when you use the mysql command, it should work fine.
Sorry I can't be of more help :(

i think it was because i created a "full backup" from my php bulletin board instead of a "data only" backup"... so i went ahead and got the data online backup, uploaded it, and ran the command again... only to get the following error:

ERROR 1062 (00000) at line 32: Duplicate entry '1' for key 1

what does this mean?

as for the file location, that wasn't even a problem. that error i posted was from another command i tried to put thru.

Hi, this was helpful for me. thanks. I have a question though about transferring to a database that isn't hooked up to a real domain yet.

for example I own foo.com which is currently a real site hosted elsewhere.

I now created foo.dreamhosters.com and transferred all of my files there.
I created a db called terry@foo.foo.com that my account has access to, but of course foo.com isn't hosted on dreamhost, so how do I specify this database to copy my sql data to?

thanks again for your help
-terry

Transferring MySQL Database