Linux Exercise: SQL Data Management

Important: The current LPI exam expects knowledge of MySQL. However, after the LPI exam was made available the MySQL project forked and the most important fork at the moment is MariaDB. This is also the database product included in Red Hat 7/CentOS 7. MariaDB is fully downwards compatible with MySQL though, and you can use the normal MySQL client tools to talk to MariaDB. So at the LPI-102 level there is no practical difference.

Installing MySQL/MariaDB

  1. If MariaDB/MySQL is not yet installed, install it. Then activate it.
    • # yum install mariadb-server
    • # systemctl start mariadb
    • # systemctl enable mariadb

Create a MySQL/MariaDB user account

  1. Change the MariaDB/MySQL password to "topsecret" using the mysqladmin tool. Logon to the database as root.
    • # mysqladmin password topsecret
    • # mysql -h localhost -u root -p
      Enter the password. You should then see the "mysql>" prompt.
  1. Create a user account for yourself and set a password.
    • mysql> create user 'yourname'@'localhost' identified by 'password';

Create database and tables

  1. Create a database "bookshelf". Give your user account unlimited privileges to this database.
    • mysql> create database bookshelf;
    • mysql> grant all privileges on bookshelf.* to 'yourname'@'localhost';
  1. Make a list of all databases. In addition to the bookshelf database you should also see a few other, default MySQL databases.
    • mysql> show databases;
  1. Logout from the MySQL client, login again with your own username and see which databases are available now.
    • mysql> exit;
    • # mysql -h localhost -u yourname -p
      Enter your password when prompted.
    • mysql> show databases;
      You should only see the bookshelf databse, as you have no access to the other databases.
  1. Start using the "bookshelf" database.
    • mysql> use bookshelf;
  1. Create a table "books". This table should have five fields and attributes:
    • id: integer, primary key, auto_increment, not null
    • title: varchar(256)
    • year: integer
    • author_id: integer
    • written: integer
    • mysql> create table books ( id integer primary key auto_increment not null, title varchar(256), year integer, author_id integer, written integer );
  1. Create a table "authors". This table should have three fields:
    • id: integer, primary key, auto_increment, not null
    • first_name: varchar(256)
    • last_name: varchar(256)
    • mysql> create table authors ( id integer primary key auto_increment not null, first_name varchar(256), last_name varchar(256) );
  1. Make a list of tables in your database, and pull up the characteristics of each table.
    • mysql> show tables;
    • mysql> describe books;
    • mysql> describe authors;

Add data to the database

  1. Add three authors, Sean Walberg, Ross Brunson and Ada Lovelace, to the Authors table. Look at the Authors table. What happened to the "id" field?
    • mysql> insert into authors (first_name, last_name) values ('Sean', 'Walberg');
    • mysql> insert into authors (first_name, last_name) values ('Ross', 'Brunson');
    • mysql> insert into authors (first_name, last_name) values ('Ada', 'Lovelace');
    • mysql> select * from authors;
      The id field is automatically assigned and will increment every time you add a new author. This guarantees uniqueness.
  1. Add four books to the books table. Use the list on page 377 of your book. Look at the Books table.
    • mysql> insert into books (title, year, author_id, written) values ('Linux and Windows 2000 integration', 2001, 2, 2000);
    • mysql> insert into books (title, year, author_id, written) values ('LPIC 1 Exam Cram 2', 2004, 2, 2004);
    • mysql> insert into books (title, year, author_id, written) values ('Check Point CCSA Exam Cram 2', 2005, 1, 2004);
    • mysql> insert into books (title, year, author_id, written) values ('Wireless All In One For Dummies', 2009, 2, 2009);
    • mysql> select * from books;

