MySQL is a reliable and mature open-source relational database management system, and its first release dates back to 1995. It has been in constant development since then and is one of the most used database management systems. MySQL exists in different editions, a free and open-source community edition, as well as several commercial licenses offering additional features. The software was initially developed by the company “MySQL AB” and is currently owned and developed by Oracle. The source code of the MySQL server is publicly available on GitHub.
The database management system provides a stand-alone client, allowing users to interact directly with their databases using Structured Query Language (SQL). MySQL is used as database engine for many popular web applications like Drupal, phpBB, Joomla, and WordPress. It is also being used by numerous other popular websites, including Facebook, Twitter, and YouTube, to name a few.
MySQL is also part of the LAMP software stack, an acronym for Linux, Apache (webserver), MySQL, and PHP.
Ready-to-use binary packages of MySQL are available for various operating systems, from MacOS X, Windows to most Linux distributions. This tutorial will teach you how to install MySQL on Ubuntu Focal Fossa (20.04 LTS). For installation instructions on other platforms, refer to the official installation guide provided by MySQL.
Requirements:
- You have an account and are logged into console.scaleway.com
- You have configured your SSH Key
- You have a compute instance running on Ubuntu Focal Fossa (20.04)
- You have sudo privileges or access to the root user.
Oracle provides a .deb package to configure the MySQL repository on your machine automatically.
1 . Download the MySQL apt configuration from the official MySQL developer server:
wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
2 . Install the package using dpkg by running the following command:
dpkg -i mysql-apt-config_0.8.15-1_all.deb
3 . During installation, you are prompted which MySQL product you want to install. You can keep the default configuration. Confirm by pressing OK:
4 . The tool will generate a configuration file for the apt package manager. You can verify its content by running the command cat /etc/apt/sources.list.d/mysql.list. The output should look like the following example:
### THIS FILE IS AUTOMATICALLY CONFIGURED ###
# You may comment out entries below, but any other modifications may be lost.
# Use command 'dpkg-reconfigure mysql-apt-config' as root for modifications.
deb http://repo.mysql.com/apt/ubuntu/ focal mysql-apt-config
deb http://repo.mysql.com/apt/ubuntu/ focal mysql-8.0
deb http://repo.mysql.com/apt/ubuntu/ focal mysql-tools
#deb http://repo.mysql.com/apt/ubuntu/ focal mysql-tools-preview
deb-src http://repo.mysql.com/apt/ubuntu/ focal mysql-8.0
If it does not look like the example above, or if you want to add additional features to MySQL you can reconfigure the configuration file by running dpkg-reconfigure mysql-apt-config.
5 . Update the apt package manager to ensure it uses the newly added repository:
apt update
6 . Install the MySQL server and client applications using apt:
apt install mysql-server mysql-client -y
Once the MySQL server is installed on your system, it needs to be initialized to secure connections to it.
1 . Run the interactive configuration tool:
mysql_secure_installation
2 . You will be prompted the following questions during the configuration:
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: Y <-- Type y or Y and press Enter to confirm
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 <-- Enter 2 to force strong passwords with a password length >= 8, numeric, mixed case, special characters and validation against a dictionary file
Please set the password for root here.
New password: <-- Enter your strong password
Re-enter new password: <-- Repeat your password
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y <-- MySQL has evaluated the strengh of your password in the line above. If you wish to continue with the password provided, type y or Y and confirm by pressing Enter.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y <-- Type y or Y to remove anonymous users from the MySQL server
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y <-- Type y our Y and press Enter to restrict connections to the database server from localhost. If you have need to access your database from a remote machine, enter any other key before confirming by pressing Enter.
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y <-- Type y or Y to remove the MySQL test database then confirm by pressing Enter.
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y <-- Type y or Y to reload the MySQL privilege table to ensure all changes made so far will take effect. Confirm by pressing Enter.
All done!
Your MySQL server application is now secured and ready for production.
As your MySQL server is up and running now, you can connect to it for the first time using the mysql client application.
1 . Run the following command to log into MySQL using the superuser account root:
mysql -u root -p
2 . When prompted, enter the password set during the initialization process and confirm by pressing Enter:
Enter password:
3 . You are now logged into the MySQL shell. The server displays some connection information as well as information about the installed MySQL version. In the last line the mysql> prompt appears. Send your SQL statements from this prompt to the server for execution:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Later in this tutorial, we will store and modify data in a database. Therefore we need to create a new database and verify its creation:
1 . To get an idea of the existing database structure on our MySQL server, we will use the SHOW DATABASES command which returns a list of all existing databases on the server:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
As you can see in the output above, our server currently contains four databases: information_schema, mysql, performance_schema, and sys. These are internal databases in use by MySQL.
2 . Create a new MySQL database called tutorial with the following command. We will use this database to store our data:
mysql> CREATE DATABASE tutorial;
The response from the server looks as follows: Query OK, 1 row affected (0.00 sec) and confirmes that the query has been executed.
2 . To ensure that the database has been created, run the SHOW DATABASES command again:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tutorial |
+--------------------+
5 rows in set (0.00 sec)
As you can see, the output includes one line more - our database tutorial. It has been created successfully.
We do not want to use the superuser account root to manipulate data in our tutorial database for security reasons. Therefore we will create a regular user with restricted permissions.
1 . Add a new user using the CREATE USER command:
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'a_secret_password';
Replace myuser with the login for your new user, the part localhost defines from which host your user is allowed to login. As we have restricted the access to the MySQL server to our local machine, keep this value. Replace a_secret_password with a strong and secure password for the user.
2 . By default, the user has no permissions. Grant permission to the tutorial table so the user is able to make changes to all data stored in this table:
mysql> GRANT ALL PRIVILEGES ON tutorial.* TO 'myuser'@'localhost';
The ALL privilege allows a user to have full access to a designated database. Other GRANT commands define more restrictive permissions:
CREATE grants permission to a user to create new tables or databasesDROP grants permission to a user to delete tables or databasesDELETE grants permission to a user to delete rows from tablesINSERT grants permission to a user to insert rows into tablesSELECT grants permission to a user for using the SELECT command to read through databasesUPDATE grants permission to a user to update table rowsThe syntax for all grant options is the same and can be applied on database or even table level:
mysql> GRANT grant_type ON database_name.table_name TO ‘myuser’@'localhost’;
3 . Once the user is created and permissions are configured, leave the superuser account:
mysql> EXIT;
Now, as we have our table and user ready, it is time to create some tables, fill them with data and update the data.
1 . Reconnect to the MySQL prompt using the myuser user, you created in the previous step:
mysql -u myuser -p
2 . The MySQL prompt displays. Change into the tutorial database you have created in a previous step:
mysql> USE tutorial;
A Database changed message indicated that you have changed into the tutorial database.
3 . Create a new table employees to manage the an inventory of all employees of a fictive company
mysql> CREATE TABLE employees
(
id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
name VARCHAR(150) NOT NULL, # Name of the employee
team VARCHAR(150) NOT NULL, # Team of the employee
birthday DATE NOT NULL, # Birthday of the employee
PRIMARY KEY (id) # Make the ID the primary key
);
4 . Use DESCRIBE to visualize the table structure and to verify everything has been created accordingly:
mysql> DESCRIBE employees;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| team | varchar(150) | NO | | NULL | |
| birthday | date | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
5 . Now as we have the table ready, update it by filling in some fictitious emplyoee data into it. When you look at the query, you notice that the value for ID is not in it. This value will be auto-incremented by MySQL when entering the data:
mysql> INSERT INTO employees ( name, team, birthday) VALUES
( 'Peter Smith', 'DevOps', '2003-07-21' ),
( 'John White', 'Marketing', '1988-06-04' ),
( 'Steve Shining', 'DevOps', '2001-06-04' ),
( 'Charlie Connor', 'Support', '1987-03-03' );
MySQL will check the data and return a status report of your query: Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0.
The SELECT statement can be used to retrieve data stored in an existing table.
1 . Use a wildcard (*) to retrieve a list of all your employees:
mysql> SELECT * FROM employees;
+----+----------------+-----------+------------+
| id | name | team | birthday |
+----+----------------+-----------+------------+
| 1 | Peter Smith | DevOps | 2003-07-21 |
| 2 | John White | Marketing | 1988-06-04 |
| 3 | Steve Shining | DevOps | 2001-06-04 |
| 4 | Charlie Connor | Support | 1987-03-03 |
+----+----------------+-----------+------------+
4 rows in set (0.00 sec)
2 . You can use a WHERE clause on specific columns and rows to filter results by a certain condition. Run the following command to return the names of all employees in the DevOps team that are born before January 1st 2003:
mysql> SELECT name FROM employees WHERE birthday < '2003-01-01' AND team = 'DevOps';
+---------------+
| name |
+---------------+
| Steve Shining |
+---------------+
1 row in set (0.00 sec)
In case one of your employees has left the company, you want to delete his record on your employee database.
1 . Your employee Stehe Shining has left the company. Remove his data from the database using the DELETE statement:
mysql> DELETE FROM employees WHERE name='Steve Shining';
Query OK, 1 row affected (0.09 sec)
2 . Verify if the row has been removed from the table:
mysql> SELECT * FROM employees;
+----+----------------+-----------+------------+
| id | name | team | birthday |
+----+----------------+-----------+------------+
| 1 | Peter Smith | DevOps | 2003-07-21 |
| 2 | John White | Marketing | 1988-06-04 |
| 4 | Charlie Connor | Support | 1987-03-03 |
+----+----------------+-----------+------------+
3 rows in set (0.00 sec)
You also want to keep a record of your employees’ email addresses to make internal mailings easier for the marketing team.
1 . Add a column using an ALTER TABLE...ADD statement. By specifycing, for example, an AFTER clause, you can define the location of the new column:
mysql> ALTER TABLE employees ADD email varchar(150) AFTER name;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
2 . Verify that the table has been modified as wanted:
mysql> DESCRIBE employees;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| email | varchar(150) | YES | | NULL | |
| team | varchar(150) | NO | | NULL | |
| birthday | date | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
3 . In case you have no longer the need for a certain column of your table, remove it using ALTER TABLE...DROP:
mysql> ALTER TABLE employees DROP birthday;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
You can update the MySQL root password of your database server when logged as root onto the MySQL shell:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secret_password';
You have now discovered the basics of managing MySQL databases. For more information, refer to the official documentation.