MySQL 5.7 on a new Laptop

December 17, 2014

MySQL 5.7.5-m15 shoud be an easy install on a new laptop but I got bit! My faitful ol’ laptop has been put to pasture and I am busy getting a new laptop loaded with all the items I need for my job. I set up my Ubuntu 14.04 software and started adding all the usual suspects. That means Ubuntu providing MySQL 5.5 but I wanted 5.7. So I added the package to access the MySQL apt-get repository (see A Quick Guide to Using the MySQL APT Repository. It is proper procedure to go 5.5 to 5.6 to get to 5.7 and I went to 5.6. Login as root after the install and all is fine.

I reconfigure apt-get to grab 5.7 and all installs correctly, or so I thought. I can’t login! Dang. Check the log and see Access denied for user 'root'@'localhost' (using password: YES. And I knew the password I was not being fat fingered.

To make a long story short, I added the following two lines to my /etc/mysql/my.cnf file under the [mysqld] section and was able to login.
plugin-load=validate_password.so
validate_password_policy = LOW

MySQl 5.7 has a slew of new secutiry features and I guessed that my old password did not fit a new default policy. But looking at the installed plugins showed validate_password was not loaded. I also use rather simple passwords on instances that I expierment on and can get by with less than optimal passwords so I set the policy to low.

Below are the relvant variables.
mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
6 rows in set (0.00 sec)

mysql> show variables like '%password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| default_password_lifetime | 360 |
| disconnect_on_expired_password | ON |
| old_passwords | 0 |
| report_password | |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
10 rows in set (0.00 sec)

So now I have my new laptop running MySQl 5.7.5 and there are only fifty dozen otehr packages to add.

Advertisements

MySQL 5.7 user table: password_last_changed & password_lifetime

April 14, 2014

MySQL 5.7.4 has added two fields to the mysql.user table — password_last_changed, a timestamp and password_lifetime, a small but unsigned integer. Several blogs ago I started to cobble together a password expiration tracking script before these two columns were added. But I could see three ways of tracking expired passwords but none of them were palatable. Todd Farmer was working on a similar idea.

So when you run mysql_upgrade after upgrading to 5.7.4, you will find these two new columns. The password_last_changed will be set to the time you ran the upgrade and password_lifetime will be set to null.

You can set global password lifetime policy in the options file.
[mysqld]
default_password_lifetime=180

So 180 is about six months and zero would set a never expire policy.

ALTER USER 'dave'@localhost' PASSWORD EXPIRE INTERVAL 90 DAYS;
ALTER USER 'john'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jane'@'localhost' PASSWORD EXPIRE DEFAULT;