Perhaps you’ve never used a database system before or maybe you’ve used one as a user but have never had any need to set up one from scratch. Or perhaps you’ve decided to discover what all the fuss is about database systems in general. Or maybe you’ve used MySQL only as a developer never seeing how to setup and configure the server.
In this bloge note, I present a short introduction to MySQL in the general SQL interface sense (traditional MySQL). Not only will you see how MySQL 8 is setup, you will also be introduced to some of the basics of the SQL interface, which is necessary and indeed required to fully manage a MySQL server. That is, the new shell, X protocol, X DevAPI, and the features that build on it but do not offer a complete mechanism for managing the server; you will need to continue to use SQL commands for those tasks.
So, although MySQL 8 offers an excellent NoSQL interface for both applications and interactive sessions, you still need to know to use the SQL interface. Fortunately, I present the basics in a short primer on how to use MySQL. Let’s begin with a brief foray into what MySQL is and what it can do for us.
Getting to Know MySQL
MySQL is the world’s most popular open source database system for many excellent reasons. First, it is open source, which means anyone can use it for a wide variety of tasks for free. Best of all, MySQL is included in many platform repositories this makes it easy to get and install. If your platform doesn’t include MySQL in the repository (such as aptitude), you can download it from the MySQL web site.
The Oracle Corporation owns MySQL. Oracle obtained MySQL through an acquisition of Sun Microsystems, which acquired MySQL from its original owners, MySQL AB. Despite fears to the contrary, Oracle has shown excellent stewardship of MySQL by continuing to invest in the evolution and development of new features as well as faithfully maintaining its open source heritage. Although Oracle also offers commercial licenses of MySQL - just as its prior owners did in the past - MySQL is still open source and available to everyone.
Is Open Source Really Free?
Open source software grew from a conscious resistance to the corporate property mind-set. Richard Stallman is credited as the father of the free software movement who pioneered a licensing mechanism to help protect ownership of software and yet make the use of the software and to some degree its revision free to all. The goal was to reestablish a community of developers cooperating with a single imperative: to guarantee freedom rather than restrict it.
This ultimately led to the invention of some cleverly worded (read legally binding) licensing agreements that permits the code to be copied and modified without restriction, states that derivative works (the modified copies) must be distributed under the same license as the original version without any additional restrictions. One such license (created by Stallman) is called the GNU Public License (GPL). This is the license that is used by Oracle to license MySQL and as such it is indeed free for anyone to use.
However, GPL and similar licenses are intended to guarantee freedom to use, modify, and distribute; most never intended “free” to mean “no cost” or “free to a good home.” To counter this misconception, the Open Source Initiative (OSI) formed and later adopted and promoted the phrase open source to describe the freedoms guaranteed by the GPL license. For more information about open source software and the GPL, visit www.opensource.org.
MySQL runs as a background process (or as a foreground process if you launch it from the command line) on your system. As with most database systems, MySQL supports structured query language (SQL). You can use SQL to create databases and objects (using data definition language; DDL), write or change data (using data manipulation language; DML), and execute various commands for managing the server.
How Do I Connect to MySQL?
We have already seen a brief look at the new MySQL Shell for connecting to and working with MySQL servers, the AdminAPI to configure an InnoDB Cluster, and the X DevAPI to access with data. However, there is another client that has been around in MySQL for decades. It is an application named mysql, which enables you to connect to and run SQL commands on the server. It is interesting that this MySQL client was originally named the MySQL monitor but has long since been called simply the “MySQL client,” terminal monitor, or even the MySQL command window.
New Default Authentication
Prior to MySQL version 8.0.4, the default authentication mechanism used an authentication plugin called the mysql_native_password plugin, which used the SHA1 algorithm. This mechanism was fast and did not require an encrypted connection. However, since the National Institute of Standards and Technology (NIST) suggested that they should stop using the SHA1 algorithm; Oracle has changed the default authentication plugin in MySQL version 8.0.4 to the cachin_sha2_password plugin.
The consequences of this change should not be an issue to any organizations that install MySQL 8.0.4 but may be a concern for those upgrading to 8.0.4 or those who have older installations of MySQL. The biggest issue is that the older client utilities, such as the mysql client from version 5.7, may not be able to connect to newer installations of MySQL 8.0.4 or later.
Although you can change your MySQL 8.0.4 to use the older authentication mechanism, it is not recommended and you should upgrade all your client tools to 8.0.4 or later to work with the latest versions of MySQL.
If you would like to learn more about the changes including why Oracle made the change and the advantages for users, see this link.
To connect to the server using the MySQL client (mysql), you must specify a user account and the server to which you want to connect. If you are connecting to a server on the same machine, you can omit the server information (host and port) because they default to localhost on port 3306. The user is specified using the
-u) option. You can specify the password for the user on the command, but the more secure practice is to specify
-p), and the client will prompt you for the password. If you do specify the password on the command line, you will be prompted with a warning encouraging you to not use that practice.
Using the mysql client on the same machine without the
--host (or -h) and --port option does not use a network connection. If you want to connect using a network connection or want to connect using a different port, you must use the loopback address. For example, to connect to a server running on port 3307 on the same machine, use the command
mysql -uroot -p –h127.0.0.1 --port=3307. Listing 1 shows examples of several SQL commands in action using the mysql client.
$ mysql -uroot -proot -h 127.0.0.1 --port=3307 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.11 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> CREATE DATABASE greenhouse; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE greenhouse.plants (plant_name char(50), sensor_value int, sensor_event timestamp); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO greenhouse.plants VALUES ('living room', 23, NULL); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM greenhouse.plants; +-------------+--------------+--------------+ | plant_name | sensor_value | sensor_event | +-------------+--------------+--------------+ | living room | 23 | NULL | +-------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> SET @@global.server_id = 106; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye
Listing 1 Commands Using the mysql Client
In this example, you see DDL in the form of the
CREATE DATABASE and
CREATE TABLE statements, DML in the form of the
SELECT statements, and a simple administrative command to set a global server variable. Next you see the creation of a database and a table to store the data, the addition of a row in the table, and finally the retrieval of the data in the table. Notice how I used capital letters for SQL command keywords. This is a common practice and helps make the SQL commands easier to read and easier to find user-supplied options or data.
A great many commands are available in MySQL. Fortunately, you need master only a few of the more common ones. The following are the commands you will use most often. The portions enclosed in <> indicate user-supplied components of the command, and […] indicates that additional options are needed.
CREATE DATABASE<database_name>: creates a database
USE <database>: sets the default database (not an SQL command)
CREATE TABLE <table_name> [...]: creates a table or structure to store data
INSERT INTO <table_name> [...]: adds data to a table
UPDATE [...]: changes one or more values for a specific row
DELETE FROM <table_name> [...]: removes data from a table
SELECT [...]: retrieves data (rows) from the table
SHOW [...]: shows a list of the objects
Although this list is only a short introduction and not a complete syntax guide, there is an excellent online MySQL reference manual that explains every command (and much more) in greater detail. You should refer to the online MySQL reference manual whenever you have a question about anything in MySQL. You can find it at this link.
One of the more interesting commands shown allows you to see a list of objects. For example, you can see the databases with
SHOW DATABASES, a list of tables (once you change to a database) with
SHOW TABLES, and even the permissions for users with
SHOW GRANTS. I find myself using these commands frequently.
If you think that there is a lot more to MySQL than a few simple commands, you are correct. Despite its ease of use and fast start-up time, MySQL is a full-fledged relational database management system (RDBMS). There is much more to it than you’ve seen here. For more information about MySQL, including all the advanced features, see the online MySQL reference manual.
How to Get and Install MySQL
The MySQL server is available for a variety of platforms including most Linux and Unix platforms, Mac OS X, and Windows. As of this writing, MySQL 8 was not a GA release and as such only offered as a development milestone release (DMR). DMRs are an excellent way for you to try out new versions and features before they are released as GA. Generally, non-GA releases are considered developmental or in the case of early release candidates such as MySQL 8.0.4, a release candidate. Thus, you should not install and use DMR releases on your production machines.
To download GA releases of MySQL 8, visit web-site and click Community, then MySQL Community. You can also click on the link near the bottom of the downloads page named Community (GPL) Downloads, then click MySQL Community Server . This is the GPLv2 license of MySQL. The page will automatically detect your operating system. If you want to download for another platform, you can select it from the dropdown list.
The download page will list several files for download. Depending on your platform, you may see several options including compressed files, source code, and installation packages. Most will choose the installation package for installation on a laptop or desktop computer. Figure 1 shows an example of the various download options for macOS platforms.
Figure 1 Download page for macOS
One of the most popular platforms is Microsoft Windows. Oracle has provided a special installation packaging for Windows named the Windows Installer. This package includes all the MySQL products available under the community license including MySQL Server, Workbench, Utilities, and all of the available connectors (program libraries for connecting to MySQL). This makes installing on Windows a one-stop, one-installation affair. Figure 2 shows the download page for the Windows installer.
Figure 2 Download page for Windows Installer
However, you should note that some of the more advanced features and some of the plugins that also are in a developer milestone release (DMR) state may not be included in the Windows Installer. Thus, you should consider installing by using the server package. We see these below the Windows Installer download link in Figure 2. You can choose either the Windows Installer 32- or 64-bit installation. Note that the package may be nothing more than a .zip file containing the server code. In this case, you may need to either run the server from the unzipped folder or do a local, manual install.
Fortunately, as MySQL 8 matures, more packaging options will become available allowing you to use a semi-automated installation mechanism. Let’s see one of those in action. In this scenario, we will install MySQL 8 on a macOS Sierra machine. In this case, I have downloaded the file
mysql-8.0.11-macos10.13-x86_64.dmg, which is a compressed file containing a package installation program named
mysql-8.0.11-macos10.13-x86_64.pkg for macOS. Once I launch the installer, the first step is agreeing to the license. Figure 3 shows the license agreement panel of the installation dialog.
Figure 3 License agreement
The license shown is the GPLv2 license for the community edition. You can read the license and when ready, click Continue. You will see an acceptance dialog open, which will give you another chance to. When you’re ready to accept the license, click Accept. Figure 4 shows the license acceptance dialog.
Figure 4 Accept license
The next panel displays the setup or installation type. Early releases such as this version may not show any installation types to choose from. If you run the Windows Installer, you will see several options. For most platforms, the default installation type is all you will need to get started. Figure 5 shows the installation type panel. When ready, click Install.
Figure 5 Installation type
The installation may ask you to authorize the installation and once done, it will proceed rather quickly installing MySQL in the
/usr/local/mysql folder (e.g., on Sierra).
If this is the first time you’ve installed MySQL 8, you will see a dialog that displays the default password for the root account. This was a change made in MySQL 5.7, which eliminated anonymous accounts and made server installations more secure. You should take note of this password, as it is a general random collection of characters and symbols that you won’t be able to guess. Figure 6 shows one such example dialog.
Figure 6 Root password notice
Figure 7 shows how you can recover this dialog on macOS from the notification center if you, like me, tend to dismiss dialogs without fully reading them.
Figure 7 Root password notice in macOS notification center
Once complete, you will get a completion dialog, which you can safely dismiss. Finally, you will be asked whether you want to keep the installation file (the .dmg) or delete it. If you are experimenting with MySQL 8 or think you may want to install it some other place, do not delete the file.
As you may have surmised, you need to change the root password as your first action after installation. Doing so is easy. Just open the MySQL client (mysql) and issue the following SQL statement. Because we installed the server in the default location, we can start the client with only the user and password prompts like this:
mysql -uroot -p. The client will prompt you for the password.
If you get a message that you cannot connect to the server, it may mean the server has not been started. You can start the server on macOS with the following command.
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
Okay, now that we have the MySQL 8 server installed, we can begin configuring the server for use. You could install the MySQL Shell at this point, but we will explore how to install the MySQL Shell in more detail in my new articles.
Configuring and Managing Access to MySQL
Now that you know how to install MySQL, let’s briefly discuss how to configure MySQL and how to grant access to the server (and databases) to others as well as how to setup the X Plugin (the key component to enable the document store). We begin with a look at the configuration file used to define the behavior and configure options in MySQL.
The primary way to configure start-up options and variables in MySQL is accomplished using a text file named my.cnf (or my.ini on Windows). This file is normally located on Posix systems in the
/etc folder. For example, on macOS, the file is named
/etc/my.cnf. Listing 2 shows the first few dozen lines from a typical MySQL configuration file.
# Example MySQL config file for small systems. # # This is for a system with little memory (<= 64M) where MySQL is only used # from time to time and it's important that the mysqld daemon # doesn't use much resources. # # MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 16K max_allowed_packet = 1M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 1024K ... innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_log_files_in_group = 2 slow-query-log general-log ...
Listing 2 MySQL Configuration File Excerpt
Note that we have settings grouped by section defined using square brackets
. For example, we see a section named
[client], which is used to define options for any MySQL client that reads the configuration file. Likewise, we see a section named
[mysqld], which applies to the server process (because the executable is named mysqld). Note that we also see settings for basic options like port, socket, and so forth. However, we also can use the configuration file to set options for InnoDB, replication, and more.
I recommend that you locate and browse the configuration file for your installation so you can see the options and their values. If you encounter a situation in which you need to change an option - say to test the effect or perhaps to experiment - you can use the
SET command to change values either as a global setting (affects all connections) or a session setting (applies only to the current connection).
However, if you change a global setting that is also in the configuration file, the value (state) will remain only until the server is rebooted. Thus, if you want to keep global changes, you should consider placing them in the configuration file.
On the other hand, setting a value at the session level could be beneficial for a limited time or may be something you want to do only for a specific task. For example, the following turns off the binary log, executes a SQL command, and then turns the binary log back on. The following is a simple but profound example of how to perform actions on a server that participate in replication without having the.
SET sql_log_bin=0; CREATE USER 'hvac_user1'@'%' IDENTIFIED BY 'secret'; SET sql_log_bin=1;
For more information about the configuration file and how to use it to configure MySQL 8 including using multiple option files and where the files exist on each platform, see the section, “Using Option Files” in the online MySQL reference manual.
Creating Users and Granting Access
There are two additional administrative operations you need to understand before working with MySQL: creating user accounts and granting access to databases. MySQL can perform both with the
GRANT statement, which automatically creates a user if one does not exist. But the more pedantic method is first to issue a
CREATE USER command followed by one or more
GRANT commands. For example, the following shows the creation of a user named hvac_user1 and grants the user access to the database room_temp:
CREATE USER 'hvac_user1'@'%' IDENTIFIED BY 'secret'; GRANT SELECT, INSERT, UPDATE ON room_temp.* TO 'hvac_user1'@'%';
A Note About Security
It is always a good idea to create a user for your application that does not have full access to the MySQL system. This is so you can minimize any accidental changes and also to prevent exploitation. For example, it is recommended that you create a user with access only to those databases in which you store (or retrieve) data.
Also be careful about using the wildcard % for the host. Although it makes it easier to create a single user and let the user access the database server from any host, it also makes it much easier for someone bent on malice to access your server (once they discover the password).
The second command allows access to databases. There are many privileges that you can give a user. The example shows the most likely set that you would want to give a user of a sensor network database: read (SELECT), add data (INSERT), and change data (UPDATE). See the online MySQL reference manual for more about security and account access privileges.
The command also specifies a database and objects where to grant the privilege. Thus, it is possible to give a user read (SELECT) privileges to some tables and write (INSERT, UPDATE) privileges to other tables. This example gives the user access to all objects (tables, views, and so on) in the room_temp database.
As mentioned, you can combine these two commands into a single command. You are likely to see this form more often in the literature. The following shows the combined syntax. In this case, all you need to do is add the
IDENTIFIED BY clause to the GRANT statement. Cool!
GRANT SELECT, INSERT, UPDATE ON room_temp.* TO 'hvac_user1'@'%' IDENTIFIED BY 'secret';
Next, let’s see how to configure the server for use with the document store; to be more specific by installing the X Plugin.
Configuring the Document Store
The last thing you want to do before exploring the MySQL Document Store is to ensure the X Plugin is installed. If you installed MySQL on Windows, and you chose to enable the Enable X Protocol/MySQL as a Document Store, you can skip this step. However, other platforms may require configuring the server for use with the document store.
To enable the X Protocol on older MySQL servers, we need to install the X Plugin. The X Plugin is named MySQLX and is easily installed with the following command. The INSTALL PLUGIN command takes the name of the plugin (mysqlx) and the name of the shared library. By convention, shared libraries are named the same as the plugin with the .so suffix (Windows machines use .dll).
INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
You can check to see what plugins are enabled using the following command. You will see all plugins installed and their current state. Note that we see the X Plugin in the list as enabled.
mysql> SHOW PLUGINS \G *************************** 1. row *************************** Name: keyring_file Status: ACTIVE Type: KEYRING Library: keyring_file.so License: GPL *************************** 2. row *************************** Name: binlog Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL ... *************************** 43. row *************************** Name: mysqlx Status: ACTIVE Type: DAEMON Library: mysqlx.so License: GPL 43 rows in set (0.00 sec)
That’s all there is to it. Once enabled, your server will communicate with the X Protocol to the MySQL Shell or any other system, service, or application that uses the X Protocol.
If there is a need to uninstall the X Plugin, you can do so with the following command:
UNINSTALL PLUGIN mysqlx;
In the following section, I take a longer tour of the MySQL server, to show how to use basic SQL commands.