Home Tutorials Training Consulting Books Company Contact us

Get more...

This tutorial explains how to install MySQL and how to create and query a database. It includes important SQL statements which you need to use to create, update and read from database tables.

1. What is MySQL

MySQL is a open source relational database. Applications can use this database to persist data.

This tutorial covers the installation of MySQL and the usage of the database.

To work in MySQL databases you use SQL. SQL is a query language for relational databases.

2. Installation of MySQL

2.1. Ubuntu

Under Ubuntu you can install MySQL with the following command:

sudo apt-get install mysql-server

The installation procedure allow you to set the root password.

2.2. Windows

Under Windows download the community edition from http://dev.mysql.com/downloads/. For windows you have installer program available which guides you through the installation process.

2.3. Resetting the password

See Ubuntu password reset to learn how to set and reset your password under Ubuntu.

3. Connecting to MySQL

MySQL provides the mysql command line interface for administrative work. To connect to the MySQL database server use the following command:

# connect as root to MySQL server
mysql -u root -p

3.1. Initial setup of the dabase

To create a database and a user with access rights, use the following commands.

CREATE DATABASE vogella_blog;
CREATE USER 'myblog'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON * . * TO 'myblog'@'localhost';
GRANT ALL ON vogella_blog.* TO 'myblog'@'localhost' IDENTIFIED BY 'my_password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Once you have created a database on this server you can also connect directory to the database with the following command.

mysql db_name -u user -pmypassword

The default port of the database server is 3306. For example, the Java JDBC connection string to connect to the iotcloud database would be jdbc:mysql://localhost:3306/iotcloud.

4. See databases and tables

To see all databases and to connect to the vogella_blog database.

show databases;
connect vogella_blog;

List all tables in the currently used database;

show tables;

For example, to show the content of a wp_users of table use the following command.

SELECT wp_users.* FROM wp_users ;

The mysql command line interface allows you to export your database and import existing backups. The mysqldump command provides the option to create backup of your database, e.g. export your database.

To export the database use the following command.

# creates a dump of the database vogella_blog
mysqldump vogella_blog > vogella_blog.sql -p

To import an existing SQL dump file use the following command.

# vogella_blog.sql is your exported SQL file
# use option -p to enter your password
mysql vogella_blog < /var/www/vhosts/vogella.com/vogella_blog.sql -p