First steps in MySQL

In this chapter, we are going to make our first steps with MySQL. We will start the server, connect to the server with a client tool, create a new user and issue our first SQL statements.

Starting/stopping the MySQL server

MySQL server is a daemon which runs in the background. The way you start MySQL depends on your system and the installation type that you have done.

$ sudo /etc/init.d/mysqld start
$ sudo /etc/init.d/mysqld stop

On traditional init based systems, we would use the above commands to start and stop the MySQL server.

$ sudo service mysql start
$ sudo service mysql stop

Debian has migrated to Upstart, which is an event based daemon used for starting tasks and services and supervising them. On systems that use Upstart, we would start and stop MySQL server using the above commands.

$ sudo /usr/local/mysql/support-files/mysql.server start
$ sudo /usr/local/mysql/support-files/mysql.server stop

If we have installed MySQL from sources, we can use the mysql.server command to start and stop MySQL.

Checking MySQL status

We are going to show how to check the status of MySQL server.

$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Pi 2017-01-27 16:32:12 CET; 2h 7min ago
 Main PID: 9439 (mysqld)
   CGroup: /system.slice/mysql.service
           └─9439 /usr/sbin/mysqld

jan 27 16:32:10 t400 systemd[1]: Starting MySQL Community Server...
jan 27 16:32:12 t400 systemd[1]: Started MySQL Community Server.
jan 27 16:32:26 t400 systemd[1]: Started MySQL Community Server.

We check the status with the service mysql status command.

$ mysqladmin -u root -p ping
Enter password: 
mysqld is alive

We use the mysqladmin tool to check if MySQL server is running. The -u option specifies the user which pings the server. The -p option is a password for the user. If the password is omitted, the mysqladmin prompts for one. The characters that you type after the prompt are not visible. This is a more secure solution for working with mysqladmin. This way no one behind your back can see the password you have typed and it is not stored in the history of the shell.

The mysqladmin tool

The mysqladmin is a client for performing administrative operations.

$ mysqladmin -uroot -p shutdown

We use the mysqladmin tool to shut down the MySQL server.

$ mysqladmin -u root -p version
Enter password: 
...
Server version          5.7.17-0ubuntu0.16.04.1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 45 sec
...

We use mysqladmin to check the version of MySQL server.

$ mysqladmin -u root -p create testdb

It is possible to create a database with mysqladmin.

$ mysqladmin -u root -p drop testdb

This command deletes a database.

$ mysqladmin -u root -p password 
Enter password: 
New password: 
Confirm new password: 

We can use mysqladmin to change user password. We enter the old password and two times the new one.

The mysql tool

The mysql is a MySQL command line tool. It is a simple shell. It supports interactive and non-interactive use.

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)

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>

We connect to the server with the mysql tool. Note that we have omitted the password after the -poption. We type the password after the 'Enter password' prompt.

The mysql command line tool has mysql> as prompt. At this prompt we can issue both mysql built-in commands and SQL statements. We need to familiarise ourselves with the mysql tool. The Ctrl+Lclears the screen, the Ctrl+D or the quit command quit the mysql. We need to distinguish between mysql commands and SQL statements. SQL statements are terminated with a semicolon.

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
...

Type help to get a full list of mysql commands.

mysql> system pwd
/home/janbodnar

The system command can execute a shell command. We have launched the pwd command to find out our current working directory.

mysql> quit
Bye

The quit command terminates the mysql shell.

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

The mysql can be used also non-interactively. Here we get the version of the tool.

Creating a database

Now we are going to create our database.

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

The SHOW DATABASES statement shows all available databases on our system. Note that SQL statements are terminated with a semicolon. There are four databases present. The information_schema, mysql, and performance_schema are MySQL system databases. The sys is a set of schema objects used for tuning and diagnosis use cases. There are no user defined databases yet.

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0,00 sec)

This statement creates a new database. Throughout this tutorial, we will use the mydb database. To create a new database, we need to have certain privileges. Remember that we have connected to the server with the root user, which is a superuser and has all privileges.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0,00 sec)

Showing all databases, the mydb database is among them.

mysql> USE mydb;
Database changed

In order to work with a database, we must first select it. We select a specific database with the USEcommand.

mysql> SHOW TABLES;
Empty set (0.00 sec)

The SHOW TABLES statement shows all available tables in a database. Since it is a newly created database, no tables are found.

mysql> source cars.sql
Database changed
Query OK, 0 rows affected (0.20 sec)

Query OK, 1 row affected (0.08 sec)

...

In the first chapter, we have provided some SQL scripts to create some tables. We use the source command to execute the cars.sql script, which creates a Cars table for us.

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| Cars           |
+----------------+
1 row in set (0,00 sec)

Now the SHOW TABLES statement displays one table available.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0,00 sec)

And this is the data in the table.

Creating a new user

Similarly to a Unix root account, it is advised not to use the MySQL superuser root account for our daily tasks. We should use the root account only when it is necessary. We create a new account that we will use. This user will have limited privileges. When using the root user we could accidentally do a lot of harm to our data.

mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';

The above command creates a new user called user12. The accout has password 34klq*. The user is created, but he has no privileges.

mysql> GRANT ALL ON mydb.* TO user12@localhost;

This statement grants all privileges to user12 for all database objects on the mydb database. These privileges will be sufficient for the examples in this tutorial.

mysql> quit
Bye
$ mysql -u user12 -p

Now we can connect to MySQL with the new user account.

$ mysql -u user12 -p mydb -e "SELECT * FROM Cars"
Enter password: 
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We connect to the mydb database non-interactivly and execute an SQL statement. The statement to execute is specified after the -e option.