Mysql Refuses to Start

Question

On HOST OS RHEL 6.5, I upgraded mysql server from 5.1 to 5.7.9. And when i try to start it refuses and give me these messages in Logs

2016年08月01日T07:29:42.939712Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2016年08月01日T07:29:43.106871Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016年08月01日T07:29:43.107788Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.14) starting as process 530 ...
2016年08月01日T07:29:43.111259Z 0 [Note] InnoDB: PUNCH HOLE support available
2016年08月01日T07:29:43.111311Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016年08月01日T07:29:43.111323Z 0 [Note] InnoDB: Uses event mutexes
2016年08月01日T07:29:43.111357Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2016年08月01日T07:29:43.111390Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016年08月01日T07:29:43.111439Z 0 [Note] InnoDB: Using Linux native AIO
2016年08月01日T07:29:43.111687Z 0 [Note] InnoDB: Number of pools: 1
2016年08月01日T07:29:43.111830Z 0 [Note] InnoDB: Using CPU crc32 instructions
2016年08月01日T07:29:43.112928Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016年08月01日T07:29:43.120425Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016年08月01日T07:29:43.122567Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016年08月01日T07:29:43.135210Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016年08月01日T07:29:43.150778Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016年08月01日T07:29:43.150900Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016年08月01日T07:29:43.275761Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016年08月01日T07:29:43.276475Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016年08月01日T07:29:43.276514Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016年08月01日T07:29:43.277645Z 0 [Note] InnoDB: 5.7.14 started; log sequence number 2519139
2016年08月01日T07:29:43.279034Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2016年08月01日T07:29:43.279734Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160801 10:29:43
2016年08月01日T07:29:43.279734Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016年08月01日T07:29:43.282155Z 0 [Warning] System table 'plugin' is expected to be transactional.
2016年08月01日T07:29:43.282599Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016年08月01日T07:29:43.282687Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certif icate and private key
2016年08月01日T07:29:43.282880Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016年08月01日T07:29:43.282973Z 0 [Note] IPv6 is available.
2016年08月01日T07:29:43.283011Z 0 [Note] - '::' resolves to '::';
2016年08月01日T07:29:43.283051Z 0 [Note] Server socket created on IP: '::'.
2016年08月01日T07:29:43.284349Z 0 [Warning] Failed to open optimizer cost constant tables
2016年08月01日T07:29:43.284662Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
2016年08月01日T07:29:43.284766Z 0 [ERROR] Aborting
2016年08月01日T07:29:43.284803Z 0 [Note] Binlog end
2016年08月01日T07:29:43.284854Z 0 [Note] Shutting down plugin 'ngram'
2016年08月01日T07:29:43.284886Z 0 [Note] Shutting down plugin 'ARCHIVE'
2016年08月01日T07:29:43.284895Z 0 [Note] Shutting down plugin 'partition'
2016年08月01日T07:29:43.284919Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2016年08月01日T07:29:43.284927Z 0 [Note] Shutting down plugin 'MyISAM'
2016年08月01日T07:29:43.284937Z 0 [Note] Shutting down plugin 'CSV'
2016年08月01日T07:29:43.284964Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2016年08月01日T07:29:43.284972Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016年08月01日T07:29:43.284996Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016年08月01日T07:29:43.285002Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016年08月01日T07:29:43.285011Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016年08月01日T07:29:43.285016Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016年08月01日T07:29:43.285020Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016年08月01日T07:29:43.285044Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016年08月01日T07:29:43.285051Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016年08月01日T07:29:43.285055Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016年08月01日T07:29:43.285078Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016年08月01日T07:29:43.285084Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016年08月01日T07:29:43.285089Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016年08月01日T07:29:43.285112Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016年08月01日T07:29:43.285118Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016年08月01日T07:29:43.285122Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016年08月01日T07:29:43.285127Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2016年08月01日T07:29:43.285150Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2016年08月01日T07:29:43.285156Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016年08月01日T07:29:43.285161Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016年08月01日T07:29:43.285183Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016年08月01日T07:29:43.285190Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016年08月01日T07:29:43.285194Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016年08月01日T07:29:43.285217Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016年08月01日T07:29:43.285224Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016年08月01日T07:29:43.285228Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016年08月01日T07:29:43.285251Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2016年08月01日T07:29:43.285258Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016年08月01日T07:29:43.285262Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2016年08月01日T07:29:43.285284Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2016年08月01日T07:29:43.285290Z 0 [Note] Shutting down plugin 'InnoDB'
2016年08月01日T07:29:43.285351Z 0 [Note] InnoDB: FTS optimize thread exiting.
2016年08月01日T07:29:43.285514Z 0 [Note] InnoDB: Starting shutdown...
2016年08月01日T07:29:43.385850Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2016年08月01日T07:29:43.386745Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 160801 10:29:43
2016年08月01日T07:29:45.309017Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2519158
2016年08月01日T07:29:45.310815Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016年08月01日T07:29:45.310860Z 0 [Note] Shutting down plugin 'MEMORY'
2016年08月01日T07:29:45.310873Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016年08月01日T07:29:45.310932Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2016年08月01日T07:29:45.310963Z 0 [Note] Shutting down plugin 'sha256_password'
2016年08月01日T07:29:45.310971Z 0 [Note] Shutting down plugin 'mysql_native_password'
2016年08月01日T07:29:45.311085Z 0 [Note] Shutting down plugin 'binlog'
2016年08月01日T07:29:45.311243Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2016年08月01日T07:29:45.324479Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Answer 1

I have a very big heads-up for you

The password field in mysql.user is no longer in existence. Passwords are now stored in the column called authentication_string. I wrote about this a while ago:

• May 06, 2016 : MYSQL refuses to set password for new user
• Oct 28, 2015 : Install MySQL for Windows from .zip and reset root password (See the heading UPDATE 2015年11月17日 16:24 EST)
• Jan 15, 2015 : MySQL 5.7 - Where is the mysql_secret file on Windows 7?

This may explain the reason you got this error message

2016年08月01日T07:29:43.284662Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.

You need to run mysql_upgrade using the --upgrade-system-tables option

mysql-upgrade --upgrade-system-tables

This will copy the contents of the password column into the authentication_string column, remove the password column, and other adjustments needed for MySQL 5.7.

CAVEAT

This may still not work because you are leaping 3 major versions

What to do ??? You could on of the following

• Upgrade MySQL 3 times
• Dump all your data and MySQL grants, loading them into brand new MySQL 5.7 instance and call it a day (See my post MySQL upgrade 5.0.88 to latest on example how to do this)
• Attempt to fix the columns in place (RISKY BUT POSSIBLE)
  • MySQL service stops after trying to grant privileges to a user
  • Cannot GRANT privileges as root (to see column layouts from past versions of MySQL)

Answer 2

Upgrading from 5.1 to 5.7 is not that trivial.

Plan A: Upgrade from 5.1 to 5.5, then to 5.6, then to 5.7. At each step, be sure to run mysql_upgrade.

Plan B: Dump the data; remove 5.1; install 5.7; load the data.

There are many discussions in forums about either technique; review them. I have only given you an overview.