Tag Archives: MySQL

Ongoing MySQL myths

There’s an interesting post over at Olery‘s blog about a successful migration story from using MySQL/MongoDB to PostgreSQL as the persistence layer for applications that, however, lists a couple of cons of using MySQL that I personally think are no longer valid complaints (or at least not as big as they used to be). I did not find a way to reply to the post so, hopefully for the benefit of people who are new to MySQL and may hear the same recycled myths, I am doing so here.

Now I am aware that PostgreSQL has feature MySQL lacks (bitmap indexes are a personal favourite), but still, it is frustrating that instead of using actual arguments consisting of actual use cases in which one database is better than another, these old stories get recycled. I think it’s a disservice to both MySQL and PostgreSQL.

The first paragraph that caught my attention is:

MySQL was the first candidate as we were already using it for some small chunks of critical data. MySQL however is not without its problems. For example, when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it.

The sql_mode variable is available since at least 4.0 (GA in 2004 if I’m not mistaken, which is over a decade ago) and while yes, the default value is permissive, and any evil programmer will be able to change the value for the variable (Roland made very good suggestions about this some time ago), in my experience it has provided people with enough protection against this kind of data integrity problems.

And here’s the second one:

Another problem with MySQL is that any table modification (e.g. adding a column) will result in the table being locked for both reading and writing. This means that any operation using such a table will have to wait until the modification has completed.

Innodb supports some online DDL operations starting with MySQL 5.6 (GA in February 2013, over two years ago) and of course, there’s always pt-online-schema-change, which can usually help in the cases were online DDL is still not supported, or when the impact on replica lag makes that approach unfeasible.

To wrap up, my message is that it’s a big world out there, with different technologies that all have cons and pros. You need to make your due diligence and investigate the biggest of each when evaluating one technology, or comparing a few of them, to make sure you pick the best tool for the job (and for your context, because tools are not context-free. A kitchen knife is not a very good screwdriver, but a few times in my life I found myself in the situation of using it, and with success!), but most importantly, please base your decision on actually experience-verified facts, and not on things you’ve heard others say, or that perhaps you experienced a long time ago.

 

Recovering MySQL access

Ever found yourself working on a MySQL server where root’s password is unavailable? It has happened to me a few times, always because the person who set up the DB left the place long ago, and this information was not documented anywhere.

If you have root access to the OS, MySQL lets you restart the server bypassing access checks, using the skip-grant-tables option, which requires a service restart.

However, if you need to regain root access and want to minimize service impact, you can take advantage of the way the server responds to SIGHUP signals and the fact that access credentials are stored on a MyISAM table.

MySQL uses a few tables to store credentials and privileges for users (you can find more about this here), but for this procedure, we only need to work with the mysql.user table.

Specifically, we will work with the columns ‘user’, ‘host’ and ‘password’ from this table.

Here’s an example of how this can look on a server:

mysql> select user,host,password from mysql.user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | mysql     | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | 127.0.0.1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | ::1       | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
|           | localhost |                                           |
|           | mysql     |                                           |
| dba       | %         | *4FC8D8270BEC4364C78799065996F5306139B412 |
| readwrite | localhost | *202273E75BD11D06FBE2F057BFA1B1BB2B26549C |
| readonly  | localhost | *FC69E042CE30D92E2952335F690CF2345C812E36 |
+-----------+-----------+-------------------------------------------+
9 rows in set (0.00 sec)

To start, we’ll need to make a copy of this table to a database where we can change it. On this example server, this means the ‘test’ schema, as the ‘readwrite’ user has write privileges on it. Even if root’s password was lost, you can typically get a less privileged MySQL account by checking the applications that connects to this database. If for some reason this is not the case, you can achieve the same results by copying this table to another server, and copying it back after the necessary changes have been made.

The following command happen on the datadir:

[root@mysql mysql]# cp mysql/user.* test/; chown mysql.mysql test/user.*

Please don’t overwrite an existing table when doing this! Rename the copied files as needed instead …

Now you should be able to access (and write) to this table:

[root@mysql mysql]# mysql -ureadwrite -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select user,host,password from user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | mysql     | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | 127.0.0.1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | ::1       | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
|           | localhost |                                           |
|           | mysql     |                                           |
| dba       | %         | *4FC8D8270BEC4364C78799065996F5306139B412 |
| readonly  | %         | *FC69E042CE30D92E2952335F690CF2345C812E36 |
| readwrite | %         | *202273E75BD11D06FBE2F057BFA1B1BB2B26549C |
+-----------+-----------+-------------------------------------------+
9 rows in set (0.00 sec)

