Uploading MySQL database from my computer to server

[ignatz]

Can anybody post (or direct me to a post) directions on uploading my MySQL DB from my computer to the server. I'm using MacOS X Jaguar on my computer (if that makes any difference). The server I will be uploading to is linux.

I would love to have GUI to do this job, but if anybody knows of a good site with really clear directions for doing it through the terminal it would be most appreciated.

Thank you kindly (in advance),

ignatz

[slur]

The best reference for this is at MySQL.com. Read about the "mysqldump" and "mysqlhotcopy" commands. Typically you'd use mysqldump to export the database to a text file, then use rcp, scp, or ftp to transfer the file to the other server, and then use the "mysql" command with input redirection to import the text file into the database on the destination server.

The approach I describe requires some understanding of shell scripting and how to set up automatic authentication via ftp, rcp, or scp (ssh), so you ought to read up on all that too.

On the other hand there may already be some pre-built solutions for doing just what you want, and I'd expect to find references for such stuff at the MySQL site, and probably at SourceForge.

[ignatz]

thanks slur! I'm still having a hard time with php/mysql, but your tip helped.

thanks!

ignatz

[podmate]

A fairly easy way to do it is to use mysqldump (as slur suggested).

Here is example code you can use from the terminal commandline:
mysqldump --opt -p DATABASENAME > filepath/DATABASENAME.sql

--opt adds the most commonly used mysqldump flags
-p requires you to use the password for DATABASENAME

move the file created above to your server.

To read the dumped file into the database on your server is simple, but can cause you loads of problems.

Here is the most common problem (in my experience). The information in the database is OVERWRITTEN by the data being loaded into the database by the mysqldump when using the code below.

from the terminal command line:
cat filepath/DATABASENAME.sql | mysql -p -uroot DATABASENAME

-uroot is telling mysql that you are using a different user called root
you could say -ufred if you wanted to use user freds password. the whole -u thing is optional.
-p requires you to use the password for the database or the password for the specified user.


I looked around for some GUI things to do this for the users that I support. Haven't found one yet.

Good luck.

[coolgrafix]

If phpMyAdmin is installed on the Linux machine then your chore is ridiculously simple, as you can simply send your mySQL dump text file to the server thru the phpMyAdmin web interface and it will create the database for you. Haven't done this in a while, but unless I'm delerious, it's that simple... provided phpMyAdmin is available. =)