Getting Around in MySQL

We have learned how to build and manage a web server from scratch. The next component of our course will cover how to administer and maintain a MySQL database. Databases are useful for storing vast amounts of data. This data can come in many forms. For instance, WordPress stores all of its user-generated content into database tables. Another example is capturing and storing data from web forms about a survey or product.

To build a better internet business, you will need to have a basic knowledge of databases. Therefore, our focus will be on how to manage, maintain, and use databases. While we will be including SQL commands, this is meant to be more of a guideline for basic SQL usage.

The Problems we will solve:

How do I install a database on my system?
What commands start and stop the database?
How do I view data in my database?
How do I administer security in my database?

What we will cover:

Overview
Start and stopping the server
Securing your server
Console MySQL: login, select a database, list tables, quit
Creating users and changing passwords
Assigning ownership
SQL commands: Create, Insert, Update, Select, Delete

Class Goal: Walk through the typical administration steps for a database server. Learn enough MySQL commands to navigate your new system.

What is MySQL?

MySQL is an open source and freely available database engine. There is a commercial version of MySQL, but we will use the one that allows us free use. MySQL is the default database for WordPress and one that small to medium sized businesses use for a variety of purposes. The database gorilla Oracle owns MySQL and has been improving it for the last several years. Visit the MySQL home page at http://dev.mysql.com/ for more information. There is also an excellent overview of databases that we provide in another class available at http://develpreneur.com/itoverview/DPDBOverview-2.0.pdf

Installing MySQL

To install MySQL on EC2 execute this command:

sudo yum install -y mysql55-server php56-mysqlnd

If you have done the steps in Native LAMP on Amazon AWS, then you have already setup MySQL with Apache on your Linux instance. If not, then please do so now.

Starting and Stopping the Database

In general, you should not have to start and stop your database that often. Typically, a database only needs to be restarted when applying configuration changes, system updates, or if the database crashes. The following is a list of commands and descriptions that allow you to control your database.

The command to start/stop/restart/status looks like this:

sudo service mysqld start|stop|restart|status

Start – Starts up the MySQL instance if it is not already running. For example,

sudo service mysqld start

Stop – Will cleanly stop the MySQL instance.

Restart – Will trigger a stop command before starting up the instance. When in doubt use this option to make sure the server is not running before trying to start it again.

Status – This returns the state (stopped or running) of your instance. Running this command will return a message like:

mysqld (pid 1111) is running...

Troubleshooting

If a database does experience a crash, then you will need to start researching why the database went down. You need to start out by checking the mysql.log file located in the directory /var/log. This log file is full of useful information about what is going on with your database. To open the log file type:

sudo vi /var/log/mysqld.log

There may be a substantial amount of text in this file. Fortunately, the structure of the log file is relatively easy to follow. For instance, here is a sample line from a mysqld.log file:

160826 20:38:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Each line starts out with a sequence number indicating the session of a database instance. Following the sequence number is the date/time stamp of when the event took place. The line ends with the transaction message. Typically, when an error occurs, you will see [ERROR] written after the date/time. The [ERROR] syntax makes it easy to search the file to find potential problems.

Connect to The MySQL Console

To access your database you need to connect to the MySQL console. For this course, we will be using the MySQL command-line client. The list below provides some of the variations a user can enter to log into the console. However, before you begin, make sure you have a valid username and password. This account will also need the correct database permissions. Otherwise, you will not be able to gain access to the database.

mysql --user=username --password db_name
or
mysql -u username -p db_name
or
mysql --user=username --password=password db_name
or
mysql -u username -ppassword db_name

To specify the username use either -u or –user and must be followed by a valid MySQL username. If you wish to specify a password use either –p or —password. Unlike username, the password value can be left blank. In doing so, the program will prompt you to enter the password before continuing.

After a successful login you will be taken to the MySql console screen below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16195
Server version: 5.5.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Console Commands

Once you are on the MySQL console, you can run any number of SQL commands and select MySql commands.

Command	Description
show databases;	Lists all databases on the system.
use db_name;	Switches database view.
show tables;	Lists the available tables in the database.
show columns from table_name;	Displays the table details for each column. These are: Field – Column Name Type – char, enum, int, blog, text Null – a boolean value indicating if the column value is null. Key – a boolean value indicating if this column is a primary key. Default – Default value for the column. Extra – additional information about the column.
describe table_name;	Displays the table details for each column. These are Field, Type, Null, Key, Default, and Extra
exit;	Logs out of the MySQL Console.
flush privileges;	Tells the server to reload the grant table.

Account Administration

Account administration requires making changes to the MySQL database tables. Therefore, you must log in as root or as a user with admin privileges.

mysql -u root -p
use mysql;

List users already added to the database.

The following query will display all the MySql users. For instance, the results will contain only three records on a new install.

SELECT host, user, password FROM mysql.user;

Create a new MySql user

CREATE USER 'jsmith'@'localhost' IDENTIFIED WITH mysql_native_password;

Change a users password

