Should I give up?
Perish the thought! ;-).
try:
mysqldump $database $table |mysql -uuser -ppass -hhost
This is the correct way to do it, but it's likely a little confusing to a newbie and won't work in all situations depending on the server configuration so I'm going to try elaborating a bit...
First you need to dump your current data from your local MySQL db. mysqldump is the tool for the job, or you can use PHPMyAdmin if you have it installed locally.
1) Dumping your data
Using mysqldump:
$ mysqldump -c -u username -p databasename > outputfilename
'-c' will dump the database structure and all data. This way the database tables, their structure, and the data will be inluded in the output file.
'-u' should be followed by the username that has access to your local MySQL database
'-p' causes mysqldump to prompt for the user's password
'databasename' is the name of the database being dumped
'> outputfilename' directs the output of mysqldump to the file 'outputfilename'.
Using PHPMyAdmin:
Log into PHPMyAdmin and navigate to the main page for the database in question (ie click on it's name in the left frame). In the main window there should be the option to 'View dump (schema) of database'. Select the 'Structure and data' radio button and the 'Complete inserts' check box, then click 'Go'. You should now see a 'dump' of your database in the main frame of PHPMyAdmin (this is the same data that is created using mysqldump). Copy and paste everything from this screen (except for the highlighted title at the top of the screen) into a text file.
2) Importing your data to the remote MySQL server
Using the command line:
In order to import the data via the command line you must either have shell access on the remote machine or the remote MySQL server must be configured to allow your local machine to access it.
From a shell on your local machine:
$ mysql -u username -p -h remotehostname databasename < outputfilename
'-h' is followed by the host name of the remote MySQL server, the other switches have the same meaning as before.
'< outputfilename' is the name (and path if necessary) to the file that was created using mysqldump (or PHPMyAdmin) previously. The less than sign means that we are reading from 'outputfilename', whereas earlier the greater than sign mean that we were writing to the file.
From a shell on the remote machine:
First you have to copy your outputfile from the earlier mysqldump (or PHPMyAdmin) step to the remote machine. scp or ftp should do the trick. Once you've got your outputfile on the remote machine:
$ mysql -u username -p databasename < outputfilename
Basically this is the same as when it is done remotely, except without the -h switch.
Using PHPMyAdmin:
Log into PHPMyAdmin and if it has not already been created, create the database (but not the tables or their structure). Click on the database's name in the left frame.
In the main frame find the 'Run SQL query/queries on database databasename window. Below it you will see 'or Location of the textfile:'. Click on the 'Browse' button next to the form field and navigate to the output file created with mysqldump or PHPMyAdmin in the first step. Click 'Go'.
3) Open beer. Consume. Repeat :-).
I hope this helps. If you're stuck feel free to email, just remove the spam stuff from my addy.
BTW, what the first person suggested would work as long as the remote MySQL db server is configured to allow connections from your remote machine.
- Ben