Archive

Posts Tagged ‘mysql’

MySQL Workbench SSH Connection on Joyent Server

April 7, 2012 Leave a comment

Here is how I was able to setup MySQL Workbench to manage the MySQL databases on my Joyent SmartOS web server.

In my opinion, the most confusing part I had to understand was creating a MySQL user that connects “locally”. By locally, I mean the user that is created is connecting from the server itself. This is in contrast to what I was expecting which was creating a user that connects from a remote location (work or home).

What you will need to know:
Public IP Address – Your public IP Address was assigned to you by Joyent when you provisioned your machine. This can be found in your Joyent Cloud administration located under Machines tab. I will use 64.65.66.100 in this example.

SSH Hostname – This will be your public IP Address combined with port #22. Port 22 is typically the SSH port. In this example, the hostname is 64.65.66.100:22.

SSH Username and Password – The username/password of the account you will use to SSH into your Joyent machine. For example, I will use the admin account which has some elevated privileges.

MySQL Hostname – Enter your public IP address here. I am assuming that MySQL server is installed on the same server.

MySQL Server Port – Typically this is 3306. You can validate this by checking the configuration file. See below.

Username & Password – Enter the username and password of the MySQL user here. Since we are using SSH to securely connect to the server, the MySQL user is going to be a local user. Again this is local to the Server, not to the remote location you are connecting from. See below. In this example, the username is ‘yourUserName@abc123.local’.

Check which port number MySQL Server is using:
SSH into your server and view your MySQL configuration file. If you aren’t sure where it is located – use the find command.

#Use the find command to locate my.cnf
sudo find / -name my.cnf 2>/dev/null

#Open the configuration file to find the port number.
sudo vi /etc/my.cnf

You will see the port number in two locations. Honestly, I am not sure the difference but I suspect it relates to the client and mysql daemon. You should see: port = 3306 in either case. At this time, I am not interested in researching it.

Create a “local” MySQL user:
At your terminal window prompt, connect to MySQL server to create a new user.

#First find the local hostname (in this example is abc123.local). Type
#the hostname command at terminal prompt. The output is used in your
#MySQL username.

$ hostname
$ abc123.local
$ _

#Connect to MySQL Server
$ mysql -u root -p

#Create a user
mysql> create user ‘yourUserName’@’abc123.local’ identified by ‘password’;

#Grant Privileges
mysql> grant all privileges on *.* to ‘yourUsername’@’abc123.local’ identified by ‘password’;

Set your bind address:
You will need to set the bind address to the public IP address. I’ve read on the web that there should not be a bind address of 127.0.0.1. I don’t understand why. So I have changed it to the public IP address.

#Open the configuration file for editing.
sudo vi /etc/my.cnf

#Change the bind-address value to the public IP Address. Save and quit.
bind-address = 64.65.66.100

In MySQL Workbench, Create a new Server Instance Profile:
1. In MySQL Workbench, click the “New Server Instance” link to create a connection to your server.

2. Choose “Remote Host” and enter the Joyent public IP address. Click next.

3. Fill out the connection details below.

Connection Name: Change to a meaningful name if you like. Ex: “My Connection”
Connection Method: Choose Standard TCP/IP over SSH
SSH Hostname: Set to public IP address
SSH Username: Enter username of your SSH account. I used admin in this example.
SSH Password: Store the password of the user in the vault.
SSH Keyfile: I kept blank.
MySQL Hostname: Set to the public IP address
Username: Set to the username you created. Use only the username (without hostname appended). For example, if the MySQL user is “yourUserName@abc123.local”, use “yourUserName” here. In the photo below I used “you@abc123.local” which is the same idea.
Password: Store the password in vault. In this example, I set the password to “password”. See SQL scripts.
Default Schema: I kept blank.

4. Proceed through rest of the Setup wizard…

Hopefully you should be ready to start querying the database. You can do so by double clicking the Connection located under SQL Development on the Home Tab.

Advertisements
Categories: MySQL Tags: ,

mySQL Remote Access in my LAN

January 31, 2012 Leave a comment

Example of Shared Local Network

My steps taken to allow remote access to mySQL server running on Shared Local Network. For example, allowing your java application to connect to mysql server requires permission for remote access.

