Monday, June 14, 2021

It Is Time To Double Check Your Backups

    It is time to double check your backups!   Every so often you should randomly check that data you have been backing up.  Set up some fresh bare metal or cloud based fresh metal and get your MySQL instance running.  Simple, easy, and no problem right?

    Well, I hope that is true. But please give it a try, just to put your mind at ease. 

Catastrophe

    I had a call this morning from an old friend who received an automated message overnight that a software build failed. After some digging, the cause of the failure was tracked to an on premises system with hardware issues.  Luckily spare parts were on hand and the server was soon back on line and the build completed.

    Then the friend doubled checked their cron based backup scripts and the logs looked good. A spare machine was fired up and the friend decided to show some new hires how to restore a backup. The new machine was built with a later version of the Linux distribution the company favored. By default that distro enables a stricter version of selinux than used in their shop. Some coffee and RTFM time later, they were able to install their version of MySQL (8.0.24) and have it run.  They added their configuration settings to the configuration file and were ready to reinstall. Right?

Wrong!

    Do to some network configuration issues they could not reach their backup machine and they highly restricted access to this machine.  That is a great practice to secure your data. But in this case they had to wait for their security team to end their Monday morning standup to add the new machine to the allowed server list.  

    Then they only had ten gig of data to restore from a mysqldump.  Just a simple mysql -u root -p < 10gigbackup.sql and we are ready to roll, right? Alas, nope.  The accounts used for this server had not been setup on the server (Please see https://github.com/lefred/mysqlshell-plugins/wiki/user for how to do this easily with the MySQL Shell), were not part of the dump, and that took a little more time to get them setup.

  By now the new hires were referring to the internal documentation on database restorations and making revisions.  

  The restoration with the dump file finished.  Done? Nope.  The old friend randomly checked a few dozen records and was happy for about five minutes.  Then one of the new hires asked about Instance Dump Utility (https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html). The new hire started asking some good questions.

  • Do we need to update our backup methods?
  • Should we be saving the account records with the data?
  • Is the new utility feature rich enough to switch to that as the backup methodology? 
  • Can any of this be automated?
  • How does this fit with our corporate methodology, including security practices? 

    Hopefully you can do a quick check on your backups even if it is for your peace of mind.

Tuesday, June 8, 2021

MySQL Group on Linkedin.Com passes 20,000 Members

     A few weeks ago the MySQL Group on Linked.in passed 20,000 members. For those of you who do not know about Linked.in, it is a business and employment oriented online platform that is mainly used for professional networking, and allows job seekers to post their resumes and employers to post jobs.

    You can find job positing, announcements, relevant blog posts, and more on the MySQL page. Please join us if you already have an account.

Friday, June 4, 2021

Dutch PHP Conference -- Learn about using indexes versus histograms with MySQL

 The Dutch PHP Conference is June 17th and 18th and I am lucky enough to be presenting again this years.  This is an amazing event to attend but this year it is virtual. At 10:00 to 13:00 CEST on the 17th I will be presenting on MySQL 8.0 Indexes, Histograms, and Other Ways to Speed Up Your Queries.  This talk starts with assuming you are new to adding indexes or histograms, do not know how to find your query plan, and have not been introduced to ways of speeding up your database queries.  Now if you have some knowledge in this area, this session will help you build on what you know already. 

Now if you want to learn more on indexes and histograms you can attend this conference too!  And this conference is FREE!  However for workshops, like my session, there are a limited number of virtual seats and you need to preregister

And like all my other session, I love to get your questions as they arise rather than have you wait, just looking for the perfect moment to quiz me, and then losing focus on the talk.  So if you have a question please provide it when you think of it!  

Dutch PHP Conference

Need a quick row number on your query output?

    Recently on Reddit someone asked how to automatically get a row number generated in front of the output from a query.  There is a ROW_NUMBER() function and it very simple to use with Windowing Functions.

    The sample data for this example is very simple.

> SELECT name, qty FROM sample;
+------+-----+
| name | qty |
+------+-----+
| a    |   5 |
| b    |   2 |
| c    |   4 |
+------+-----+
3 rows in set (0.0019 sec)


    There are two way to write Windowing Functions.  The simplest is to add the ROW_NUMBER() function and  OVER() keyword to the query in the middle of the query. 

> select row_number() over () as 'no'
         name, 
         qty 
  from sample;
+----+------+-----+
| no | name | qty |
+----+------+-----+
|  1 | a    |   5 |
|  2 | b    |   2 |
|  3 | c    |   4 |
+----+------+-----+
3 rows in set (0.0011 sec)


    Or define the window at the end of the query. I prefer this version for readability reasons.

> select row_number() over w as 'no'
         name, 
         qty 
from sample 
window w as();
+----+------+-----+
| no | name | qty |
+----+------+-----+
|  1 | a    |   5 |
|  2 | b    |   2 |
|  3 | c    |   4 |
+----+------+-----+
3 rows in set (0.0009 sec)

    So now you know how to add a simple row number to your query output and two ways to format that request.