mysql old password, updating the password hash

Today I was trying to dump and restore mysql grants to a new Percona Server 5.6 system and I got an error about the password hash being in an unexpected format.

ERROR 1827 (HY000) at line 56: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

 I logged in checked the password hash:
mysql> select user, host, password from mysql.user;

There was an application user which had a shorter password hash kind of like this:
6f8c114b58f2ce9e

I wanted to upgrade the password to the newer hash so I had to first get a hold of the application password. After retrieving the application password I did a compare of the two different hashing algorithms like this:

SELECT OLD_PASSWORD('mypass');
6f8c114b58f2ce9e

SELECT PASSWORD('mypass');
*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4

I was able to confirm that the old password hash matched what I saw when I ran the OLD_PASSWORD('xxx') function.

Then I changed the application password like this:

SET PASSWORD FOR 'my_app'@'%' = PASSWORD('mypass');

You could also drop and re-create the user with the new password hash.

Finding a slave server in a cluster when show slave hosts is blank

I'm working for a client now that has hundreds of clusters of MySQL servers. I'm working on going through them and standardizing the MySQL install and fixing issues. I came across a cluster today that had half a dozen slaves but there was no documentation on which slaves were in the cluster. Part of the work I'm doing is adding "report_host = <server_name>" to each of the my.cnf files for MySQL servers so that when I run "show slave hosts" on the master I can know what slaves are in the cluster (and so that Orchestrator will be able to see them).

When I ran "show slave hosts" on the master there were several blank entries. In order to find them I ran this query:

mysql> select user, host from information_schema.processlist WHERE user = 'replication';
+-----------------+-----------------------------------------+
| user            | host                                    |
+-----------------+-----------------------------------------+
| replication     | xx.xx.xx.xx:xxxxx                       |
| replication     | xx.xx.xx.xx:xxxxx                       |
| replication     | xx.xx.xx.xx:xxxxx                       |
| replication     | xx.xx.xx.xx:xxxxx                       |
| replication     | xx.xx.xx.xx:xxxxx                       |
| replication     | xx.xx.xx.xx:xxxxx                       |
+-----------------+-----------------------------------------+
6 rows in set (0.00 sec)

This provided me with either the hostname or the IP address of each of the slaves. Using this information, I was able to SSH into each of the slaves, update the my.cnf and restart the slave so that it would appear in orchestrator.

Dumping mysql users with pt-grants or manually

In my opinion, the best way to dump and restore mysql users is using pt-grants from the Percona Toolkit. The tool can be run like this or with a combination of other parameters:

pt-show-grants --user='USERNAME' --ask-pass --database=mysql --host=my_server.com > xxxx_mysql_user_grants.sql

Next, the grants can be imported. When I take logical backups with mydumper/mysqldump, I don't export the mysql schema. This is why having a method to backup and restores users is important.

 There can be circumstances where the Percona Toolkit cannot be installed for whatever reason and you have to do it manually.

This question has been discussed here:
http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server

Here are couple of unique ways to get the same data (copied from the above link):

mysql -uUSER -pPASSWORD -h'my_server.lan' --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -uUSER -pPASSWORD  -h'my_server.lan' --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

"-uUSER -pPASSWORD" could be replaced with a defaults file so you don't need user/password on the command line.

In a bash script you could create a function like this:

mygrants()
{
mysql -B -N --host=prod-db1 --user=admin --password=secret -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
) AS query FROM mysql.user WHERE user NOT IN ('root','phpmyadmin','debian-sys-maint')"  | \
mysql --host=prod-db1 --user=admin --password=secret | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

Another caveat though is the above query doesn't work with MySQL 5.7.  From the manual, "As of MySQL 5.7.6, SHOW GRANTS output does not include IDENTIFIED BY PASSWORD clauses. Use the SHOW CREATE USER statement instead. " If you used this syntax on a MyQL 5.7 system then it would save the grants but not the password hash and if restoring on a MySQL 5.6 system, your passwords would be empty. See here: http://dev.mysql.com/doc/refman/5.7/en/show-grants.html

In order to get the password hash, you need to use:

mysql> SHOW CREATE USER 'root'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for root@localhost: CREATE USER 'root'@'localhost'
IDENTIFIED WITH 'mysql_native_password'
AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

See here:

http://dev.mysql.com/doc/refman/5.7/en/show-create-user.html

Running a shell command while in the mysql prompt

Most applications in linux allow you to run a linux command while the application is running. MySQL is no exception. To run a linux command you just need to prefix it with "\! "


Usage: \! shell-command

mysql> \! hostname

vmware.db1_0