List of content you will read in this article:
- 1. What will you need?
- 2. Step 1: Install MySQL
- 3. Step 2: allow remote access to the server.
- 4. Step 3: Start MySQL service
- 5. Step 4: Configuring the interfaces
- 6. Step 5: Launch at reboot
- 7. Step 7: Test the MySQL server.
- 8. How to use MySQL
- 9. Wrapping up
MySQL is an open-source relational database that is free to use for anyone around the world. It is commonly installed with the LAMP (Linux, Apache, MySQL and PHP) stack. It uses structured query language (SQL for short) to manage its data and implements the relational model.
Before we get started, let’s go through the prerequisites.
What will you need?
- A server running Ubuntu 18 or Ubuntu 20 operating system
- Basic understanding of Linux commands
- A root user account or a user with root privileges
Step 1: Install MySQL
Before installing the server, updating the package index on your server is recommended. To do this, enter the following:
sudo apt update
You can install the MySQL server using the Ubuntu operating system package manager. This will install MySQL and all its dependencies.
sudo apt install mysql-server
For fresh installations of MySQL, we recommend using the secure installation utility. This should start automatically but if not, enter the following command:
sudo mysql_secure_installation utility
This prompts you to define the MySQL root password and other security-related properties, which includes removing remote access to the root user and resetting the root password.
Step 2: allow remote access to the server.
Iptables are usually turned On, which prevents users from connecting to the MySQL server remotely. If you want to connect to the MySQL database from another machine, you must allow the port from your firewall settings (the default port is 3306).
Note: this is unnecessary if the application that uses MySQL runs on the same server.
sudo ufw enable
sudo ufw allow mysql
Step 3: Start MySQL service
Once the installation is complete, you can start the service by running the following command. If the service is already active, you will be given a message informing you that the service is already up and running.
sudo systemctl start mysql
Step 4: Configuring the interfaces
MySQL does not listen to any remotely accessible interfaces by default. Therefore you will need to make some changes to the configurations file. We will use the nano text editor to make the following changes:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
Enter the following data:
bind-address = 127.0.0.1 ( The default. )
bind-address = aaa.aaa.aaa.aaa ( The IP address of your Public Net interface. )
bind-address = bbb.bbb.bbb.bbb ( The IP address of your Service Net interface. )
bind-address = 0.0.0.0 ( All IP addresses. )
Save and close the configurations file. Press CTRL + x, press y and press ENTER.
Step 5: Launch at reboot
To ensure that the database server launches after a reboot, run the following command:
sudo systemctl enable mysql
Restart the MySQL service:
sudo systemctl restart mysql
Step 7: Test the MySQL server.
MySQL should be running perfectly, regardless of how you installed it. To test this, we should enter the following command.
systemctl status mysql.service
If you see an output similar to the one below, your server is running perfectly:
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2021-04-28 12:56:48 UTC; 6min ago
Main PID: 10382 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 1137)
Memory: 370.0M
CGroup: /system.slice/mysql.service
└─10382 /usr/sbin/mysqld
That’s all for installing the MySQL server on your Ubuntu system. However, in the article's next section, we will go through how to use MySQL basics.
How to use MySQL
Start the MySQL shell.
There is more than one way to work with the MySQL server, but we will focus on the most basic and compatible approach in this section of the article: the MySQL shell.
To enter the MySQL shell, enter the following command from your terminal:
/usr/bin/mysql -u root –p
Now you will be prompted to enter a password. Here you should enter the password that you set up during the installation. If you didn’t set a password, then press Enter.
You should see the MySQL shell prompt starting from mysql>.
Setting the root password.
If you entered the shell without entering a password, or if you would like to change the root password, you can do it using the following steps.
- For versions earlier than MySQL 5.7
UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
- For versions later than MySQL 5.7
UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';
Instead of password, enter your new password. To make the changes to be effective, enter the following command:
FLUSH PRIVILEGES;
Creating a Database
Database servers and databases are very different, even though it’s used interchangeably. MySQL is a database server that tracks databases and controls access to them. Log into the MySQL shell and run the following command to create a database. Be sure to change the testdb name with your database name.
CREATE DATABASE testdb;
To test if the database was created successfully, enter the following command. It’s a query to list all databases.
SHOW DATABASES;
Wrapping up
Congratulations on successfully installing MySQL on your Ubuntu system. We hoped that this article was of help and that you can directly start using MySQL to control your databases.
If you face any problems along the way, let us know in the comments below. We value your feedback, and for any query you have, we will help you.
People also read: