pt-upgrade

I love the idea of pt-upgrade in the Percona Toolkit but have struggled to effectively use the tool.

I've taken a backup of all the databases of a database server and restored that backup onto two different hosts, one running MySQL 5.1 and one running MySQL 5.6. I want to find failing queries that work on MySQL 5.1 but don't work on MySQL 5.6.

For initial testing, I just want to find SELECT statements that return different results or fail.

I've read over the documentation and this is how I'm executing the tool:

pt-upgrade h=SERVER1 -uUSERNAME -pPASSWORD  h=SERVER2 uUSERNAME -pPASSWORD --type='genlog' --max-class-size=2 --max-examples=2  --run-time=10m 'general_log_mysqld.log' 1>report.txt 2>err.txt &

I've chosen to use the general log in this example instead of the slow query log but I've tested with both. The slow query log is more bloated with useless info while the general log has more bang for your buck in terms of size. The big problem I have with this tool is that I have hundreds databases on my server. Inside the general log are lots of "use <database>" statements before each query. However, when I run the tool it complains about not being able to run the query because the tables are missing:


On both hosts:

DBD::mysql::st execute failed: No database selected [for Statement "....

I can't just select one database when I run the command, the tool needs to read the USE <database> from the log file.

I tried again and found a database server with only a few databases. Sometimes it was working and sometimes it was failing to use the correct databases. As a work around I created the same tables inside each database. This only worked if all the tables had unique names.  This way it would not matter which database the tool was running against. At this point, at least I got a report with useful results.

It isn't always feasible to do what I did by putting the same tables in every database...what am I doing wrong with this tool?

Issues I've had with Percona Upgrade:

1. Queries pulled from production can be too intensive for a test machine running with limited resources. Queries I was testing with  were taken from systems with 30+ cores and 64 GB of RAM, queries would time out after a few minutes and the tool would stop working, queries taken from prod should be run on a prod like database servers, also queries like this "SELECT SQL_NO_CACHE * FROM table_name" which came from a backup job seemed  to break the tool
2. Some queries broke the tool, this included not only the backup queries, but weird custom SQL that were written by developers internally
3. The same query would happen so frequently in the logs (100s of thousands of times) that the reports became useless
4. There were a lot of failed queries when running the SELECT statements because of missing tables, these were not true temporary tables but tables that are created for a short time and then dropped, these tables were not part of my dump and restore because I didn't know the code was creating these "transitory" tables
5. The tool would not database context switch for me, I don't know why but when the logs would issue  a "use database_name" it would ignore it and try to run a query that was meant for a different database. The tool would report a huge number of failed queries because of this. My work around was to create the same tables in every schema so I would not get those failed queries anymore

After engaging Percona consulting they told me to only use the SLOW LOG files. For my system they said I would need to massage the files with pt-query-digest before running pt-upgrade like this:

Step 1: Massage a slow query log for SELECT statements using pt-query-digest
pt-query-digest --filter '$event->{arg} =~ m/^select/i' --sample 5 --no-report --output slowlog my_slow.log > my_slow_massaged_for_select.log

Step 2: Next run pt-upgrade
pt-upgrade h=my_source_host.com -uUSER -pPASSWORD h=my_target_host.com -uUSER -pPASSWORD --type=slowlog --max-class-size=1 --max-examples=1 --run-time=1m 'slow_massaged_for_select.log' 1> report_1.txt 2> error_1.txt &

The above massaging worked well for SELECT statements but when testing DDL/DML I ran into more problems. I would still see a lot errors for tables that don't exist because they are tmp tables and come and go during a session.

Step 1: Massage a slow query log for SELECT/DDL/DML statements using pt-query-digest

pt-query-digest --filter '$event->{arg} =~ m/^[select|alter|create|drop|insert|replace|update|delete]/i' --sample 5 --no-report --output slowlog alpha_slow.log > my_slow_massaged_for_dml_ddl.log

Step 2: Clean up the log file to remove LOCKS

Because my slow log file had a number of LOCK statements, I used SED to remove all rows that had any references to LOCKS.

Step 3: Next run pt-upgrade

pt-upgrade h=my_source_host.com -uUSER -pPASSWORD h=my_target_host.com -uUSER -pPASSWORD --type=slowlog --max-class-size=1 --max-examples=1 --run-time=1m --no-read-only 'my_slow_massaged_for_dml_ddl.log' 1> report_2.txt 2> error_2.txt &

Even running all the DDL, I would still see a lot errors for tables that don't exist because they are tmp tables and come and go during a session. At this point, I was able to get more confidence that the upgrade was going to work. 

Why is the triggers table in the information_schema so slow?

We have a sharded MySQL infrastructure at work where we sometimes create new shards from a .sql file. Each shard has all the same tables/triggers/functions, etc but the data is unique to the customer for which that shard is assigned to. This file is created from an alpha environment which has different users than production. This started to result in a situation where we are getting definers for triggers/functions on production but for users that do not exist. I wrote a script to send alerts for these but manually fixing them was getting annoying. The permanent fix so that doesn't happen anymore is in the works but the bureaucracy at work is taking too long so I wrote a bash script to fix them automatically on production. A portion of my bash script was inspired from this site:

http://codersresource.com/news/dzone-snippets/change-ownership-of-definer-and-triggers

Here is the script from the above site:

#!/bin/sh 
host='localhost' 
user='root' 
port='3306' 
# following should be the root@localhost password 
password='root@123' 

# triggers backup 
mysqldump -h$host -u$user -p$password -P$port --all-databases -d --no-create-info > triggers.sql 
if [[ $? -ne 0 ]]; then exit 81; fi 

# stored procedure backup 
mysqldump -h$host -u$user -p$password -P$port --all-databases --no-create-info --no-data -R --skip-triggers > procedures.sql 
if [[ $? -ne 0 ]]; then exit 91; fi 

# triggers backup 
mysqldump -h$host -u$user -p$password -P$port --all-databases -d --no-create-info | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql 
if [[ $? -ne 0 ]]; then exit 101; fi 

# drop current triggers 
mysql -h$host -u$user -p$password -P$port -Bse"select CONCAT('drop trigger ', TRIGGER_SCHEMA, '.', TRIGGER_NAME, ';') from information_schema.triggers" | mysql -h$host -u$user -p$password -P$port 
if [[ $? -ne 0 ]]; then exit 111; fi 

# Restore from file, use root@localhost credentials 
mysql -h$host -u$user -p$password -P$port < triggers_backup.sql 
if [[ $? -ne 0 ]]; then exit 121; fi 

# change all the definers of stored procedures to root@localhost 
mysqldump -h$host -u$user -p$password -P$port --all-databases --no-create-info --no-data -R --skip-triggers | sed -e 's/DEFINER=[^*]*\*/\*/' | mysql -h$host -u$user -p$password -P$port 
if [[ $? -ne 0 ]]; then exit 131; fi 

My script was different but the idea was the same. However, it blew up and this is a bad idea. I tested it several times on a non-prod and it was working pretty good. My testing environment only had about a dozen databases. The particular MySQL server that has over 1500 databases. For whatever reason that I haven't been able to pinpoint, running a query on the triggers table takes 20 minutes. That little portion in the above script that creates a DROP triggers query with the SELECT concat and then sends the results back into another mysql session is not a good idea! It will cause queries to get locked up and hold up replication. It created slave lag in our clustered environment which got really far behind. There are plenty of other problem with our MySQL infrastructure which are out of my control which also contributed to this situation.

Long story short is that I'll be re-writing my version of the script to use SHOW TRIGGERS/SHOW FUNCTION STATUS/SHOW PROCEDURE STATUS because those commands run much faster. However, I'll have to loop over every single database and limit my query to only that database.

/bin/rm: Argument list too long

I was doing some replication testing with master-master on Percona 5.6 today and I kept having problems with my test instances not starting or shutting down when I changed the my.cnf setting file to use the new relay log files. I had previously used theses boxes for master-slave testing and had left it for weeks un-attended and replication had broken and relay log files were building up like crazy.

I ran this:
STOP SLAVE;
RESET SLAVE;

cd into this directory:

/var/log/mysql/

Lots of relay files like this:

relay.137105

I thought that RESET SLAVE was suppose to delete and those. Maybe it is and it just taking a long time.

So I try to delete them:

rm /var/log/mysql/relay.*
-bash: /bin/rm: Argument list too long

rm /var/log/mysql/relay.1*

bash: /bin/rm: Argument list too long

I was able to run this:

rm /var/log/mysql/relay.12*
rm /var/log/mysql/relay.13*
rm /var/log/mysql/relay.14*
and on and on

I didn't want to spend all night doing this.

I tried this:

cd /var/log/mysql/

find . -name "relay.*" -print | xargs rm

Copied from here: http://itigloo.com/how-do-i/binrm-argument-list-too-long-error/

Still a no go. It just hangs. Seems to be too much for my underpowered VM to handle.

I tried it again with only one file:

find . -name "relay.097436" -print | xargs rm

It worked. 

How about a few more files:

find . -name "relay.3*" -print | xargs rm

That worked.

I tried this again:

find . -name "relay.*" -print | xargs rm

At last it worked!!

SSL error mysql dump

I was trying to do a database dump (non locking) from this server that was running MySQL 5.5 and I kept getting this annoying SSL error when running the dump command from some of my linux boxes. The command looked like this:

mysqldump -h'servername.com' -uroot -p --routines --lock-tables=false --quick  --databases database_name   > dump.sql

Error would look like this:
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

I found this post which helped me finally get a dump file:
http://stackoverflow.com/questions/31413031/mysql-error-2026-hy000-ssl-connection-error-error00000001lib0func0re

I just had to add in --skip ssl to get my data:

mysqldump -h'servername.com' -uroot -p --routines --lock-tables=false --quick --skip-ssl  --databases database_name   > dump.sql

mysqlslap

When I don't have time to do decent benchmarking and just want to create some activity on a mysql instance, I'm glad there is a handly tool called mysqlslap available. All I have to do is run this and voila, activity generated:

mysqlslap -hlocalhost -uroot -p --concurrency=100 --iterations=100 --auto-generate-sql

DROP DATABASE command causes MySQL 5.1 to stall briefly

We have a database server running MySQL 5.1 which I cannot issue a DROP DATABASE command during the day or it will hit max connections.

We've put about 1500 smallish databases on it. A funny thing happens whenever a DROP DATABASE command is issued during the day. It will immediately run out of connections and the max connections errors will start occurring. I tested it today when the load wasn't very heavy near end of business day. I had a database to DROP and wanted to see this in action. There were about 150 threads connected and about 10 threads running. When I issued the drop database command it started removing 400+ tables in that particular database and I watched connected threads spike from 150 to 600 instantly and shortly thereafter hit max connections.



I don't understand why this happens, initially I thought it was something to do with replication being single threaded but not sure.

I read up on this blog post and suspect this may be the problem:
https://www.percona.com/blog/2009/06/16/slow-drop-table/

MySQL could be executing LOCK_open for each of those tables causing mutex locks.

Creating users and changing their passwords in MySQL

Creating a new user on MySQL is easy. You would use the CREATE USER syntax like this and choose a username, hostname and password:

CREATE USER 'jsmith'@'10.0.%' IDENTIFIED BY 'Captain';


"FLUSH PRIVILEGES" is not needed unless you are manually editing the contents of the user table. This is why I have it in STRIKEOUT text.

At this point the user would exist but have no privileges to do anything but logon and wouldn't be able to see or do anything. If the "test" database exists, they may be able to see that database or any database that starts with the word "test" (this is why the test database is considered insecure and you shouldn't have database called "test" or starting with "test.." on your prod servers).

