Code, InfoSec and Server Stuff

Views are my own and not my employer's

Personal Development Blog...

Coding for fun since 1996, Learn by doing and sharing.

Buy a domain name, then create your own server (get $25 free credit)

"If you're not still learning, you're already dying."
- Ryan Holiday - Ego is the Enemy

Follow me on Twitter: @FearbySoftware

View All Posts.

Backup

How to backup and restore a MySQL database on Windows and Linux

by

Why backup and restore

This is a quick guide demonstrating how you can backup and restore a MySQL database on Windows and Linux using Adminer.

You may need to know how to backup a restore a database for a number of reasons..

e.g

  • Send the database to someone to debug or give feedback while learning.
  • Move the database from a local machine to the cloud
  • Move the database from cloud vendor A to cloud vendor B
  • etc.

Advertisement:



Having a backup of the VM is good but having a backup of the database too is better. I use UpCloud for hosting my VM’s and setting backups is easy. But I cannot download those backups.

UpCloud Backup Screen

Murphy’s Law

“If anything can go wrong, it will”

The most important reason for taking a backup and knowing how to restore it is for disaster recovery reasons.

Backup (the easiest way) with Adminer

Adminer is a free PHP based IDE for MySQL and other databases. Simply install Adminer and save the file on your local computer or remote web server directory.

FYI: The Adminer author Jakub Vrana has a patron page, I am a patron of this awesome software.

Snip from Adminers website. “Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consist of a single file ready to deploy to the target server. Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch andMongoDB.”

adminer.php file icon screenshot

TIP: The file would be publicly accessible to anyone so don’t save it to a common area, obfuscate the file, protect it of delete the file when you are done using it.

Advertisement:



Once Adminer is installed load it in a web browser, login with your MySQL credentials. Once you login you will see all databases and an Import and Export menu.

Adminer main screen, all databases and import and export menu.

tbtest is a simple database with one table and 4 fields (ID, Key, Value and Modified)

.Click Export to open the export screen.

Export screen showing a list of databases and export options

Click Export, a SQL file will be generated (this is the export of the database).

Here is a save of the file:
https://fearby.com/wp-content/uploads/export.txt

Exported view of https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html

Its that simple.

If I add a binary blob file to the table and upload a PNG file lets see how the export looks.

Screenshot o the new table with a blog field in Adminer UI

Advertisement:



Let export the database again in Adminer and check out the output. I used Sublime Text editor to view the export file.

New Export shows the binary file in the Backup SQL file

Restore (the easiest way) with Adminer

OK lets delete the tbtest database and then restore it with Adminer. I used Adminer to delete (DROP) the database.

Database dropped with Adminer

Database “dbtest” deleted.

Now lets create a blank database to restore to (same name).

Create database screen.

Database created.

dbtest created.

Advertisement:



Now lets import the database backup using Adminer.

Click Import, select the backup file and un-tick Stop on errors.

Import screenshot, dxtest selectded, Restore file selected, stop on errors disabled

TIP: The 2MB next the the choose file button is defined by your web server and PHP configuration. If you are trying to import a larger database (e.g 80MB) first increase the limits in your web server and PHP (via php.ini).

The Import (restore should take seconds)

Import Success

The database was imported from a backup, all tables and records imported just fine.

The database was imported from a backup

Bonus methods.

On Ubuntu use this guide to backup from the command line. If you use the Oracle MySQL Workbench read this.

I hope this helps someone.

Advertisement:



How to backup WordPress on a host that has CPanel

by

This is a simple guide that demonstrates how you can log in to a host that offers the CPanel tools to backup all of your website files (and databases). Backing up your website should be done often and especially before you migrate to any another website host. I used to change hosts every few years (they don’t own your site, you do).

Advertisement:



I have a number of guides on moving away from CPanel, setting up VM’s on UpCloud, AWS, Vultr or Digital Ocean along with installing and managing WordPress from the command line copying files to a server via command line editing remote files locally etc but how do you manage a website with CPanel?

