Author: Gabriela D'Ávila Ferrara

How to reset your `root` password on your MySQL server

How to reset your `root` password on your MySQL server

You don’t need this tutorial if you have access to the root user or another one with SUPER and GRANT privileges.

The following instructions works for MySQL 5.7. You will need to stop the MySQL server and start it with mysqld_safe with the option skip-grant-tables:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root mysql

If you get an error on start, chances are there is no folder created for the mysqld_safe executable to run, on my tests I was able to solve by doing:

sudo mkdir /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld

And then trying to start the mysqld_safe process again.

After this, the MySQL console will pop up, and you need to set up a new password for root. The second line is necessary due to a MySQL bug #79027:

UPDATE mysql.user SET authentication_string=PASSWORD('mypassword') WHERE User='root';
UPDATE mysql.user SET plugin="mysql_native_password" WHERE User='root';

Once finished, kill all MySQL processes and start the service again:

ps aux | grep mysql
sudo kill -9 [pid]
sudo service mysql start

Done, you have reset the root password! Make sure to keep it safe this time around!

See something wrong in this tutorial? Please don’t hesitate to message me through the comments or the contact page.

A small dive into the MySQL 8.0 X-DevAPI

A small dive into the MySQL 8.0 X-DevAPI


What is the X-DevApi? From there is a definition of the X-DevAPI and its features in the following paragraphs:

The X DevAPI is the common client-side API used by all connectors to abstract the details of the X Protocol. It specifies the common set of CRUD-style functions/methods used by all the official connectors to work with both document store collections and relational tables, a common expression language to establish query properties such as criteria, projections, aliases, and a standard set of additional database management features for handling things like transactions, indexes, etc.

The fact that most of these features share the same format and API between connectors, makes the X DevAPI a perfect fit for modern polyglot development environments such as microservices, and the fact that they are based on a well-documented format allows advanced users to extend client-side implementations and build new middleware components or extensions tailor-made for their use case.

With MySQL 8.0, the X-DevAPI can be used either with the MySQL Shell, or with the MySQL Connectors that supports the X-Protocol. For this blogpost I will be showing you code using the Node.js driver as interface with it.

As of the moment of writing of this post, the latest version for the driver (available on npm) is the 8.0.11.

Hands-on, the relational way

You can use the X-DevAPI to use it in a more “relational” way, consider a table teams from the worldcup schema:



Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
name varchar(255) NO NULL
players json YES NULL

Notice that the field players is a JSON and it can be NULL.

Returns on a browser (data clipped):

Or on your terminal:

Extra padding on strings

This looks handy, specially if you are working as a document store (which I will talk in a bit), however there are some issues with JSON and VARCHAR fields for now:

  1. VARCHAR fields are getting padded. Take a look at the third line, where it is “Brazil” actually has trailing spaces. Notice on the terminal is how far off the closing quote is.
  2. JSON does work, but a bit in a limited way, in this case, it is only bringing the result set because the column is set to be possibly NULL, on the other hand if you execute the following you will have trouble:
Error on NOT NULL for JSON columns

See how the error is in a string parser that tries to concatenate whatever to it. On my tests usually is empty space, I had zeroes padded on the right too (yes zero on a string), problem 1 looks like is causing problem 2.

They are aware of it, and apparently it will be fixed on a future release.

Hands-on, the document store way

MySQL 8.0 has been marketed extensively as an alternative for NoSQL, although you can actually use JSON with it, there are some caveats to the feature.

I restructured data to be inside a collection instead of a table.

To query it now, you do the following:

Something feels wrong to me with this approach of having to write strings instead of dealing with JavaScript objects inside the .find() method, using the “literal” word for OR and AND. I think there is room for improvement here. At this point I am sorry to say this looks more like as an hasty ORM put together than a real document store interface.

By the way, problem 1 and problem 2 don’t happen here:

Result set for a query

Should I use it now?

I would recommend to wait a bit more. The current version is the first General Availability version, which means next one will have bug fixes and more stability added to it. This definitely is a nifty way for those using Node.js to be able to manipulate data inside MySQL more “natively”, with that in mind, I still feel that I am actually writing SQL to be able to use it the “document store” way.


You can actually use the X-DevAPI on Google Cloud Functions.

You need to add the @mysql/xdevapi to package.json and put the code above inside a “wrapper” that looks like this:

Keep in mind that .execute() doesn’t return a promise, but rather receives a callback function to do your data processing of each individual row. This could be a bit annoying for you.

Image header of the post belongs to Pexel. And yes, I know the code shown is not SQL or JavaScript 🙂

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, 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/, 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 (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”
  3. Then, unselect “Use” on “Driver Files” and click on the + sign. Select the jar file you downloaded on the previous section.
  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.