This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)
This error appears when running mysqldump directly from the command line, exporting the database using a client like MySQL Workbench or if you’re managing the WordPress database through WP-CLI’s export command.
In my case, I encountered the problem when running a routine Python script for a Drupal to WordPress migration client. My script uses WP-CLI to export a database dump file and deploy it to a remote server.
Solutions for fixing the mysqldump process privilege error
The mysqldump command requires at least the following privilege assigned to the user:
SELECT privilege for dumped tables
SHOW VIEW for dumped views
TRIGGER for dumped triggers
LOCK TABLES if you don’t use the --single-transaction option
PROCESS if you don’t use the --no-tablespaces option
The last PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and may be the root source of your problem. You can solve the mysqldump process privilege error in two ways:
Updating the privileges for your database user.
Runing mysqldump with the --no-tablespaces option.
Solution 1: Update the user privileges
Granting the PROCESS privilege for the user is perhaps the simplest option for fixing the mysqldump process privilege error. Keep in mind that this option presents security issues. You should therefore really only use this option for your own local development server installation.
To grant the PROCESS privilege, log in as an administrator user and run the following query:
Node that PROCESS is a global level privilege. It can’t apply to individual databases. Global privileges are either administrative or apply to all databases on your MySQL server. Trying to grant them on individual databases deplays the following error:
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
To grant the privilege to all databases you must use the ON *.* ... syntax.
Solution 2: Use the --no-tablespaces option
If you cannot assign global level privileges to your user, for example, when doing so presents unacceptable security issues, you must specify the --no-tablespaces option when dumping your database.
mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql
What are MySQL tablespaces?
We are usually only concerned with logical database objects when working with databases. However, the data must be physically stored somewhere. This is where tablespaces come in. Tablespaces are physical datafiles stored in the host file system holding data for one or more tables and indexes.
The diagram below provides a handy illustration. It’s from the Oracle Concepts documentation, Introduction to Tablespaces, Datafiles, and Control Files and refers to Oracle databases. Nevertheless, it may help you understand how tablespaces relate to logical database objects and datafiles.
You can therefore use the --no-tablespaces option if you don’t need to dump tablespace information. This may be the case for routine database dumps, for example when exporting databases for WordPress migrations.
About the access mysqldump denied PROCESS privilege error
mysqldump accesses tablespace information in the FILES table. Prior to MySQL 5.7.31 and 8.0.21, your user could run mysqldump without the PROCESS privilege. However, users running mysqldump after the update need PROCESS privileges to access the INFORMATION_SCHEMA.FILES table. Running mysqldump without PROCESS privilege ends up giving you an Access denied error.
Be careful with the PROCESS privilege
According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.
It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as UPDATE user SET password=PASSWORD, may show secrets.
Some hosting providers restrict customers to a single SSL/TLS certificate per socket. (In simple terms, a socket is the combination of IP address and port number.) Since Apache listens to port 80 for non-SSL connections and port 443 for SSL connections on the same IP address, customers usually need a separate IP address for each certificate.
At the same time, you can configure Apache for multiple domains to share a single IP address using virtual hosts. Each virtual host gets its own port and Apache listens to this port, redirecting connections to the appropriate domain.
The combination of the above behaviours can sometimes cause complications when you install a single SSL Certificate on a shared IP address. Secure connections to port 443 of an IP address will be directed to the virtual host and domain assigned to that port. Thus, if you try to make a secure connection to a domain on a shared IP address, Apache will create a socket to the actual domain listening to port 443. Depending on your configuration, this domain may be a default virtual host or one that is explicitly set to listen to port 443.
The possible solutions depend on the types of configurations supported by your hosting provider. These include:
Moving each domain with SSL certificates to its own IP address.
This is a guide for setting up an Apple Mac OS X workstation with SSH key-based authentication to a remote FreeBSD server. I won’t go into any detail about these protocols or try to make a case for using them. If you’re reading this, you probably already have a basic grounding on SSH, SFTP and the implications of SSH key-based authentication. My goal is to outline the steps needed so you can start using key-based authentication on your Mac.
I. Intended audience
The instructions here are aimed at Mac OS X based web developers with at least a moderate level of systems administration knowledge. Most likely you host websites for your clients or employer on *nix servers controlled through a command line interface. You generally work on multiple servers per project. Repeatedly entering secure long random passwords is becoming a hassle. If this sounds familiar, this guide is for you.
I’m on a Mac OS X workstation, currently Yosemite, using the Mac Terminal App to connect via SSH and Panic’s Transmit FTP client to transfer files. The server instructions here are for FreeBSD but you should still find the information useful if you run a Linux-based web server. I expect that you’re familiar with your own environment and have a preferred way of doing things so won’t list every command needed.
The prerequisites for this guide are that you:
already have your server set up to SSH and SFTP with password authentication;
have an account on the server that you use for day-to-day web development;
also have root access to the server.
II. Setting up your Mac workstation and server for SSH Key-Based Authentication
We’ll need to do two main things to get everything working:
Set up your server to accept the key files instead of a password.
Configure your Mac OS X workstation to use SSH key files.
We’ll be moving back-and-forth between the them so for clarity, open up two terminal windows: one will be used for configuring the server, which I’ll refer to as your server terminal and another for configuring your Mac workstation, which I’ll call the Mac terminal.
A. Configure the server
First make sure your server is configured to accept key-based authentication. On the server terminal, open a secure shell to the server as you normally would. Edit the configuration file for your OpenSSH Daemon at /etc/ssh/sshd_config.
Look for the following lines and uncomment or add them:
You may need to return to this file later but for now, save it and restart sshd. On FreeBSD, it’s the command:
# /etc/rc.d/sshd reload
B. Generate the key files on your Mac
Now you need to generate your private and public SSH key files. The private key file will remain on your Mac and you’ll place a copy of the public key file on the server. To generate the key files, switch to your Mac terminal and run:
$ ssh-keygen -t rsa
The -t flag specifies that you’re creating an RSA key.
You’ll be asked to enter a file name for the key. The default for key files on Mac OS X is /Users/username/.ssh/id_rsa.pub. While you can simply select the default, it might be a good idea to create a key file specifically for each project. That way, if your Mac workstation is ever compromised, you minimise the risk of access to servers used for past projects where your account has been inadvertently left active.
ssh-keygen will also ask if you want to set a password for the key. If you set a password, you’ll have to enter it every time you try to authenticate with the key file. 1
The result should be two new key files in your .ssh directory:
The public key: /Users/username/.ssh/yourkeyfile_rsa.pub
The private key: /Users/username/.ssh/yourkeyfile_rsa
If you use a service like GitHub you may also see your GitHub key files already in that directory.
C. Copy the public key to the server
Transfer your public key to the .ssh directory of the server account you use to do your day-to-day web development. There are two ways to do this on a standard-install Mac OS X workstation. You can follow these steps:
Go to the server terminal and cd into the account’s home directory.
Create a .ssh directory (~/.ssh/).
Switch back to the Mac’s terminal and copy the public key text using the command:
$ pbcopy < ~/.ssh/yourkeyfile_rsa.pub
Move back to the server terminal and create an authorized_keys file within the .ssh directory.
Paste the public key text into your authorized_keys file. (Remember that the pbpaste command won’t work on a non-Mac operating system.)
Alternatively, you can run the following command on your Mac’s terminal:
However it’s done, you should end up with a file ~/.ssh/authorized_keys for the developer account on the server.
It’s worth making this clear so there’s no confusion: you are giving the public key, yourkeyfile_rsa.pub, to the developer account that you’d previously been using to connect using a password. You should not be giving it to the root account. Make sure you put the authorized_keys file in the developer account’s home folder. Also make sure you don’t accidentally copy and paste the private key, yourkeyfile_rsa.
D. Test authentication to the remote server over SSH
Make sure everything worked by checking if you can connect to the server without having to supply a password. Log out of your existing connection on the server terminal and try connecting again:
The -i flag should be the path to the private key file we generated in step B. Also, if you set a key password, you will be asked to supply it before you can access the key.
You should see your server motd and terminal prompt after pressing enter.
E. Optional: disable password authentication
Since you’ve gone to the trouble of setting up SSH key-based authentication, you may want to disable password authentication after you’ve successfully testing the connection. Uncomment or add the following lines in /etc/ssh/sshd_config:
Finally, restart sshd:
# /etc/rc.d/sshd reload
Make absolutely sure that everything works before disabling password authentication, otherwise you may find yourself without any way to remotely connect to your server.2 Unscheduled trips to the data centre are no fun. (Can you tell I’m writing from experience?)
F. Optional: use a ssh config file
An option to save yourself some typing is to put the server and key details into an ssh config file. On your Mac terminal, create a config file at /Users/username/.ssh/config with the following:
This will let you login just by typing:
III. Setting up the Transmit FTP client for SSH Key-Based Authentication
After configuring SSH key-based authentication on your server, the SFTP service will start rejecting your login attempts unless you supply your key. Setting up Panic’s Transmit FTP client with key-based authentication is simple but it can be a little buggy.
In Transmit, delete your old connection details and add new connection settings 3
Leave the password blank
From the menu, select Favourites > Import SSH Key File
Or click the key picker, which is the key icon to the right of the password box
Import your private key file yourkeyfile_rsa
Connect with these new settings and the SFTP server will grant you access.
If you try this process with a password-encrypted key, Transmit will give you the error: “The file is not in a supported format.” This is a bug and you’ll have to apply a workaround. Panic’s customer support sent me the following instructions:
If the private key is passphrase encrypted (as yours is), or if it lives in a directory other than ~/.ssh/, there are a few extra steps needed to get up and running.
Instead of using the key picker, edit your ~/.ssh/config file and add the following:
Then set the site’s Password field to your key’s passphrase and try connecting.
Alternatively, you can try this method:
First, add the keyfile and password to your keychain:
ssh-add -K path/to/.ssh/yourkeyfile_rsa
Now add the connection details to Transmit but do not enter the password or set a key file.
Transmit will connect using the credentials in you keychain
In addition to being able to conveniently log in to your various servers, you will now also be able to use Transmit for your regular web development file transfers.
It’s tempting to think that setting a password for your key negates the whole point of this exercise. After all, if you have to enter a password to authenticate with the key file, you might as well keep the existing model of password authentication to the server, right? Not quite and here’s why: the ssh key is a cryptographically secure way of authenticating to your server and is exactly what you want for something accessible to the open internet. While manually typing in the key itself every time you want to open a connection isn’t practical, SSH key-based authentication handles key exchange automatically.
The key password protects against a different threat. It prevents unauthorised use of the key itself. This has a lower threat profile because it’s stored on your own private workstation. It therefore can be something that’s easier to remember and type.
Remember to backup your keys and store the backups in a secure location. If you lose your keys and have also disabled password authentication, you won’t be able to access the server without physical access or help from your hosting technical support.
I’ve found that editing a saved connection sometimes doesn’t work. This might be a bug in Transmit. Deleting the old connection and creating a new one seems to always work.
Privacy & Cookies Policy
Traffic analytics cookies help me improve my website. They don't give me any personal information about you but feel free to opt-out by adjusting the settings below.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.