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.
Thursday, October 27, 2016
Monday, October 17, 2016
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.
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.
Tuesday, October 11, 2016
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,
In order to get the password hash, you need to use:
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.htmlIn 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.htmlWednesday, October 5, 2016
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
Usage: \! shell-command
mysql> \! hostname
vmware.db1_0
Tuesday, September 27, 2016
Importing files into mysql with LOAD_FILE but data returning NULL
I really wanted to be able to import text files into a column for a little project I'm working on. I know that MySQL isn't the ideal place for storing TEXT files but the project was small and so this is what I wanted. But the LOAD_FILE simply wouldn't work. Eventually I found that someone had posted a work around. You have to put the files in directoy inside of this directory: /var/lib/mysql and then give the correct permissions.
I found the information from here for the "ugly workaround":
http://stackoverflow.com/questions/4607486/mysql-load-file-returning-null
Steps I performed:
Create a new directory under mkdir -p /var/lib/mysql/:
mkdir -p /var/lib/mysql/upload_data/
Create a test file:
ps -ef > /var/lib/mysql/upload_data/test_import.txt
Verify the file has some data:
head /var/lib/mysql/upload_data/test_import.txt
Changing permissions wasn't needed on my system but others said it might be needed:
chown mysql:mysql /var/lib/mysql/upload_data/*
chmod go+rw /var/lib/mysql/upload_data/*
Give your user the needed permissions:
mysql -u root
mysql>
GRANT file ON *.* to 'test'@'%';
GRANT file ON *.* to 'test'@'localhost';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' IDENTIFIED BY 'test_password';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test_password';
exit
mysql -u test
mysql> SHOW GRANTS;
DROP TABLE IF EXISTS `test`.`table_a`;
CREATE TABLE IF NOT EXISTS `test`.`table_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`output` text COLLATE utf8_unicode_ci COMMENT 'program output from command execution if available',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='stores test files';
REPLACE INTO `test`.`table_a` (`id`, `output`) VALUES (1, NULL);
SELECT hex(LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt'));
UPDATE test.table_a
SET output=LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt')
WHERE id=1;
SELECT * FROM test.table_a WHERE id =1;
One of the problems with this ugly work around is the new folder in var/lib/mysql/ will look like a new database (unless your data directory is some where else). One way to get around this it to make the folder hidden by starting the folder name with a period.
I found the information from here for the "ugly workaround":
http://stackoverflow.com/questions/4607486/mysql-load-file-returning-null
Steps I performed:
Create a new directory under mkdir -p /var/lib/mysql/:
mkdir -p /var/lib/mysql/upload_data/
Create a test file:
ps -ef > /var/lib/mysql/upload_data/test_import.txt
Verify the file has some data:
head /var/lib/mysql/upload_data/test_import.txt
Changing permissions wasn't needed on my system but others said it might be needed:
chown mysql:mysql /var/lib/mysql/upload_data/*
chmod go+rw /var/lib/mysql/upload_data/*
Give your user the needed permissions:
mysql -u root
mysql>
GRANT file ON *.* to 'test'@'%';
GRANT file ON *.* to 'test'@'localhost';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' IDENTIFIED BY 'test_password';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test_password';
exit
mysql -u test
mysql> SHOW GRANTS;
DROP TABLE IF EXISTS `test`.`table_a`;
CREATE TABLE IF NOT EXISTS `test`.`table_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`output` text COLLATE utf8_unicode_ci COMMENT 'program output from command execution if available',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='stores test files';
REPLACE INTO `test`.`table_a` (`id`, `output`) VALUES (1, NULL);
SELECT hex(LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt'));
UPDATE test.table_a
SET output=LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt')
WHERE id=1;
SELECT * FROM test.table_a WHERE id =1;
One of the problems with this ugly work around is the new folder in var/lib/mysql/ will look like a new database (unless your data directory is some where else). One way to get around this it to make the folder hidden by starting the folder name with a period.
Monday, September 19, 2016
Replication errors on tables in mysql schema ERROR 1146 (42S02): Table doesn't exist
I've been trying to setup orchestrator but when making a slave a co-master I would get errors like this:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(server_name:3306) show slave hosts: Will not resolve empty hostname
In order to get passed that error, I needed to create the slave_master_info table on each server. And then after creating the table, I would grant SELECT access to the orchestrator user.
This is what I was running on my servers (in addition to modifying the my.cnf):
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
COMMIT;
However, on some servers (maybe 10% of them), my attempts to create it on a slave would break replication and then the table wouldn't exist when I would grant SELECT access. I would end up with an error like this:
Error 'Table 'mysql.slave_master_info' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON mysql.slave_master_info TO ...
I found this useful blog post:
That blog post mentioned yet another post:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(server_name:3306) show slave hosts: Will not resolve empty hostname
In order to get passed that error, I needed to create the slave_master_info table on each server. And then after creating the table, I would grant SELECT access to the orchestrator user.
This is what I was running on my servers (in addition to modifying the my.cnf):
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
COMMIT;
GRANT SELECT ON mysql.slave_master_info TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*xxx';
However, on some servers (maybe 10% of them), my attempts to create it on a slave would break replication and then the table wouldn't exist when I would grant SELECT access. I would end up with an error like this:
Error 'Table 'mysql.slave_master_info' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON mysql.slave_master_info TO ...
I found this useful blog post:
http://anothermysqldba.blogspot.com/2013/09/error-1146-42s02-table-doesnt-exist.html
That blog post mentioned yet another post:
http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/scripts/mysql_system_tables.sql#L103
By creating the slave_master_info table like this on the servers where replication was broken, I was able to get passed these errors:
use mysql;
CREATE TABLE IF NOT EXISTS `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
GRANT SELECT ON mysql.slave_master_info TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*xxx';
Tuesday, September 13, 2016
Setting up orchestrator
I've been testing Orchestrator for MySQL and after following the installation instructions, I kept getting these error and simply could not figure out what was wrong. I opened up a ticket and within a day, the developer got back to me and let me know what was probably wrong. I had setup replication and the MySQL instances with IP address on my local network and never setup DNS entries. Orchestrator used the hostname, not the IP address to setup replication. Every time I issued an orchestrator command, replication would start to fail. Here is the issue I opened on GitHub:
https://github.com/outbrain/orchestrator/issues/249
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) Cleanup: dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) Cleanup: dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex2:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex1:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) Cleanup: dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex0:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:28 DEBUG outdated keys: []
2016-08-29 01:38:29 DEBUG outdated keys: []
2016-08-29 01:38:30 DEBUG outdated keys: []
2016-08-29 01:38:31 DEBUG outdated keys: []
2016-08-29 01:38:32 DEBUG outdated keys: []
2016-08-29 01:38:33 DEBUG outdated keys: [delloptiplex0:3306 delloptiplex1:3306 delloptiplex2:3306]
Next I noticed more errors with Orchestrator:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(delloptiplex1:3306) show slave hosts: Will not resolve empty hostname
I got passed those by adding this into the my.cnf file for each mysql instance:
report_host=my_hostname.example
Here is a good post that illustrates that:
https://avdeo.com/2015/04/19/show-slave-hosts-on-master-not-reporting-hostname/
https://github.com/outbrain/orchestrator/issues/249
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) Cleanup: dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) Cleanup: dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex2:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex1:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) Cleanup: dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex0:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:28 DEBUG outdated keys: []
2016-08-29 01:38:29 DEBUG outdated keys: []
2016-08-29 01:38:30 DEBUG outdated keys: []
2016-08-29 01:38:31 DEBUG outdated keys: []
2016-08-29 01:38:32 DEBUG outdated keys: []
2016-08-29 01:38:33 DEBUG outdated keys: [delloptiplex0:3306 delloptiplex1:3306 delloptiplex2:3306]
Next I noticed more errors with Orchestrator:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(delloptiplex1:3306) show slave hosts: Will not resolve empty hostname
I got passed those by adding this into the my.cnf file for each mysql instance:
report_host=my_hostname.example
Here is a good post that illustrates that:
https://avdeo.com/2015/04/19/show-slave-hosts-on-master-not-reporting-hostname/
Subscribe to:
Posts (Atom)