Linux - MySQL¶

MySQL File Formats¶

MySQL uses a number of different file formats for the storage of information. MySQL table you create is represented on disk by a .frm file that describes the table's format (that is, the table definition). The file bears the same name as the table, with an .frm extension.

Entering Queries¶

SELECT VERSION(), CURRENT_DATE;

A query normally consists of an SQL statement followed by a semicolon (There are some exceptions).

mysql> SELECT VERSION(), CURRENT_DATE;
+-------------------------+--------------+
| VERSION()               | CURRENT_DATE |
+-------------------------+--------------+
| 5.7.25-0ubuntu0.16.04.2 | 2019-03-18   |
+-------------------------+--------------+
1 row in set (0.00 sec)

mysql> select version(), current_date;
+-------------------------+--------------+
| version()               | current_date |
+-------------------------+--------------+
| 5.7.25-0ubuntu0.16.04.2 | 2019-03-18   |
+-------------------------+--------------+
1 row in set (0.00 sec)

mysql> SeLeCt vErSiOn(), current_DATE;
+-------------------------+--------------+
| vErSiOn()               | current_DATE |
+-------------------------+--------------+
| 5.7.25-0ubuntu0.16.04.2 | 2019-03-18   |
+-------------------------+--------------+
1 row in set (0.00 sec)

mysql>

You can even enter multiple statements on a single line

mysql> SELECT VERSION(); SELECT NOW();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.7.25-0ubuntu0.16.04.2 |
+-------------------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2019-03-18 11:13:26 |
+---------------------+
1 row in set (0.00 sec)

mysql>



mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

Creating and Using a Database¶

Show existing databases¶

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Use a database¶

mysql> USE mysql
Database changed
mysql>

Create database¶

mysql> CREATE DATABASE menagerie; Query OK, 1 row affected (0.00 sec)

Use this new databse:

mysql> USE menagerie;
Database changed
mysql>

The database has no tables: mysql> SHOW TABLES; Empty set (0.00 sec)

mysql>

Create a table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    ->        species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

To ensure the tables is filled:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

Loading Data into a Table¶

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

You could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values, you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).

For example create a 'pet.txt' file with:

Whistler Gwen bird \N 1997-12-09 \N Fluffy Harold cat f 1993-02-04 \N Claws Gwen cat m 1994-03-17 \N Buffy Harold dog f 1989-05-13 \N Fang Benny dog m 1990-08-27 \N Bowser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 \N Slim Benny snake m 1996-04-29 \N

Then insert the content in the table:

mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
Query OK, 1 row affected, 6 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 6

mysql>

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement.

Suppose that Diane gets a new hamster named “Puffball.” You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.00 sec)

mysql>

Retrieving Information from a Table¶

The SELECT statement is used to pull information from a table. The general form of the statement is:

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

Simplest form of SELECT is:

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

mysql>

Selecting Particular Rows¶

mysql> SELECT * FROM pet WHERE name = 'Puffball';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

mysql>

You can specify conditions on any column, not just name.

For example, if you want to know which animals were born during or after 1998, test the birth column:

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.01 sec)

mysql>

Edit Information from a Table¶

Let say Puffball owner was indeed Joey instead of Diane.

Two ways:

Delete the whole table using 'DELETE FROM pet'

mysql> DELETE FROM pet;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM pet;
Empty set (0.00 sec)

mysql>

But now you have to rebuild the table...

mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Whistler | Joey   | bird    | NULL | 1997-12-09 | NULL       |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql>

The other option is to UPDATE the erroneous record:

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Whistler | Joey   | bird    | NULL | 1997-12-09 | NULL       |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> UPDATE pet SET owner = 'Joey' WHERE name = 'Whistler';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Whistler | Joey   | bird    | NULL | 1997-12-09 | NULL       |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql>

Select only particular rows¶

If you do not want to see entire rows from your table, just name the columns in which you are interested, separated by commas.

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Whistler | 1997-12-09 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1979-08-31 |
| Chirpy   | 1998-09-11 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)

mysql>

To find out who owns pets, use this query:

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

Notice that the query simply retrieves the owner column from each record, and some of them appear more than once. To minimize the output, retrieve each unique output record just once by adding the keyword DISTINCT:

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

You can use a WHERE clause to combine row selection with column selection:

mysql> SELECT name, species, birth FROM pet
    ->        WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1979-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)

mysql>

Sorting Rows¶

You may have noticed in the preceding examples that the result rows are displayed in no particular order. It is often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause.

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

mysql> SELECT name, species, birth FROM pet
    ->        ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Buffy    | dog     | 1989-05-13 |
| Bowser   | dog     | 1979-08-31 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)

Date Calculation¶

MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.

To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function. Its arguments are the unit in which you want the result expressed, and the two dates for which to take the difference. The following query shows, for each pet, the birth date, the current date, and the age in years. An alias (age) is used to make the final output column label more meaningful.

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

Ordered:

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

Or:

mysql> SELECT name, birth, CURDATE(),
    ->        TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    ->        FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2019-03-18 |   19 |
| Chirpy   | 1998-09-11 | 2019-03-18 |   20 |
| Whistler | 1997-12-09 | 2019-03-18 |   21 |
| Slim     | 1996-04-29 | 2019-03-18 |   22 |
| Claws    | 1994-03-17 | 2019-03-18 |   25 |
| Fluffy   | 1993-02-04 | 2019-03-18 |   26 |
| Fang     | 1990-08-27 | 2019-03-18 |   28 |
| Buffy    | 1989-05-13 | 2019-03-18 |   29 |
| Bowser   | 1979-08-31 | 2019-03-18 |   39 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql>

Where is my database ?¶

By default, the database is stored in a directory using the same name as your db name, in /var/lib/mysql/

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql>

How to move a database¶

Stop mysql service¶

sudo systemctl stop mysql
sudo systemctl status mysql

Copy database to the new location¶

1	`sudo rsync -av /var/lib/mysql /mnt/mynew_db_location`

Change mysql configuration to point to the new location¶

Edit mysqld.cnf

1	`sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf`

Find and replace the datadir= value

1	`datadir=/mnt/mynew_db_location`

Backup the old database¶

sudo mv /var/lib/mysql /var/lib/mysql.bak

Re-create the directory¶

1	`sudo mkdir -p /var/lib/mysql`

Restart mysql service¶

sudo systemctl status mysql
sudo systemctl start mysql

Connect to the database¶

# mysql -u root -p

Then check the datadir:

mysql> select @@datadir;
+------------------------+
| @@datadir              |
+------------------------+
| /mnt/mynew_db_location |
+------------------------+
1 row in set (0.00 sec)

mysql>

Check the content of you database(s).

Delete old files¶

Once you checked, you can delete your backup with # rm -Rf /var/lib/mysql.bak