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
Friday, May 29, 2015
Wednesday, May 20, 2015
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.
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.
Tuesday, May 12, 2015
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/
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/
Thursday, May 7, 2015
Disabling foreign key checks on the linux command line
I've used these two commands a lot of to turn off foreign key checking and to turn it back during a migration:
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;
However, today I was importing some data for a script I'm writing and I needed to disable constraints before running the import. I was having trouble getting it to work but conveniently, I found the answer on stackoverflow...
http://stackoverflow.com/questions/15938786/disabling-foreign-key-checks-on-the-command-line
QUESTION:
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;
However, today I was importing some data for a script I'm writing and I needed to disable constraints before running the import. I was having trouble getting it to work but conveniently, I found the answer on stackoverflow...
http://stackoverflow.com/questions/15938786/disabling-foreign-key-checks-on-the-command-line
QUESTION:
I have a backup script for my MySQL database, using
mysqldump with the --tab option so it produces a .sql file for the structure and a .txt file (pipe-separated) for the content.
Some tables have foreign keys, so when I import it I'm getting the error:
ERROR 1217 (23000) at line 8: Cannot delete or update a parent row: a foreign key constraint fails
I know about using
ANSWER:SET FOREIGN_KEY_CHECKS=0 (and SET FOREIGN_KEY_CHECKS=1 afterward). If I add those to each .sql file then the import works. But then obviously on the next mysqldump those get overwritten.cat <(echo "SET FOREIGN_KEY_CHECKS=0;") imports.sql | mysql
Subscribe to:
Posts (Atom)