MySQL 8 test environment setup

Having just passed the newly-revised MySQL 2019 Implementation Specialist certification (https://www.youracclaim.com/badges/0f7ee5bd-3f91-4611-8b33-08861f67bb7d/linked_in), I thought I would pass on some of the MySQL information in the form of an introduction for those thinking about creating an environment for experimentation.

My test environment will be created on an Oracle Enterprise Linux (OEL) version 7 update 7 virtual machine running under Oracle VirtualBox version 6.0.10 because I like doing things as cheaply as possible.

There are numerous articles on creating OEL virtual machines, so let me just say that OEL is available freely through Oracle E-delivery after creating a free account and Oracle VirtualBox is free as well and runs quite well under Windows 10.  For the Mac users out there, you will have to find your own virtual machine hosting utility.  Sorry, Mac.

My OEL7 VM is a simple construct of 2 CPU cores, 5GB of memory, and a 100GB dynamically sized HDD.  You should not need that much space, but I tend to size mine large and let them grow if needed.

Once the OEL7 VM is created and running, it needs to be able to access the internet to download the MySQL repository package as well as the actual MySQL software during the installation process.  Also, if you are not installing the software as the root user, the account you will be using will need sudo privileges.

Inside the VM, go to https://dev.mysql.com/downloads/repo/yum/ and download the desired repository RPM package.  An Oracle web account will be needed prior to being able to download.  You can use the one you created for the E-delivery download earlier if necessary.  Install the package with the command: sudo rpm -Uvh <package_name>.

Execute the following command to check the available MySQL versions from the newly-installed repository:

[jonadams@localhost Downloads]$ sudo yum repolist all | grep mysql

mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community disabled
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - disabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 118
mysql-connectors-community-source MySQL Connectors Community - disabled
mysql-tools-community/x86_64 MySQL Tools Community enabled: 95
mysql-tools-community-source MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64 MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview - Source disabled
mysql55-community/x86_64 MySQL 5.5 Community Server disabled
mysql55-community-source MySQL 5.5 Community Server - disabled
mysql56-community/x86_64 MySQL 5.6 Community Server disabled
mysql56-community-source MySQL 5.6 Community Server - disabled
mysql57-community/x86_64 MySQL 5.7 Community Server disabled
mysql57-community-source MySQL 5.7 Community Server - disabled
mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 129
mysql80-community-source MySQL 8.0 Community Server - disabled

You can see which versions and tools are enabled. The enabled ones will typically be
the newest versions.  Install the enabled community server:

[jonadams@localhost Downloads]$ sudo yum install mysql-community-server -y
Loaded plugins: langpacks, ulninfo
mysql-connectors-community | 2.5 kB 00:00:00
mysql-tools-community | 2.5 kB 00:00:00
mysql80-community | 2.5 kB 00:00:00
ol7_UEKR5 | 2.5 kB 00:00:00
ol7_latest | 2.7 kB 00:00:00
(1/4): ol7_UEKR5/x86_64/updateinfo | 50 kB 00:00:00
(2/4): ol7_latest/x86_64/updateinfo | 1.1 MB 00:00:01
(3/4): ol7_UEKR5/x86_64/primary_db | 6.6 MB 00:00:01
(4/4): ol7_latest/x86_64/primary_db | 20 MB 00:00:02
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:8.0.17-1.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.17-1.el7 for package: mysql-community-server-8.0.17-1.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.17-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.17-1.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.17-1.el7.x86_64
---> Package mysql-community-common.x86_64 0:8.0.17-1.el7 will be installed
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
---> Package mysql-community-libs.x86_64 0:8.0.17-1.el7 will be obsoleting
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:8.0.17-1.el7 will be obsoleting
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================
Installing:
mysql-community-libs x86_64 8.0.17-1.el7 mysql80-community 3.0 M
replacing mariadb-libs.x86_64 1:5.5.64-1.el7
mysql-community-libs-compat x86_64 8.0.17-1.el7 mysql80-community 2.1 M
replacing mariadb-libs.x86_64 1:5.5.64-1.el7
mysql-community-server x86_64 8.0.17-1.el7 mysql80-community 415 M
Installing for dependencies:
mysql-community-client x86_64 8.0.17-1.el7 mysql80-community 32 M
mysql-community-common x86_64 8.0.17-1.el7 mysql80-community 589 k

Transaction Summary
=============================================================================================================================
Install 3 Packages (+2 Dependent packages)

Total size: 453 M
Total download size: 415 M
Downloading packages:
No Presto metadata available for mysql80-community
warning: /var/cache/yum/x86_64/7Server/mysql80-community/packages/mysql-community-server-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Public key for mysql-community-server-8.0.17-1.el7.x86_64.rpm is not installed
mysql-community-server-8.0.17-1.el7.x86_64.rpm | 415 MB 00:01:24
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
Package : mysql80-community-release-el7-3.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : mysql-community-common-8.0.17-1.el7.x86_64 1/6
Installing : mysql-community-libs-8.0.17-1.el7.x86_64 2/6
Installing : mysql-community-client-8.0.17-1.el7.x86_64 3/6
Installing : mysql-community-server-8.0.17-1.el7.x86_64 4/6
Installing : mysql-community-libs-compat-8.0.17-1.el7.x86_64 5/6
Erasing : 1:mariadb-libs-5.5.64-1.el7.x86_64 6/6
Verifying : mysql-community-libs-8.0.17-1.el7.x86_64 1/6
Verifying : mysql-community-server-8.0.17-1.el7.x86_64 2/6
Verifying : mysql-community-common-8.0.17-1.el7.x86_64 3/6
Verifying : mysql-community-client-8.0.17-1.el7.x86_64 4/6
Verifying : mysql-community-libs-compat-8.0.17-1.el7.x86_64 5/6
Verifying : 1:mariadb-libs-5.5.64-1.el7.x86_64 6/6

Installed:
mysql-community-libs.x86_64 0:8.0.17-1.el7 mysql-community-libs-compat.x86_64 0:8.0.17-1.el7
mysql-community-server.x86_64 0:8.0.17-1.el7

Dependency Installed:
mysql-community-client.x86_64 0:8.0.17-1.el7 mysql-community-common.x86_64 0:8.0.17-1.el7

Replaced:
mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!

Check the status of the mysqld service:

Start the mysqld service and check the status:

 

[jonadams@localhost Downloads]$ sudo systemctl start mysqld.service

[jonadams@localhost Downloads]$ sudo systemctl status mysqld.service
● mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2019-09-16 13:42:19 MDT; 5min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5098 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5183 (mysqld)
Status: “Server is operational”
Tasks: 39
CGroup: /system.slice/mysqld.service
└─5183 /usr/sbin/mysqld

Sep 16 13:42:13 localhost.localdomain systemd[1]: Starting MySQL Server...
Sep 16 13:42:19 localhost.localdomain systemd[1]: Started MySQL Server.

Get the temporary root password created during the first startup of the MySQL server:

[jonadams@localhost Downloads]$ sudo grep 'temporary password' /var/log/mysqld.log
2019-09-16T19:42:15.986474Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 3dh>C0N!<Tpg

Change the root password as soon as possible by logging in with the generated,
temporary password and set a custom password for the superuser account:

shell> mysql -uroot -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'SemperFidelis1!';
Query OK, 0 rows affected (0.01 sec)

Note – MySQL’s validate_password plugin is installed by default. This will require that passwords contain at least one upper case letter, one lower case letter, one digit, and one special character, and that the total password length is at least 8 characters.

[jonadams@localhost Downloads]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17

Copyright (c) 2000, 2019, 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>

mysql> quit
Bye

Stop the mysqld service and check the status

[jonadams@localhost tmp]$ sudo systemctl stop mysqld.service
[jonadams@localhost tmp]$ sudo systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Mon 2019-09-16 17:45:44 MDT; 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5183 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 5098 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5183 (code=exited, status=0/SUCCESS)
Status: "Server shutdown complete"

Sep 16 13:42:13 localhost.localdomain systemd[1]: Starting MySQL Server...
Sep 16 13:42:19 localhost.localdomain systemd[1]: Started MySQL Server.
Sep 16 17:45:42 localhost.localdomain systemd[1]: Stopping MySQL Server...
Sep 16 17:45:44 localhost.localdomain systemd[1]: Stopped MySQL Server.

Note that the entries are timestamped so at first glance it appears that the
service was started before it was shut down, but according to the timestamps,
it was stopped about 4 hours after it was started.

We now want to install the MySQL shell utility.  It allows you to execute commands in JavaScript, Python, or SQL without special considerations.

[jonadams@localhost lib]$ sudo yum install mysql-shell
Loaded plugins: langpacks, ulninfo
epel/x86_64/metalink | 11 kB 00:00:00
epel | 5.4 kB 00:00:00
mysql-connectors-community | 2.5 kB 00:00:00
mysql-tools-community | 2.5 kB 00:00:00
mysql80-community | 2.5 kB 00:00:00
ol7_UEKR5 | 2.5 kB 00:00:00
ol7_latest | 2.7 kB 00:00:00
(1/2): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(2/2): epel/x86_64/primary_db | 6.8 MB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql-shell.x86_64 0:8.0.17-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================
Installing:
mysql-shell x86_64 8.0.17-1.el7 mysql-tools-community 15 M

Transaction Summary
=============================================================================================================================
Install 1 Package

Total download size: 15 M
Installed size: 50 M
Is this ok [y/d/N]: y
Downloading packages:
mysql-shell-8.0.17-1.el7.x86_64.rpm | 15 MB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-shell-8.0.17-1.el7.x86_64 1/1
Verifying : mysql-shell-8.0.17-1.el7.x86_64 1/1

Installed:
mysql-shell.x86_64 0:8.0.17-1.el7

Complete!

Verify the MySQL shell installation:

[jonadams@localhost lib]$ mysqlsh
MySQL Shell 8.0.17

Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.
MySQL JS >

The default shell interface for the MySQL shell utility is JavaScript.  To switch between the different interfaces, use these commands.  The prompt will change to reflect the current interface.

\js for JavaScript

\sql for SQL

\py for Python

MySQL Shell comes with the option of automatically saving the history of commands, but it is disabled by default. It is a handy feature, so use this process to activate it.

Show settings and look for history.autoSave

MySQL JS > shell.options

MySQL JS > \option --persist history.autoSave=1

You can close and reopen the session and in the new one press the arrow up key to verify that the commands from previous sessions are available.

Switch to SQL mode and connect to the MySQL instance:

MySQL … JS > \sql

MySQL SQL > \c root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': ***************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

Notice that I opted for MySQL shell to save my user account password.  Next time I login, I will not have to type in the password after the connect command.

Now you can submit the usual SQL commands

SQL > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.0006 sec)

Exit from MySQL Shell with “\q” or “\exit”

MySQL … JS > \q

Now that your test environment is set up, I recommend shutting it down and backing it up with the VirtualBox export tool in case your experimentation goes wrong.  It would also be a good time to review the MySQL 8 online documentation.  I recommend concentrating first on backup and recovery commands.

https://dev.mysql.com/doc/

Enjoy!