Monday, July 27, 2020

Which Account Belongs to Whom or a GCOS Field Equivalent for MySQL Accounts

Last week I received an urgent email asking to move the time of  my performance review up a few hours. And the email sender was not in my management structure and was someone unfamiliar to me. Being a former  Xerox employee, I checked my others emails for notice of a reorganization.  Then I realized the person in question was seeking the other David Stokes at Oracle.  Yup two of us with the same name at the same company. Whew!

Coincidentally MySQL 8.0.21 added a new ability that allows you to store information about an account similar to the way the GCOS field is supposed to be used for in the UNIX/Linux world. Back in time many decades ago, account names were limited in length and the GCOS field was populated with the account's user name, office room number, office phone number, and some other relevant information.  This was the way the system administrator could contact the individual using the account directly. 

The mysql.user table has evolved to 51 fields but only as of 8.0.21 did you have a way to record who was actually using the account.  Or let others know why the accounts exists or other meta data about the account.

For an example I created an account and added a comment about the account.

CREATE USER 'dstokes'@'localhost' 
IDENTIFIED by 'S3cr3t!'  
COMMENT '{ "user" : "Dave"  }';

And the information is stored in a column named User_attributes

select User_attributes from user WHERE User='dstokes'\G
*************************** 1. row ***************************
User_attributes: {"metadata": {"comment": "{ \"user\" : \"Dave\"  }"}}
2 rows in set (0.0010 sec)

or you can use the word ATTRIBUTE in the place of the keyword COMMENT.  We do use 'comment' someplace else in the world of Structured Query Language so I would personally prefer ATTRIBUTE in this case.

CREATE USER 'jack'@'localhost' 
ATTRIBUTE '{ "test" : "yes" }';

Cool, eh?

ALTER USER supporst both keywords.

alter user 'jack'@'localhost' 
ATTRIBUTE '{ "foo" : "Example" }';
SELECT User_attributes from user where User='jack';
| User_attributes                                 |
| {"metadata": {"foo": "Example", "test": "yes"}} |

And please note that reusing a key updates the content. Here I reuse 'foo' and the contents are updated.

alter user 'jack'@'localhost' 
ATTRIBUTE '{ "foo" : "Example revised" }';

SELECT User_attributes from user where User='jack';
| User_attributes                                         |
| {"metadata": {"foo": "Example revised", "test": "yes"}} |
1 row in set (0.0005 sec)

Please remember that the JSON data type column is limited to a gig of data so you can not go too crazy recording information.  But please do start adding some account meta data to help identify your accounts.

Oracle MySQL Virtual Event: Upgrading to MySQL 8.0

Be sure to register for the July 29th  Oracle MySQL Virtual Event: Upgrading to MySQL 8.0

Join MySQL's first virtual conference and discover how upgrading to MySQL 8.0 will improve your application performance.  Agenda for the day:

8:00 – 9:00am PDT         Best Practice Tips | Upgrading to 8.0 (LeFred)

9:00 – 10:00am PDT       MySQL 8.0 Through the Eyes of the MySQL Support Team (Megha)

10:00 – 11:00am PDT     Customer Insights from UC Irvine

11:00 – 12:00pm PDT     MySQL 8.0: Indexes, Histograms and Other Ways to Speed Up Your Queries (Dave Stokes)

12:00 – 1:00pm PDT       Transforming Your Application with MySQL 8.0 (Michael Marx)

Tuesday, July 21, 2020

New Logical Backup and Restore Utilities in the MySQL Shell

The MySQL Shell or mysqlsh version 8.0.21 comes with three new utilities to perform logical backups and restore. They were designed to make it easier to move your 5.7 or 8.0 data to the new MySQL Data Service but also work well by themselves. They feature compression, the ability to show the progress of the activity, and can spread the work over multiple threads.

The util.dumpInstance() utility will backup the entire MySQL Instance, util.dumpSchemas() lets you determine which schemas (or databases) to backup, and util.loadDump() is the restoration tool.

Backing Up Instances