By now you’ve probably figured out what I’ll do: update test.user, changing the password column for user ‘root’ and host ‘localhost’ to the result of running the PASSWORD() function with some string of my choice, then copying this table back, and then sending SIGHUP to the server.

A couple of caveats:

  • Either make a copy of the original table file, (and?) or write down the original hash for root (the one you will replace)
  • Even if nobody on the customer’s current team knows how to get you MySQL’s root password, that does not mean they don’t have some old app someone has forgotten about that uses the root account to connect. If this is the case, access will break for this app. You can follow the same steps outlined here, but instead of permanently changing root’s password, use your regained access to create a new super user account, and then replace root’s hash with the one you saved (and flush privileges!)

For completion, here’s the rest of the process:

mysql> update test.user set password=password('newpass but this is insecure so dont use') where user = 'root' and host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host,password from test.user where user='root';
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *0A131BF1166FB756A61317A40F272D6FFDD281E9 |
| root | mysql     | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root | 127.0.0.1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root | ::1       | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql>

Time to copy the table back and reload the grant tables:

[root@mysql mysql]# 'cp' test/user.MY* mysql/
[root@mysql mysql]# kill -SIGHUP $(pidof mysqld)

And now you should be able to get back in:

[root@mysql mysql]# mysql -p'newpass but this is insecure so dont use'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*0A131BF1166FB756A61317A40F272D6FFDD281E9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

There you go. We’ve regained root access to MySQL without restarting the service!

I hope you find this useful, and I’ll leave opinions on MySQL’s security as an exercise to the reader …

RMySQL basics

Installation:

> install.packages("RMySQL", dependencies = TRUE)
> library(RMySQL)

Simple usage:

> con <- dbConnect(MySQL(), user="msandbox", password="msandbox", dbname="sakila", host="127.0.0.1", port=5527)
> dbGetQuery(con, "select * from sakila.sales_by_store")
store manager total_sales
1 Woodridge,Australia Jon Stephens 33726.77
2 Lethbridge,Canada Mike Hillyer 33679.79
> sales_by_film_category <- dbReadTable(con, "sales_by_film_category")
> qplot(category, total_sales, data=sales_by_film_category, geom="bar", fill=category)

My agenda for MySQL NoSQL Cloud 2013

On October 15 and 16 I’ll take the ferry to be at the MySQL NoSQL Cloud conference in Buenos Aires, and the schedule looks really nice so far. I think it’s a great opportunity for people in this region to get up to date with what’s going on in the MySQL ecosystem, and to have good discussions with members of the community that aren’t usually around here.

I still haven’t decided my complete agenda, as in some cases it’s difficult to pick one track and miss the other one, but for now, here are some highlighted sessions I’m definitely attending:

  • “Synchronize your data between MySQL and MongoDB”. Giuseppe’s sessions always include a live demo and I’m very interested to see Tungsten at work in an heterogeneous set up like this one.
  • “Analytic/Big Data (Non-Full Text) Search with Sphinx”. I’ve always used Sphinx for full-text searches, so I want to get a fresh perspective on it. Unfortunately, this means I’ll have to skip my colleague Martin’s presentation on Percona XtraDB Cluster, but I have seen him deliver at the Montevideo MySQL Meetup so I’m covered :) If you’re new to PXC though, I’d recommend his session.
  • Naturally, I must attend my own sessions “Proactive measures to minimize MySQL downtime” and “Percona Toolkit Recipes”. These are based on previous deliveries I’ve made, but updated, and in spanish.
  • “TokuMX: Alta perfomance en MongoDB con Indices Fractales”. I’ll get to learn more about fractal indexes, and in spanish, what could be better here?
  • “Percona Server 5.6 : New features and performance improvements”. Yes, I work with Vadim, but I don’t get to see him present too often :) If you don’t work with him and therefore don’t have a direct line to pester him with questions, this is a great chance!

There are many presentations I left out of the list, which does not mean they’re not interesting, just that I’m not sure I’ll be able to attend,since knowing myself, I’ll probably spend some time doing last minute tweaks to my talks, and/or helping others with theirs.

For people who will be attending and not worrying about presenting, the chance to see folks like Colin Charles, Ronald Bradford, or Monty Taylor presenting and to ask them questions should be priceless!