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:
None 
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
Description:
After re-creating data directory by deleting it's content and running mysql_install_db - cannot login afterwards, even if using --random-passwords and auto-generated password. When started mysqld with skip-grant-tables, it appears that there is no entry in the mysql.user table.
Login possible after adding a user manually.

Tested on both Centos6 and Ubuntu 5.6.17 installed from official yum/apt repositories from Oracle.

How to repeat:
root@ubuntu1204:~# dpkg -l|grep mysql
ii  mysql-apt-config                0.1.5-1ubuntu12.04                Auto Config Pkg for MySQL APT Repo.
ii  mysql-common                    5.6.17-1ubuntu12.04               MySQL Common
ii  mysql-community-client          5.6.17-1ubuntu12.04               MySQL Client
ii  mysql-community-server          5.6.17-1ubuntu12.04               MySQL Server
ii  mysql-server                    5.6.17-1ubuntu12.04               MySQL Server meta package depending on latest version

root@ubuntu1204:~# /etc/init.d/mysql stop
....
 * MySQL Community Server 5.6.17 is stopped

rm -fr /var/lib/mysql/*

root@ubuntu1204:~# 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 '/root/.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 '/root/.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

root@ubuntu1204:~# ls -l /root/.mysql_secret 
-rw------- 1 root root 104 May 22 20:57 /root/.mysql_secret

root@ubuntu1204:~# /etc/init.d/mysql start
No directory, logging in with HOME=/
......
 * MySQL Community Server 5.6.17 is started

root@ubuntu1204:~# mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

root@ubuntu1204:~# /etc/init.d/mysql stop
....
 * MySQL Community Server 5.6.17 is stopped

root@ubuntu1204:~# su mysql
mysql@ubuntu1204:/root$ cd /var/lib/mysql/
mysql@ubuntu1204:/var/lib/mysql$ ls -l
total 111892
-rw-rw---- 1 mysql mysql       56 May 22 20:58 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 May 22 20:58 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May 22 20:58 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 May 22 20:57 ib_logfile1
drwx------ 2 mysql mysql     4096 May 22 20:57 mysql
-rw-rw---- 1 mysql mysql    63357 May 22 20:57 mysql-bin.000001
-rw-rw---- 1 mysql mysql  1237667 May 22 20:57 mysql-bin.000002
-rw-rw---- 1 mysql mysql      174 May 22 20:58 mysql-bin.000003
-rw-rw---- 1 mysql mysql       96 May 22 20:58 mysql-bin.index
drwx------ 2 mysql mysql     4096 May 22 20:57 performance_schema
drwx------ 2 mysql mysql     4096 May 22 20:57 test
mysql@ubuntu1204:/var/lib/mysql$ mysqld --skip-grant-tables

--second terminal:
root@ubuntu1204:~# mysql
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.00 sec)

Suggested fix:
mysql_install_db should create initial user.
[4 Jun 2014 11:19] Umesh Shastry
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] Umesh Shastry
// 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] Umesh Shastry
Thank for the conf details.
I shall check this sometime tomorrow and keep you posted.

Thanks,
Umesh
[5 Jun 2014 8:14] Umesh Shastry
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.