You can normal login to CPanel tools on a shared host by loading www.yourdomainnam.com/cpanel (failing that login to your domain hosts web GUI and find your CPanel interface there).

Step 1: Login to your Host

Login to your web host

Login to Host

Step 2: Find your CPanel Interface

Hosts are a bit different but in this case, I just click my domain to find the CPanel link.

Domain

I found it, I clicked the CPanel login.

Cpanel link

Step 3: CPanel Applications

CPanel does offer good tools to manage your websites like web-based File Manager and Database tool called phpMyAdmin.

CPanel Options

Aside: CPanel/Hosts Downsides

The thing I don’t like about hosts that offer CPanel is they usually limit delivery of your website to extract more money. Nothing worse than receiving Resource Limit Is Reached errors.

cpenal_usage_exceeded

Also shared hosts usually lag way behind in newer software versions like PHP and MySQL (this is a security concern).

TIP: You can scan your site for vulnerabilities using Qualsys Freescan, Zap or Kali Linux.

Here is a security scan of a shared host (with CPanel) that I was using in 1999. Note the high vulnerabilities and old version of Linux.

Static Website

Also, a shared host will often overcharge you (e.g $150 a year) for a poorly configured SSL certificate.

This was an SSL cert I paid $150 a year for (evaluated with SSL Labs SSL Test) on a shared host with CPanel.

Aside: Self Managed Upsides

After I moved my domain to a self-managed virtual machine I migrated WordPress, set up a free SSL certificate, sped up my site with a CDN, setup Cloudflare, setup better TLS security etc

When you manage your own server you can install a free SSL certificate in under 1 minute.

Below is my SSL certificate. A strong SSL certificate will increase search engine traffic

SSL Cert

Aside: Compare Shared host speed v Self Managed

FYI: https://gtmetrix.com/ is a great site for measuring the speed of a website (shared of self-managed). I found great speed improvements after moving away from a host offering CPanel, tweaking the server and setting up cloudflare. A self-managed server will allow you to tweak anything you want.

GTMetrix results:

Shared Host Speed

I like how self-managed servers allow you to scale the server’s resources yourself, move servers or add storage etc.

Aside: SSL Certificate

If you have an SSL cert you should test it often as vulnerabilities pop up from time to time.

FYI: All sites will soon require an SSL certificate to be sent traffic from search engines (no SSL = lower traffic).

SSL Test my site: https://dev.ssllabs.com/ssltest/analyze.html?d=fearby.com&s=104.27.154.69

Now enough with the self-managed serve asides and back to how to backup your website with CPanel tools.

Step 4: Backup your web files in CPanel

Use the File Explorer app in CPanel

File Manager

Highlight all files that you want to backup (highlight everything but not past backup files).

backup-cpanel-006

View the files to compress summary

Compress Summary

Click Compress Files(s) and view the backup progress

Backup Progress

You can now download the backup zip file in your browser (click the file and click Download).

Downaload ZIp

Download Progress.

Download File

Step 5: Backup your database in CPanel

Now we need to backup any MySQL database(s) that may be used by WordPress

Open the phpMyAdmin app in CPanel.

phpMyAdmin

FYI: Alternatively, you can use a free tool called Adminer to backup and restore our database.

Click your WordPress database (on the left). You can identify your current WordPress database by opening the wp-config.php file.

Database Options

The first step is to perform an online cold backup of the WordPress database.

  1. Click the database you want to backup (on the left)
  2. Click the Options tab
  3. Under the Copy To frame enter a new database name to copy the database too
  4. Click Go

Online Backup

Now you have an online cold spare that you can use just in case the original database corrupts itself. You can rename the database or configure WordPress to point to this new database if need be.

backup-cpanel-013

Now let’s download a copy of the database (Repeat for multiple databases).

  1. Click the database you want to backup
  2. Click the Export tab
  3. Click Go

backup-cpanel-014

You should now have a backup of your website in a zip file and an export of your database in a .sql text file, SQL files can be re-imported to databases later.

TIP: Backup often.

Export Done

I hope this guide helps someone.

Ask a question or recommend an article

