MySQL DBA compensation in the US

Posted: 20th April 2011 by Darren Cassar in MySQL
0

Hi,

Maybe you heard about salaryshare.me or maybe you haven’t, but either way, if you are a MySQL DBA in the US you should participate (ananymously) in order to learn how you compare to other MySQL DBAs. Fair enough, people have different experience level, some contract, some live in Santa Clara and some live in Ohio, some get free health insurance and awesome 401ks while others don’t … the list can go on forever, but it’s at least a guideline!

This website is anonymous and there must be at least four people submitting their salary (again, anonymously) for it to show salary statistics, no one will know what you exactly make but doing so you’ll help yourself and others.

PLEASE do not use the link unless you are in the US so as not to skew the results but feel free to create one for your own country for free by going to http://www.salaryshare.me.

http://salaryshare.me/30e46ca3e2ee340da9ea22d84fcd55ac

Because sharing is caring …

VN:F [1.9.7_1111]
Rating: 9.0/10 (3 votes cast)
VN:F [1.9.7_1111]
Rating: +3 (from 3 votes)

Recovering a MySQL `root` password – Three solutions

Posted: 21st March 2011 by Darren Cassar in Intermediate, Linux, Mac OS, MySQL, Solaris
Tags: , , , , , , ,
1

Three ways to recover a root user password:

The order of solutions here under gets more creative on the way down :)

1. obviously, before starting messing around check my.cnf or scripts for passwords entries, then try home directories for password files
2. secondly – can you restart mysql? if yes, restart with –skip-grant-tables, log into mysql, change your password and restart without –skip-grant-tables
3. third option – (on linux / unix ONLY)
If you haven’t found the password anywhere and can’t afford to restart your mysql.

cd data/mysql
cp -rp user.MYD bck_user.MYD_`date +%Y%m%d`
cp -rp user.MYD /tmp/user.MYD
vi /tmp/user.MYD #(edit the hashed passwords next to root*)
cp -rp /tmp/user.MYD user.MYD
sudo kill -HUP `pidof mysqld`

Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the data directory ONLY readable and writable by the user used to start/stop mysql (don’t user *nix root user to own mysql since that opens another can of worms … it’s a whole other blog post).

VN:F [1.9.7_1111]
Rating: 7.8/10 (4 votes cast)
VN:F [1.9.7_1111]
Rating: +2 (from 2 votes)

MySQL – changing a user password

Posted: 21st March 2011 by Darren Cassar in MySQL
Tags: , , , , , , ,
2

Disclaimer:

This post is for educational purposes only and no responsibility will be taken if you execute any of the commands. You mess it, you fix it!

Replacing a password for a user on MySQL can be done in at least four ways. Three ways at least.

1. set password for ‘user’@'host’=password(‘abc’);

2. grant usage on *.* to ‘user’@'host’ identified by ‘abc’;

3. update mysql.user set password=password(‘abc’) where user=’user’ and host=’host’;

mysql Wed Mar  9 14:27:17 2011 > set password for 'dc'@'%' = password('d');
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:27:39 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*84869AED8A7235127BFD0AD4A55E335B29ADE3AD' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:27:40 2011 > grant usage on *.* to 'dc'@'%' identified by 'y';
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:27:59 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*7446F64EFCFB1294A6DE20CAE7E49C2377A9AA25' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:28:01 2011 > set password for 'dc' = password('d');
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:28:20 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*84869AED8A7235127BFD0AD4A55E335B29ADE3AD' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:28:22 2011 > grant usage on *.* to 'dc' identified by 'y';
Query OK, 0 rows affected (0.00 sec)

When updating MySQL privilege tables manually remember to flush privileges as follows. Otherwise any changes will not be noticed by MySQL when authenticating users until the instance is actually restarted.

mysql Wed Mar  9 14:28:29 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*7446F64EFCFB1294A6DE20CAE7E49C2377A9AA25' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:28:30 2011 > update mysql.user set Password=PASSWORD('xyz') where User='dc' and Host='%';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql Wed Mar  9 14:30:01 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*7446F64EFCFB1294A6DE20CAE7E49C2377A9AA25' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:30:04 2011 > flush privileges;
Query OK, 0 rows affected (0.04 sec)

mysql Wed Mar  9 14:30:27 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Another way is to use:

4. mysqladmin

[preacher /sandboxes/msb_5_1_51/data/mysql 14:51:47]$ mysql -uroot -poldpass -h127.0.0.1 -P5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[preacher /sandboxes/msb_5_1_51/data/mysql 14:51:57]$ mysqladmin -uroot -poldpass -h127.0.0.1 -P5151 password "newpass"
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:05]$ mysql -uroot -poldpass -h127.0.0.1 -P5151
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:07]$ mysql -uroot -pnewpass -h127.0.0.1 -P5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:16]$ mysqladmin -uroot -pnewpass -h127.0.0.1 -P5151 password "oldpass"
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:27]$ mysql -uroot -poldpass -h127.0.0.1 -P5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:33]$

The last way I can think of using just command line (there are so many tools you can use):

5. vi data/mysql/users.MYD (don’t do it!)

As a friend of mine put it, it’s for the brain dead, but hey, its yet another way to do it!! :)

VN:F [1.9.7_1111]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.7_1111]
Rating: +1 (from 1 vote)

EffectiveMySQL Meetup in NY

Posted: 12th March 2011 by Darren Cassar in MySQL
Tags: , , , , , ,
0

The first EffectiveMySQL meetup will be held in NY on Tuesday 22nd March 2011 by Ronald Bradford. Details here

The title of the talk is “How better indexes save you money”. Saving money? Hey sure thing :) I’m in Ronald.

For those of you who do not know Ronald Bradford, he’s an Oracle Ace Director in the MySQL field, a long time community contributor and a MySQL expert.

I hope to see you at 902 Broadway New York, NY on Tuesday 22nd March 6pm.

VN:F [1.9.7_1111]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.7_1111]
Rating: 0 (from 0 votes)

Older Entries
Newer Entries