【MySQL】MySQL 5.6.x 升级到5.7.x 问题处

问题1： 无法执行授权

$  /path/to/mysql    -u root   --socket=/path/to/mysql.sock   

> grant all privileges  on  *.*  to  root@'localhost'  identified by  'MySQL@123'  with grant option;
> grant all privileges  on  *.*  to  root@'127.0.0.1'  identified by  'MySQL@123'  with grant option;

ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50641, now running 50721. Please use mysql_upgrade to fix this error.

曾经升级过数据库，升级完后没有使用mysql_upgrade升级数据结构造成的。

解决方法：

$  /path/to/mysql_upgrade     -u root   --socket=/path/to/mysql.sock   

Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv            OK
mysql.db                   OK
mysql.engine_cost             OK
mysql.event                 OK
mysql.func                 OK
mysql.general_log             OK
mysql.gtid_executed           OK
mysql.help_category           OK
mysql.help_keyword            OK
mysql.help_relation           OK
mysql.help_topic             OK
mysql.innodb_index_stats        OK
mysql.innodb_table_stats        OK
mysql.ndb_binlog_index         OK
mysql.plugin                OK
mysql.proc                 OK
mysql.procs_priv             OK
mysql.proxies_priv            OK
mysql.server_cost            OK
mysql.servers               OK
mysql.slave_master_info         OK
mysql.slave_relay_log_info       OK
mysql.slave_worker_info         OK
mysql.slow_log               OK
mysql.tables_priv             OK
mysql.time_zone              OK
mysql.time_zone_leap_second      OK
mysql.time_zone_name           OK
mysql.time_zone_transition       OK
mysql.time_zone_transition_type   OK
mysql.user                 OK
Upgrading the sys schema.
Checking databases.
lottery.mhq                 OK
sys.sys_config               OK
Upgrade process completed successfully.
Checking if update is needed. 

问题2： 无法执行mysqldump

export LANG="en_US.UTF-8"
mysql_passwd="MySQL@123"
backup_dir="/path/to/mysqlBackup"
dump_opts="-p${mysql_passwd} --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --single-transaction"

$ /path/to/mysqldump   -u root   --socket=/path/to/mysql.sock ${dump_opts}  test   > ${backup_dir}/test_`date +'%Y-%m-%d_%H-%M-%S'`.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)

解决方法：

在升级完成MySQL服务之后重启MySQL，然后重新执行mysqldump命令。

$  sudo  systemctl  restart mysql-server