Step-by-step guide for how to install an SQL server on Ubuntu 22.04

Step-by-step guide for how to install an SQL server on Ubuntu 22.04

Installing the SQL server

Firstly update your desktop and install the SQL server

sudo apt-get update

sudo apt-get install mysql-server

Verify that the server is running with the following command

sudo systemctl start mysql.service

sudo systemctl status mysql.service

The result should look like this:

Configure MySQL

You will need to set the password for the root account if you are running the installation on an Ubuntu machine because authentication is deactivated on Ubuntu by default. So as to avoid an error, you’ll need to configure the root account authentication method

sudo mysql

Change the password for root using ALTER USER:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Exit after making this change

mysql> exit

Secure your MySQL root user account

In securing your server, you will need to execute the following command to setup your password policy

sudo mysql_secure_installation

The password policy given will apply to subsequent users accounts created.

The next is to authenticate using the root user’s password:

mysql -u root -p

This command gives the root user access to the MySQL cli, and also to interact directly with the MySQL server.

Then go back to using the default authentication method using this command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This will allow you connect using the sudo mysql command again.

Creating default/new MySQL user and its privileges

It is bad OpSec to use the root account to perform regular day-to-day action on the database. The best option is to create a user account with limited privileges.

This is first done by logging in as root with the following command:

sudo mysql

Alternatively, if you have previously set a password for the root account use this instead:

mysql -u root -p

Next create a new user:

mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

After entering the command, follow the prompt and fill in your username, hostname (localhost if you’re using Ubuntu).

For authentication, you have the options of using auth_socket plugin which provides string security without requiring a password but has a shortcoming of preventing remote connections,authentication_plugin plugin, caching_sha2_password which is the default MySQL plugin, but its shortfall is that some versions of PHP are not compatible with it or mysql_native_password plugin.

mysql> CREATE USER 'jack'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

You can also alter an existing user using:

mysql> ALTER 'jack'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';

Assigning privileges

After creating the user, you can assign it with privileges with the following syntax.

mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';

Using GRANT ALL PRIVILEGES instead will give the user superuser privileges similar to that of root. Such flag will otherwise defeat the purpose of creating a separate user account from root.

The PRIVILEGE variable represents what action a user is allowed to perform. Global privileges can also be granted by replacing database.table with *.

Below we will be granting user permissions to create, modify, delete, insert, select, update and delete data from a table by using CREATE, ALTER, DROP, INSERT, SELECT, UPDATE and DELETE respectively.

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT on *.* TO 'jack'@'hostname' WITH GRANT OPTION;

The WITH GRANT OPTION flag allows the user to grant privileges it has to other users.

Next we will use the FLUSH PRIVILEGES command to empty the cache and free up memory:

mysql> FLUSH PRIVILEGES;

After you can exit the MySQL cli

mysql> exit

You can now log in back using your credentials

mysql -u jack -p

Testing the MySQL server

You can now verify that the MySQL server is running with the following command:

systemctl status mysql.service

Alternatively, you can connect to the MySQL database using the administrative command tool mysqladmin.

sudo mysqladmin -p -u jack version