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’.
    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            =
  • Change to, 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;

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;