Sample Connection String for mySQL Server Database Connection:

  try
  {
    String myConnString = "jdbc:mysql://192.168.1.8:3306" +
                          "/myDatabaseName?user=myUserName" +
                          &password=myPassword";

    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection(myConnString);

    //Print to console a message for testing.
    System.out.println(DatabaseConnection.class.getName() +
                       " > Opened connection successfully.");
  }
  catch (ClassNotFoundException | SQLException ex)
  {
    System.out.println(DatabaseConnection.class.getName() +
                       " > FAILED DB CONNECTION.");
    //Log Error
    Logger.getLogger(DatabaseConnection.class.getName()).log(
                     Level.SEVERE, null, ex);
  }

Example error message when the user doesnt have permission to connect to the specified database.

java.sql.SQLException: Access denied for user ‘myUserName’@’192.168.1.7’ (using password: YES)

To allow for remote database access:

1. Log into mySQL server, enter password at prompt.

mysql -u root -p

2. Create new user to connect to mySQL Server. Note the single quotes around both username and ip address. The at symbol is not enclosed in quotes. The IP Address should be the ip address of the computer where the user will connect. The reserved keyword “identified by” sets the password for the user.

create user ‘myUserName’@’192.168.1.7’ identified by ‘myPassword’;

In a local network, you might use the wildcard % in the ip address to allow for any computers within the range to connect.

create user ‘myUserName’@’192.168.1.%’ identified by ‘myPassword’;

3. Grant permissions to manage database. Note the IP Range wild card used here to apply to all. Now user can perform the basic database operations.

GRANT SELECT, INSERT, UPDATE, DELETE ON myDatabaseName.* TO ‘myUserName’@’192.168.1.%’;

Reference ebook: MySQL Enterprise Solutions, by Pachev, Alexander Sasha, 2003, ISBN: 0-471-26922-0 , Pages: 85, 86

Install and setup MySQL, a few notes.

January 28, 2012 Leave a comment

To install mysql server on ubuntu server (command line):

Here are a few notes about setting up mysql server. I set it up for local use. These are my personal notes in the event i need to repeat the steps. I hope you find it beneficial.

Conventions used:
commands to run are in green
output from commands are in burnt orange
comments

Install MySQL Server

1. at terminal prompt enter the command:

sudo apt-get install mysql-server

…install will proceed. enter a password for mysql “root” user.

2. Test the install is successful and verify mysql server is running by typing the command:

sudo netstat -tap | grep mysql

gregg@server:~$ sudo netstat -tap | grep mysql
[sudo] password for gregg:
tcp 0 0 localhost:mysql *:* LISTEN 1776/mysqld
gregg@server:~$

When running successfully, the output is similar to above. Note if netstat isnt installed – install it (see bottom of page).

Managing Databases in MySQL

At this point MySql server is now installed and ready for use. To manipulate MySQL system, use the client tool provided which is called “mysql”. This tool can be found in the /bin directory of an installation.

You can execute this tool by entering mysql at the terminal window or command prompt. This tool connects to the local installation and returns the welcome message.

1. Connect to MySQL

To connect to mysql, use the command below to prompt for password and login as mysql root user – which was setup during the install.

mysql -u root -p

A successful login will show the welcome message:

gregg@server:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Note that if the error 1045 is encountered, the password is required. To request that mysql prompt for the password – type the command as shown above. mysql -u <username> -p

-u: requires the username.

-p: tells the mysql client tool to display the prompt to enter password.

gregg@server:~$ mysql
ERROR 1045 (28000): Access denied for user ‘gregg’@’localhost’ (using password: NO)

2. View currently installed databases
Remember to terminate commands with a semi colon.
show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql>

Above shows the two default databases that are installed. Do not use either of these for personal use. Create new database for business applications.

3. Create a user defined database

Lets create a new database called items. Consider using camelback notation for naming databases.  Keep database name short and meaningful. But if two or more words, set first word to lowercase, capitalize each other word.

For example, creating a database for content management system maybe named: contentManagement; although cms might be shorter, but isnt as meaningful.

Lets create a database. Type the command at the mysql command prompt:

create database products;

The output should resemble the following:

mysql> create database products;
Query OK, 1 row affected (0.0 sec)
mysql>

