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
Ubuntu Linux 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.
$ su Password: # /usr/local/mysql/bin/mysqld_safe & # exit exit $
The final option is to start and stop MySQL server manually. If you have followed this tutorial from the beginning and have installed the MySQL from sources, this is the way to go. The mysqld_safe script starts the MySQL server. First we change to the root account and then we start the script.
$ sudo passwd root Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully $ su root Password: root@spartan:/usr/local/mysql#
On Ubuntu, the root account is not enabled by default. Here we show, how to enable it.
Then we can use the
su (switch user) command to switch to root to start
the MySQL daemon.
After we have started the server, we can check if it is alive.
$ mysqladmin -uroot -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.
$ mysqladmin -uroot -p shutdown
We use the
mysqladmin tool to shut down the MySQL server.
Connecting to the MySQL server
mysql is a MySQL command line tool. It is a simple shell.
It supports interactive and non-interactive use. It is located in the
/usr/local/mysql/bin directory. (On our installation.)
$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.9 Source distribution Copyright (c) 2000, 2010, 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/vronskij/programming/mysql
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
Creating a database
Now we are going to create our database.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
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. The
database is available as a workspace for users to try things out. It is empty;
there are no tables.
mysql> CREATE DATABASE mydb;
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 | | test | +--------------------+
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 a use command.
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 | +----------------+
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 | +----+------------+--------+
And this is the data in the table.
Creating a new user
Similarly to Unix root account, it is advised not to use the MySQL superuser root account for our tasks. We should use the root account only when it is necessary. We rather create a new account that we will use for our tasks. 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 statement created 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.
In this chapter, we did our first steps with MySQL database system.