Nick Bartlett

Download the remote file
scp my_username_on_remote_server@remote_server_ip_address:fullPathToFileOnRemoteServer pathToPutFileOnLocalMachine

If the file was zipped, then we’ll need to unzip it locally
gunzip -f pathToFileOnLocalMachine

Then load the data into the database
mysql -u root -p -h localhost databaseName < pathToUnzippedFileOnLocalMachine

Get into an infinite loop or query that’s taking longer than your patience will allow? Using mysql’s “kill” command will be helpful in this case. First log into mysql and view the process list “select * from information_schema.processlist;” . View the list of processes and identify the process id number of the process you want to stop, then run “kill (process id);”. Be careful when stopping queries other than selects because you may run into some data corruption.

I wrote a query similar to the one below that worked in my development environment using MySQL, but failed on other environments.
SELECT customer_id, sum(rating) FROM customer

The reason it failed is because for proper SQL, columns not included in an aggregate function like “SUM” need to be specified in the group by clause. However, more recent versions of MySQL will handle this for you automatically. If the version of MySQL differs between your environments as mine did, you might run into an issue.

The solution is to write the sql query properly as below and edit your MySQL’s my.cnf file by adding the line sql_mode = ONLY_FULL_GROUP_BY .
SELECT customer_id, sum(rating) FROM customer group by customer_id