Friday, November 30, 2012

MySQL user authentication

I was looking around on the MySQL forums when I ran across this post and it got me thinking. First, thank you to Scott for helping in the community forum.  But also about how some still have issues with MySQL user authentication. So I thought I would put together a simple quick post about MySQL user authentication.

The forum post I mentioned above talks about user connection issues and how a user "is likely the record that is getting used by MySQL."

Well, lets review a simple example to ensure that we always will know what user is being authenticated by MySQL.

First we build out a few options for users.

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'some_pass';
CREATE USER '%'@'localhost' IDENTIFIED BY 'some_pass';
CREATE USER 'foo'@'%' IDENTIFIED BY 'some_pass';

GRANT SELECT, INSERT, DELETE ON *.* TO 'foo'@'localhost';
GRANT SELECT, INSERT, DELETE ON *.* TO '%'@'localhost';

mysql> select User, Host,Password, Select_priv, Insert_priv, Delete_priv From user Where User = 'foo'\G
*************************** 1. row ***************************
       User: foo
       Host: localhost
   Password: *BF06A06D69EC935E85659FCDED1F6A80426ABD3B
Select_priv: Y
Insert_priv: Y
Delete_priv: Y
*************************** 2. row ***************************
       User: foo
       Host: %
   Password: *BF06A06D69EC935E85659FCDED1F6A80426ABD3B
Select_priv: Y
Insert_priv: Y
Delete_priv: Y

Some might look at this and say that we have a duplicate user and that localhost is not needed, so their 1st reaction is to remove it.

DROP USER 'foo'@'localhost';

Then they end up have user permission/connection issues and are confused as to why?

Always use "CURRENT_USER();" so you can see who your  being authenticated as. USER() -- is the name given by the client.

| USER()        | CURRENT_USER() |
| foo@localhost | foo@%          |

So based on the CURRENT_USER result we realize that we need to look at other accounts.

mysql> select User, Host,Password, Select_priv, Insert_priv, Delete_priv From user Where Host = 'localhost'\G
*************************** 1. row ***************************
       User: %
       Host: localhost
   Password: *BF06A06D69EC935E85659FCDED1F6A80426ABD3B
Select_priv: Y
Insert_priv: Y
Delete_priv: Y
*************************** 2. row ***************************
       User: root
       Host: localhost
   Password: *2447D497B9A6A15F2776055CB2D1E9F86758182F
Select_priv: Y
Insert_priv: Y
Delete_priv: Y

The problem was not 'foo'@'localhost'  but instead the problem is '%'@'localhost'. MySQL will authenticate on Host 1st before it checks the User. So since it found a quick match to '%'@'localhost' we ended up with:

mysql -u foo -p

| foo@%          |

Using '%'@'localhost' is never a good idea in my opinion. You always want to lock the database down to User and Host/ip. A wild card as part of an IP address I am ok with as well. ie: 'foo'@'192.168.0.%'

This is a very simple example but the point is still a valid one. Control the permission, users and hosts with a watchful eye.

Keep in mind the mysql_secure_installation options for a more secure server as well.

# mysql_secure_installation


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Helpful urls :

Thursday, November 22, 2012


So a couple of blogs posts have come out recently that got me curious to do a little more digging on how I might actually use "SHOW PROCESSLIST". Btw those blogs posts are:
 So we all know how this works:

Server version: 5.6.8-rc-log MySQL Community Server (GPL)

MySQL 5.6.8 RC> show processlist\G
*************************** 1. row ***************************
Id: 20007
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: executing
Info: select 10000
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
2 rows in set (0.00 sec)

That is quick and easy. It give you the ID, user , host , state and time running. It also shows the command so your able to take review any obvious issues or take it and execute an explain so you can check for indexes. In a normal database you will have a lot more rows and have to parse out information accordingly to find what your after. How many rows are acceptable is dependant on the application and database. A simple start by using"pager" is one way to at least be able to see the information available, when you might have 200+ rows of information for example.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
MySQL 5.6.8 RC> pager more
PAGER set to 'more' # Turns it on
MySQL 5.6.8 RC> show processlist\G

MySQL 5.6.8 RC> pager
Default pager wasn't set, using stdout. # turns it off

You can less as well but this is just an example and a starting point.

In Mark's blog he points how issues with SHOW PROCESSLIST like stealing connections, non-blocking and views. He talks about creating a view with information from the tables within the performance_schema and information_schema so we can gather more information in a non-blocking way. So if started to just dig around some might think that the events_statements_current would be a replacement ( seen below). But as you can see below it is not, lots of information but not a direct replacement.  
MySQL 5.6.8 RC> select * from events_statements_current\G

*************************** 2. row ***************************
THREAD_ID: 90033
EVENT_ID: 334676
EVENT_NAME: statement/com/Query
TIMER_START: 3807813684782000
SQL_TEXT: select a+a+a+a+a+a+a+a+a+34673 from bench1
2 rows in set (0.00 sec)

