Automating MySQL Shell util.dumpInstance and util.dumpShema backups With CRON

    The UNIX/Linux cron command is a popular way to schedule repetitive tasks.  There are many examples on the web on how to use cron with mysqldump to backup MySQL Instances.  I am sure many systems have something similar to the following:

Local Host mysql Backup:
0 1 * * * /usr/bin/mysqldump -uroot -ppassword --opt myDatabase > /directory/MyDumpFilename.sql

    But what if you want to use the new, super-slick MySQL Shell dump utilities? Well, the good news is that it is as simple as it was with the old program. Want to use dumpSchema for the world schema?

0 1 * * * /usr/bin/ mysqlsh root@localhost -e "util.dumpSchemas(['world'],'/tmp/w2')"

    Or dump the entire instance?

0 2 * * * /usr/bin/mysqlsh root@localhost -e "util.dumpInstance('/tmp/i2',{ 'showProgress' : 'false'})"  

     Be sure to double check the path of the mysqlsh program with which:

[dstokes@localhost ~]$ which mysqldump
/usr/bin/mysqldump

    I highly recommend using these new utilities as they are not only much faster than the old mysqldump program but they are also more flexible.  And the security conscious of you may have spotted the embedded password in the first example which can be a major security problem.  But mysqlsh can cache you passwords which takes them off the crontab line.

 

Test Drive the MySQL Data Service July 1st or July 8th

 Hands-On-Lab Series

Maximize MySQL Performance with HeatWave and Oracle Cloud

Sessions on July 1st and 8th

10:30 a.m. to 12:30 p.m. CEST

MySQL is the most popular database among the tech community. However, not all the instances perform the same. As systems and applications scale, IT leaders need to constantly improve the performance of their solutions without compromising its security to keep customers' data safe.

HeatWave is a new, in-memory query accelerator for MySQL Database Service available in Oracle Cloud. You will not need any additional skills, tools nor costs to move real-time data from your OLTP database into an OLAP database. Benchmark results show that HeatWave is 2.7x faster and 70% cheaper than Amazon Redshift.  

This series of three hands-on-lab sessions will provide you with the following key takeaways:

• Understand how HeatWave improves MySQL performance without the need of changing a single line of code
• See how working with external data sources using the Oracle Data Integrator Cloud is fast and easy
• Master the process of moving your MySQL data to the MySQL Database Service on Oracle Cloud Infrastructure

• Select your Workshops and Register

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.

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.

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!  

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.