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.sql
If 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.

This page sets out to be that "one place".

There are basically 3 steps:

  1. Create and configure the appropriate mysql user
  2. Create and configure the appropriate mysql options (my.cnf) file
  3. Craft the correctly syntaxed mysqldump command

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';
Query OK, 0 rows affected (0.01 sec)

> GRANT SELECT, EVENT, TRIGGER, SHOW VIEW, LOCK TABLES ON database.* TO 'mydumper'@'localhost';
Query OK, 0 rows affected (0.00 sec)

> flush privileges;
Query OK, 0 rows affected (0.00 sec)

>
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 level (eg. ON database.* ). If you try to execute the following you will receive an error:
> GRANT EVENT, LOCK TABLES ON database.tablename TO 'mydumper'@'localhost';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
>
SELECT, TRIGGER, and SHOW VIEW may be specified at the table level (eg. ON database.tablename ). Therefore, you may choose to limit the access of mydumper to a specific table, as follows:
CREATE USER 'mydumper'@'localhost' identified by 'dumppass';
GRANT LOCK TABLES, EVENT ON database.* TO 'mydumper'@'localhost';
GRANT SELECT, SHOW VIEW, TRIGGER ON database.tablename TO 'mydumper'@'localhost';

Learn more about GRANTs:
dev.mysql.com/doc/refman/8.0/en/grant.html
www.mysqltutorial.org/mysql-grant.aspx

OK, you've created your mydumper MySQL user with the correct permissions. Great!

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
/usr/local/mysql/bin/mysqld
$ echo $PATH
/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
(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:
  1. add to your $PATH (left as an exercise for the reader)
  2. invoke mysqld using the full path $ /usr/local/mysql/bin/mysqld
  3. $ cd /usr/local/mysql/bin and invoke with $ ./mysqld
If this is all Greek to you, consult any of numerous reference sources.

Then check to see if any configurations are active:
$ mysqld --print-defaults
mysqld would have been started with the following arguments:
$
Good, 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-defaults
mysqldump would have been started with the following arguments:
$
Now determine which files mysqldump will check for various configurations/options (these are the same as for mysqld):
$ mysqldump --help --verbose | head -n 15
.
.
.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
.
.
.
$
Your 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.cnf
cat: /etc/my.cnf: No such file or directory
$ cat /etc/mysql/my.cnf
cat: /etc/mysql/my.cnf: No such file or directory
$ cat /usr/local/mysql/etc/my.cnf
# using ~/.my.cnf instead
$ cat ~/.my.cnf
[mysqldump]
user=mydumper
password=dumppass
$
In 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:
[mysqldump]
user=mydumper
password=dumppass

The directives in the my.cnf files are the same as would be used on the command line but without the leading dashes (eg. --password is simply password). Do not use any spaces around the "=" sign.

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.
$ pwd
/Users/owner
$ ls -la | grep my.cnf
-rw-r--r-- 1 owner staff 55 Dec 28 21:06 .my.cnf
$ chmod 400 .my.cnf
$ ls -la | grep my.cnf
-r-------- 1 owner staff 55 Dec 28 21:06 .my.cnf
$

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.sql
Actually, 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:
  • /usr/local/mysql/bin/mysqldump use the full path to avoid unexpected $PATH deficiencies
  • -u username is removed, since this information is now in your ~/.my.cnf file
  • -p is removed, since this information is now in your ~/.my.cnf file
  • --defaults-file=~/.my.cnf this must be the first option and directs the system to seek information in the specified file. Also, NO SPACES around the "="!
Now try it out (before putting it into a shell script or cron job). You'll need to be removing any old tablename.sql result files to be sure you are creating them fresh.

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
15 23 * * 1 mv -f ~/tablename.sql ~/previous_tablename.sql
17 23 * * 1 /usr/local/mysql/bin/mysqldump --defaults-file=~/.my.cnf database tablename > ~/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.