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 mysql-test-run.pl test suite driver to create the initial system tables and data for me.

Obviously this has disadvantages too. mysql-test-run.pl 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!