MySQL is the most popular multi-user database.
In this tutorial, you’ll learn to
- Install MySQL Server and Client on Ubuntu
- Automate MySQL login
- Create, read, update and delete data (CRUD)
Test Environment
You can do this on any server, but I tested this tutorial with Vagrant 1.8.1. If you want to test it the same way
$ vagrant init ubuntu/trusty32 $ vagrant up $ vagrant ssh
If you have problems running Vagrant on Ubuntu 14.04 host, see my fix.
Install MySQL Server and Client
$ sudo apt-get update $ sudo apt-get -y install mysql-server mysql-client
Give a good password that’s different from any other password. MySQL root is different from system root, as MySQL has its own database users, separate from system users.
You can easily change MySQL password when you forget it (sudo dpkg-reconfigure mysql-server-[tab][tab][tab]).
$ mysql -u root -p Enter password: [...] mysql>
Well done, you have now installed MySQL server and logged into it as MySQL root user.
Create Database and User
mysql> CREATE DATABASE student CHARACTER SET utf8;
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student | +--------------------+ 4 rows in set (0.00 sec) mysql> GRANT ALL ON student.* TO student@localhost IDENTIFIED BY 'dk387KaaJEE-.d//NA';
Use difficult password, as you will soon write it into code or configuration anyway. Don’t just hit keyboard with both hands, type something purposefully complicated.
Use the password (IDENTIFIED BY) when creating user and giving rights (GRANT ALL). Otherwise, a minor typing mistake could create two users: one with password, another with all rights.
Test New Database User
mysql> exit Bye
$ mysql -u student -p Enter password: [..] mysql>
It’s much safer now: a simple mistaek won’t drop all tables from all databases.
mysql> CREATE DATABASE noicant; ERROR 1044 (42000): Access denied for user 'student'@'localhost' to database 'noicant'
Automate MySQL login
You can store MySQL credentials in a configuration file in your home directory.
$ cd $ nano .my.cnf
[client] user="student" password="dk387KaaJEE-.d//NA" database="student"
Now that you use ‘mysql’ command, you are automatically logged in without specifying name or password, and MySQL runs “USE student” for you automatically.
$ mysql mysql>
We’re now ready to try CRUD: Create, Read, Update, Delete.
C: CREATE TABLE
Let’s work in our own database.
mysql> USE student; Database changed mysql> CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(1024)); Query OK, 0 rows affected (0.01 sec)
All tables should have an AUTO_INCREMENT PRIMARY KEY id field. When creating VARCHARS, make them long enough.
mysql> DESCRIBE students; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(1024) | YES | | NULL | | +-------+---------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
C: Create records: INSERT
mysql> INSERT INTO students(name) VALUES ("Tero");
Query OK, 1 row affected (0.00 sec)
You can insert some other values to play with:
mysql> INSERT INTO students(name) VALUES ("Matti");
mysql> INSERT INTO students(name) VALUES ("Maija");
R: Read: SELECT
mysql> SELECT * FROM students; +----+-------+ | id | name | +----+-------+ | 1 | Tero | | 2 | Matti | | 3 | Maija | +----+-------+ 3 rows in set (0.00 sec)
You can limit what you find with WHERE
mysql> SELECT * FROM students WHERE name LIKE 'Ma%'; +----+-------+ | id | name | +----+-------+ | 2 | Matti | | 3 | Maija | +----+-------+ 2 rows in set (0.00 sec)
U: UPDATE
mysql> UPDATE students SET name="Tero Karvinen" WHERE name="Tero";
mysql> SELECT * FROM students; +----+---------------+ | id | name | +----+---------------+ | 1 | Tero Karvinen | | 2 | Matti | | 3 | Maija | +----+---------------+ 3 rows in set (0.00 sec)
D: DELETE
mysql> DELETE FROM students WHERE name="Matti"; Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students; +----+---------------+ | id | name | +----+---------------+ | 1 | Tero Karvinen | | 3 | Maija | +----+---------------+ 2 rows in set (0.00 sec)
Well done, you have now done the whole CRUD for MySQL: Create, Read, Update, Delete.
Well Done!
Now you can install MySQL and create your own databases.
Next, you can try Two Common MySQL Tricks: LAST_INSERT_ID() and Trash.