Setup MySQL and Tomcat on AWS Instance

First we nee to launch an Amazon EC2 instance and install Java on it.

Install MySQL

SSH to the instance, run mysql to check if MySQL is available on it.

Here we have three things to do:

install MySQL
start MySQL server
grant privileges to anonymous user

To install MySQL, execute the command:

sudo yum install mysql-server

Then start MySQL service by executing sudo service mysqld start, here are three command related to MySQL start, stop and restart:

sudo service mysqld start
sudo service mysqld stop
sudo service mysqld restart

Now we can login to MySQL using root user by executing mysql -u root. As all the user information are stored in the schema named mysql, we can use show databases; to list all schemas in the database:

mysql -u root // login to MySQL database
show databases; // show all the schema in the MySQL database

In this case, we need to change the information in a table named user of the mysql schema, here we use use mysql to swith to that schema, and then select main information of all users from table user:

use mysql;
select host, user, password from user;

Here we need to grant the privilege of anonymous user, The command format of granting privilege is:

grant all on shemaname.tablename to 'username'@'userhost';

We want the anonymous user can access to the database without password from anywhere, that means the username should be an empty string as well as the password; accessing from anywhere means the user’s host can be *, then we run the grant command and flush the privileges:

grant all on *.* to ''@'*';
flush privileges;

Load data from file to MySQL database

Now we have granted the privilege of the anonymous user. Here we create a schema named demo by running:

create database demo;

Then exit MySQL database, and create a .sql file using vim, here we name it as table_demo.sql, and fill SQL script into it:

DROP TABLE IF EXISTS `demo`.`table_demo`;
CREATE TABLE  `demo`.`table_demo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The command format of create table by using sql script is:

mysql [username] [password] [schemaname] < [/path/of/sqlscript/scriptname].sql

Then execute the following command, we use anonymous user so that we do not enter username and password:

mysql demo < table_demo.sql

Also if you want to specify the character set, then command should be like:

mysql --default-character-set=utf8mb4 demo < table_demo.sql

Login to MySQL database and see whether the table is been created successfully.

Now assume we have a .csv file named names.csv under the current directory:

Make a copy of it under the directory /var/lib/mysql/demo/ by running:

sudo cp names.csv /var/lib/mysql/demo/

If the permission is denied then you need to open the access to the folder mysql and folder demo:

// cd to lib:
cd /var/lib

// open access of mysql
sudo chmod 777 mysql

// cd to mysql
cd mysql

// open access of demo
sudo chmod 777 demo

Now go back to the directory where you place the .csv file, run the copy command again.

The command of loading data from file is:

load data infile '[filename]' INTO TABLE [tablename] FIELDS TERMINATED BY '[fields terminating character]' LINES TERMINATED BY '[lines terminating character]';

Then login to MySQL database, run the following commands:

use demo;
load data infile 'names.csv' INTO TABLE table_demo FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Now you load the data into MySQL database:

If you want to improve the performance when quering some column, you can add index to that column in following format:

create index nameindex on table_demo (name);

Install Tomcat

Run the following command to see if tomcat is intalled on the instance:

sudo service tomcat start

If not then run the following command to intall Tomcat:

sudo yum install tomcat8 tomcat8-webapps

When the installation is completed, run following command to start Tomcat service:

sudo service tomcat start

Once you start the Tomcat service, launch browser and go to http://instanceDNS:8080, and you can see:

That means Tomcat is been installed on the instance successfuly, and you can access your web application by sending request to the instance’s DNS on port 8080:

http://instanceDNS:8080/[webapplication_name]

Change the Tomcat listening port from 8080 to 80

However, if you want to access your web application from port 80 instead of 8080, which means you want to access your web application in following way:

http://instanceDNS/[webapplication_name]

Then you need to change the Tomcat listening port from 8080 to 80. Here we have two methods for Tomcat server:

Change port to 80
Redirect connections on port 80 to 8080

For Tomcat users, go to the Tomcat configuration folder:

cd /usr/share/tomcat8/conf

There are two files you need to modify:

• server.xml
• tomcat8.conf

On server.xml change port from 8080 to 80 on line 69:

<Connector port="80" protocol="HTTP/1.1"           connectionTimeout="20000"           redirectPort="8443" />
           connectionTimeout="20000"
           redirectPort="8443" />

On tomcat8.conf change the value of TOMCAT_USER from tomcat to root on line 27:

TOMCAT_USER="root"

Then restart Tomcat using sudo service tomcat restart, launch browser and go to http://instanceDNS, and you can see:

In some situtation, we are not allowed to actually open the port 80 for our web applications, which means we are only allowed to use port 8080 and letting the user access our web application through http://instanceDNS/[webapplication_name]. Then we need to redirect connections on port 80 to 8080 by executing the following commands:

sudo iptables -t nat -A PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080
sudo iptables -t nat -I OUTPUT -p tcp -d 127.0.0.1 --dport 80 -j REDIRECT --to-ports 8080

And if you want to access your web application by entering the DNS name without web application name like:
http://instanceDNS instead of http://instanceDNS/[webapplication_name], go to /usr/share/tomcat8/webapps:

Rename the ROOT file to ROOT_bak:

sudo mv ROOT ROOT_bak

Then name your web application file (.war file) to ROOT.war and copy it to /usr/share/tomcat8/webapps. Restart Tomcat service and open the URL http://instanceDNS, now you get it.