Buy Percona ServicesBuy Now!

Issues after MySQL 5.6 to 5.7 upgrade

  • Filter
  • Time
  • Show
Clear All
new posts

  • Issues after MySQL 5.6 to 5.7 upgrade

    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
    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;my sql_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:

    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?

  • #2
    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.


    • #3
      You are correct!
      I looked in /etc/mysql/my_i1.cnf and found this:


      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?


      • #4
        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.