Copyright © 2008, 2009, 2010, 2011, 2012, 2013 vogella GmbH
Table of Contents
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.
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.
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.
See Ubuntu password reset to learn how to set and reset your password under Ubuntu.
MySQL provides the
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
The default port of the database server is 3306, e.g., the Java JDBC connection string to connect to the iotcloud database would be jdbc:mysql://localhost:3306/iotcloud.
In this exercise you create a new database, a new user and an
table. For this
connect to the MySQL server via the
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', 'email@example.com','http://www.vogella.com', '2009-09-14 10:33:11', 'Summary','My first comment');
Please see SQL Tutorial for the usage of SQL commands.
mysql command line interface
allows you to import existing backups and the
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