I'm running a Pixelbook with Crostini, and my MariaDB database sits in my linux instance. Sometimes, weird things happens and my root user loses the password and I can't access my database.

So here is how to reset your root user:

Stop Services

You'll need to stop all mysql instances.

$ sudo service mysql stop
$ sudo service mariadb stop
$ sudo systemctl stop mysql
$ sudo systemctl stop mariadb
$ sudo /etc/init.d/mysql stop

Then, run mysql in safe mode

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

The & in the end is important, since that lets you continue to work on the terminal you're working on (running the mysql service in the background). I also included the --skip-networking option because in ChromeOS, the linux is in a container. Localhost is not localhost. You should see some logging indicating it has started

200323 14:53:06 mysqld_safe Logging to syslog.
200323 14:53:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Creating User

Start by going into MySQL as you would regularly. We're interested in the database mysql.

$ sudo mysql -u root mysql

MariaDB [mysql]> SELECT * FROM user;
Empty set (0.00 sec)

As you can see, I don't have any user. I must have made some error configuration somewhere and screwed up my database. No worries, we'll create one.

MariaDB [mysql]> FLUSH PRIVILEGES;

MariaDB [mysql]> CREATE USER 'root'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> UPDATE user SET authentication_string = PASSWORD('') WHERE User = 'root';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

You'll need to FLUSH PRIVILEGES; first, or else it'll return an error: ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

Grant Privileges

Now we need to add privileges to root user

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;

MariaDB [mysql]> show grants;
+----------------------------------------------------------------+
| Grants for root@localhost                                      |       
+----------------------------------------------------------------+
+----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'              |
| IDENTIFIED BY PASSWORD '*A294464' WITH GRANT OPTION            |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION  |
+----------------------------------------------------------------+

All Ready

This is how I did it to help me restore some sanity after the missing root user issue. Hope it helps you too.

© 2020. All Rights Reserved.