When I create users for humans, I typically create them using MySQL Workbench and let the person enter in their own password so I don't actually know their password. Once the user is created, I'll go get the password hash and re-create that user on several other servers.

First I go get the password hash:

SELECT user, password FROM mysql.user WHERE user = 'jsmith';

Next I create the user and grant that user privileges. In MySQL it is possible to create a user and grant the user privileges at the same time. This can be a security risk because you could grant privileges to a user that doesn't exist and then MySQL will automatically create a user with a blank password. Thus, whenever I grant privileges to a user, I ALWAYS include the password hash like this:

GRANT SELECT ON test.* TO 'jsmith'@'10.0.%'   IDENTIFIED BY PASSWORD '*60EFCDAD5384A26891E48146CB2D6BB5A8312E0D';


Thus, if the user doesn't exist, it won't get created with a blank password because I've included the password hash. Any slight typo in the username or host will create a new user which could go unnoticed unless you are regularly checking for users with a blank password.

I the above example I grant the SELECT priv to a database named test while also assigning a password at the same time. If NO_AUTO_CREATE_USER is not enabled then this syntax would automatically create the user, assign a password and grant the priv all at the same time. This particular hash is insecure and is easily crackable since it has been pre-computed at sites such as https://crackstation.net/

You can privileges without deleting the user by using a REVOKE statement like this:

