LAMP Stack 101

Overview

This guide serves as the teaching notes and class curriculum for Class 5 of our LAMP Stack 101 Learning Track. Herein, we explore configuration and common housekeeping tasks performed from the MySQL command-line.

As is the case with all of our study guides, this classroom tutorial is a living document. It will be reviewed, and updated on a regular basis.

PRO TIP 1: Using This Guide

If you are using this tutorial on a live site, be sure to substitute any usernames and passwords used herein with your own unique values.

As of January 8, 2016, this guide has been thoroughly tested on MySQL Version 5.5.46 running on Ubuntu 14.04. To view your version run the following from the command line:

$ mysql -V

This will produce output similar to:

mysql  Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.3

PRO TIP 2: Query Syntax

We use standard Structured Query Language (SQL) conventions in this guide by typing directives in all-caps. Custom variables such as database and table names are in lower-case.

Port Configuration and IP Address Binding

PRO TIP 3: Add an Additional Layer of Database Access Security

As discussed in Class 2: LAMP Server Install and Security Basics for additional security (i.e., security by obscurity) you may use my.cnf to change the default database connection port. If you make this modification, you will also need to update your IP Table Rules file to reflect this change.

Modify the MySQL configuration file by typing:

$ sudo nano /etc/mysql/my.cnf

Next, change the MySQL bind address to your server’s IP address. This is necessary if you intend to administer your database remotely.

Create A New Database

Login to your server’s MySQL command-line tool. Below is the general syntax for logging on:

$ mysql -u username_to_logon_as -p

To log on as root, change username_to_logon_as to root.

PRO TIP 4: A Note on SQL Queries via the Command Line

Be sure to terminate every line of your SQL statements with a semicolon ;.

Create a new database:

$ mysql> CREATE DATABASE web_app_database;

Create MySQL User With Super User Privileges

PRO TIP 5: SQL Connection Wildcards: % and *.*

localhost is the location which gets access to your database. Changing localhost to the % wildcard allows connections from all locations to the database. Alternatively, you may use a specific IP address (or hostname such as example.org) to only allow database access from a specific location.

Finally, note that *.* is wildcard syntax for [databaseName].[tableName].

The example below uses the aforementioned wildcard syntaxes.

Since we prohibited remote MySQL root access in Class 2, you may want to temporarily create a super user for troubleshooting purposes. At MySQL command prompt type: 

$ mysql> GRANT ALL PRIVILEGES ON *.* TO 'super_username'@'%' IDENTIFIED BY 'super_user_password' WITH GRANT OPTION;

Create A MySQL User For Your Web App

TIP 6: Viewing MySQL Users & Their Privileges

MySQL stores user metadata (name, passwords, and privileges) within a table called users inside a database called mysql

$ mysql> SELECT host, user, password FROM mysql.user;

For creating a user for your apps (including WordPress), the below 10 privileges are most common:

$ mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON web_app_database.* TO 'web_app_username'@'localhost' IDENTIFIED BY 'web_app_user_password';

For giving the same user access to another DB, just remove the password assignment command: 

$ mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON another_web_app_database.* TO 'web_app_username'@'localhost';

Basic SQL Queries

In order to increase your comfort level with some basic SQL statements try experimenting with the below queries.

1. Show The Version of MySQL Installed:

$ mysql> SELECT version();

2. Display a List of MySQL Users, Their Permitted Hosts, and Hashed Passwords:

$ mysql> SELECT host, user, password FROM mysql.user;

PRO TIP 7: What Does This Mean?

  1. Columns selected are host, user, and password
  2. Database name is mysql
  3. Table name is user

3. List All Databases:

$ mysql> SHOW DATABASES;

4. View Privileges Of A Specific MySQL User:

$ mysql> SHOW GRANTS for 'web_app_username'@'localhost';

5. Change Password of The MySQL User That You Are Logged In As:

$ mysql> SET PASSWORD = PASSWORD('cleartext password');

6. Change Password Of A Specific MySQL User That You’re Not Necessarily Logged In As:

$ mysql> SET PASSWORD FOR 'web_app_username' = PASSWORD('cleartext password');

7. Delete A MySQL User:

$ mysql> DROP USER 'web_app_username'@'%';

8. Delete An Entire Database:

$ mysql> DROP DATABASE web_app_database;

References and Online Resources:

  1. Ubuntu Help Docs: LAMP Housekeeping After installing MySQL
  2. Ubuntu Help Docs: Ubuntu MySQL Server Guide
  3. MySQL Manual: Understanding Grant Syntax and Levels
  4. MySQL Manual: The MySQL Show Grants Command
  5. MySQL Manual: Set Password Syntax
  6. MySQL Manual: When to Flush User Privileges