MacLochlainns Weblog

Fresh install of Ubuntu on my MacBook Pro i7 because Apple said the OS X was no longer upgradable. Time to install and configure MySQL Server. These are the steps to install MySQL on the Ubuntu Desktop.

Installation

1. Update the Ubuntu OS by checking for, inspecting, and upgrading any available updates with the following commands:

   sudo apt update sudo apt list sudo apt upgrade

   sudo apt update sudo apt list sudo apt upgrade

2. Check for available MySQL Server packages with this command:

   apt-cache search binaries

   apt-cache search binaries

   It should return:

   mysql-server - MySQL database server binaries and system database setup mysql-server-8.0 - MySQL database server binaries and system database setup mysql-server-core-8.0 - MySQL database server binaries default-mysql-server - MySQL database server binaries and system database setup (metapackage) default-mysql-server-core - MySQL database server binaries (metapackage) mariadb-server-10.6 - MariaDB database core server binaries mariadb-server-core-10.6 - MariaDB database core server files

   mysql-server - MySQL database server binaries and system database setup mysql-server-8.0 - MySQL database server binaries and system database setup mysql-server-core-8.0 - MySQL database server binaries default-mysql-server - MySQL database server binaries and system database setup (metapackage) default-mysql-server-core - MySQL database server binaries (metapackage) mariadb-server-10.6 - MariaDB database core server binaries mariadb-server-core-10.6 - MariaDB database core server files

3. Check for more details on the MySQL packages with this command:

   apt info -a mysql-server-8.0

   apt info -a mysql-server-8.0

4. Install MySQL Server packages with this command:

   sudo apt install mysql-server-8.0

   sudo apt install mysql-server-8.0

5. Start the MySQL Server service with this command:

   sudo systemctl start mysql.service

   sudo systemctl start mysql.service

6. Before you can run the mysql_secure_installation script, you must set the root password. If you skip this step the mysql_secure_installation script will enter an infinite loop and lock your terminal session. Log in to the mysql monitor with the following command:

   sudo mysql

   sudo mysql

   Enter a password with the following command (password is an insecure example):

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'C4nGet1n!';

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'C4nGet1n!';

   Quit the mysql monitor session:

   quit;

   quit;

7. Run the mysql_secure_installation script with this command:

   sudo mysql_secure_installation

   sudo mysql_secure_installation

   Here’s the typical output from running the mysql_secure_installation script:

   Securing the MySQL server deployment.   Enter password for user root:   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   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 Using existing password for root.   Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : N   ... skipping. 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 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 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 - 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 Success.   All done!

   Securing the MySQL server deployment. Enter password for user root: 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 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 Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : N ... skipping. 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 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 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 - 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 Success. All done!

Configuration

The next step is configuration. It requires setting up the sample sakila and studentdb database. The syntax has changed from prior releases. Here are the new three steps:

1. Grant the root user the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQL root user:

   mysql> GRANT ALL ON *.* TO 'root'@'localhost';

   mysql> GRANT ALL ON *.* TO 'root'@'localhost';

2. Download the sakila database, which you can download from this site. Click on the sakila database’s TGZ download.

   

   When you download the sakila zip file it creates a sakila-db folder in the /home/student/Downloads directory. Copy the sakila-db folder into the /home/student/Data/sakila directory. Then, change to the /home/student/Data/sakila/sakila-db directory, connect to mysql as the root user, and run the following command:

   mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-schema.sql mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-data.sql

   mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-schema.sql mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-data.sql

3. Create the studentdb database with the following command as the MySQL root user:

   mysql> CREATE DATABASE studentdb;

   mysql> CREATE DATABASE studentdb;

4. Create the user with a clear English password and grant the user student full privileges on the sakila and studentdb databases:

   mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Stud3nt!'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost'; mysql> GRANT ALL ON sakila.* TO 'student'@'localhost';

   mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Stud3nt!'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost'; mysql> GRANT ALL ON sakila.* TO 'student'@'localhost';

You can now connect to a sandboxed sakila database with the student user’s credentials, like:

mysql -ustudent -p -Dsakila

or, you can now connect to a sandboxed studentdb database with the student user’s credentials, like:

mysql -ustudent -p -Dstudentdb

MySQL Workbench Installation

sudo snap install mysql-workbench-community

You have now configure the MySQL Server 8.0.