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 /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;