Your Name (required)

Your Email (required)

Your Question

Revision History

v1.0 Initial post

How to backup an Ubuntu VM in the cloud via crontab entries that trigger Bash Scripts, SSH, rsync and email backup alerts

by

Here is how I backup a number of Ubuntu servers with crontab entries, bash scripts and rsync and send backup email.

Advertisement:



Read more on useful terminal commands here for as low as $2.5 a month. Read on setting up a Digital Ocean Ubuntu server here for as low as $5 a month here ($10 free credit). Read more on setting up an AWS Ubuntu server here.

I have  6 numbered scripts in my scripts folder that handle backups, I call these scripts at set times via the crontab list.

fyi: Paths below have been changed for the purpose of this post (security).

https://crontab.guru/ is great for specifying times to run jobs on each server (I backup one server at 1 AM,, another at 2 AM etc (never at the same time))

Bring up your crontab list

Check out the Crontab schedule generator here.

Below is the contents of my /scripts/0.backupfiles.sh (sensitive information removed).

I use this script to backup folders and configuration data

Below is the contents of my /scripts-folder/1.backupdbs.sh (sensitive information removed).

I use this script to dump my MySQL database.

Below is the contents of my /scripts-folder/2.shrinkmysql.sh (sensitive information removed).

I use this script to tar my SQL dumps as these files can be quite big

Below is the contents of my /scripts-folder/3.addtobackuplog.sh (sensitive information removed).

This script is handy for dumping extra information.

Below is the contents of my /scripts-folder/4.syncfiles.sh (sensitive information removed).

This script is the workhorse routine that rsyncs files to the source to the backup server (a dedicated Vulr server with an A Name record attaching the server to my domain).

I installed sshpass to pass in the ssh user password (after ssh is connected (authorized_keys set), I tried to setup a rsync daemon but had no luck).  I ensured appropriate ports were opened on the source (OUT 22, 873) and backup server (IN 22 873).

ufw firewall has great rules for allowing certain IP’s to talk on ports.

Set Outbound firewall rules (to certain IP’s)

Change 123.123.123.123 to your backup server.

Set Inbound firewall rules (to certain IP’s)

Change 123.123.123.123 to your sending server.

You can and should setup rate limits on IP’s hitting certain ports.

Install Fail2Ban to automatically ban certain users. Fail2Ban reads log file that contains password failure report
and bans the corresponding IP addresses using firewall rules.  Read more on securing Ubuntu in the cloud here.

Below is the contents of my /scripts-folder/5.sendbackupemail.sh (sensitive information removed).

This script sends an email and attaches a zip file of all log files generated through the backup process.

Read my guide on setting up sendmail here.

Security Considerations

You should never store passwords in scripts that talk to SSH connections, create MySQL dumps or when talking to email servers, I will update this guide when I solving all of these cases.  Also, create the least access required for user accounts where possible.

Target Server Configuration

Alos you can see in /scripts-folder/4.syncfiles.sh that I am saving to the ‘/backup-folder/1.www.server01.com/’ folder, you can make as many folders as you want to make the most of the backup server.  I would advise you not use the server for anything else like web servers and apps as this server is holding important stuff.

I have a handy script to delete all backups (handy during testing).

Ensure your backup server is just for backups and only allows traffic from known IP’s

Change the 123.x.x.x servers to your servers IP’s

Tip: Keep an eye on the backups with tools like ncdu

Installing SSH on OSX

If you want to backup to this server with OSyouou will need to install sshpass

sshpass should be installed

