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
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: Starting MySQL Community Server... jan 27 16:32:12 t400 systemd: Started MySQL Community Server. jan 27 16:32:26 t400 systemd: 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
for one. The characters that you type after the prompt are not visible.
This is a more secure solution for working with
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
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 ...
mysqladmin to check the version of MySQL server.
$ mysqladmin -u root -p create testdb
It is possible to create a database with
$ 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
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
-p option. We type the password
after the 'Enter password' prompt.
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
The Ctrl+L clears the screen, the Ctrl+D
quit command quit
mysql. We need to distinguish between
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> system pwd /home/janbodnar
system command can execute a shell command. We have launched
pwd command to find out our current working directory.
mysql> quit Bye
quit command terminates the
$ mysql --version mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
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)
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
performance_schema are MySQL system databases.
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
mysql> SHOW TABLES; Empty set (0.00 sec)
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
script, which creates a
Cars table for us.
mysql> SHOW TABLES; +----------------+ | Tables_in_mydb | +----------------+ | Cars | +----------------+ 1 row in set (0,00 sec)
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
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
In this chapter, we did our first steps with MySQL database system.