REVOKE SELECT ON test.* FROM  'jsmith'@'10.0.%';

There are different ways to grant privileges in MySQL. Some privileges global permissions applicable to the entire instance while others are specific to a database object.

This grant statement would give full access including the ability to create new users (only a DBA should have this much access):

GRANT ALL PRIVILEGES ON *.* TO 'jsmith'@'10.0.%' IDENTIFIED BY PASSWORD '*60EFCDAD5384A26891E48146CB2D6BB5A8312E0D' WITH GRANT OPTION;


This grant statement would be specific to a table named users in the test database:

GRANT SELECT ON test.users TO 'jsmith'@'10.0.%' IDENTIFIED BY PASSWORD '*60EFCDAD5384A26891E48146CB2D6BB5A8312E0D';


This grant statement would be specific to a column named user_name on a table called users in the test database:

GRANT UPDATE (users.user_name) ON test.users.user_name TO 'jsmith'@'10.0.%'
  IDENTIFIED BY PASSWORD '*60EFCDAD5384A26891E48146CB2D6BB5A8312E0D';


When granting specific global permission such as Process or the ability to see replication status you grant it to *.* like in the following example:

GRANT Process, REPLICATION CLIENT ON *.* TO 'jsmith'@'10.0.%' IDENTIFIED BY PASSWORD '*60EFCDAD5384A26891E48146CB2D6BB5A8312E0D';