Lets view the database list again. This shows the products database was successfully created:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| products |
+--------------------+
3 rows in set (0.00 sec)

Add a Table to our MySQL database.

Remember that UNIX is case sensitive. Type database names exactly as they are named. This is where camelback naming convention comes in handy. This naming convention adds structure to database and table names.

To begin adding tables, connect to the products database by using the “use” command. This command puts the focus of our client tool “mysql” to the products database. At the mysql command prompt type: use products;

use products;

The output should be as following:

mysql>use products;
Database changed
mysql>

Again the use command applies all commands entered into mysql client to the database we are using.

Note there are several types of tables that can be created. I am not a DBA by any stretch of the imagination. Understand I have no idea what the benefits are of each type of table. For now I choose to use non transactional table type (the default for mysql).

I will create 3 fields.

– itemID (an int that is the primary key)
– itemName (a string with 30 character limit)
– itemDescription (a string with 100 character limit)

The sql command looks like this:
create table items (itemID int primary key, itemName varchar(30), itemDescription varchar(100));

Running the sql command results in output below:

mysql> create table items (itemID int primary key, itemName varchar(30), itemDescription varchar(100));
Query OK, 0 rows affected (0.03 sec)

Note: use auto_increment to automatically increment the ID field. For example, create table items (itemID int auto_increment primary key, itemName varchar(30), itemDescription vharchar(100));

View tables in our MySQL database

Now that a table has been added, lets view it (and any others that might exist). Run the command show tables at the mysql command prompt.

show tables;

Running the sql command results in output below:

mysql> show tables;
+--------------------+
| Tables_in_products |
+--------------------+
| items              |
+--------------------+
1 row in set (0.00 sec)
mysql>

So our items tables exists. Great. Now lets verify that the columns in the table are correct before adding data. To verify the table was built correctly and all columns have the right datatypes, use the describe sql command.

describe items;

Running the sql command results in output below:

mysql> describe items;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| itemID          | int(11)      | NO   | PRI | NULL    |       |
| itemName        | varchar(30)  | YES  |     | NULL    |       |
| itemDescription | varchar(100) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql>

If something is wrong, use the alter command to fix. The table structure looks fine. Now add some data.

INSERT INTO items  VALUES (1001, ‘Apple’, ‘Eat one apple every afternoon.’);

INSERT INTO items  VALUES (1002, ‘Grapes’, ‘Eat 17 grapes as a mid day snack.’);

INSERT INTO items  VALUES (1003,  ‘Orange’, ‘Eat one orange every morning.’);

Output is as followed:

mysql> INSERT INTO items  VALUES (1001, ‘Apple’, ‘Eat one apple every afternoon.’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items  VALUES (1002, ‘Grapes’, ‘Eat 17 grapes as a mid day snack.’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items  VALUES (1003,  ‘Orange’, ‘Eat one orange every morning.’);
Query OK, 1 row affected (0.00 sec)

mysql>

View Items in MySQL Database

Now time to perform a select query.

SELECT * FROM items;

The output is as followed:

mysql> SELECT * FROM items;
+--------+----------+-----------------------------------+
| itemID | itemName | itemDescription                   |
+--------+----------+-----------------------------------+
|   1001 | Apple    | Eat one apple every afternoon.    |
|   1002 | Grapes   | Eat 17 grapes as a mid day snack. |
|   1003 | Orange   | Eat one orange every morning.     |
+--------+----------+-----------------------------------+
3 rows in set (0.00 sec)

mysql>

Installing netstat:

Netstat (network statistics) is a command-line tool that displays network connections (both incoming and outgoing), routing tables, and a number of network interface statistics. Netstat is a useful tool for checking your network configuration and activity. In this case, use netstat to test mysql server daemon is running. See how to use netstat.

Transfer MySQL Database:
Detailed instructions can be found at: Import sql dump file to mysql database.

#Export a database for backup or to transfer. This creates a sql file that will drop all tables,
#create new tables and import data.
username@server:~$ mysqldump -u USER -p PASSWORD DATABASE > myBackup.sql
username@server:~$

#Import database into another MySQL Server instance. Note the backup
#does not create a new database.
If copying to a new database, create a
#blank database first. Then import.

username@server:~$  mysql -u root -p DATABASE < myBackup.sql
username@server:~$