My shop just upgraded a server from MySQL 5.6 to 5.7 and on the surface, everything looks fine. I am able to start/stop MySQL. I’m able to connect to and query databases.
However, I noticed our backup script was failing with the following message:
xtrabackup: recognized server arguments: --server-id=1 --tmpdir=/var/lib/mysql/i1/tmpdir --log_bin=/var/lib/mysql/i1/binlogs/mysql-bin --datadir=/var/lib/mysql/i1/da
ta --innodb_data_home_dir=/var/lib/mysql/i1/innodb/data --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --innodb_log_file_size=64M --innodb_log_group_home_d
ir=/var/lib/mysql/i1/innodb/logs
xtrabackup: recognized client arguments:
190807 12:46:50 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.
190807 12:46:50 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/i1/mysql.sock’ as ‘bac
kup’ (using password: YES).
190807 12:46:50 version_check Connected to MySQL server
190807 12:46:50 version_check Executing a version check against the server…
A software update is available:
190807 12:46:51 version_check Done.
190807 12:46:51 Connecting to MySQL server host: localhost, user: backup, password: set, port: not set, socket: /var/lib/mysql/i1/mysql.sock
Using server version 5.7.25-28-log
Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
This only happens when I run the backup from a script. The script runs as user mysql. I can run innobackupex from the command line using root and the backup is successful. Here is the command line command:
innobackupex --defaults-file=/etc/mysql/my_i1.cnf --user=root --password=xxxxxx /db2/backup/mysql_backups/test_ABC
Here is the very simple backup script I’m using:
#!/bin/bash
CONF=$1
BKUPBASEPATH=$2
OUTPUTLOG=“/home/mysql/backup/backup_$(date +‘%Y%m%d%H%M%S’).log”
output1=$(/usr/bin/innobackupex --defaults-file=$CONF ${BKUPBASEPATH} 2>&1)
echo “${output1}” >> “${OUTPUTLOG}”
I suspect this is some sort of permissions issue, but so far, I’ve been unable to pin it down. Can anyone else shed any light on the issue?
Most probably you use 2 different users to connect MySQL. When you run Xtrabackup as ‘root’ you probably use a specific configuration file, like /root/.my.cnf. When you run it as ‘mysql’ system user you probably use /etc/my.cnf or /etc/mysql/my.cnf.
If I’m right, check which MySQL usernames are specified in these files. They are written in [client] or [xtrabackup] section.
You are correct!
I looked in /etc/mysql/my_i1.cnf and found this:
[xtrabackup]
user=backup
password=xxx
socket=/var/lib/mysql/i1/mysql.sock
xtrabackupuser=backup
xtrabackuppw=xxx
xtrabackupsock=/var/lib/mysql/i1/mysql.sock
If I substitute in root and the root password, the script works fine. So the issue is with the user ‘backup’.
I displayed the grants on a working 5.6 version system and on my upgraded 5.7 version system and they are slightly different.
Version 5.6
mysql> show grants for backup;
±------------------------------------------------------------------------------------------------------+
| Grants for backup@% |
±------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘backup’@‘%’ IDENTIFIED BY PASSWORD ‘*16B6X41FB4238ACXDB9463028B0F19F82X52XB89’
| GRANT SELECT, INSERT, UPDATE, DELETE ON “hobbitmonitor”.“backupcfg” TO ‘backup’@‘%’
| GRANT SELECT, INSERT, UPDATE, DELETE ON “hobbitmonitor”.“backuphist” TO ‘backup’@‘%’
±------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
Version 5.7
mysql> SHOW GRANTS FOR ‘backup’@‘%’;
±-------------------------------------------------------------------------------------+
| Grants for backup@% |
±-------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘backup’@‘%’ |
| GRANT SELECT, INSERT, UPDATE, DELETE ON “hobbitmonitor”.“backupcfg” TO ‘backup’@‘%’
| GRANT SELECT, INSERT, UPDATE, DELETE ON “hobbitmonitor”.“backuphist” TO ‘backup’@‘%’
±-------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
I don’t know if the fact the encrypted password doesn’t show up in 5.7 is the issue or if this is just not displayed in 5.7. I dropped and created the id/grants, but still no joy. Even tried granting ‘process’ to ‘backup’. Any other insights you can share?
As you guessed, MySQL 5.7 doesn’t show passwords. Not even in the logs or in the history of mysql command-line client.
Back to your problem: you didn’t show me how you assign PROCESS privilege. That should do the trick, so probably you don’t do that in the correct way. It should be something like:
GRANT PROCESS ON *.* TO backup'@'%';
A suggestion: specify a hostname or IP for the user backup, instead of ‘%’. It’s more secure.