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.

Table of Contents

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 the mysql server config file (/etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/mariadb.conf.d/50-server.conf or something similar, check in /etc/mysql for server’s .cnf files) and look for this 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 systemctl restart mysql
  • Now, (if port 3306 is open) you can access your database from a remote computer using any MySQL client: 
    mysql -h <server-ip> -u <user> -p
  • If you want to connect through SSL (with a CA certificate): 
    mysql -h <server-ip> -u <user> --ssl-ca=<CA certificate file>

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;