You can use wildcards to grant permissions to all databases that start with a specific name:

GRANT SELECT, UPDATE, INSERT, DELETE  ON `beta\_%`.* TO 'jsmith'@'10.0.%' IDENTIFIED BY PASSWORD '*60EFCDAD5384A26891E48146CB2D6BB5A8312E0D';


Sometimes people come to me and complain that they cannot change their password in MySQL. They can. It is as easy as running this:

SET PASSWORD = password('My_new_$uper_Sekrit_P@ssword!');

The problem is that I manage hundreds of MySQL instances. They are not federated and not using active directory or PAM authentication. They are totally independent of each other. So if one of our support specialists that has access to prod wants to change his password, it will need to be done on hundreds of MySQL instances. I've written bash scripts to do this for me automatically, however it still requires a few manual steps of grabbing the username/host/password hash from one server and running a SET PASSWORD command against all the servers. If the support specialist knew how to use my bash script he could do this himself but I usually end up doing this on their behalf.

How to change password using the actual password:

SET PASSWORD FOR 'jsmith'@'10.0.%' = PASSWORD('Pocahontas');


How to change password using password hash:

SELECT user, password FROM mysql.user WHERE user = 'jsmith';

SET PASSWORD FOR 'jsmith'@'10.0.%' = '*9F5F9D1D6E63F7B524A2EF022D018522B68D4255';


If you want to drop the user, it would be done like this:

DROP USER 'jsmith'@'10.0.%';

There can be some gotchas when dropping users, especially if they are being referenced as a definer in stored procedures/triggers or views. Here is a nice blog post which discussed this: https://www.pythian.com/blog/properly-removing-users-mysql/

These sites also have really good tutorial and explanations on MySQL user management:
http://www.mysqltutorial.org/mysql-create-user.aspx
http://www.mysqltutorial.org/mysql-grant.aspx
http://www.mysqltutorial.org/mysql-revoke.aspx
http://www.techonthenet.com/mysql/change_password.php
http://dbahire.com/stop-using-flush-privileges/