PHP MySQL tutorial First steps

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 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 noone 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

The 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.
The mysql command line tool has mysql> as prompt. At this prompt we can issue both mysql built-in commands and SQL statements. Familiarize yourself with the mysql tool. Ctrl + l clears the screen. Ctrl + d or the quit command quits the mysql shell. We need to distinguish between mysql shell 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/vronskij/programming/mysql

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.

Creating a database

Now we are going to create our database.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

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 test 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)

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           |
+----------------+

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 |
+----+------------+--------+

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 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.
In this chapter, we did our first steps with MySQL database system.