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
[mysql] user=userName password=userPassword [mysqldump] user=userName password=userPassword
.my.cnf in place, issuing
mysql -u userName
signs you into MySQL without prompting for a password. It works the same way for
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.
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]
scp firstname.lastname@example.org:/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.
scp do.sql email@example.com:/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 firstname.lastname@example.org 'cd /var/www;mysqldump -u roger homestead > do.sql' # 2. Copy it to current folder on local PC as do.sql. scp email@example.com:/var/www/do.sql do.sql # 3. Import it into local DB. mysql -u user homestead < do.sql
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 firstname.lastname@example.org:/var/www # 3. Import it into remote DB. ssh email@example.com "cd /var/www;mysql -u roger homestead < do.sql"
This Bash script shows how you could restore the database on the remote server with a previously-created
local.sql backup file.
cialis samples cialis pills online cialis 5mg price comparison
Submitted by cialis samples cialis pills online cialis 5mg price comparison1 year ago
Cialis Generico Mejor Precio Arourn [url=https://bansocialism.com/]buying cialis online safe[/url] Smigue Levitra Italia Acquisto
Submitted by KexAreree11 months ago
<a href=http://vskamagrav.com/>kamagra 100mg reputable site[/url]
Submitted by KexAreree7 months ago