Archive
mySQL Remote Access in my LAN
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.
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:~$
introjava 