SET PASSWORD FOR 'jsmith'@'localhost' = PASSWORD('mypass');

Assigning ownership

Ownership and privileges control what a user can see and do on a database. That is why after creating an account, you need to assign rights to the account. For example, let’s create an account with no restrictions and full access to the database.

GRANT ALL ON database.table TO 'user'@'host'

GRANT ALL means the user will be able to have unrestricted access to the database.table. Unrestricted access means they will be able to view and change the data. However, to make the changes for all the tables change database.table to database.*. Otherwise, you can modify the database.table to *.*, this grants all privileges to everything.

For the next example, we will limit a user’s permission. Let’s allow our jsmith user only to have the ability to view the table invoice in the database db2.

GRANT SELECT ON db2.invoice TO 'jsmith'@'localhost';

For a full list of privileges go to the MySQL reference page.

SQL Commands

The command language of Databases is SQL. SQL stands for Structured Query Language. This language is responsible for communicating with a database to create, read, update, or delete data (CRUD for short). There is some variation in the SQL commands from database to database. Therefore, we will only be covering the most commonly used commands and providing examples of how to use them. Be aware that some database providers handle additional commands differently. These tend to affect the joining of information between tables. Because of the complexity of this topic, we will not be covering it in this course. However, you can find more information about advanced querying techniques here.

CREATE DATABASE

Use this command to create a new database.

CREATE DATABASE db_name;

CREATE TABLE

Use this command to create a table in a database. A table consists of rows and columns; each table must have a name. However, before you can create a table, you must have first selected a database. For a full list of data_types check out the MySQL reference page.

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

SELECT

Use this command to retrieve data from a database. There are two ways to select data. One is defining which column data you want by listing the column names. Another option is to return the data for every column in the table. To do this replace the column name list with one asterisk (*). Finally, you can limit a query’s results only to show the records you want. For instance, say you want to get all records by account id or date of birth. Then you will need to use the WHERE clause.

SELECT column_name,column_name FROM table_name;
or
SELECT * FROM table_name;

INSERT

Use this command when you want to insert new records in a table. There are two options for inserting data into a table. The first one requires specifying a list of the columns to update followed by the values for those columns. The other option is just to record the values without defining the columns. You should avoid doing this because you do not know how this will execute between databases.

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

UPDATE

Use this command to update records in a table. You will need to specify the column you wish to update followed by the new value. This example will update the column values for every row in the database table. While this is possible, it is unlikely that you want to update every row. The chances are that all you want to do is update a single record. So instead, include a WHERE clause following the list of column#=value# that you are setting.

UPDATE table_name SET column1=value1,column2=value2,...

DELETE

Use this command to delete records in a table. For example, the following syntax will remove every record from a table. Otherwise, if you wish only to delete a particular row or rows make sure to include a WHERE clause.

DELETE FROM table_name
OR
DELETE * FROM table_name

ORDER BY

This command goes at the end of our query. Thus, will return the result set in a specific ascending or descending order.

ORDER BY column_name ASC|DESC, column_name ASC|DESC;

WHERE

The WHERE is not a command but instead it is a conditional clause. Use this to specify which records to include with your statement. The WHERE condition goes at the end of the query before ORDER BY. You can limit your results based on a single condition or multiple conditions. The most common operators to use with WHERE are “AND” and “OR”. The AND operator acts as an inclusion. Therefore, for the condition to be true, both the left and the right conditions must be true. If either is false, then the condition fails. However, the OR operator does the exact opposite. As long as either condition is true on the left or right, then the statement will pass. So the only time the OR operator would fail is due to both conditions being false.

WHERE column1=value1
or
WHERE column1=value1 AND column2 ...
or
WHERE column1=value1 OR column2 ...

Additional WHERE Operators

Operator	Description	Example
=	Must Be Equal.	column1=value1
!= or <>	Not Equal To	column1 != value1 OR column1 <> value1
>	Greater Than	column1 > value1
>=	Greater Than And Equal To	column1 >= value1
<	Less Than	column1 < value1
<=	Less Than And Equal To	column1 <= value1
BETWEEN	Between an inclusive range.	column1 BETWEEN value1 AND value2. Equivalent to column1 >= value1 AND column1 <= value2
LIKE	Search for a Pattern using Wildcards	column1 LIKE value1
IN	To specify multiple possible values for a column.	column1 IN (value1,value2,…). Equivalent to column1=value1 AND column1=value2 …

Assignments

Go ahead and create a couple of MySQL accounts. Once completed, go ahead and set the privileges for the first account to be All. Then for the second account, limit the user’s access to a handful of tables.
Build a query to collect all of the WordPress Users email addresses for a newsletter. Start by using show database to find your WordPress database. Then, connect to your WordPress database. Finally, create a query to fetch all the email addresses from the WordPress users table: table wp_users.

Bonus: Using Vi go into your backup job and add a line to backup a single database. Then create an additional line that will do a full MySQL dump of all your databases.

That completes this class. It is now time to move on to the next step: Domain Registration and Configuration