Using mysql_embedded and mysqld –bootstrap to tinker with privilege tables

Posted on June 3, 2014 by
spacer

A couple weeks ago, I started wondering about some alternative approaches to resetting the MySQL or MariaDB password. The tried-and-true methods of --skip-grant-tables and --init-file are fine, but I thought there must be some more “simple” (in terms of fewer steps at least) and more programmatic way. It occurred to me that it should be possible to use the embedded server (libmysqld) to accomplish this task. In this post, I’ll look at how to use the mysql_embedded tool to inspect and modify grant tables, as well as how to use the --bootstrap option to mysqld to do the same. MariaDB and MySQL distributions include an executable called mysql_embedded. The documentation for this thing is very poor, but basically it is just a MySQL client statically linked to libmysqld. When you execute the program, an embedded MySQL or MariaDB server is instantiated and you can execute statements just as you would using a normal MySQL client. Because mysql_embedded will in effect start a new “server” instance, do not do this while your MySQL or MariaDB server is running. If you have installed the client RPM, you can simply run mysql_embedded and get a command-line that allows you to interact with tables, even when the server isn’t running. This, of course, requires that the user running mysql_embedded has filesystem access to the data files.

[kolbe ~]$ sudo service mysql status
 ERROR! MySQL is not running
[kolbe ~]$ sudo mysql_embedded -e 'select user, host, password from mysql.user where user="root"'
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | db1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | 127.0.0.1 | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | ::1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+------+-----------+-------------------------------------------+

If I try doing this as a user without the right privileges, I get lots of errors and I can’t see anything:

[kolbe ~]$ mysql_embedded -e 'select user, host, password from mysql.user where user="root"'
2014-05-29 20:45:22 7f14105767e0  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2014-05-29 20:45:22 7f14105767e0  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2014-05-29 20:45:22 7f14105767e0  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
ERROR 1146 (42S02) at line 1: Table 'mysql.user' doesn't exist

As we can see, the server is starting InnoDB here, which is not necessary if we just want to read privilege tables (until MySQL 5.7!). So, we can actually tell the embedded server not to bother loading InnoDB at all, which makes the whole thing much faster and means we aren’t going to accidentally start crash recovery or have to worry about the variety of other strange problems we could probably cause ourselves by interacting with the InnoDB tablespace. We can pass options to the embedded server by using the --server-arg option to mysql_embedded:

[kolbe ~]$ time sudo mysql_embedded --server-arg='--skip-innodb' --server-arg='--default-storage-engine=myisam' --server-arg='--log-error=/tmp/mysql.err' -e 'select user, host, password from mysql.user where user="root"'
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | db1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | 127.0.0.1 | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | ::1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+------+-----------+-------------------------------------------+

real    0m0.244s
user    0m0.078s
sys     0m0.135s

[kolbe ~]$ time sudo mysql_embedded -e 'select user, host, password from mysql.user where user="root"'
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | db1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | 127.0.0.1 | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | ::1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+------+-----------+-------------------------------------------+

real    0m2.525s
user    0m0.157s
sys     0m0.221s

So just disabling InnoDB saves a couple seconds here. Notice that I included both --skip-innodb as well as --default-storage-engine=myisam. If your default storage engine is InnoDB, the server will refuse to start unless you set it to something else, because the server won’t start if default_storage_engine is not a recognized storage engine. In 5.6, it is also necessary to set --default-tmp-storage-engine. Since we can execute any query we like, it’s easy enough to just reset the password for 'root'@'localhost' to whatever we like:

[kolbe ~]$ sudo mysql_embedded --server-arg='--skip-innodb' --server-arg='--default-storage-engine=myisam' -e 'update mysql.user set password=password("newpass") where user="root" and host="localhost"'
[kolbe ~]$ sudo mysql_embedded -e 'select user, host, password from mysql.user where user="root"'
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 |
| root | db1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | 127.0.0.1 | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| root | ::1       | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+------+-----------+-------------------------------------------+

This is a pretty straightforward technique that can be used without modifying server configuration, worrying about the security implications of starting the server without any privilege checking in place, et cetera. You don’t have to worry about flushing privileges, because the server is not actually running at the time the password is changed. The server will “see” the new password when it is started again. If your platform doesn’t support the embedded server, or there is some other problem with using it, there is another technique: mysqld --bootstrap. The --bootstrap mode is normally used by mysql_install_db to execute an SQL script before any privilege or other system tables exist. It can also be used for our purposes to start the server in a minimal mode in order to execute a small number of queries. Because mysqld --bootstrap will in effect start a new “server” instance, do not do this while your MySQL or MariaDB server is running. You don’t get a resultset back when using --bootstrap, but you can do SELECT ... INTO OUTFILE, like this:

[kolbe ~]$ sudo -u mysql mysqld --bootstrap 

Let's disable InnoDB as we did before, and we can of course then also reset the password using this technique:

[kolbe ~]$ sudo rm /tmp/mysql.users [kolbe ~]$ sudo -u mysql mysqld --skip-innodb --default-storage-engine=myisam --log-warnings=0 --bootstrap Note that you need to have a literal newline in there. The server requires a single SQL statement per line in --bootstrap mode. You can also add normal command-line arguments such as --skip-innodb and --datadir as necessary. You could of course also put the statements into a file and redirect those to stdin of the mysqld process. Both the mysql_embedded and mysqld --bootstrap techniques can be used to inspect and modify privilege tables without having to start the server at all, or especially to edit the server's configuration before starting it. These might be attractive options in some situations where it makes more sense to verify what users exist on a system before trying to start the server, or to reset passwords in a more programmatic way without having to start and later restart the server. Even if you don't use these methods to do anything wth privilege tables, I hope they provide a little bit of insight into a couple relatively unknown features and behaviors available in virtually all installations of MySQL.