I have not got sshp[ass working yet error “Host key verification failed.”  I had to remove the back known host from ~/.ssh/known_hosts” on OSX

But this worked on OSX

Note: Enter the servers sshuser@ before the hostname or rsync will use the logged in OSX username

Don’t forget to check the backup serves disk usage often.

disk usage screenshot

Output from backing up an incremental update (1x new folder)

Update with no files to upload

Backup is easy..

If you want incremental and full backups try Duplicity.

Hope this helps.

 

 

Donate and make this blog better




Ask a question or recommend an article

Your Name (required)

Your Email (required)

Your Question

v1.7 Duplicity

What is you backup plan

by

What would you do right now if the computer or device you were reading this on died? Do you have adequate backups and do you keep your most important files safe?

Advertisement:


I wrote an article back in 2013 about good backup plans and I used to spend 3 hours every month burning backup DVD’s and adding them to an indexed catalog. I did not trust Apple iCloud to backup my photos and have had my fair share of lost photos. I came up with a plan for managing and sorting photos from multiple digital devices that put me in charge of copying photos from devices, backing up high res photos and shrinking photos for local use.

Fast forward 3 years and I am now capturing insane amounts of digital photos and videos and I am no longer backing up every month due to the massive amounts of data to now what seems like the small capacity of DVD’s. I am instead throwing all of my new files into an “Incoming” folder to be processed at a later date. This was fine as I assumed the data would be safe.

Murphy’s Law: Anything that can go wrong will go wrong.

On a recent hot summers day, I was performing maintenance on my primary computer (removing dust from the processor fan) and one of the hard drives dropped 30 centimeters to the ground. I thought that all would be fine and the data would be safe as the hard drive was a good quality one. Nope, the drive is dead, the drive was corrupted and fails to show files. The drive spins up ok and does not sound like anything is broken inside. That drive had my last 3 years of photos on it.

How can I prevent this in future?

This happened because I failed to allocate time to sort and backup files and I assumed that Murphy’s Law would not apply to me.

My traditional golden rules of backup:

  1. Backup to Three different locations.
  2. Two of the media need to be different.
  3. One of the locations needs to be offsite.

Simon’s new rules of backup:

  1. Backup to Four different locations (g DropBox, Time Machine, Backblaze, Google Drive, NAS).
  2. Three of the backup media need to be different (g Cloud, DVD, NAS).
  3. Two of the locations need to be offsite (g Dropbox, Backblaze).
  4. One of the backups needs to be automated (e.g Dropbox, Backblaze, Time Capsule).
  5. Zero worries

Why so many backups you may ask? Each backup method is best for different scenarios. Dropbox is good for recently changing files, Cloud backups are good for everything and local backups are good for when cloud providers stuff up or to cover major infrastructure damage/failure overseas.

The ultimate local backup solution is a Drobo 5N 5 NAS from https://www.pccasegear.com/ . Simply insert hard drives (up to 5 3.5″/64TB) plug it into a router or switch and you are good to go. Read more here. Even my new NBN router has a slot for a UBS stick to be inserted (see on the right below) allowing me to create a local WiFi file share.

Router

Simple Cloud Backup Options:

Dropbox is the simplest and safest method of syncing and storing files offsite. You can store 2GB of files for free or 1TB (10,000 GB) for $11.58 AUD a month). Dropbox also works on mobile and tablet device too so it is a no-brainer. Encryption options are not baked in so it is not as secure. Install Dropbox at home and work and share files between locations via Dropbox.

