I'm experiencing an issue with MySQL 8 on my VPS, where users with standard privileges are unable to leverage mysqldump. Specifically, the VP plugin is unable to perform this action.

Upon consulting with support, I was informed that as of MySQL 8.0.21, regular users require additional options or database user privileges to download databases.

For more information, please refer to the MySQL 8.0.21 release notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-security

As a non-native English speaker, I'm struggling to grasp the root cause of the issue and the necessary steps to resolve it.

Is it possible to configure MySQL to grant these privileges by default to all users, including future ones? If so, how can this be achieved in ISP6?

Alternatively, is the issue indeed related to the plugin, and should I reach out to the developer for assistance?

Lastly, I'd like to clarify that we're discussing server/site users, rather than database users, correct?

The issue stems from a security enhancement in MySQL 8.0.21, which restricts the ability of regular users to perform mysqldump operations. This change affects all users, including those created in the future, unless explicitly granted the necessary privileges. The root cause lies in the new default setting for the mysqldump command, which now requires the LOCK TABLES privilege or the SELECT privilege on the mysql database.

To resolve this, you can grant the LOCK TABLES privilege to all users by running the following SQL command: GRANT LOCK TABLES ON *.* TO 'username'@'%'. However, this approach requires manual intervention for each user and doesn't address future users. A more scalable solution involves creating a custom MySQL role that includes the necessary privileges and assigning it to all users. In ISP6, you can achieve this by creating a new role with the required privileges and assigning it to the users.

Regarding the plugin, it's possible that the issue is related to the way it interacts with MySQL. If the plugin relies on the mysqldump command, it may need to be updated to accommodate the new security restrictions. Reaching out to the developer for assistance may be necessary to resolve any compatibility issues.

To clarify, we're discussing database users, not server or site users. Database users are entities that interact with the MySQL database, whereas server or site users are entities that interact with the web server or website.

Have the ISP control panel and MySQL been upgraded to their latest iterations, ensuring optimal performance and security?

It's essential to note that mysqldump operates independently of WP plugins, as it doesn't require direct access to them. Instead, it leverages PHP commands to execute its functions. Moreover, to run mysqldump, you need to have SSH access, which provides a secure channel for remote execution.

Now, could you please enlighten me about the specific error message you're encountering with the plugin? Is it a fatal error, a warning, or perhaps a deprecated function notice?

When encountering issues with the plugin, it's essential to troubleshoot by invoking mysqldump with the --no-tablespaces flag, thereby bypassing potential tablespace-related bottlenecks. If problems persist, a viable workaround involves cherry-picking the PHP code and manually iterating through it to identify the pain points. Alternatively, you can opt for a more hands-on approach by creating a database dump via the control panel.

Another strategy is to explore alternative plugins that might offer more robust functionality and better compatibility with MySQL 8.0.21, which was released on July 13, 2020. It's astonishing that, despite being in the market for years, the plugin still fails to properly leverage mysqldump, highlighting the need for more rigorous testing and quality assurance.