Tag: mysql 8

How to use MySQL 8.0.4 with a GUI

If you want to have a look on what is about to come in the new version of the popular database and is used to Syntax Highlighting you don’t need to be chained to the Terminal.

Some of you may use tools like MySQL Workbench or Sequel Pro (as of the release of this post both tools had the following error occurring), and even if you are using the Terminal (if you are using an old version of mysql​, like 5.7) you may encounter this error:

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin ‘caching_sha2_password’ cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

The reason for that is because since the 8.0.4 RC release, MySQL now uses SHA-2 Pluggable Authentication. In another words, how the database does authentication now changed.

Graphical User Interface

As of now, the only tool I could verify that it is working is Datagrip. But there is some steps to make sure you can successfully connect to the server. Follow the steps

1. The JDBC Connector

  1. Open the JDBC Connector page. Click on “Development Releases” tab and select your operating system, as of this post 8.0.9 was the latest version.
  2. Select the zip version of the file, if you are using macOS, select “Platform Independent”.
  3. The website it will ask for you to login, you don’t need to login, there is a link on the bottom of the page that says: “No thanks, just start my download.”.
  4. Unzip the zip​ file mysql-connector-java-8.0.9-rc.zip (the name may be different for you if the version is different)
  5. A folder will be created with the name of the compressed file, inside copy the jar file to a location where you can access it later easily, for example, I put mine in ~/workspace/drivers folder.

2. The GUI configuration

  1. Open Datagrip. Go to “File > Data Sources“. A window will open, right click on top of the “MySQL” name and select Duplicate.
  2. A new Driver is added with the name “MySQL [2]”, rename it to “MySQL 8.0”
    Data_Sources_and_Drivers.png
  3. Then, unselect “Use” on “Driver Files” and click on the + sign. Select the jar file you downloaded on the previous section.
    Data_Sources_and_Drivers.png
  4. Click in Apply.

3. Adding the source

  1. On the same window, click on the + sign on the top left. Select “MySQL 8.0
    Screen Shot 2018-02-22 at 14.50.28.png
  2. Fill out the details as you would for a connection: Data_Sources_and_Drivers
  3. Click on “Test Connection“.
  4. If everything worked, just click in “OK” to exit the screen.

From MySQL 8.0.0 to MySQL 8.0.1 – or any other dev milestone

Disclaimer: This post is aimed to you, the curious developer, sys-admin, technologist, whatever-title-you-use. DO NOT run the following lines on production. Not even in a stable environment, do this if you don’t care about the outcome of the current data.

If you want to keep up with the newest MySQL developer milestones I have news for you: there is no upgrade available for milestone versions. The way to go is to remove old version and install new one, according to their website:

Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. For example, upgrading from 8.0.0 to 8.0.1 is not supported, as neither are GA status releases.

So if you, like me, had the 8.0.0 version and want to test the 8.0.1 (alhtough 8.0.3 milestone is already in development) you need to do something like the following (tutorial based on Debian/Ubuntu servers).

Stop your service:

$ sudo service mysql stop

Download Oracle’s repository and install it, as of now this is the current version, you can get the new package here:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.6-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.6-1_all.deb

Clean your old install, you will lose all the data. Be careful, back up is on you!

$ sudo apt-get remove --purge mysql-server mysql-client mysql-common
$ sudo apt autoremove
$ sudo apt-get autoclean
$ sudo apt-get install mysql-server

This is the way to go to test the new features such as Descending Indexes and others. Remember, the new default encoding was changed from latin1 to utf8mb4.

Short feature list:

The complete list is available here.

Roles on MySQL 8.0

This is a blogpost about MySQL 8.0.0, the content here could be subject to changes in the future since this is not a stable release.

This is a feature that will make life the many DevOps and DBAs easier. Believe it or not, a lot of people control access to the database by sharing the same username and password. Which is completely insecure for a number of reasons:

  • If someone is fired, they still have access
  • If you get hacked for using the same password, well, I only can say: good luck

That also means: no more querying to Stack Overflow to get that giant GRANT statement for each user you need to create. (Thank you @mwop for reminding me of this).

Creating a Role

This is a group of privileges that will be assigned to users:

CREATE ROLE 'admin';

You can also create more than one role at once, however if one of them is already created, the whole statement fails and it won’t be created:

CREATE ROLE 'dba', 'developer', 'readonly';

Response:

mysql> CREATE ROLE 'dba', 'developer', 'readonly';
Query OK, 0 rows affected (0.01 sec)

If you try to run:

mysql> CREATE ROLE 'dba';
ERROR 1396 (HY000): Operation CREATE USER failed for 'dba'@'%'

It causes an error because the role already exists.

Granting privileges

Notice you didn’t state the privileges at this point, you need use GRANT to do so, and it works the same way as granting privileges to a user:

For each of the responses you will get something similar to this:

Query OK, 0 rows affected (0.00 sec)

Creating Users

In my example I will add Lisa Simpson as ‘dba’, Millhouse V. Houten as ‘developer’ and Bart Simpson and Homer Simpson as ‘readonly’.

So this is the proposed idea:

USER ROLE
lisa_simpson dba
millhouse_houten developer
homer_simpson readonly
bart_simpson readonly

The generated SQL will be:

After the user is created is time to tell which roles they are using, a role can have more than one user and a user can have more than one role. To attribute users to a role, do the following:

Granting the roles doesn’t mean they will start automatically using them. Remember, a user can have multiple roles, so you need to tell the server which role the user is using:

This way, the default role for that user on that host will be the one used, as shown at the example below:

mysql> SHOW GRANTS FOR millhouse_houten@'localhost';
2 rows in set (0.00 sec)

Result:

+---------------------------------------------------------+
| Grants for millhouse_houten@localhost                   |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `millhouse_houten`@`localhost`    |
| GRANT `developer`@`%` TO `millhouse_houten`@`localhost` |
+---------------------------------------------------------+

Conclusion

This way is simpler to change user permissions in batch, not having to go user by user. If the user is using a different role for any reason, it must be informed before any SQL manipulation.

Source: MySQL 8.0.0 manual.

Update

Previously I had use FLUSH PRIVILEGES when granting permissions to users, this however is not needed because I didn’t manipulate the table users using INSERT, UPDATE, DELETE. In this case I manipulated the creation and grating using the specific commands for it.

Thank you, Paul DuBois, for the reminder.