Ubuntu install MySQL: installation on Ubuntu 20.04

Step-by-step instructions: configure MySQL on Ubuntu 20.04¶

You can install MySQL on Ubuntu 20.04 using the Advanced Package Tool (APT) package management system. After installation, you must install and configure MySQL before you can use it in production. In this context, you must in particular define the root password and access for external clients. We show you in our tutorial, step by step, how to do it.

Step 1: Update the Package Index¶

Firstly, it is advisable to update the list of packages. To do this, run the following command:

Step 2: Install MySQL Server¶

Now install the MySQL server package with APT :

$ sudo apt install mysql-server

To verify that the server is running, you can start it manually with the command systemctl :

$ sudo systemctl start mysql.service

Step 3: Configure MySQL¶

After installation, MySQL does not yet meet the recommended security standards. To remedy this, MySQL offers a script which modifies the server protection settings. For example, it takes care of configuring the root password, removing anonymous users and limiting remote access.

However, certain precautions must be taken for the script to run correctly. Indeed, the application wants to modify the password of the root account, which is disabled by default under Ubuntu. To avoid an error, you must adapt the root user authentication method.

Launch the MySQL command prompt:

Use the command ALTER USER to set a password for the root administrator:

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

Close the MySQL prompt:

Run the security script:

$ sudo mysql_secure_installation

To authenticate as root, enter the following information:

Once the script is finished, you can change the default authentication method again:

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

This will allow you to log in again with the command sudo mysql.

Step 4: Create a MySQL User¶

Installing MySQL creates a root user who has full privileges for the MySQL server and has complete control over all databases, tables and users. For greater security, it is therefore advisable to create a user with limited rights.

To do this, call the MySQL command prompt:

If the authentication method is a password, use the following expression:

Now create a new user for MySQL:

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

Instead of “username”, enter your username and instead of “host”, enter the name of your host. If you are running Ubuntu locally, write localhost. The expression “WITH authentication_plugin” is optional. The “auth_socket” plugin has strong security settings and does not need a password to connect.

If nothing else is specified, MySQL uses the “caching_sha2_passwor” plugin for authentication. However, some versions of PHP are not compatible with the latter. Instead, you can use the older but proven “mysql_native_password” plugin:

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

Or use the “ALTER” function for an existing user:

mysql> ALTER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

Step 5: Assign rights¶

Now determine what privileges the new user should have. The general syntax is as follows:

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

Multiple privileges are separated by a comma. To apply rights overallreplace “database.table” with an asterisk (“*”) each time.

In the following example, we give a user the right to create (CREATE), modify (ALTER), delete (DROP) databases and insert (INSERT), select (SELECT), (UPDATE) and delete (DELETE) data in a table).

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

With the “WITH GRANT OPTION” option, the user can also grant their rights to other people.

Now clear the cache with “FLUSH PRIVILEGES”:

mysql> FLUSH PRIVILEGES;

You can then close the MySQL command prompt:

Log in with your new username:

$ mysql -u username -p

Step 6: Test MySQL¶

Check that MySQL is running correctly, for example with System Manager Systemd:

$ systemctl status mysql.service

You can also connect to the MySQL database. The following command connects to MySQL and displays the server version:

$ sudo mysqladmin -p -u username version