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.
Table of Contents
- 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
- 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:
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
.cnffiles 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
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
INSERT INTO test_table (name) VALUES ('Peter Jackson');
- 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;