How come mysqldump into a file doesn’t work within the docker container (with an unintuitive error message)?

When using the mysql:5.7 docker image, I go into the docker container by doing exec -it /bin/sh to try to dump out a backup of the database. I have a volume mounted on /share, which is where I am attempting to save the dump file.

If I do the following:

mysqldump -u user -ppassword database

Then I successfully see a dump of the database being printed on the screen.

If I do the following:

echo "hello world" > /share/dump.sql

Then I successfully see the /share/dump.sql file containing hello world.

But if I try to combine that into:

mysqldump -u user -ppassword database > /share/dump.sql

Then I get a seemingly unrelated error, saying:

Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces

Obviously it’s not a case of missing privileges, since my mysqldump call without saving into a file works. So, what gives?

For anyone wanting to try it out, here’s the relevant compose file:

version: '3.8'

services:
  db:
    image: mysql:5.7
    environment:
      MYSQL_DATABASE: database
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - ./share:/share

volumes:
  db:

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Per mysqldump reference manual on privileges require “And (as of MySQL 5.7.31) PROCESS if the –no-tablespaces option is not used”.

Testing with using --no-tablespaces with:

$ podman run --rm --name mysql57 -e MYSQL_DATABASE=database 
    -e MYSQL_USER=user -e MYSQL_PASSWORD=password 
    -e MYSQL_ROOT_PASSWORD=root -d  mysql:5.7

8411383e46549f4fd028ee27d74b4799f56e4b23b63321e2e445ebd7577bf411

And the exec:

<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="deacb1b1aa9ee6eaefefede6edbbeae8ebea">[email protected]</a>:/# mysqldump -u user -ppassword --no-tablespaces database
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost    Database: database
-- ------------------------------------------------------
-- Server version   5.7.35

/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a8e7e4ecf7ebe0e9fae9ebfcedfaf7fbedfcf7ebe4e1ede6fc95e8">[email protected]</a>@CHARACTER_SET_CLIENT */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d59a99918a969d948794968190878a8690818a87908680998186e895">[email protected]</a>@CHARACTER_SET_RESULTS */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="88c7c4ccd7cbc7c4c4c9dcc1c7c6d7cbc7c6c6cdcbdcc1c7c6b5c8">[email protected]</a>@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="dd92919982899490988287929398e09d">[email protected]</a>@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="91deddd5cec4dfd8c0c4d4ced2d9d4d2dac2acd1">[email protected]</a>@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="632c2f273c252c31262a242d3c28263a3c202b262028305e23">[email protected]</a>@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6b24272f34383a273426242f2e562b">[email protected]</a>@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="88c7c4ccd7dbd9c4d7c6c7dccddbb5c8">[email protected]</a>@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7e2a37333b212431303b433e31323a">[email protected]</a>_TIME_ZONE */;

/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a0f3f1ecffedefe4e59de0efece4">[email protected]</a>_SQL_MODE */;
/*!40014 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e8aea7baada1afa6b7a3adb1b7aba0adaba3bbd5a8a7a4ac">[email protected]</a>_FOREIGN_KEY_CHECKS */;
/*!40014 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="edb8a3a4bcb8a8b2aea5a8aea6bed0ada2a1a9">[email protected]</a>_UNIQUE_CHECKS */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="15565d544754564150474a4650414a56595c505b4128555a5951">[email protected]</a>_CHARACTER_SET_CLIENT */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6f2c272e3d2e2c3b2a3d303c2a3b303d2a3c3a233b3c522f20232b">[email protected]</a>_CHARACTER_SET_RESULTS */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="52111d1e1e13061b1d1c0d111d1c1c1711061b1d1c6f121d1e16">[email protected]</a>_COLLATION_CONNECTION */;
/*!40111 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c89b99849786879c8d9bf58887848c">[email protected]</a>_SQL_NOTES */;

-- Dump completed on 2021-10-03  3:20:12

It’s right, no errors.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0