Not the answer you need?
Register and ask your own question!

Issues after MySQL 5.6 to 5.7 upgrade

dharterdharter EntrantCurrent User Role Novice
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?

Comments

  • Federico RazzoliFederico Razzoli Contributor Current User Role Patron
    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.
  • dharterdharter Entrant Current User Role Novice
    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 [email protected]% |
    +
    +
    | 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 [email protected]% |
    +
    +
    | 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?
  • Federico RazzoliFederico Razzoli Contributor Current User Role Patron
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.