Wednesday, June 30, 2021

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

    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.