Initialize Your MySQL 5.7 Instances with Ease

MySQL 5.7.6 brings in a simplification that solves the very first problem that I encountered back in the days when I first started using MySQL 5.0. Namely…

How do I create a new database instance?

I know it sounds like a very basic question. But as it turned out, the answer was not that simple. I tried mysqld --help. Nothing there. And then, after reading the manual and trying out the complex command line (including redirection) based steps a couple of times, I just resorted to employing the test suite driver to create the initial system tables and data for me.

Obviously this has disadvantages too. is a test tool, so it creates the database in an extra-permissive mode. It has a “test” database/schema that everybody can write to. It has users I don’t want. It recently started having an “mtr” database too that I didn’t want either. That’s tolerable when you’re developing the server, but certainly not when you’re setting up a database server for production use.

Another potential option was running mysql_install_db… except that I was running on MS Windows, and it is not natively supported on Windows.

Then my colleagues—who were also fed up with having to create and maintain their own custom versions of mysql_install_db for Windows—started insisting that it’s high time we do something about this.

This is exactly why it’s great to be a MySQL server developer. You can actually scratch your own itches. 🙂

So we started looking into it, and thanks to the wonderful system of Development Milestone releases (DMRs) we had an ideal medium with which to communicate our plans to everyone. We first tried making mysql_install_db easier to use. We got rid of the Perl scripting and re-wrote it in C. It started looking better.

But after having fully analyzed exactly what mysql_install_db was trying to do, it became obvious that there’s no way this would work reliably cross platform without another large command line tool.

It just was not the right approach.

What do you do in such cases?

You cut out the middle man.

mysql_install_db‘s primary job was to invoke the mysqld (MySQL server) binary in a special mode and pipe in a bunch of SQL scripts to it.

What if mysqld could instead do the job all on its own? Without the need for all of these extra SQL files? So you don’t have to carry them around and worry if they match the binary? Why not have mysqld bootstrap itself and eliminate all of this complexity?

This is how the new --initialize MySQL server option was born.

A single option to initialize your database instance! Using nothing but your database server itself! Always consistent and cross platform!

Bye bye scripts/mysql_system*.sql!

Since it’s the same server working with the same data directory, what about cases where there’s already existing data within the data directory? You don’t want that overwritten! So if the data directory exists and there’s even a single item in it, then the --initialize procedure will stop right there with a helpful and descriptive error message. If the data directory does not yet exist, then it will simply create the data directory for you. This work keeps things nice and simple, offering one step setup and initialization.

I was aware that different people like their servers initialized differently. But continuing to support all of these various options that were using all of these various languages and that had all of these secondary path and file dependencies, was just far too complicated—it had a large development cost for us and it provided a poor user experience.

Less is More!

So I’ve decided to go for the lowest common denominator, and to make sure that the aptly named --init-file Server option works well with the new --initialize Server option, so that people who want custom installation methods can add custom install related commands and processes they need that way.

Having that part sorted out, I then set out to ensure that the --initialize step creates the absolute minimum of system tables—the mysql database/schema and the mysql command line tool help files—and a single user account: root@localhost.

Since I primarily work on security related features, the password for that root@localhost account had to be secure. Thus I’ve also made mysqld generate a default password that’s random enough to meet the default policy criteria for the password validation plugin, and finally it also marks it as expired (it’s temporary, and you should change it ASAP).

So far so good, but where do I put the generated temporary password so that you can see it when doing the installation?

I could have written it to the $HOME/.mysql_secret file (just like mysql_install_db was doing).

But I realized that this password should be easy for people to see and use.

People don’t like extra steps: read the manual, go open an obscure hidden file, and so on. Scripts need that. Not people.

Real people just want the password on their screen. One they can copy, paste, and login.

This is why I decided to scrap the file generation and just print the password to STDERR. This way it’s right in front of you. You can simply copy it, run mysql -u root -p, and then paste the temporary password. Note: you also now set the password using a more obvious and straightforward syntax: mysql> set password='mypass';

The concept was now ready.

But then I confronted real life and all the scripts and related files that were then in need of adjustments when using the new method.

Scripts don’t need no random passwords!

All they need is to be able to reliably log in and do stuff on their own. And the only thing preventing them from doing that was the generated random temporary password.

This is why I then also added the --initialize-insecure option to turn the random password generation off and instead leave the root@localhost account with an empty, non-expired password.

And yes, I’ve picked the name on purpose. 🙂

Now it was time to do some clean up chores.

Obviously mysql_install_db was now going to ride off into the sunset. Not just yet though of course, as we need to give users time to adjust their scripts and habits. So we’ve now simply marked it as deprecated in 5.7.

Then we also realized that the primary use case for the --bootstrap Server option was to support mysql_install_db. So we went ahead and marked this as deprecated too.

Cleaning up is important! My wife would be so proud of me for saying that. 🙂

There it is.

A better way to bootstrap your MySQL database server!

We look forward to your feedback on this new work! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.

As always, THANK YOU for using MySQL!