util.dumpInstance("/tmp/instance",{ "showProgress" : "true" })
<some output omitted for brevity>
1 thds dumping - 100% (52.82K rows / ~52.81K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed         
Duration: 00:00:00s                                                                                        
Schemas dumped: 4                                                                                          
Tables dumped: 26                                                                                          
Uncompressed data size: 3.36 MB                                                                            
Compressed data size: 601.45 KB                                                                            
Compression ratio: 5.6                                                                                     
Rows written: 52819                                                                                        
Bytes written: 601.45 KB                                                                                   
Average uncompressed throughput: 3.36 MB/s                                                                 
Average compressed throughput: 601.45 KB/s   

The above was performed on an old laptop with a spinning disk, limited ram, and running the latest Fedora.  I have used these utilities on much bigger instances and have found the performance to be outstanding. 

This utility and the others featured in this blog have a log of options and I suggest setting a 'pager' to read through the online help with \h util.dumpInstance.

Schema Backup

I created a quick test database named demo with table named x (don't you love the creativity here) filled with about one million records of four INTEGERS plus an INTEGER primary key.  And remember the output below is from a ten plus year old laptop.

JS > util.dumpSchemas(['demo'],"/tmp/demo")
Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Preparing data dump for table `demo`.`x`
Writing DDL for schema `demo`
Writing DDL for table `demo`.`x`
Data dump for table `demo`.`x` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `demo`.`x` will be written to 1 file
1 thds dumping - 100% (1000.00K rows / ~997.97K rows), 577.69K rows/s, 19.89 MB/s uncompressed, 8.58 MB/s compressed
Duration: 00:00:01s                                                                                                 
Schemas dumped: 1                                                                                                   
Tables dumped: 1                                                                                                    
Uncompressed data size: 34.44 MB                                                                                    
Compressed data size: 14.85 MB                                                                                      
Compression ratio: 2.3                                                                                              
Rows written: 999999                                                                                                
Bytes written: 14.85 MB                                                                                             
Average uncompressed throughput: 20.11 MB/s                                                                         
Average compressed throughput: 8.67 MB/s                 

That is impressive performance.  And yes you can back up multiple schemas at one time by putting their name in the JSON array in the first argument.

And Restoring

The best backups in the world are useless unless you can restore from them.  I did a quick rename of the table x to y and then restored the data.

Be sure to have local_infile set to "ON" before proceeding.

JS > util.loadDump("/tmp/demo")
Loading DDL and Data from '/tmp/demo' using 4 threads.
Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `demo`
Executing DDL script for `demo`.`x`
[Worker003] demo@x@@0.tsv.zst: Records: 999999  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                        
1 chunks (1000.00K rows, 34.44 MB) for 1 tables in 1 schemas were loaded in 20 sec (avg throughput 1.72 MB/s)
0 warnings were reported during the load.


These three utilities are very fast and powerful tools for keeping your data safe.  Maybe mysqldump has seen it's day.  And these three utilities and the clone plugin are proof that you can quickly save, copy, and restore you data faster than ever.

Monday, July 13, 2020

JSON_VALUE() now in MySQL 8.0.21

MySQL 8.0.21 was released today and one of the many new features is the JSON_VALUE() function. The main motivation is to ease index creation values from JSON data but there is more to it.

JSON_VALUE() finds a specified scalar JSON value in JSON data and returns it as a SQL value.


I will use the mysql_x example database data for examples.   So let us start with getting the life expectancy data.

SELECT JSON_EXTRACT(doc, "$.demographics.LifeExpectancy") AS raw 
FROM countryinfo 
| raw                |
| 78.4000015258789   |
| 45.900001525878906 |
| 38.29999923706055  |
| 76.0999984741211   |

That is great information if not exactly human eye friendly.

We can use JSON_VALUE() to make it a little easier for humans

JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,2)) AS trimmed FROM countryinfo 
| trimmed |
|   78.40 |
|   45.90 |
|   38.30 |
|   76.10 |

And it can be very useful in a WHERE clause. In this example there is no RETURNING clause.

SELECT doc->"$.Name" 
FROM countryinfo 
WHERE JSON_VALUE(doc, "$.demographics.LifeExpectancy" 
     RETURNING DECIMAL(6,3)) > 80.1;
| doc->"$.Name" |
| "Andorra"     |
| "Japan"       |
| "Macao"       |
| "San Marino"  |

The optional RETURN clause will cast your data as FLOAT, DOUBLE, DECIMAL, SIGNED,

There are even on empty and on error clauses.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'               
        DEFAULT 'No last name found' ON ERROR) AS "Last Name";
| Last Name          |
| No last name found |

And of course it can be used to help define indexes.

 key((json_value(j, '$.id'))) 

MySQL 8.0 Certifications

     Recently the MySQL 8.0 Developer Certification became available joining the MySQL 8.0 Developer Certifications.  I have recently seen posts on various social media sites asking what is on the exam, is there an exam guide, and what do you 'really need to know' to pass the exam.

   Both tests are very tough.  Neither is going to be passed by rote memorization as many of the questions require analysis of system output before trying to adjust for the desired result.  There are no true or false questions and no essays.  What you will get is a series of questions written by subject matter experts that range from testing basic conceptual knowledge to very tough assessments of how to fix problems.  BTW the exam software lets you mark questions for later review and it does help to bypass the tougher questions for later review at the end of the test.

  Are the question tricky?  No, not really.  They really require in depth knowledge of MySQL 8.0.  If you have ever interviewed someone for a DBA job you know there are certain points of knowledge (pain points) that a candidate MUST have to be trusted with your precious data.  Imagine going to your physician and having to identify which body part is a knee and which part is an elbow.  I personally would trust anyone with either the 8.0 DBA or 8.0 Developer certification to know their stuff.

  Certification guide? Nope, sorry. Back in the 5.0 days there was a team effort to produce a comprehensive exam guide.  But 5.0 was a long time ago and there was an effort back in the 5.6 days for a resurrected guide that did not come to fruition. And having written a technical book, I can assure you that any such work is like writing in sand on a busy holiday vacation spot beach as the content is constantly changes and the community is adding or dropping tools continually.

   I highly recommend Oracle's classes like MySQL For Developers and from regular reviews of the course materials on my part tell me that the curriculum team  does a fantastic job conveying the complex material way in an easy to consume package of literature and exercises.  I still recommend practicing the exercises for several weeks after the class before taking the exam.

  Exam crams?  Do not waste you money.  The 'dumps' I have examined in the past are either poor third-hands recitations of poor interpretations of what was on a test or bad copies from the manual. 

How to Have a Good Chance to Pass the Exam

  Review the exam topics below, using it as a check list.  Go to the MySQL Manual enter and read the sections for that area. Make sure you understand any examples and please do try them on your own scratch server.  Please write down notes in a notebook if you think having a consolidated review tool is handy for you. 

  Also check the MySQL Server Team blog and for blogs from other Oracle MySQL Teams.

  And if you have questions please ask away on and mysqlcommunity.slack,com.  I monitor the Certification board on the forums if you have questions

The Exam Topics 

DBA Certification 


  • Configure client connections to the server
  • Understand how MySQL stores data
  • Understand how InnoDB stores data and logs
  • Configure buffers and caches
  • Understand and use the Data Dictionary


  • Create user accounts and roles
  • Use authentication plug-ins
  • Control user and role permissions
  • Recognize common security risks
  • Secure MySQL server connections
  • Provide password and login security
  • Secure the MySQL host environment
  • Prevent SQL injection attacks
  • Encrypt MySQL data
  • Configure MySQL Enterprise Firewall

Query Optimization

  • Examine how MySQL optimizes queries
  • Analyze queries with MySQL Enterprise Monitor
  • Create indexes to improve server performance
  • Monitor and understand index statistics

High Availability Techniques

  • Explain how replication provides high availability and scalability
  • Configure replication
  • Explain the role of the binary log in replication
  • Configure multisource replication
  • Explain the role of replication threads
  • Monitor and troubleshoot replication
  • Describe MySQL InnoDB cluster and Group Replication
  • Configure a MySQL InnoDB cluster
  • Perform an InnoDB cluster recovery

Connectors and APIs

  • Choose between connectors for a given application
  • Demonstrate connector use, management and configuration
  • Retrieve data from the database by using a connector
  • Handle special values
  • Secure credentials and connections

MySQL Schema Objects and Data

  • Design, create, and alter views
  • Store and process string data
  • Store and process numeric data
  • Store and process temporal data
  • Store and process spatial data

Query Optimization

  • Use indexes for optimization
  • Analyze queries for optimization
  • Rewrite queries for optimization

JSON and Document Store

  • Create and store JSON documents
  • Process data in JSON documents
  • Explain application development with NoSQL and XDevAPI
  • Create and access a document store
  • Use MySQL Shell to access document stores

Data-driven Applications

  • Use prepared statements
  • Set SQL Modes to change MySQL behavior
  • Handle and interpret errors and warnings
  • Display query results
  • Aggregate and summarize data
  • Advanced Report Generation


  • Control transactions in SQL
  • Control transactions in applications
  • Resolve consistency problems with isolation levels
  • Understand locking mechanisms within MySQL

MySQL Stored Programs

  • Create and execute stored routines
  • Explain the programming constructs used in stored routines
  • Create and execute triggers
  • Schedule database operations

Server Installation and Configuration

  • Install and use the MySQL server and client programs
  • Identify the files and folders created during installation
  • Start and stop MySQL
  • Upgrade MySQL
  • Configure MySQL by using options and option files
  • Configure MySQL variables
  • Launch multiple MySQL servers on the same host

Monitoring and Maintenance

  • Configure and view MySQL log files
  • Monitor MySQL processes and status
  • Configure MySQL Enterprise Audit
  • Use MySQL Enterprise Monitor to view activity in MySQL
  • Monitor database growth and explain capacity planning
  • Troubleshoot problems with locked resources

Backups and Recovery

  • Distinguish between the different types of backup
  • Implement a backup strategy
  • Backup and restore data with MySQL Enterprise Backup
  • Use mysqldump and mysqlpump to perform logical backups
  • Explain when and how to use raw file backups
  • Back up the binary log