Support free tutorials:











vogella training Training Books



MySQL - Tutorial

Lars Vogel

Version 1.1

23.05.2013

Revision History
Revision 0.1 03.02.2008 Lars
Vogel
created
Revision 0.2 - 1.1 09.01.2009 - 23.05.2013 Lars
Vogel
bug fixes and enhancements

MySQL

This tutorial explains how to install MySQL and how to create and query a database. This tutorial is using Ubuntu.

This article uses Java 1.6 and MySQL 5.5.


Table of Contents

1. Overview
2. Installation
2.1. Ubuntu
2.2. Windows
2.3. Resetting the password
3. Connecting to MySQL
4. Exercise: create example database
5. Further SQL commands
6. Import and export
7. Support free vogella tutorials
7.1. Thank you
7.2. Questions and Discussion
8. Links and Literature

1. Overview

2013 MySQL is a popular Open Source database which can be used free of charge. MySQL is a relational database. Web applications can use this database to store persistence data.

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

To use MySQL with Java see MySQL and Java Tutorial.

2. Installation

2.1. Ubuntu

Under Ubuntu you can install MySQL with the following command:

sudo apt-get install mysql-server
 

The installation will asked you questions regarding your desired installation, e.g. the password to use, etc.

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 

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 

4. Exercise: create example database

In this exercise you create a new database, a new user and an example table. For this connect to the MySQL server via the mysql command line client.

Create a new database called feedback and start using it with the following command.

create database feedback;
use feedback; 

Create a user with the following command.

CREATE USER sqluser IDENTIFIED BY 'sqluserpw'; 

grant usage on *.* to sqluser@localhost identified by 'sqluserpw'; 
grant all privileges on feedback.* to sqluser@localhost; 

Now create a sample database table with example content via the following SQL statement.

CREATE TABLE COMMENTS (id INT NOT NULL AUTO_INCREMENT, 
    MYUSER VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(30), 
    WEBPAGE VARCHAR(100) NOT NULL, 
    DATUM DATE NOT NULL, 
    SUMMARY VARCHAR(40) NOT NULL,
    COMMENTS VARCHAR(400) NOT NULL,
    PRIMARY KEY (ID));

INSERT INTO COMMENTS values (default, 'lars', 'myemail@gmail.com','http://www.vogella.com', '2009-09-14 10:33:11', 'Summary','My first comment'); 

5. Further SQL commands

Please see SQL Tutorial for the usage of SQL commands.

6. Import and export

The mysql command line interface allows you to import existing backups and 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 

7. Support free vogella tutorials

Maintaining high quality free online tutorials is a lot of work. Please support free tutorials by donating or by reporting typos and factual errors.

7.1. Thank you

Please consider a contribution if this article helped you.

Flattr this

7.2. Questions and Discussion

If you find errors in this tutorial, please notify me (see the top of the page). Please note that due to the high volume of feedback I receive, I cannot answer questions to your implementation. Ensure you have read the vogella FAQ as I don't respond to questions already answered there.

8. Links and Literature

http://www.mysql.com MySQL homepage

http://dev.mysql.com/downloads/ Download link for MySQL

http://forums.mysql.com/ MySQL forum