Class 5: SQL Basics & MySQL Configuration
Last Updated by Code Sport. Filed under classes, courses, mysqlOverview This guide serves as the teaching notes and class curriculum for…
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.
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
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.
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.
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.
Be sure to terminate every line of your SQL statements with a semicolon ;
.
Create a new database:
$ mysql> CREATE DATABASE web_app_database;
%
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;
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';
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;
host
, user
, and password
mysql
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: