MySQLdump as Cron Job
This article is about my experience using mysqldump on MacOS, specifically with MySQL 5.6.22 on MacOS 10.13 (High Sierra) and MySQL 8.0.19 on MacOS 10.15 (Catalina). I expect this information will be very useful across multiple versions of both.
mysqldump is a command line utility included with MySQL that reads a database table and produces a text file that contains all the commands needed to reproduce that table from scratch. This is a great way to move a table from one installation of MySQL to another (version change!) and is a great option for creating a backup copy of your data.
mysqldump may be invoked as follows:
mysqldump -u username -p database tablename > ~/tablename.sqlIf you enter this command in the Unix shell (using the Terminal utility program on MacOS, aka "command line"), you will be prompted for a password. Enter it, and voilá! You have a .sql (text) file!
A "cron job" is a Unix command that is executed automatically and quietly in the background according to a time schedule you create. This article assumes a basic familiarity with setting up cron jobs and editing the crontab file. Many resources for learning about cron are available online.
When I set out to create a cron job to run mysqldump I discovered that the password request was incompatible with running "quietly in the background". I spent hours searching the Internet for a way to have the appropriate password automatically available to the cron task. Eventually I found the solution, and it is a fairly simple solution, but I found nowhere that this solution was neatly presented in one place.
There are basically 3 steps:
Create the MySQL User
The username and password you use with mysqldump (whether from the command line, a shell script, or a cron job) are the username and password for MySQL, and for the specific table(s) you are dumping. They are not your unix shell name and password (unless you have set them to be the same). So the first thing you want to do is create a MySQL user specifically for running mysqldump. Here are the GRANT commands you might invoke ( from the mysql monitor command line tool, NOT your unix shell):
> CREATE USER 'mydumper'@'localhost' identified by 'dumppass';Obviously you will want to specify a username, password, and database that work for your needs.
Note: LOCK TABLE and EVENT must be specified at the database level, not the table levelOK, you've created your mydumper MySQL user with the correct permissions. Great!
> GRANT EVENT, LOCK TABLES ON database.tablename TO 'mydumper'@'localhost';SELECT, TRIGGER, and SHOW VIEW may be specified at the table level
CREATE USER 'mydumper'@'localhost' identified by 'dumppass';
Learn more about GRANTs:
MySQL Options Files
MySQL can look for any of several configuration files. To discover what specific files those are, do these things in the command line:
$ which mysqld(your actual path to mysqld may vary, and your actual $PATH may vary)
The purpose of the above is to ensure that the path to mysqld (in this case /usr/local/mysql/bin ) is in your $PATH, so that simply typing mysqld works as expected. If it is not, you have 3 choices:
Then check to see if any configurations are active:
$ mysqld --print-defaultsGood, none are active. Blank slate. If you see anything else, you'll need to do more exploring.
To be thorough, you should check this as well:
$ mysqldump --print-defaultsNow determine which files mysqldump will check for various configurations/options (these are the same as for mysqld):
$ mysqldump --help --verbose | head -n 15Your specific output may vary. You may need to view more than 15 lines to find this section.
I suggest you examine each file (many or all may not exist at all) to see if there is any content or pre-configured options.
$ cat /etc/my.cnfIn the example above you see that I have already congured ~/.my.cnf and you can probably easily deduce the format. The [mysqldump] means that the content that follows is to be used when mysqldump is run.
This is for educational/demonstration purposes only. If the ~/.my.cnf file actually contained the content shown, then the results of mysqldump --print-defaults would be different.
To be crystal clear, for the purposes of this endeavor, the contents of ~/.my.cnf should be:
The directives in the my.cnf files are the same as would be used on the command line but without the leading dashes
There is a lot more to know about mysql options files, but for the limited purposes of this page, this is enough.
The my.cnf file that you use should be secured by setting the read/write/execute permissions to 400 (ie. readable only by the file owner). And of course mysqldump should run only under that same user, which should be your own unix shell login.
Craft the mysqldump command
When using mysqldump interactively in your unix shell, you would enter something like this:
mysqldump -u username -p database tablename > ~/tablename.sqlActually, test it out right now on the command line. Go ahead, I'll wait right here.
It works, Great! Good job!
If it doesn't, you'll need to do some debugging. Check your $PATH, make sure you are referencing the correct MySQL user and entering the correct password, make sure the database and table actually exist, make sure you are using the actual names and not the generic demonstration names used here, etc.
Depending on what you find, you may need to modify the my.cnf you intend to use.
When crafting for non-interactive use (shell scripts or cron jobs) use this construct instead:
/usr/local/mysql/bin/mysqldump --defaults-file=~/.my.cnf database tablename > ~/tablename.sql
Notice these differences:
Once you have confirmed the integrity of everything so far, add it to your crontab. Notice the initial two housekeeping lines in this sample.
14 23 * * 1 touch ~/tablename.sql
I have created a script to create the crontab code for automating backups of your MySQL databases. Try it out.
© 1997-2022 Jeffrey W Baumann dba LinkedResources. All Rights Reserved. Last Updated December 28, 2021.