Use this link ( https://db.tt/iZmBvErC ) to signup at Dropbox and get a bonus 500MB (2,500MB total) for free.

Backblaze.com was my favorite cloud storage provider for Windows of Mac that offers encrypted backups. Use this link to get a month free https://secure.backblaze.com/r/017nsm. It costs $5 a month and there are virtually no limits on the data you can store. My laptop has 90GB of data stored in Backblaze and it is all encrypted. Back blaze care about backup so much they have open sourced their backup enclosure designs: https://www.backblaze.com/blog/backblaze-storage-pod-4/

Use this link to get a month’s free cloud backup to give it a try: https://secure.backblaze.com/r/017nsm

Advanced Cloud Backup:

If you do not trust cloud providers to store your data and you want to have offsite backups on the internet you can buy your own server online and manage your own files encryption and sync methods.

Digital Ocean allows you to create and store your own files in the cloud from $5 a month (but you seed to set it up). Use this link and get a free server for 1 x month with 20GB storage. You can also create a 50GB cloud repository using a Vultr server. I even back up my servers to another server automatically.

Manual Backup

Performing a manual backup by plugging in a removable hard drive feels good but chances are you will not do it often enough. Most of the time simply highlighting all files and copy paste it to the new drive fails to be effective because files are often in use or access is denied to them by the operating system. Manually copying all files every month talks time and copying many smaller files over and over again will increase the wear on your drive and fragment your drive.

If you are working on important documents then do save as to a new filename frequently (and copy those to offsite destinations), never trust computers.

Manual backups are ok but they are only as good as the data you copy and the frequency you do it.

Network Attached Storage (NAS)

The main downside of cloud backup is it can take weeks if not months to get a full backup into the cloud. That’s where NAS (Network Attached Storage) comes into save the day.

A NAS is a mini computer that sits on your network (wired or wireless) and allows you to store files quickly with or without redundancy.

The ultimate local backup solution is a Drobo 5N 5 NAS from https://www.pccasegear.com/ . Simply insert hard drives (up to 5 3.5″/64TB) plug it into a router or switch and you are good to go. Read more here.

You can choose a NAS with different drive capacities and you can allocate more space to redundancy (referred to as RAID read about raid here https://www.cloudwards.net/what-is-raid/ ). You would want to at least mirror (duplicate) data (RAID 1) so if one drive fails you have a copy of files on the other disk.

Windows Advice:

Windows 7 had a nice Backup feature built in but Microsoft has changed that in Windows 8.1 and 10

https://support.microsoft.com/en-au/help/17143/windows-10-back-up-your-files

I feel Windows approach to automatically create backups of files on the C drive is a bad idea and copies of hundreds of thousands of files only increased the space ( http://www.apple.com/au/shop/product/ME182X/A/airport-time-capsule-3tb?fnode=5f ) used and wears out the drive.

Apple Mac Advice:

Apple OSX comes with a handy feature called Time Machine, if you plug in a large capacity drive OSX will ask if it can use the drive to store backups of your computer. Time Machine does a great job of keeping multiple versions of files and automatically phase out older versions when space on the backup drive gets low. The only catch is you may need to plug in your backup drive every few days/weeks to let Time Capsule do its thing.

Apple sells a wireless Time Capsule for those that do not want to plug in a drive to backup. Time capsules can be a bit tricky to setup if your home or office has a wireless network already but once it is setup it is rock solid.
http://www.apple.com/au/shop/product/ME182X/A/airport-time-capsule-3tb?fnode=5f

Technical Bonus:

Don’t assume new storage devices are more reliable if anything the cut-throat nature of the PC industry is driving down reliability and quality. If someone is telling you otherwise they are lying or are trying to sell you something.

Most larger capacity drives (over 1TB) store data on spinning metal platters and the quality and reliability varies between brand and models. Backblaze has a great write-up on hard drive reliability (based on experience of tens of thousands of drives): https://www.backblaze.com/blog/hard-drive-failure-rates-q3-2017/

Ever increasing “data capacity” of devices is delivering devices that are more fragile and ones that dedicate less physical space to actually store every bit of data on your computer. Most Solid State drives store data in shared cells ( MLC ) and data is more prone to being corrupted due to manufacturers increasing consumers risk to stay profitable.

My recovery tool of choice for any dead or deleted SD card, USB key or physical drive is http://www.diydatarecovery.nl as it ignores the file application table and looks at the sectors and tried to build files back from there. The only catch is recovery scans take days and you need a new/empty drive that is bigger than the drive you are scanning to restore files to. Here is my mini review os DIY DataRecovery.

So by now, you have:

  • Signed up for 2GB free Dropbox using this link to get a bonus 500MB
  • Signed up for a free Backblaze account to get a free months trial using this link
  • Reviewed your local backup options?

Don’t forget to use good filenames

The World’s Best File Naming System (YouTube)

Good luck and happy backing up.

Restoring Files

If you have backup often up then all will be well when disaster strikes, if not you may need to use Restore Software.

Donate and make this blog better




Ask a question or recommend an article

Your Name (required)

Your Email (required)

Your Question

v1.3 File naming…