Notes: On MySQL

DISCLAIMER: this is not meant to be coherent post. Sorry. Just notes for future reference. As such this post is subject to possible future changes.

Logging in after installation

Since mysqld is quite different from Postgres we need to adjust expectations. When greeted with error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

just after installation we need to find the temporary password from the startup logs, like so:

$ grep 'generated' /var/log/mysqld.log 
2019-06-11T08:45:48.773272Z 1 [Note] A temporary password is generated for root@localhost: hunter2

I see why they might do that I just personally don't like it (info during installation would be nice).

Short memorable password for development

As for changing the password after first successful login - first you'll need to set a secure password. Afterwards you can issue:

UNINSTALL PLUGIN validate_password;
-- for checking that it's gone
SHOW PLUGINS;

Now you can set the password for something concise and fitting for development.

Warning: never do it on production systems!

Enabling connection from outside

You'll need to create a separate user that can be identified from its connection string (kinda similar to the Postgres approach, but in different place).

Connect locally as SU and then:

GRANT ALL ON *.* TO 'root'@'a.b.c.d' IDENTIFIED BY 'hunter2';
-- verify the new user creation
SELECT * FROM mysql.user WHERE host = 'a.b.c.d'\G

That's it -- you'll be able to connect to MySQL from remote host.

Warning: Not for production use.

Speeding up restore of a dump with InnoDB storage

Since the standard practice for restoring the dump file is to simply execute it, like:

mysql < dump.sql

there's no denying that it is dead simple. Problem starts to show up when the file size far exceeds one of the toy example realm. This is due to number of default settings that combined slow down the automated process of inserting data. In order to gain some speed we can use this snippets before and after executing the dump.sql file.

--- before.sql
SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

--- after.sql
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

Having those files we can simply run this incantation and be over with the dump:

cat before.sql dump.sql after.sql | mysql