I had an issue recently where I need to download activity and feedback data from the remote server. The tables were getting quite large ~500MB so I needed way to truncate them and insert them to a local version. From the local host I can run all kind of analysis.
The way I did this is using a make file with the following commands.
From local command line use ssh to log into the remote server and MySQL dump to export the table. If you do not have SSH please find a tutorial and set that up.
Truncate the table using ssh and then mySQL
ssh [email protected] 'mysqldump -u USER -pXXXXX db_name tableName > tableName.sql' ssh [email protected] 'mysql -u USER -pXXXXX db_name -e "TRUNCATE TABLE tableName"'
- Next we send the table from remote to local using scp
scp [email protected]:/remoteOriginPath/tableName.sql ./destinationPath/
- Import the downloaded table to a database just for this kind of imported data
mysql -u localUser db_import < ./destinationPath/tableName.sql
- Next you can run any SQL operations to mutate the data, or execute a file from command line
mysql -u user db_import -e "UPDATE tableName SET value=value+example"
- The last step in the make recipe is to import the data to the existing table. This way you just add on the most recent updates
mysql -u user db_import -e "INSERT INTO localDB.tableName SELECT * FROM tableName"
And that should be it. Please ask questions below. What is your method? What is the better way to do this? Look forward to hearing your answers.