MySQL added a new 'feature' in 5.7 which does the following when installing from their 300 MB + zip file on windows :
- Generates a random root password
- Does not echo the password to any file
- Echo's the password as part of an error message in Windows Event log
- Expires root passwords by default after a very short period.
I have written an application in c# which downloads the current zip, expands it, uses their new command to initialize the data directory ( 5.7.7+ ), however I am stuck at this whole random root password thing.
From the mysql log file (mysql.err)
2016-03-28T12:33:46.525571Z 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. 2016-03-28T12:33:46.525571Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
The other errors in the event log :
Failed to open log (file '2016-03-28T12:33:48.852814Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.', errno 13)For more information, see Help and Support Center at http://www.mysql.com.
Could not open log fileFor more information, see Help and Support Center at http://www.mysql.com.
Failed to open log (file '2016-03-28T12:33:48.500082Z 0 [Warning] InnoDB: New log files created, LSN=45790', errno 13)For more information, see Help and Support Center at http://www.mysql.com.
Could not open log fileFor more information, see Help and Support Center at http://www.mysql.com.
And the warnings in the event log :
InnoDB: New log files created, LSN=45790For more information, see Help and Support Center at http://www.mysql.com.
InnoDB: Creating foreign key constraint system tables.For more information, see Help and Support Center at http://www.mysql.com.
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: 5243756b-f4e1-11e5-bf9f-c8600006aa50.For more information, see Help and Support Center at http://www.mysql.com.
Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.For more information, see Help and Support Center at http://www.mysql.com.
After some time, the mysql.err file reports the following (lagged):
2016-03-28T12:33:46.525571Z 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.
2016-03-28T12:33:46.525571Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2016-03-28T12:51:11.085041Z 0 [ERROR] --verbose is for use with --help; did you mean --log-error-verbosity?
2016-03-28T12:51:11.085041Z 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.
2016-03-28T12:51:11.085041Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2016-03-28T12:51:11.085041Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2016-03-28T12:51:11.085540Z 0 [Note] bin\mysqld (mysqld 5.7.11-log) starting as process 4924 ...
2016-03-28T12:51:11.226559Z 0 [ERROR] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'
2016-03-28T12:51:11.230064Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-03-28T12:51:11.231065Z 0 [Note] InnoDB: Uses event mutexes
2016-03-28T12:51:11.231565Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2016-03-28T12:51:11.232559Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-28T12:51:11.234066Z 0 [Note] InnoDB: Number of pools: 1
2016-03-28T12:51:11.234565Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2016-03-28T12:51:11.300490Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-03-28T12:51:11.313506Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-03-28T12:51:11.367971Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-28T12:51:11.558971Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-03-28T12:51:11.559971Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-03-28T12:51:11.769085Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2016-03-28T12:51:11.778533Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-03-28T12:51:11.779533Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-03-28T12:51:11.780533Z 0 [Note] InnoDB: Waiting for purge to start
2016-03-28T12:51:11.832079Z 0 [Note] InnoDB: 5.7.11 started; log sequence number 2494086
2016-03-28T12:51:11.833581Z 0 [Note] InnoDB: Loading buffer pool(s) from d:\server\data\mysql\ib_buffer_pool
2016-03-28T12:51:11.833581Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: File '2016-03-28T12:33:48.992984Z 1 [Note] A temporary password is generated for root@localhost: aVV,7T)iiFug' not found (Errcode: 13 - Permission denied)
2016-03-28T12:51:11.851582Z 0 [ERROR] Failed to open log (file '2016-03-28T12:33:48.992984Z 1 [Note] A temporary password is generated for root@localhost: aVV,7T)iiFug', errno 13)
2016-03-28T12:51:11.853082Z 0 [ERROR] Could not open log file
2016-03-28T12:51:11.853583Z 0 [ERROR] Can't init tc log
2016-03-28T12:51:11.854083Z 0 [ERROR] Aborting
2016-03-28T12:51:11.854583Z 0 [Note] Binlog end
2016-03-28T12:51:11.855084Z 0 [Note] Shutting down plugin 'ngram'
2016-03-28T12:51:11.855583Z 0 [Note] Shutting down plugin 'partition'
2016-03-28T12:51:11.856083Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2016-03-28T12:51:11.857083Z 0 [Note] Shutting down plugin 'ARCHIVE'
2016-03-28T12:51:11.857583Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-03-28T12:51:11.859086Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2016-03-28T12:51:11.860083Z 0 [Note] Shutting down plugin 'MyISAM'
2016-03-28T12:51:11.860585Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2016-03-28T12:51:11.862584Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-03-28T12:51:11.863588Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-03-28T12:51:11.864584Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-03-28T12:51:11.865584Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-03-28T12:51:11.866585Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160328 8:51:11
2016-03-28T12:51:11.866585Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-03-28T12:51:11.868585Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-03-28T12:51:11.869588Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-03-28T12:51:11.870585Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-03-28T12:51:11.872085Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-03-28T12:51:11.872585Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-03-28T12:51:11.873613Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-03-28T12:51:11.874110Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-03-28T12:51:11.875086Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-03-28T12:51:11.875586Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-03-28T12:51:11.876086Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-03-28T12:51:11.876586Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2016-03-28T12:51:11.877087Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2016-03-28T12:51:11.878086Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-03-28T12:51:11.878586Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-03-28T12:51:11.879087Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-03-28T12:51:11.879587Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-03-28T12:51:11.880087Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-03-28T12:51:11.880587Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-03-28T12:51:11.881586Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-03-28T12:51:11.882087Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-03-28T12:51:11.882587Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2016-03-28T12:51:11.883087Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-03-28T12:51:11.883587Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-03-28T12:51:11.884087Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2016-03-28T12:51:11.884587Z 0 [Note] Shutting down plugin 'InnoDB'
2016-03-28T12:51:11.885587Z 0 [Note] InnoDB: FTS optimize thread exiting.
2016-03-28T12:51:11.886102Z 0 [Note] InnoDB: Starting shutdown...
2016-03-28T12:51:11.987153Z 0 [Note] InnoDB: Dumping buffer pool(s) to d:\server\data\mysql\ib_buffer_pool
2016-03-28T12:51:12.000601Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 160328 8:51:12
2016-03-28T12:51:13.328404Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2494105
2016-03-28T12:51:13.329404Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016-03-28T12:51:13.330405Z 0 [Note] Shutting down plugin 'MEMORY'
2016-03-28T12:51:13.330905Z 0 [Note] Shutting down plugin 'CSV'
2016-03-28T12:51:13.331405Z 0 [Note] Shutting down plugin 'sha256_password'
2016-03-28T12:51:13.331904Z 0 [Note] Shutting down plugin 'mysql_native_password'
2016-03-28T12:51:13.332405Z 0 [Note] Shutting down plugin 'keyring_file'
2016-03-28T12:51:13.334904Z 0 [Note] Shutting down plugin 'binlog'
2016-03-28T12:51:13.342906Z 0 [Note] bin\mysqld: Shutdown complete
Take a particular interest where the password eventually shows up, and for the time it claims it was for. It claims 2016-03-28T12:33:48.992984Z but it is inserted just after 2016-03-28T12:51:11.833581Z. Even if I did write my installer to account for a 20 minute delay for the passwords mystical appearance, and handled the parsing -- or looped through a seemingly random number of mysql events (in the picture is 9, but seems to vary between 8 and 40 event entries so far on each test it's kind of random), but even if I did decide to account for this ridiculous mess, that would mean the user would need to wait for X random period of time prior to being able to access the database from a fresh install.
I have looked into passing other parameters such as --skip-random-passwords, but that only resulted in 'unknown parameter'.
How can I reliably script an install from zip (aka what parameters can i pass to mysqld or set in the ini file) to prevent this horrible 'feature' from breaking it -- or to at worst, get the password during script processing in a reasonable manner ?
Update
Switched --initialize to --initialize-insecure, but still getting this message
Server MySQL: File '2016-03-28T13:39:21.469620Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.' not found (Errcode: 13 - Permission denied)
... which is preventing proper startup. It looks like it thinks 2016-03-28T13:39:21.469620Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. is the filename to log to somehow, so here is the my.ini file :
# For advice on how to change settings please see
# http://ift.tt/ZQDJna
[mysqld]
server_id = 1
explicit_defaults_for_timestamp=1
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin = myserver-bin
log_error = d:/server/var/log/mysql_log
# These are commonly set, remove the # and set as required.
basedir = d:/server
datadir = d:/server/data/mysql
plugin-dir = d:/server/bin/modules/mysql
port = 3306
# Enable general logging
general-log = 0
general-log-file = d:/server/var/log/mysql_log
# Enable slow query logging
slow_query_log = 0
log_queries_not_using_indexes = 0
slow_query_log_file = d:/server/var/log/mysql_slow_log
long_query_time = 20
# Define server process location
pid_file = d:/server/var/run/mysql.pid
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# ssl-ca=$DIR/ca.pem
ssl-cert=d:/server/var/certificates/localhost.crt
ssl-key=d:/server/var/certificates/localhost.key
Aucun commentaire:
Enregistrer un commentaire