LINUX PASS-THROUGH AUTHENTICATION (SSH KEY GENERATION) FOR UBUNTU SIMPLIFIED AND CLARIFIED

If you’ve ever struggled with setting up pass-through authentication using generated SSH keys, this article is for you.

Using SCP (secure copy) on a linux box ensures that your files will be transferred in an encrypted state, unlike FTP which sends data in clear text.  SCP file transfers are very useful when scripting automated processes like database backups – the backup can be run, compressed, and then immediately shipped over to a different server for storage.  The problem with scripting this connection between two servers is the fact that a password is required (hopefully!) on the remote server, and you don’t want your script to halt, waiting for a user to type in the password.  So, SCP pass-though authentication can be a solution.  The steps to setting it up go like this:

1. Confirm that an identical user exists on both servers, using the same password.
2. On the server SENDING the files using SCP, run the following to generate the key pair:  ssh-keygen -t rsa
3. You’ll see the following:
   Enter file in which to save the key (/home/seth/.ssh/id_rsa):

   Just hit enter to save in the default directory.  Next you will see:

   Enter passphrase (empty for no passphrase):
   It is recommended that you enter a passphrase for additional security, but not required.
4. After adding a passphrase (or skipping it) you may see a randomart image for the generated key, along with notification of the paths that your public and private key pair has been saved in.
5. Now for the important parts.  Copy the public key file (id_rsa.pub in the .ssh directory for Ubuntu) into the remote server’s .ssh directory (again, for Ubuntu).  You can use SCP to transfer the file.
6. RENAME the public key file to authorized_keys (or append the contents of the public key file to authorized_keys if it already exists.  DO NOT copy and paste the text of the public key to the authorized_keys file. I have done this and wound up with invisible characters or something that wouldn’t allow pass-through authentication to work).
7. That’s it.  No need to log off, the pass-through authentication should be working immediately.  Try a test by sending a file like this:  scp testfile.txt seths_server:/home/seth/.  You should see SCP doing its thing without asking for a password.

Whenever I’ve attempted to set this up in the past, I’ve always tried copying and pasting the public key, rather than appending it or renaming the file, and the result didn’t work.  If you have an existing authorized_keys file, use cat to append the new key to any existing contents in the file:  cat id_rsa.pub >> ~/.ssh/authorized_keys.

ADMINISTERING WITH GOOGLE DOCS 'SNIPPETS'

While administering any system, be it MySQL, SQL Server, or web servers, I use some commands and procedures infrequently enough to forget the syntax. So, I use Google Docs to store my commands and statements – ‘snippets’ of code. This way, I have my tools immediately available wherever I have Internet access.  Note:  Google just implemented the capability of uploading ANY file, not just docs and spreadsheets.  http://docs.google.com

USING MYSQLDUMP

I’m diving headfirst into MySQL administration lately, and one of the first tools I’ve learned to use frequently is mysqldump, and is it powerful! Mysqldump makes copying, backing up, and restoring databases and/or tables easier than any available tool in SQL Server. I’m going to focus on some database uses for this post, and save the table operations for a later date.

BACKUPS & RESTORES
First, a simple backup of a database named ‘apptsol’ (from a linux command prompt):

mysqldump -uroot -pmypassword apptsol  > ./backups/apptsol_backup.sql

The apptsol_backup.sql file that’s created is a text file of actual MySQL statements! Drop table, create table, etc.

Now, a super-fast restore using the mysql command:

mysql -uroot -pmypassword apptsol < ./backups/apptsol_backup.sql

Checking the restored database reveals that I lost all of my stored procedures! To prevent this, use the –routines parameter in the mysqldump command to include the procs:

mysqldump -uroot -pmypassword –routines apptsol > ./backups/apptsol_backup.sql

COPYING DATABASES
I’m using MySQL replication on my servers, and periodically I want to manually reconfigure replication, as sometimes the servers get out of sync. I need to replace the slave’s copy of the apptsol database with the master’s copy. I could make a backup, tarball, gzip and scp it over to the slave, then restore it to the slave. Instead, I use a nice fast copy operation using mysqldump:

mysqldump –routines -umasterroot -pmasterpass apptsol | mysql -C -uslaveroot -pslavepass apptsol

SHAZZAM!  The database has been copied, with all stored procedures and triggers.

SQL SERVER DUPLICATES

I’ve recently had an article published on SQLServerCentral.com on the subject of removing duplicates.  Check it out here!

SQL SERVER 2008 COMPREHENSIVE REFRESHER BY PINAL DAVE

In the overwhelming quest to become a knowledgeable SQL Server DBA, you may feel that there is so much to learn that you are collecting knowledge at breakneck speed in a jumbled fashion. I have experienced that, and have also had the sense that I am still lacking much in fundamental areas. In times like those, it’s nice to find solace in a structured overview of the essentials. I’d like to point anyone interested to Pinal Dave’s SQLAuthority.com blog, and specifically to his series of SQL Server 2008 Interview Questions and Answers posts here. In a clear and systematic style, he runs through a comprehensive list of key SQL Server fundamentals that will add to your aggregation of SQL Server smarts. Hopefully, much of it will be a refresher, or, if you are a beginner, set you on course to obtain a solid foundation. Enjoy!