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
Install mysql-server
- You can install
mysql-server
(from Oracle) ormariadb-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
to0.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;
Featured content: