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'
IDENTIFIED BY 'XXX123!!!'
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.
Monday, July 27, 2020
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)
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.
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.
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.
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.
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.
Summary
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
LIMIT 4;
+--------------------+
| 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
SELECT
JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,2)) AS trimmed FROM countryinfo
LIMIT 4;
+---------+
| 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,
UNSIGNED, DATE, TIME, DATETIME, CHAR, or JSON.
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.
CREATE TABLE xdemo (j JSON,
x INTEGER,
key((json_value(j, '$.id')))
);
JSON_VALUE() finds a specified scalar JSON value in JSON data and returns it as a SQL value.
Examples
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
LIMIT 4;
+--------------------+
| 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
SELECT
JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,2)) AS trimmed FROM countryinfo
LIMIT 4;
+---------+
| 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,
UNSIGNED, DATE, TIME, DATETIME, CHAR, or JSON.
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.
CREATE TABLE xdemo (j JSON,
x INTEGER,
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.
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 Planet.mysql.com for blogs from other Oracle MySQL Teams.
And if you have questions please ask away on forums.mysql.com and mysqlcommunity.slack,com. I monitor the Certification board on the forums if you have questions
DBA Certification
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 Planet.mysql.com for blogs from other Oracle MySQL Teams.
And if you have questions please ask away on forums.mysql.com and mysqlcommunity.slack,com. I monitor the Certification board on the forums if you have questions
The Exam Topics
DBA Certification
Subscribe to:
Posts (Atom)