MySQL installation on Windows - and how to fix missing mysql.user?

Question

I was using an old MySql 5.0 installation. Then decided to get newest 5.7 .zip release.

This is what I have done:

mysqld -initialize-insecure

(As I intend to use this for localhost/root usage only)

This creates some files in the root of the MySQL data directory.

However, running

mysqld --console

gives error

2017-02-03T02:52:01.076801Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exis

I then intended to move over my two 5.0 databases (each in a subfolder in my old mysql 5.0 "data dir" directory.)

Possibly I could then run mysql_upgrade then?

My full error log is here:

D:\databases\MySQL-5_7(x86)\bin>mysqld --console 2017-02-03T02:52:00.227754Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-02-03T02:52:00.227754Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 2017-02-03T02:52:00.227754Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set. 2017-02-03T02:52:00.227754Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2017-02-03T02:52:00.227754Z 0 [Note] mysqld (mysqld 5.7.17) starting as process 13872 ... 2017-02-03T02:52:00.235754Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2017-02-03T02:52:00.236754Z 0 [Note] InnoDB: Uses event mutexes 2017-02-03T02:52:00.236754Z 0 [Note] InnoDB: Memory barrier is not used 2017-02-03T02:52:00.237754Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-02-03T02:52:00.237754Z 0 [Note] InnoDB: Number of pools: 1 2017-02-03T02:52:00.238755Z 0 [Note] InnoDB: Not using CPU crc32 instructions 2017-02-03T02:52:00.240755Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2017-02-03T02:52:00.249755Z 0 [Note] InnoDB: Completed initialization of buffer pool 2017-02-03T02:52:00.363762Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2017-02-03T02:52:00.498769Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2017-02-03T02:52:00.499769Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2017-02-03T02:52:00.745783Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB. 2017-02-03T02:52:00.933793Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2017-02-03T02:52:00.934792Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2017-02-03T02:52:00.936793Z 0 [Note] InnoDB: Waiting for purge to start 2017-02-03T02:52:00.988797Z 0 [Note] InnoDB: 5.7.17 started; log sequence number 1209961 2017-02-03T02:52:00.988797Z 0 [Note] Plugin 'FEDERATED' is disabled. 2017-02-03T02:52:00.990797Z 0 [Note] InnoDB: Loading buffer pool(s) from W:\b-databases\MySql\5.0.x\data\ib_buffer_pool mysqld: Table 'mysql.plugin' doesn't exist 2017-02-03T02:52:00.991796Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 2017-02-03T02:52:00.993796Z 0 [Note] Salting uuid generator variables, current_pid: 13872, server_start_time: 1486090320, bytes_sent: 0, 2017-02-03T02:52:00.993796Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170203 3:52:00 2017-02-03T02:52:00.997796Z 0 [Note] Generated uuid: 'bc608dfe-e9bb-11e6-b692-00248c156225', server_start_time: 1486104192, bytes_sent: 361844000 2017-02-03T02:52:00.997796Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bc608dfe-e9bb-11e6-b692-00248c156225. 2017-02-03T02:52:01.041799Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2017-02-03T02:52:01.041799Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key 2017-02-03T02:52:01.043799Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2017-02-03T02:52:01.044802Z 0 [Note] IPv6 is available. 2017-02-03T02:52:01.045801Z 0 [Note] - '::' resolves to '::'; 2017-02-03T02:52:01.045801Z 0 [Note] Server socket created on IP: '::'. 2017-02-03T02:52:01.054800Z 0 [Warning] Failed to open optimizer cost constant tables

2017-02-03T02:52:01.076801Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 2017-02-03T02:52:01.077801Z 0 [ERROR] Aborting

2017-02-03T02:52:01.078801Z 0 [Note] Binlog end 2017-02-03T02:52:01.079801Z 0 [Note] Shutting down plugin 'ngram' 2017-02-03T02:52:01.079801Z 0 [Note] Shutting down plugin 'partition' 2017-02-03T02:52:01.080801Z 0 [Note] Shutting down plugin 'BLACKHOLE' 2017-02-03T02:52:01.081801Z 0 [Note] Shutting down plugin 'ARCHIVE' 2017-02-03T02:52:01.081801Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2017-02-03T02:52:01.082801Z 0 [Note] Shutting down plugin 'MRG_MYISAM' 2017-02-03T02:52:01.082801Z 0 [Note] Shutting down plugin 'MyISAM' 2017-02-03T02:52:01.083801Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL' 2017-02-03T02:52:01.083801Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2017-02-03T02:52:01.084801Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2017-02-03T02:52:01.084801Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2017-02-03T02:52:01.085801Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2017-02-03T02:52:01.087801Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2017-02-03T02:52:01.088802Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2017-02-03T02:52:01.089802Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2017-02-03T02:52:01.089802Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2017-02-03T02:52:01.090802Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2017-02-03T02:52:01.091802Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2017-02-03T02:52:01.091802Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2017-02-03T02:52:01.092802Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2017-02-03T02:52:01.093802Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2017-02-03T02:52:01.093802Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2017-02-03T02:52:01.094802Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2017-02-03T02:52:01.094802Z 0 [Note] Shutting down plugin 'INNODB_METRICS' 2017-02-03T02:52:01.095802Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO' 2017-02-03T02:52:01.097802Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2017-02-03T02:52:01.098802Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2017-02-03T02:52:01.099802Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2017-02-03T02:52:01.099802Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2017-02-03T02:52:01.100802Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2017-02-03T02:52:01.100802Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2017-02-03T02:52:01.101802Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM' 2017-02-03T02:52:01.102802Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2017-02-03T02:52:01.102802Z 0 [Note] Shutting down plugin 'INNODB_CMP' 2017-02-03T02:52:01.103803Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2017-02-03T02:52:01.103803Z 0 [Note] Shutting down plugin 'INNODB_LOCKS' 2017-02-03T02:52:01.104802Z 0 [Note] Shutting down plugin 'INNODB_TRX' 2017-02-03T02:52:01.104802Z 0 [Note] Shutting down plugin 'InnoDB' 2017-02-03T02:52:01.105803Z 0 [Note] InnoDB: FTS optimize thread exiting. 2017-02-03T02:52:01.107803Z 0 [Note] InnoDB: Starting shutdown... 2017-02-03T02:52:01.209808Z 0 [Note] InnoDB: Dumping buffer pool(s) to W:\b-databases\MySql\5.0.x\data\ib_buffer_pool 2017-02-03T02:52:01.214809Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170203 3:52:01 2017-02-03T02:52:02.408874Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1209980 2017-02-03T02:52:02.408874Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2017-02-03T02:52:02.409874Z 0 [Note] Shutting down plugin 'MEMORY' 2017-02-03T02:52:02.410874Z 0 [Note] Shutting down plugin 'CSV' 2017-02-03T02:52:02.410874Z 0 [Note] Shutting down plugin 'sha256_password' 2017-02-03T02:52:02.411874Z 0 [Note] Shutting down plugin 'mysql_native_password' 2017-02-03T02:52:02.412874Z 0 [Note] Shutting down plugin 'binlog' 2017-02-03T02:52:02.412874Z 0 [Note] mysqld: Shutdown complete

And this is my.ini file:

[mysqld]
basedir=D:/databases/MySQL-5_7(x86)
datadir=W:/b-databases/MySql/5.0.x/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

Answer 1

Somehow I got it working now - I redid what was mentioned here and this time the user tables were created as well. Not quite sure why it did not work befoe, but still deserves to be an answer: https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html