MySQL in SSH: Basic Guide

There are some hosting companies that do not offer an SSL (Secure Socket Layer) environment for MySQL. It is important to have an SSL environment for MySQL, because your sessions are protected with “encryption.” Fortunately, if you can’t get SSL with your hosting company, there is an alternative: SSH.

The Secure Socket Layer security feature protects sensitive information (such as credit card information, passwords, SS numbers, etc.) from falling into the hands of an unauthorized third party that might be sniffing the packets as it travels the Internet.

Below are some standards that most MySQL servers using phpMyAdmin should offer to their hosting customers:

1. An SSL-protected phpMyAdmin login page. Not all hosting companies can offer this. If you are entering your MySQL username and passwords into a non-SSL supported phpMyAdmin page, then this sensitive information will travel through the Internet in “clear text format.” If someone sniffs your traffic/packets, your MySQL login information could be compromised.

Make sure that you see https:// in the address bar of your browser when you are about to log into your MySQL database using phpMyAdmin.

2. SSL protected sessions. Once you are logged in to your MySQL database, you can then start working on it to create database tables, entering database records. It is also important that these sessions be encrypted, because you might be dealing with sensitive information.

What if your web hosting company fails to deliver SSL protected MySQL sessions and log-in? This is where you can use SSH in MySQL. This tutorial will teach you how you can work with your MySQL database in an SSH environment.

SSH (Secure Shell) – Alternative to SSL

SSH is an encrypted protocol much like SSL. This is one of the standard ways of connecting and encrypting communications to a remote server. The remote server can be your FTP hosting server or MySQL server.

However, SSH is commonly used by webmasters to connect and transfer files between your FTP server “encrypted.” One rare application is to use SSH when connecting and engaging in sessions with your MySQL server.

This means that you can engage in a variety of tasks in a SSH environment that offers encryption. These include:

  • Connecting to your MySQL server
  • Creating a database
  • Creating a table
  • Inserting records
  • Selecting, updating and deleting records

{mospagebreak title=Connecting to MySQL Server using the Putty SSH Client}

Let’s start with the very basic: connecting to your MySQL server using SSH. When you connect using SSH, you need a client. One of the most recommended SSH client is Putty

This works for Windows. If you use Linux, Putty will run using the same set of SSH commands, but with a different installer (using its Linux version).

It is very important that you make sure you have SSH enabled for your website hosting. If you do not, then you will never be able to communicate with your MySQL server in SSH.

To connect to your MySQL server:

Step 1: Launch Putty.

Step 2: In the Putty Configuration, you will need to basically enter the following required information:

Host Name: {hostname of your FTP server, NOT MySQL server}

Port: {the port required to connect to SSH; some hosting companies may NOT use the default port 22, so you need to check with them}.

Connection Type: SSH

Example:

Step 3: Once all are set, click “Open.”

Step 4: You are then required to enter your SSH user name (this is still NOT your MySQL user name). You can get your SSH access from your web host.

Step 5: Once you have entered your user name, Putty will ask you to enter your password, which you will need to type blindly.

Step 6: Your connection with your hosting SSH server is successful if you do not see password warning errors, and you do see the SSH prompt (example):

-bash-2.06b$

This prompt is different on other web hosting companies offering SSH, but it should end with $ sign.

Step 7: In the prompt, you enter you MySQL login credentials. For example, if you have the following fictitious login:

MySQL hostname: pdb3.alienwebhosting.com

MySQL username: codexm

MySQL password: interstellarguy

You will enter it in the prompt as:

-bash-2.06b$ mysql -hpdb3.alienwebhosting.com -ucodexm -pinterstellarguy

The red bolded words in the syntax are the MySQL login details. The syntax for the MySQL command line login is:

-bash-2.06b$ mysql -hHOSTNAME -uUSERNAME -pPASSWORD

Step 8. If you see the MySQL command prompt after entering the login details in Step 7:

mysql>

Then you have successfully logged in and connected to your MySQL server using SSH.

{mospagebreak title=Basic MySQL Database and Table Commands in SSH}

After logging in,  below you will find the important commands that you should know (bolded words are the exact commands, while the part that is not bolded is the MySQL prompt):

1. Showing all MySQL databases in your server

mysql> show databases;

2. Access the database and use it:

mysql> use databasename;

For example, if your MySQL database name is moviedatabase then:

mysql> use moviedatabase;

3. Showing all the MySQL tables for the specified database name:

mysql> show tables from databasename;

4. Show all of the records from a specified table

mysql> select * from databasetablename;

To view the entire records of a specified table, you should ensure that:

  • You have selected the database to work using USE command.
  • You know the exact database table name; if not, execute the “SHOW TABLES from databasename;” command to get the list of tables.

5. Show all of the database table field names, type and configuration of a certain table:

mysql> describe databasetablename;

Before you try to view database table specifics like its configuration, make sure that you have selected a database using the USE command.

6. Exiting the MySQL server

mysql> exit

{mospagebreak title=Creating a New MySQL Table and Configuration}

Aside from manipulating a MySQL database using the basic commands outlined earlier, you can also create new MySQL tables and their associated fields in an SSH environment. For example, suppose you are told to create the following MySQL database table in an existing database codex:

Database name: codex

Database table name: codexmovies

Database field names:

  •  movieid ? type: int(10), Unsigned, NOT NULL, Auto-Increment
  • movietitle ? type: varchar(100) NOT NULL default ‘ ‘
  • moviegenre ? type: varchar(100) NOT NULL default ‘ ‘

It’s important to note that some hosting companies do not allow the creation of a new database in an SSH environment; they let their customers create new databases using the hosting control panel. So you’d better check first with your hosting company.

Step 1: Connect to your MySQL server using the Putty SSH client (refer to the previously discussed section).

Step 2: At the MySQL prompt, select the database to work with (name of database is codex):

mysql> use codex;

Step 3: Create the table, its fields and configuration in one command line:

mysql> CREATE TABLE codexmovies (movieid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,movietitle varchar(100) NOT NULL default ”,moviegenre varchar(100) NOT NULL default ”, PRIMARY KEY (movieid)) TYPE=MyISAM;

Below is the link to the screen shot of the actual MySQL query done in SSH:

http://www.php-developer.org/using_ssh_to_create_mysql_table.jpg

Discussion of the design elements of the MySQL database is beyond the scope of this article. It is suggested that you refer to the following materials if you need some references:

http://www.devshed.com/c/a/MySQL/Designing-a-MySQL-Database-Tips-and-Techniques/  

http://dev.mysql.com/doc/refman/5.0/en/data-types.html  

You have successfully created the MySQL database table codexmovies.

Likewise, you can view the structure of this table (showing the field types) using the following command:

mysql> describe codexmovies;

Inserting Records into a MySQL Table

Suppose you need to insert the following records into the table “codexmovies:”

movie title1: Iron Man 2

movie genre1: Action

movie title2: Sherlock Holmes

movie genre: detective

movie title3: Milk

movie genre: drama

mysql> INSERT INTO codexmovies (movietitle,moviegenre) VALUES (‘Iron Man 2′,’Action’);

mysql> INSERT INTO codexmovies (movietitle,moviegenre) VALUES (‘Sherlock Holmes’,’detective’);

mysql> INSERT INTO codexmovies (movietitle,moviegenre) VALUES (‘Milk’,’drama’);

Okay, now you need to view all of the codexmovies records in SSH:

mysql> select * from codexmovies;

You can even execute a more advanced MySQL queries like UPDATE, DELETE, etc. and other MySQL commands that are needed for complex database administration.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye