MySQL: Installing and using a database server

Learning how SQL databases work is essential for any backend web developer. In this post you will learn how to install a MySQL server and how to create databases, tables, users, etc.

Install mysql-server
Create a new user and a database
Access from a remote computer
Create a table
Add a record
View records
Update a record
Delete a record

Install mysql-server

You can install mysql-server (from Oracle) or mariadb-server (an open-source fork).
```
# Ubuntu
sudo apt install mysql-server -y
```
Secure your MySQL server by typing the next command and following the instructions:
```
sudo mysql_secure_installation
```

Create a new user and a database

Create a user and his password.

sudo mysql -p -u root

CREATE USER 'user' identified by 'password';

Create a database and grant permissions to ‘user’.

CREATE DATABASE test_db CHARACTER SET 'utf8';
GRANT ALL PRIVILEGES ON test_db.* TO 'user';

Access from a remote computer

If you need to access to your database remotely (without using SSH), you need to change /etc/mysql/mysql.conf.d/mysqld.cnf. This path may be different in your case, check in /etc/mysql for .cnf files that contain next line:
```
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
```
Change 127.0.0.1 to 0.0.0.0, save changes and restart mysql service:
```
sudo service mysql restart
```
Now, (if port 3306 is open) you can access your database from a remote computer using any MySQL client:
```
mysql -h <server-ip> -p -u <user>
```

Create a table

This will create a table with two columns: “id” (integer values that auto increment for every row) and “name” (with text values).
```
USE test_db;
CREATE TABLE test_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name TEXT);
```

Add a record

This will add a new entry in the table (you don’t need to specify an “id” value because of AUTO_INCREMENT option).
```
INSERT INTO test_table (name) VALUES ('Peter Jackson');
```

View records

This will show all columns from all records on “test_table”.

SELECT * FROM test_table;

+----+---------------+
| id | name          |
+----+---------------+
|  1 | Peter Jackson |
+----+---------------+
1 row in set (0,00 sec)

Update a record

This will update a record based on the “id” key.

UPDATE test_table SET name='Joe Jackson' WHERE id=1;

Delete a record

This command deletes a record based on the “id” key.
```
DELETE FROM test_table WHERE id=1;
```