Installing Apache2, MySQL, and PHP in Linux
In Windows, it is possible to create a WAMP or XAMPP server that runs Apache2, MySQL, and PHP legally, and free of charge.
Often times, servers run a variation of Linux because Linux works well in a server environment, and because Linux is a free operating system. In this lab assignment you will go through the installation of a LAMP (Linux, Apache2, MySQL, and PHP) server and configure MySQL.
Assuming you have root (administrative) access to a system running Ubuntu or another Debian variant, run the following commands. Note that if you installed the operating system yourself, you should have administrative access to it.
The following command will update the Advanced Packaging Tool (apt) cache of available packages.
sudo apt-get update
In Ubuntu, after you type “sudo”, you will be asked to enter your password. If you completed the “Installing Ubuntu on VirtualBox” lab, this password should be the same as the one you used when you installed Ubuntu.
Installing Apache2 and PHP
The following commands will install Apache2, PHP, and the proper extensions.
sudo apt-get install apache2
sudo apt-get install php5 libapache2-mod-php5
sudo a2enmod php5
Use a text editor like nano to create a configuration file for apache2 with the following command.
sudo nano /etc/apache2/conf-available/myserver.conf
Then add the following text to the file:
ServerName localhost
Next, save the file and enable it with the following command:
sudo a2enconf myserver
Next restart Apache with the following command
sudo /etc/init.d/apache2 restart
Verify that Apache works by running the following commands:
sudo ifconfig
The command above will provide your IP address. Write down your IP address, then navigate to your server by typing the IP address into a web browser. If everything is working, you should see a welcome page.
To modify the contents of your web server, you can place html and php files in the following directory:
/var/www/html
Try to modify the text of your welcome page.
Installing MySQL
The following command will install MySQL.
sudo apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql
You can stop, start, and restart the MySQL server with the following commands:
sudo /etc/init.d/mysqld stop
sudo /etc/init.d/mysqld start
sudo /etc/init.d/mysqld restart
After installation, you should disable symbolic links in the MySQL server. To do this, open the following file with the nano editor.
/etc/mysql/my.cnf
Add the following lines to the end of the file:
skip-symbolic-links
local-infile=0
Additionally, be sure that MySQL is associated with a different user than the Ubuntu root user. If associated with the Ubuntu root user, MySQL can be used to make dangerous changes to the file system including deleting all files on a user’s hard drive.
Running MySQL
Ensure that your mysql server is started by attempting to log onto MySQL with the following command.
mysql -u username -p
You will be prompted to enter your password, and the cursor will not move while you enter your password.
You can then use the following commands to view data in MySQL.
show databases; #To see a list of all databases
use db_name; #To use a database
show tables; #To see tables in a database after selecting a database with “use”
describe table name; #To see the fields within a table
User Privileges
MySQL maintains its own set of user accounts, separate from operating system users. The MySQL root user has direct access to the filesystem on which it is installed. It is therefore, very important to establish strict access control by creating user accounts.
It is possible to control user privileges from root including the use of INSERT, SELECT, UPDATE, DELETE, LOAD, and other commands.
Be aware that it is not possible to grant permissions to a user to drop or add tables without allowing that same user to drop or add a database.
Additionally be aware that it is not possible to password protect a single object such as a table, database, or a routine. You may only protect the account associated with it.
The MySQL privilege system uses several key statements:
CREATE USER - used to create a user
GRANT - used to grant privileges
REVOKE - used to revoke privileges
MySQL considers both the host name and username in its identification process.
There are 3 types of privileges
Administrative - to manage operations of the MySQL Server (Global)
Database - these apply to a database and all its objects
Database Object Privileges - for tables, indexes, views, and routines
Information about account privileges are stored in the user, db, host, tables_priv, columns_priv, procs_priv tables in the mysql database. Be aware that the mysql database is used for access control.
Do NOT give any MySQL user except the root MySQL user access to the mysql access control database.
To create a user with a privileged account, use the following statement.
CREATE USER 'username'@'localhost' IDENTIFIED BY 'passphrase';
Try to create a user in MySQL, then try to login to MySQL with that user and try to view the list of databases.
Writing a program that makes use of a database in Java.
First, download the MySQL connector to your home directory with the following command:
wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.34.zip
Then, create a Java program that will create a connection to the Database.
To use a database in Java, you must first connect to it.
Example:
Connection conn = null;
String db = "mydb";
String user = "username";
String password = "password";
Next, you must connect to the database:
try
{
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306", user, pass);
}
catch(SQLException e)
{
e.printStackTrace();
System.exit(0);
}
To make use of your database, you will need to use the following statements:
PreparedStatement stmt = PreparedStatement("use db_name");
stmt.execute();
Later in your program, you can make use of the database through queries and updates.
Example:
PreparedStatement stmt = null;
stmt = conn.prepareStatement("Select * from ?");
stmt.setString(1, "myTable");
stmt.executeQuery();
Note that we use the method executeUpdate() with statements that update the database.
Now create a database with one table using the MySQL command prompt, insert some dummy data into the database, and write a program to retrieve the data.