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.
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:
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
5 . Update the
apt package manager to ensure it uses the newly added repository:
6 . Install the MySQL server and client applications using
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:
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
mysql -u root -p
2 . When prompted, enter the password set during the initialization process and confirm by pressing Enter:
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:
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';
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';
ALL privilege allows a user to have full access to a designated database. Other
GRANT commands define more restrictive permissions:
CREATEgrants permission to a user to create new tables or databases
DROPgrants permission to a user to delete tables or databases
DELETEgrants permission to a user to delete rows from tables
INSERTgrants permission to a user to insert rows into tables
SELECTgrants permission to a user for using the
SELECTcommand to read through databases
UPDATEgrants permission to a user to update table rows
The 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:
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;
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.
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
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
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.