How to refresh a MySQL database on a remote server with SCP and SSH

Posted on: Sunday, Dec 17, 2017

Copying a project's code to its production server is easily done with Git--either manually with a Git pull or through a Git hook (usually using the post-receive hook). I needed a similar, hands-off process for refreshing the MySQL/MariaDB database on the server. This process needs to be effortless as possible and be able to copy the database to or from the server and local dev PC. The two Bash scripts below solve this challenge.

These scripts assume you have SSH keys in place for logging into the remote server without needing a password.

Automating MySQL signin

Before getting to the Bash scripts to copy the database, the issue of MySQL prompting for the password needs to be resolved. MySQL's .my.cnf user configuration file does this nicely--it's designed for exactly these kinds of purposes.

In the root of the user that will sign into MySQL, add the following file where userName is the user's name and userPassword is the user's password. You need a pair of these entries, one for mysql and one for mysqldump.

Caching the user and password in MySQL's .my.cnf
[mysql]
user=userName
password=userPassword

[mysqldump]
user=userName
password=userPassword

With the .my.cnf in place, issuing

mysql -u userName

signs you into MySQL without prompting for a password. It works the same way for mysqldump.

Set rights to .my.cnf

To protect the .my.cnf file from abuse, be sure to make it read-only for the user. This ensures no other account has access to this file. In your environment you may want to push this file to the server on an as-needed basis (with SCP as this article shows) and delete it after having used mysqldump to take a snapshot of the database).

chmod 600 .my.cnf

Be sure the .my.cnf file is deployed on both your local dev PC and the server.

Using SCP

Secure Copy (SCP) is a secure way to copy files, using the SSH protocol, between hosts on a network. It provides a great answer to the challenge of quickly copying a file across platforms. It deserves a top shelf in your Linux toolbox.

The syntax for copying a file with SCP is:

scp [fromfile] [tofile]

Files on the remote server are specified as

[username]@[remote compute name or IP address]:[filename]

for example:

scp  user@122.127.77.986:/var/www/do.sql  do.sql

copies the file /var/www/do.sql from the remote server to a file named do.sql in the current directory of the local PC.

and

scp  do.sql  user@122.127.77.986:/var/www/do.sql

copies the file do.sql in the current directory of the local PC to the file /var/www/do.sql on the remote server.

Copy DB from a remote server to the local dev PC
#!/usr/bin/env bash

# 1. Dump remote DB.
ssh user@205.127.59.386 'cd /var/www;mysqldump -u roger homestead > do.sql'

# 2. Copy it to current folder on local PC as do.sql.
scp user@205.127.59.386:/var/www/do.sql do.sql

# 3. Import it into local DB.
mysql -u user homestead < do.sql    

copy-from-server-db Bash script to copy database from host server. 

Step 3 above is optional. If all you want to do is snag a copy of the database locally, omit it. The use case for Step3 is to refresh a local MySQL (or MariaDB) instance with the contents of a product or test server's data.

Copy DB from the local dev PC to a remote server
#!/usr/bin/env bash

# 1. Dump local DB.
mysqldump -u roger homestead > do.sql

# 2. Copy it to /var/www/local.sql on Droplet.
scp local.sql roger@205.127.59.386:/var/www

# 3. Import it into remote DB.
ssh roger@205.127.59.386 "cd /var/www;mysql -u roger homestead < do.sql"

copy-to-server-db Bash script to copy database from host server.

This Bash script shows how you could restore the database on the remote server with a previously-created local.sql backup file.


Comments

  • cialis samples cialis pills online cialis 5mg price comparison


    Submitted by cialis samples cialis pills online cialis 5mg price comparison
    1 year ago
  • Cialis Generico Mejor Precio Arourn [url=https://bansocialism.com/]buying cialis online safe[/url] Smigue Levitra Italia Acquisto


    Submitted by KexAreree
    11 months ago
  • <a href=http://vskamagrav.com/>kamagra 100mg reputable site[/url]


    Submitted by KexAreree
    7 months ago



Add your comment
You email is never shared with anyone else.

© Copyright 2017 by Roger Pence. All rights reserved.