Data retrieval, select, sort

  1. Look at all data from the books table. Sort by title.
    • mysql> select * from books order by title;
  1. Look at all data from the books table. Sort by title in descending order.
    • mysql> select * from books order by title desc;
  1. Look at the data from the books table. Sort by title, and only show the first two books.
    • mysql> select * from books order by title limit 2;
  1. Make a list of book titles that were published in 2001.
    • mysql> select title from books where year = 2001;
  1. Make a list of book title and author_ids of books published between 2002 and 2005.
    • mysql> select title, author_id from books where year between 2002 and 2005;
  1. Make a list of books that have the word "Cram" in their title.
    • mysql> select * from books where title like "%cram%";

Managing indexes (optional)

Note that managing indexes is not an LPI requirement. It is included here for completeness.

  1. Create an index on last_name (authors) and title (books).
    • mysql> create index last_name on authors( last_name );
    • mysql> create index title on books( title );
  1. Look at the table definitions. Do you see the index.
    • mysql> describe authors;
    • mysql> describe books;

Unfortunately computers are so fast, and our tables are so small, that you will not notice any difference if you perform queries where these indexes would make a difference. However, with large databases (millions of records), indexes can have a very pronounced effect on performance. But indexes are not free: Every time you update a record in a table, you also need to update the corresponding index. Determining which indexes are required for a database is one of the most important tasks of a DBA.

Database join operations

  1. Perform a "cardinal" join of both tables, without telling MySQL what the relation is between the tables. What happens?
    • mysql> select * from books, authors;
  1. Run the "cardinal" join again, but indicate that it should only contain those combination records where books.author_id equals authors.id.
    • mysql> select * from books, authors where books.author_id = authors.id;
  1. Do the same thing, but now use a left join or a right join. What's the difference?
    • mysql> select * from books left join authors on books.author_id = authors.id;
    • mysql> select * from books right join authors on books.author_id = authors.id;
      The difference between a left join and a right join is what happens if one of the tables has no relation with the right table. As in our case, where we have an author that did not write any books.

Modify and delete data

  1. Change the title of "Wireless All In One For Dummies" into "Wireless All In One For Smartypants".
    • mysql> update books set title="Wireless All In One For Smartypants" where title="Wireless All In One For Dummies";
    • mysql> select * from books;
  1. Delete the author Ada Lovelace from the table.
    • mysql> delete from authors where id=3;
    • mysql> select * from authors;

Database backup and restore (optional)

Note that database backup and restore is not an LPI requirement.

  1. End the MySQL interface tool.
    • mysql> exit
  1. Use the mysqldump command to create a backup of the "bookshelf" database. Send the output to stdout to begin with. Do you recognize this? Then save the output to the file bookshelf.sql.
    • # mysqldump -h localhost -u yourname -p bookshelf
    • # mysqldump -h localhost -u yourname -p bookshelf > bookshelf.sql
  1. Look at the contents of the directory /var/lib/mysql. What files do you see? Look at the contents of /var/lib/mysql/bookshelf as well.
    • # ls -l /var/lib/mysql
    • # ls -l /var/lib/mysql/bookshelf
  1. Start the MySQL tool again. "Drop" all tables and verify that all tables have been destroyed. Stop the MySQL tool.
    • # mysql -h localhost -u yourname -p
    • mysql> use bookshelf;
    • mysql> drop table authors;
    • mysql> drop table books;
    • mysql> show tables;
    • mysql> exit
  1. Look at the contents of /var/lib/mysql/bookshelf again. What happened?
    • # ls -l /var/lib/mysql/bookshelf
  1. Restore the database. Login and verify you've got all your data again.
    • # mysql -h localhost -u yourname -p bookshelf < bookshelf.sql
    • # ls -l /var/lib/mysql/bookshelf
    • # mysql -h localhost -u yourname -p
    • mysql> use bookshelf;
    • mysql> show tables;
    • mysql> describe authors;
    • mysql> describe books;
    • mysql> select * from authors;
    • mysql> select * from books;
  1. Exit the MySQL tool
    • mysql> exit
End of exercise