Make File to Export, Truncate, and Insert (Merge) SQL data from Remote Server to Local

Tutorials
1

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.

  1. 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.

  2. 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"'
  1. Next we send the table from remote to local using scp
scp [email protected]:/remoteOriginPath/tableName.sql ./destinationPath/
  1. Import the downloaded table to a database just for this kind of imported data
mysql -u localUser db_import < ./destinationPath/tableName.sql
  1. 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"
  1. 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. :slight_smile:

1 Like
2

I’d probably do it with shell script, something like that. :slight_smile:

I don’t know if it’s better, but if you’re using node now, you can also run scripts from your package.json file.

If what you’re doing now works, no need to change, but if you want to do it from package.json, you could put all your shell commands in a file like scripts/backup.sh (or whatever you want to name it). As you make new scripts, add them to the scripts directory.

Then in each file:

#!/bin/bash

echo 'hello world'
# put your commands here

The first line tells the computer what interpreter to use (bash).

Make the file executable so it can be run as a program:

$ chmod +x scripts/backup.sh

Then in the scripts section of package.json:

"scripts": {
    // the dot is required to run the script
    "backup": "./scripts/backup.sh",
    // other scripts can go here
}

To run the script, type npm run backup (or whatever you named it in package.json).

If you don’t add it to package.json you can manually run it by typing in the path to the file with a leading dot, and it will do the same thing:

$ ./scripts/backup.sh

I don’t know if there is any advantage to that way — just another option for running scripts if you’re using node. :slight_smile:

1 Like
3

Thanks Josh!

1 Like