So instead of digging across all the tables, while taking phone calls and etc... I will just check out the ps_helper information that Mark has available.

First per Mark's blog update the setup_consumers table; MySQL 5.6.8 RC> update 

setup_consumers set ENABLED = 'YES' WHERE NAME IN ('events_stages_current','events_stages_history','events_statements_history','events_waits_current','events_waits_history');
MySQL 5.6.8 RC> SELECT * FROM setup_consumers;
| events_stages_current | YES |
| events_stages_history | YES |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
12 rows in set (0.00 sec)
mysql -p < /tmp/ps_helper_56.sql_.txt

( Btw I had to take the processlist_full view from Mark's blog as was not in the ps_helper_56.sql_.txt file at the time I wrote this blog. )

MySQL 5.6.8 RC> use ps_helper
MySQL 5.6.8 RC> select * from processlist_full \G 

*************************** 2. row ***************************
thd_id: 180043
conn_id: 180024
user: root@localhost
db: test
command: Query
state: freeing items
time: 0
current_statement: select a+a+a+a+a+a+a+a+a+53833 from bench1
last_statement: NULL
last_statement_latency: NULL
lock_latency: 33.00 µs
rows_examined: 2
rows_sent: 2
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: YES
last_wait: wait/io/table/sql/handler
last_wait_latency: 618.26 ns

I know have the information I was reviewing before with additional information. I like the "full_scan" field included into this so you can see if indexes are being used right away.

So the perks overall are, by taking the ps_helper code you should be able to easily install the views and not have to piece all of the different information together. All of that work has been done for you. Would I use the views all the time? No. SHOW PROCESSLIST will be just fine for me a lot of the time for now. I will take this and have it available, because when you do need it, it will be great information to have. I also think that once installed and available it will be a tool that will slowly be used more and more for debugging issues and concerns. Great work guys.

Some other show processlist references to note:

Monday, November 19, 2012

Thank you Davi and Twitter

Just a quick note to send a big thank you to Davi Arnaut from Twitter. He has continued to support and contribute bug fixes for MySQL and the following bugs have all been accepted.

Bug #65469 Infinite loop when opening a corrupted table
Bug #65692 Deadlock between START SLAVE and setting a system variable
Bug #65715 Wrong connection ID (thread ID) in the general and slow query logs
Bug #56240  please write proper dates to the error log
Bug #67156 Sporadic query cache related crash in pthread_rwlock_init()

A big thank you and we look forward to more contributions from Davi and the others from the MySQL Community.

Tuesday, November 13, 2012

Oracle’s MySQL OEM Tech Tour - Miami

Oracle’s MySQL OEM Tech Tour - Miami :

Tuesday, December 11, 2012
10:00 AM – 2:00 PM

Oracle Office
6505 Blue Lagoon Dr., Suite 400
Miami, FL 33126 Rm: DEMO-4004

Embedding MySQL:
Higher Application Performance and Streamlined Development with MySQL Embedded

Join us and get tips directly from the MySQL technical experts on how to better develop, integrate, secure, and tune MySQL for your application.

MySQL is not only the most popular open source database for Web applications, it's also a popular embedded database relied on by over 3,000 ISVs and technology providers around the world such as Adobe, Dell, Sage and Symantec. Whether for distributed on-premise applications or hosted SaaS offerings, MySQL is ideally suited for use as an embedded database.

Come and learn how to leverage MySQL's unique architecture, development tools and newly released features to speed your engineering efforts with MySQL and ensure that your application performs at its peak. Topics include how to:

  • Make the most of MySQL's Architecture
  • Streamline development and QA with MySQL Tools
  • Secure your customers' data with New Security Features
  • Improve performance with Optimization and Performance Tuning tips 

Register Now!

The server quit without updating PID file

With MySQL 5.6.8 RC available more and more people will start to test installs and upgrades.  This often comes up for some people so I hope this helps some.

If you seeing the error "The server quit without updating PID file" then obviously something recently changed on your environment.

So lets take it step by step:

# /etc/init.d/mysql start
Starting MySQL..The server quit without updating the PID file (/var/lib/mysql/

While yes people do say just move the my.cnf file and restart the database to by pass this error... You need to understand why. Why would moving the my.cnf allow mysql to start? Check the error logs that is how and why you will be able to fix it and get it restarted.

Issue 1:
"Fatal error: Can't open and lock privilege tables: Table '' doesn't exist."

So this is a result of a bad MySQL upgrade, most likely. You can start MySQL by avoiding the grants and keeping your my.cnf in place. 

# /etc/init.d/mysql start --skip-grant
Starting MySQL..                                           [  OK  ]

You also need to fix the missing grants tables.
# mysql_install_db
Installing MySQL system tables...
Filling help tables...

Issue 2:
InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size

So your  my.cnf did change and innodb_data_file_path is now a different size than before. So you need to put back or remove the ibdata files so they can be rebuilt; the later of course will remove the data.

These are just two simple examples but if you take your time and look into the log file data you will find the reason why MySQL might not start. Moving the my.cnf is not the answer.

Additional References:
The Error Log
Monitoring MySQL – The error log
How to diagnose errors in the MySQL error log

Friday, November 9, 2012

MySQL monitoring, backups and recovery

Being a MySQL DBA is more than just being able to understand the explain command to optimize a query.  The best optimized queries in the world are worthless with the database is down.

Some of the big responsibilities with being a MySQL DBA include monitoring, backup and recovery. This is a high level overview of those topics.


A single monitor is just not good enough. If you are running a Nagios server with the MySQL plugins that is a great start. This  should not be your only solution. The first thing that should be added is another remote Nagios server to monitor the 1st master Nagios  monitor. After all if the monitor goes down how do you know?

When running a revenue generating MySQL Server it is easy to suggest using the MySQL Enterprise Monitor.  I have used the MySQL Enterprise Monitor as an additional resource for monitoring. It helps you spot trends as well as offer suggestions for tuning.

Monitor your logs. In order to be able to recover, you need to understand what happened. Monitoring log is more than just the error log. You need to watch the slow_query_log, error_log as well as OS logs. Knowing how to gather everything from network stats to memory or raid status is key.

Monitor your reports. Put in place events || etls || checks to validate reporting numbers. Every application is different so this is a custom solution per application but be able to get valid notifications when key reporting numbers fall under thresholds. This could be an early warning sign to other issues.

Monitor your backup logs and sizes. If you fail to watch the sizes of your backups as well as the logged output then I wish you the best of luck. Something like an upgrade could change your backup procedure. While you consider it to still be running just fine, maybe they error out now? A simple cron job that executes a script to record file sizes and checks for errors can put the output into a the database. Then another script can be used for reporting on that file size output. You will then have all the trends of file sizes to make positive thresholds.

Take out the human factor.  If all the monitoring notifications go to one person, then that is a single point of failure itself. Make sure fine tune notifications to remove false positives and alert more than one person. Text messages to smart phones are not enough. People are allowed to sleep and it is very easy to sleep through a txt message. I enjoy the old school pager because it simply works. If you can take advantage of asterisks then use it. A phone call wakes people up more than just a txt message.

This quick overview highlights that monitoring is more than just a single entity. It is a vast process that should never be taken for granted.

MySQL backups are the process that everyone knows is a good idea. The problem is how many people actually do it effectively. Meaning that they work, tested, and do not harm a live revenue generating site.

mysqldump  is an effective tool for backups when it is not a live system. Stopping a slave and doing a mysqldump then restarting a slave is a very safe method for your system. The problem is that to many applications or entities either take to long to do a dump and rarely tested when it is done.

mysqldbexport is another option over mysqldump because it allows different output options.

Replication is not a backup solution. It is a step in the right direction for higher availability and you can use replication for rolling historical data with time delayed replication but you still need to have the data as a backup to be safe. If using MySQL dump or the enterprise options, yes you can use them off a slave easier just replication is not a backup solution.

Take advantage of the mysqlbinlog and use it to backup your binlogs. The binlogs are the life of your MySQL server, take care of them.

The best option in my opinion is MySQL Enterprise Backup. Nothing against Percona XtraBackup but since I already encourage the use of enterprise monitor I typically would support and stay with enterprise backup since I have access to it.  Regardless of your choice, the point is simple, take advantage of on-line backups. What happens when you have a serious replication issue and your slaves are useless? Do you really want to do a mysqldump off the master?  They also offer incremental and compression to help with your backup needs.


This is often the piece of the process that tested in theory but less in reality. It is often not realistic for some companies to be able to restore their backups often. The hardware required to hold the database is not cheap and can be used for other things than just backup restores. You do need to test your backups though. Maybe it is just once a quarter and you can afford to test a slave with a restored backup.  Maybe you have a virtual environment and you can test a backup into a VM just to see if it works?  Execute some reporting on this database to verify numbers. Take advantage of the different database checksum options available in the community.

Whatever it is something is better than nothing.

This again is just a overview of the concepts about monitoring, backup and recovery. Please use the links provided to find out more information.

More community and MySQL resources and options:

Thursday, November 8, 2012

MySQL Community Server 5.6.8-rc has been released

In case you missed this......  
MySQL Server 5.6.8 RC was announced today.
MySQL Server 5.6.8 (Release Candidate) is a new version of the world's most popular open source database. 
Please download and test it out. Oracle is eager for any feedback that you might have on this release. 


Thursday, November 1, 2012

Debugging Stored Routines in MySQL

A new "Meet the MySQL Experts Podcasts" is available.

Debugging Stored Routines in MySQL with Fernando Gonzalez.

A big thanks to Fernando and the Podcast team at Oracle for helping with this podcast.

MySQL November 2012 Meetups

November is here and what do we have to be thankful for? A great community overall is the simple answer.

This month looks to have some great MySQL User Group Meetups:

A full list of user groups is available via the MySQL Wiki also has a list of user groups available.