| Bug #72724 | mysql_install_db does not set up initial root user | ||
|---|---|---|---|
| Submitted: | 22 May 2014 19:19 | Modified: | 16 Jul 2015 14:09 |
| Reporter: | Przemyslaw Malkowski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S2 (Serious) |
| Version: | 5.6.17 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysql_install_db | ||
[22 May 2014 19:19]
Przemyslaw Malkowski
[4 Jun 2014 11:19]
MySQL Verification Team
Hello Przemyslaw, Thank you for the report. I could not hit the issue which you are experiencing..Could you please confirm if you are able to login with -uroot switch? // ushastry@ushastry:~/Downloads$ dpkg -l|grep mysql ii mysql-apt-config 0.1.5-2ubuntu14.04 all Auto Config Pkg for MySQL APT Repo. ii mysql-client 5.6.17-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version ii mysql-common 5.6.17-1ubuntu14.04 amd64 MySQL Common ii mysql-community-client 5.6.17-1ubuntu14.04 amd64 MySQL Client ii mysql-community-server 5.6.17-1ubuntu14.04 amd64 MySQL Server ii mysql-server 5.6.17-1ubuntu14.04 amd64 MySQL Server meta package depending on latest version ii mysql-workbench-community 6.1.6-1ubu1310 amd64 MySQL Workbench ushastry@ushastry:~/Downloads$ sudo /etc/init.d/mysql stop .... * MySQL Community Server 5.6.17 is stopped ushastry@ushastry:~/Downloads$ ushastry@ushastry:~/Downloads$ ls -l /var/lib/mysql total 110608 -rw-rw---- 1 mysql mysql 56 Jun 4 16:23 auto.cnf -rw-rw---- 1 mysql mysql 12582912 Jun 4 16:23 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Jun 4 16:23 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Jun 4 16:22 ib_logfile1 drwx------ 2 mysql mysql 4096 Jun 4 16:22 mysql drwx------ 2 mysql mysql 4096 Jun 4 16:22 performance_schema drwx------ 2 mysql mysql 4096 Jun 4 16:22 test // removed datadir ushastry@ushastry:~/Downloads$ sudo rm -fr /var/lib/mysql/* // Confirmed nothing exists ushastry@ushastry:~/Downloads$ ls -l /var/lib/mysql total 0 ushastry@ushastry:~/Downloads$ sudo mysql_install_db --random-passwords --datadir=/var/lib/mysql --user=mysql Installing MySQL system tables...A random root password has been set. You will find it in '/home/ushastry/.mysql_secret'. OK Filling help tables...OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/home/ushastry/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file /usr/my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as /usr/my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/mysql/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server ushastry@ushastry:~/Downloads$ sudo /etc/init.d/mysql start No directory, logging in with HOME=/ ...... * MySQL Community Server 5.6.17 is started ####### Confirmed that random password is used and able to login using random password ushastry@ushastry:~/Downloads$ more /home/ushastry/.mysql_secret # The random password set for the root user at Wed Jun 4 16:22:34 2014 (local time): R24EjcGqW83U9A3b # The random password set for the root user at Wed Jun 4 16:31:50 2014 (local time): Q9ipTkIAq2HrIqfu ushastry@ushastry:~/Downloads$ mysql -p Enter password: ERROR 1045 (28000): Access denied for user 'ushastry'@'localhost' (using password: YES) ^^ Expected, no anonymous user exists // No, when I use user root with the generated random password, it allows to login. ushastry@ushastry:~/Downloads$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.17 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user,host from mysql.user; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql> set password=password('123'); Query OK, 0 rows affected (0.01 sec) mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | ushastry | +------+-----------+ 4 rows in set (0.00 sec) ============================================ Thanks, Umesh
[4 Jun 2014 12:38]
MySQL Verification Team
// Correction in earlier note ==================================== ushastry@ushastry:~/Downloads$ mysql -p Enter password: ERROR 1045 (28000): Access denied for user 'ushastry'@'localhost' (using password: YES) ^^ Expected, no anonymous user exists ==================================== It was using OS user.. hence, ushastry@localhost.. so no anonymous :) Also, I checked it on Ubuntu 14.04...But not sure whether that matters but would be good if you can check on both to confirm the issue. Thanks, Umesh
[4 Jun 2014 13:52]
Przemyslaw Malkowski
Apologies, I forgot to mention that I am initializing datadir with custom configuration, specifically the GTID enabled + autocommit set to off. The OS version does not matter, happens on both Ubuntu 12.04 and 14.04 as well as on CentOS. The config used: root@vagrant-ubuntu-trusty-64:~# cat /etc/mysql/my.cnf [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp log-error = /var/log/mysql/error.log sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES symbolic-links=0 server-id = 2 log_bin = 1 log-slave-updates gtid-mode = ON enforce-gtid-consistency = 1 autocommit = 0
[4 Jun 2014 17:44]
MySQL Verification Team
Thank for the conf details. I shall check this sometime tomorrow and keep you posted. Thanks, Umesh
[5 Jun 2014 8:14]
MySQL Verification Team
Thank you for the customized conf, I'm able to reproduce this issue now.. ////// Stop mysqld ushastry@ushastry:~/Downloads$ sudo /etc/init.d/mysql stop [sudo] password for ushastry: .... * MySQL Community Server 5.6.17 is stopped // Remove datadir and confirm it is no longer exists ushastry@ushastry:~/Downloads$ sudo rm -fr /var/lib/mysql/* ushastry@ushastry:~/Downloads$ ls -l /var/lib/mysql total 0 // Initialize system databases ushastry@ushastry:~/Downloads$ sudo mysql_install_db --random-passwords --datadir=/var/lib/mysql --user=mysql Installing MySQL system tables...A random root password has been set. You will find it in '/home/ushastry/.mysql_secret'. OK Filling help tables...OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/home/ushastry/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file /usr/my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as /usr/my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/mysql/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server // Bring up mysqld ushastry@ushastry:~/Downloads$ sudo /etc/init.d/mysql start No directory, logging in with HOME=/ ...... * MySQL Community Server 5.6.17 is started ushastry@ushastry:~/Downloads$ more /home/ushastry/.mysql_secret # The random password set for the root user at Wed Jun 4 16:22:34 2014 (local time): R24EjcGqW83U9A3b # The random password set for the root user at Wed Jun 4 16:31:50 2014 (local time): Q9ipTkIAq2HrIqfu # The random password set for the root user at Thu Jun 5 13:28:05 2014 (local time): xbcnHPmFw0xnqI5m ### Try to login using random password generated in earlier step ushastry@ushastry:~/Downloads$ mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ushastry@ushastry:~/Downloads$ mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) // Check the mysql.user files ushastry@ushastry:~/Downloads$ sudo ls -l /var/lib/mysql/mysql/ -rw-rw---- 1 mysql mysql 10684 Jun 5 13:28 user.frm -rw-rw---- 1 mysql mysql 0 Jun 5 13:28 user.MYD <---- empty data file -rw-rw---- 1 mysql mysql 1024 Jun 5 13:28 user.MYI // Start mysqld with skip grant table ushastry@ushastry:~/Downloads$ sudo /etc/init.d/mysql stop .... * MySQL Community Server 5.6.17 is stopped ushastry@ushastry:~/Downloads$ sudo vi /etc/mysql/my.cnf ushastry@ushastry:~/Downloads$ cat /etc/mysql/my.cnf |grep skip-grant-tables skip-grant-tables ushastry@ushastry:~/Downloads$ ushastry@ushastry:~/Downloads$ sudo /etc/init.d/mysql start No directory, logging in with HOME=/ ...... * MySQL Community Server 5.6.17 is started ushastry@ushastry:~/Downloads$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from mysql.user; Empty set (0.01 sec) mysql>
[16 Sep 2014 19:06]
Jonathan Nicol
So I have to manually edit grant tables to install a database. Sigh. PLEASE FIX ASAP. mysql_install_db --datadir=/db_data/mysql/ --skip-name-resolve --user=mysql Doesn't work (can't log in) mysql_install_db --datadir=/db_data/mysql/ --skip-name-resolve --user=mysql --random-passwords Doesn't work (/root/.mysql_secret is zero bytes)
[18 Feb 2015 19:02]
Jonathan Lowsley
This exact bug affects me in mysql 5.6.22 (from ndbcluster 7.3.8). Is there a work around other than manually populating the user table?
[16 Jul 2015 14:09]
Paul DuBois
Noted in 5.7.5 changelog. mysql_install_db failed to create the initial root account if autocommit was disabled.