19 thoughts on “Initialize Your MySQL 5.7 Instances with Ease

  1. If I search for ‘mysql_install_db’ in the bugs database I get 41 results. I think this fixes a large number of those. Besides a cleanup it also looks like a solid base for more improvements.

    1. Right, there are more things that can be done to make it more comfortable for sure. But since mysql_install_db was already shaping up as a pain to support I wanted to get the word out as soon as possible.

  2. Hi Joro!
    I understand and accept that you wanted to get rid of “mysql_install_db”. I haven’t yet found the time to install and try your changes, so I have to rely on your description.
    IMO your approach has some negative parts, too:
    1) You made the SQL files (to create the system tables) part of “mysqld”. This means:
    – The statements are not directly visible any more, admins cannot check them.
    – For even the most tiny change, “mysqld” must be modified and rebuilt.
    I would have much preferred if you implemented an option “read and execute these files”, for better access and flexibility.
    2) You claim that STDERR is visible for people. This is correct only for an interactive installation (probably using a tarball), not for a more batch-style installation using packages (Linux RPM or DEB, probably also OS X PKG). Admins doing that will not see the STDERR of the MySQL server process.
    I look forward to install packages with your changes to try them.

    1. Joerg,

      Thanks for your comments.

      I see #1 as simplification. The server needs to know what to create. And it can’t tolerate different system tables layout. And I believe INFORMATION_SCHEMA and the SHOW commands give you enough reflection data on the system objects.
      However you still have the option to tweak it if you like through the –init-file option.
      On #2 all I can do is refer you to –initialize-insecure and the reasoning behind it.

  3. I am very happy to see that by default, the confusing/insecure grants and multiple root accounts are not created as they were with mysql_install_db:

    mysql> select user, host from mysql.user;
    | user | host |
    | root | localhost |
    1 row in set (0.00 sec)

    mysql> select * from mysql.db;
    Empty set (0.00 sec)

    mysql_secure_installation should perhaps not ask me to change the password twice:

    Securing the MySQL server deployment.

    The existing password for the user account has expired. Please set a new password.

    New password:

    Re-enter new password:

    VALIDATE PASSWORD PLUGIN can be used to test passwords
    and improve security. It checks the strength of password
    and allows the users to set only those passwords which are
    secure enough. Would you like to setup VALIDATE PASSWORD plugin?

    Press y|Y for Yes, any other key for No: n
    Using existing root password.
    Change the root password? (Press y|Y for Yes, any other key for No) : n

    Also, the mysql_secure_installation is still vague about legacy grants to test in that the test_% grants are not mentioned:

    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
    – Dropping test database…

    – Removing privileges on test database…

    As of now, this change does seem to have the unfortunate result of getting no error when trying to start mysqld before a mount point such as /var/lib/mysql is unavailable:

    # rm -rf /var/lib/mysql
    # service mysqld start
    Initializing MySQL database: 2015-03-17T05:19:05.054987Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
    2015-03-17T05:19:06.494416Z 0 [Warning] InnoDB: New log files created, LSN=45790
    2015-03-17T05:19:06.581709Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
    2015-03-17T05:19:06.641287Z 0 [Warning] Failed to setup SSL
    2015-03-17T05:19:06.641316Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
    2015-03-17T05:19:06.642450Z 1 [Warning] A temporary password is generated for root@localhost: gPYtpV3r&
    [ OK ]
    Starting mysqld: [ OK ]

    I would like the init script to fail in this scenario, and prefer to use “service mysqld initdb” or something similar to invoke this functionality.

    1. Good points !
      Both mysql_secure_installation and “service mysqld start” look like valid problems. Thanks for bringing them up.
      Please file bugs about each of them (with all the reproduction details) to

  4. Hi Matt,
    I find that the –default-file should be specified before –initialize. Otherwise, it keeps complaining “[ERROR] unknown variable ‘defaults-file=C:\ProgramData\MySQL\MySQL'” on Windows. The arguments order counts and it is a little inconvenient.

  5. Hi guys

    thank you for improving mysql. I haveonly OSX Yosemite computers and I wanted today to install a further mysql instance besides my older ones on one yosemite mac pro server. After 2 hours I gave up.

    The command sudo /usr/local/…/mysql/bin/mysqld_safe –initialize –datadir=/Volumes/…/mysql/3307

    requires the datadir to be created, but does nothing and writes on the log file (inside the same datadir):

    2016-04-16T11:51:36.340346Z 0 [ERROR] –initialize specified but the data directory has files in it. Aborting.
    2016-04-16T11:51:36.340378Z 0 [ERROR] Aborting

    So this seems to be a nogo …

    The idea is nice but …


    1. The data directory to be must either be nonexistent or be empty.
      This is a precautionary measure to prevent you from accidentally overwriting your database instance with an empty one.

  6. Hello,

    Thanks for posting. I would like to be able to start mysql in insecure mode WITHOUT using the shell, just a config file, but unfortunately I cannot do this:


    Because that flag isn’t recognized and well, because of this:

    “–initialize-insecure is mutually exclusive with –bootstrap and –daemonize.”

    So how do you get rid of the random temporary password instead of having to scrape the logs.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.