on collations and character sets

If you’re new to MySQL, you might not know that in the default configuration ‘a’='A’. Ie, string comparisons are by default case-insensitive. If this is a surprise to you, read up on Chapter 9 of the online manual, on character sets and collations.

The default character set is latin1 and the default collation is latin1_swedish_ci (’ci’ stands for ‘case-insensitive). If you don’t want ‘a’ to equal ‘A’, you can change this by setting the variables ‘character-set-server’ and ‘collation-server’ in your config file or your startup options. For example, we want to support unicode, so we use character-set-server=’utf8′ in our config file. We also added the following (in the [mysqld] section) so that clients will use the right character set and collation, too: init_connect=’set names utf8; set collation_connection=utf8_bin’.

On the other hand, by default in linux, table names are case-sensitive, which might also be new to you if you’re new to MySQL. If you try to mysqldump a database from linux to Windows (with its case-insensitive file-system), you might want to preserve this behaviour by setting lower_case_table_names=1 in your Windows config.)

on autocommit, init_connect, and ’super’

Having switched from Oracle to MySQL we were chugging along with development when we realized that in MySQL, autocommit is set to 1 by default. Ie, if you don’t explicitly start and end a transaction, each statement will commit as it executes. Type “delete from user” in the MySQL client, hit return, think “oops”, and it’s too late. “rollback” won’t help you now.

So I used “init_connect” to set autocommit=0 for incoming connections. (In my.cnf, I added init_connect=’set autocommit=0′.) Seemed to work great. (init_connect can be used to set session-level variables for connections.)

Then we moved this change from dev and QA into production. Suddenly, our UI wasn’t working. We were scrambling to figure out what was different between QA and production. I was told that the permissions must be different. Being a real newbie, I compared all the grants for ‘UI’ in the information_schema database and said they were exactly the same. What it took me a couple of hours to figure out was that I needed to check the permissions the the mysql database too. Checked mysql.user and found out that ‘UI’ had been granted ’super’ in dev and QA, but not production. Users with ’super’ bypass init_connect. (That way, if you set something in init_connect that really screws things up and won’t let people connect, you can still get in with a ’super’ user to fix things up.)

So, how could I fix this without granting ‘UI’ the super privilege in production? I followed MySQL support’s suggestion (thanks, Todd Farmer) and created a small stored procedure that sets autocommit to 0 unless you’re ‘UI’, and changed init_connect to call the procedure.

The procedure is as follows:

CREATE PROCEDURE session_init()
BEGIN
IF USER() like ‘UI%’ THEN
SET autocommit=1;
ELSE
SET autocommit=0;
END IF;
END

And my init_connect is set to ‘call init.session_init()’. ‘init’ is a database that just holds the procedure session_init(). Then I grant all the users execute on init.*. (Is there a way to grant a user execute on a single procedure rather than on databasename.* – ie all the procedures in a database? Would be nice…) Problem solved.  (Yes, of course, there is a way to grant execute on a single procedure.  See the comments. – Ben)

MySQL backups for InnoDB, as an Oracle DBA

When I started, I found the section on backups in the manual a bit confusing. I wanted to know, what’s available for InnoDB backups that would correspond to “exports” and “hot backups” for Oracle. I had trouble extracting a clear answer from the manual.

Here’s what I’ve found over time, to the best of my knowledge.

Assuming you don’t want to buy extra software (eg, Zmanda), you can use mysqldump (”export”) and/or LVM (”hot backup”) for your backup needs. (mysqlhotcopy is not for InnoDB.) Replication is nice, too. Here’s a bit about each method.

1. mysqldump (think “export”)
You can get a full backup, even for point-in-time recovery, using mysqldump. Mysqldump is sort of like Oracle’s export. Dumps the database, and you can use it to import it back. It creates a bunch of text commands that you can redirect into a mysql client to do the import. And mysql has a tool for applying changes from binary logs, so if you record the log position for the mysqldump, you can “roll forward” from there if you have your binary logs. (Think archived redo logs.) Here’s what I might recommend:

take your mysqldump:
mysqldump ––routines ––triggers ––single-transaction ––master-data=2 -p databasename > databasename.dmp

––routines and –triggers are to export your stored procedures and triggers
––single-transaction says to get a point-in-time, consistent backup (works for InnoDB)
––master-data=2 says to record the binary log position at the beginning of the mysqldump
-p is because I assume you have a password; you’ll be prompted for it

If you need to recover, you can redirect this file into a mysql client so:
mysql -p < databasename.dmp

If you need point-in-time recovery, look inside the dump file for the binary log position (near the beginning) and use that, and mysqlbin ––start-position to replay the changes in your binary logs from that point. (See the documentation for more on that.)

The drawback is that importing from a dump file can take a long time. We have a database that’s about 55G and it can take 2 days to restore it with mysqldump dump files! (Most of that is because our one biggest table is about 35G with lots of indexing included.)

2. LVM

Install your (linux) O/S with LVM and use it to take snapshots of the database while it’s running. This takes a point-in-time snapshot of your files. And with InnoDB, you don’t have to flush or lock the tables. If you use a snapshot to recover, to InnoDB it’s like your system crashed, and it will recover. (At least, that’s the theory, and it’s worked for me.) Nice, but you have to configure your system correctly to have the space for the LVM snapshots, and taking a big snapshot can slow down your system. (No numbers here, sorry, but it was noticeable to the developers.) So we use a combination of mysqldump and replication instead. (But there is a nice perl script to manage your lvm backups, if you’re interested in one, at http://lenz.homelinux.org/mylvmbackup/)

Three pages I found talk about how to do this. But I think there was another one I can’t find now that was most useful to me. (Please leave a comment if you think you have a better page to reference.) google LVM if it’s new to you, then check out the following:
MySQL Performance Blog – Using LVM for MySQL Backup and Replication setup
Mike Krukenberg’s blog – MySQL Backups using LVM Snapshots
nixcraft – Consistent backup with Linux Logical Volume Manager (LVM) snapshots

3. replication
set up replication and replicate your database to another server. Then you can take file copies whenever you want by shutting down your replication slave and copying all the files somewhere. Very nice! If you need to use your file copy to recover, you’ll have to start up from the copy and promote the database to master. Or, instead, just switch over to your slave and promote it to master, and don’t worry about the file copies. (But file copies are nice to have, too.)

That’s a very quick overview. Hopefully it’ll help someone wondering what their choices are and how each work. You can take it from there…

(update: just found this great article from xaprb (?) on “how to make MySQL replication reliable“.  Recommended reading!  Thanks, xaprb.   Also, just found related “MySQL toolkit“, also from “xaprb” which will also help you make replication reliable, among other things!)

Playing with Permissions

Quick quiz: when can this happen? When can revoking a privilege from a user “grant” them another privilege?

I try to select from a table, and am denied.

(bentest@db3) [test]> select * from gbidsuggestion_0616 limit 1;
ERROR 1142 (42000): SELECT command denied to user ‘bentest’@'db4.adapt.com’ for table ‘gbidsuggestion_0616′

As super, I connect and revoke a different permission for user ‘bentest@db4.adapt.com’

(root@localhost) [test]> revoke insert on test.* from bentest@’db4.adapt.com’;
Query OK, 0 rows affected (0.00 sec)

Now I log back in as the original user, and I can do the select.

(bentest@db3) [test]> select * from gbidsuggestion_0616 limit 1;
+————————–+…+———+
| gbidsuggestionid |… | active |
+————————–+…+———+
| 1 |… | 0 |
+————————–+…+———+
1 row in set (0.00 sec)

How can this happen?

It has to do with how MySQL checks permissions.

In MySQL, you grant permissions to a user, or to a user logging in from a particular host or network. (This might mean you want to script your grants – you can end up doing a lot of typing, user-by-user, host-by-host.) You can do some pretty nice things with this. You can have a user’s permissions depend on where they’re connecting from, for example. (You can even have different passwords depending on where you connect from. I tried it just out of curiosity.)

I got a bit tripped up, though, at one point. I granted select access to a user from anywhere on our internal network. Then I wanted to allow them to also do inserts, but only from one particular host on the network. (Only from the proper host. Eg, to make sure someone didn’t accidentally update production from QA.) I simply granted insert to that user from that host. I figured, then they can select from anywhere on the subnet, and insert if they’re on the right host.

But it doesn’t work that way. When a user logs in, MySQL looks for the most restrictive host clause that applies to that user, and uses the grants from that. Since I didn’t explicitly grant select from that one host, MySQL didn’t find a select grant and select was denied. If I revoked the grants for that one host, MySQL then finds the grants from the next level up (for the subnet) and found the select grant.

Eg, in my example above, select on test.* was granted to bentest@’%.adapt.com’. That meant that if ‘bentest’ logged in from any host on the adapt.com subnet, he could select from any table in the test database. But then I granted insert to bentest@’db4.adapt.com’, and when bentest logged in from db4.adapt.com, MySQL found the most restrictive host clause, db4.adapt.com, and used permissions for bentest at that host.

The moral is, if you want to grant extra privileges for connecting from a particular host, grant all the applicable privileges for that user and host, not just the additional ones you wanted to grant. So I would have to use the following grants:
1. grant select on test.* to bentest@’%.adapt.com’;
2. grant select on test.* to bentest@’db4.adapt.com’;
3. grant insert on test.* to bentest@’db4.adapt.com’;

Ie, (as we saw) if you only had the 1st and 3rd grant there, the 1st grant would not allow bentest to select from test.* if he connected from db4.adapt.com, even though db4.adapt.com is part of %.adapt.com.

I think it would be nicer if it worked the way I thought it did, but oh well… It would also be nice to be able to use roles, and to be able to use kerberos-based authentication, but you can’t do those either.

scale-out: notes on sharding, unique keys, foreign keys…

adapt.com uses sharding to scale out. In my talk on moving to MySQL, I mentioned a few design issues we had to become aware of before we rolled out a sharded database environment. I promised to give more details, so here they are…

We wanted some keys to be globally unique (eg, userid and accountid). How would we do this?

auto_increment_offset and auto_increment_increment

We had been using autoincremented values. So we considered using MySQL’s auto_increment_offset and auto_increment_increment to keep autoincrement values unique across shards. (This basically tells each shard to start at a certain number (auto_increment_offset), eg 1 for one shard, 2 for the next, etc, then to skip auto_increment_increment between values. So, eg, if we set that to 1000, our next keys would be 1001 for shard one, 1002 for shard 2, etc. Then 2001 for shard one, 2002 for shard 2, and so on. This would allow up to 1000 shards.) But we were worried that we would have to be careful to roll out shards with correct, unique values for auto_increment_offset, and thought there might be a better way.

UUIDs

We considered using a UUID. MySQL has a UUID() function. However, we also wanted to be able to use replication. With MySQL 5.0, apparently UUID’s can cause a problem for replication. If you use MySQL’s UUID() function, when you replicate, the statement you used is sent to the slave and executed there. But that means the slave will also call UUID(), creating a different value on the slave. (MySQL 5.1 promises row-based replication – ie, the generated values themselves are sent to the slave, not the SQL statement.)

So we considered using a UUID generated in the application code, and executing a statement with the UUID value in it. But then we wondered about the efficiency of using a UUID for an InnoDB primary key. (How would we store it? As a char(36)? Big for an InnoDB primary key. Also, UUIDs are a bit ugly for reading and working with. (See the MySQL Performance Blog entry “To UUID or not to UUID” for performance considerations.)

global view

Then we thought of using a global view, using the federated storage engine (think database links in Oracle), that would select from each shard. We thought our code could check this view and generate a next value and use it. But then, if any shard fails, the view will bomb out, and you’re out of luck. So we dropped that idea.

global tables

What we ended up doing is have a small, “global” database that has tables for some of our keys that need to be unique. The code inserts into them, gets the values, and uses them to populate the appropriate shard.

Next issue – what about foreign keys?

Currently – as far as I know – you can’t enforce foreign keys across servers. So we set up code to check and monitor some of our foreign key relationships and alert us if it finds any anomolies.

And that is how we arrived at sharding. So far so good…

scale-out vs scale-up

Scale-up vs scale-out – what are they?

Basically, “scaling up” means to add power to your server, or get a bigger server -build it up. “Scaling out” means to add new servers to your configuration – build it out.

Oracle touts both approaches, using RAC on big servers or on “blades”, or a “grid”.

MySQL generally touts scaling out, and most of the big sites using MySQL use “scale-out”. In my experience, and according to Jay Pipe’s excellent blog entry on the subject, that’s partially because MySQL doesn’t scale up as well as it scales out. (Another reason he mentions – that also fits my experience – is that people who pay for Oracle licensing match that by also buying expensive machines to put it on.)

In Oracle, whether you scale up or out, you’ll probably use RAC, which means setting up private interconnects and basically using “shared everything” with your servers. You’re still sort of “scaling up” the database itself, growing it on one server or many. (Correct me if I’m wrong..)

In MySQL, people tend to split up, or “shard”, their databases into pieces that are put into different databases on different servers. Eg, you might have one small, global database that contains information about which server a user’s data is stored on, and then multiple “user” databases on different servers. Your database would essentially be partitioned by user into smaller databases. (That’s what we do.) That’s scaling out. Then, if you get more users, and need to grow, just add more servers.

(On the other hand, if you have a small database – that can fit entirely into memory – you can use MySQL’s clustering technology and the NDB storage engine to create a cluster that’s more like RAC. In MySQL 6 you’ll be able to 5.1 you can do this with disk-based databases. – But why?) (see comments below for why…)

Another aspect of scale-out can be to use MySQL’s replication technology in one way or another. You can use master-master replication to set up two (or a few) databases that are essentially copies of each other and keep in synch with each other. Or, more common (and what we do) is to use master-slave replication to make copies of your databases (shards) that you can use to spread read loads on (and for disaster recovery: if your master fails – promote a slave to be the new master). Most big LAMP sites seem to do this.

In summary, the common approach is to design your database to be split up into multiple shards (servers/databases), then use replication to make copies of the shards for further load-balancing and availability.

Here’s what some other sources have to say:

Definitions according to wikipedia’s entry on scalability:

Scale vertically

To scale vertically (or scale up) means to add resources to a single node in a system, typically involving the addition of CPUs or memory to a single computer. This could also mean expanding the number of running processes, such as increasing the number of Apache daemons.

Scale horizontally

To scale horizontally (or scale out) means to add more nodes to a system, such as adding a new computer to a distributed software application. An example might be scaling out from 1 web server to 3.

According to MySQL: (From the first page of “12 days of scale-out”)

What is Database Scale-Out?

Scale-Out is a modern computing architecture that enables organizations to improve application performance and scalability on an incremental, as-needed basis by adding multiple replicated database servers on low-cost commodity hardware. This is in contrast to a Scale-Up approach, which requires organizations to make a large up-front investment in more expensive and complex server hardware and database licenses in order to add capacity.

Jay Pipe’s “excellent blog entry”: The Ambiguously Vague Duo: Scale-Out and Scale-Up (SNL anyone?)

A white paper from MySQL: Guide to Cost-effective Database Scale-Out using MySQL

MySQL DBA boot camp for Oracle DBAs

Got back from the MySQL DBA boot camp in San Francisco. Very nice one-day class, covered a lot. Kudos to Ronald Bradford; he did a great job of it. Good to get a free copy of the MySQL 5.0 certification guide and free passes to the certification exams. Now I have no excuse not to get certified… A lot of it I knew already (from having done this for some months now), but I especially enjoyed the section on tuning, and got some good tips throughout.

One question came up that is to the point of this site: where should an Oracle DBA start? Check out Ronald’s excellent and thorough answer at his site, in his post, “Learning MySQL as an Oracle DBA“. Certainly could not have said it better myself.

going to San Francisco

I’ll be checking out MySQL’s “DBA Boot Camp” for Oracle DBAs on Thursday. Back next week to report on it.

Then, upcoming posts will discuss further topics from my talk, touching on scaling strategies, backups, and permissions. After that we’ll get to some more technically detailed issues that we encountered when we switched over to MySQL and got things up and running. Keep watching!…

newbie Gotcha – storage engines

As we started to make the switch to MySQL, several of our managers and developers (and I) assumed we’d use clustering to improve availability and performance. (Performance, because they thought we’d be able to spread our load among clustered servers.)

I read up on storage engines and got to explain more than once that we couldn’t use clustering, much to their surprise and initial disbelief.

Clustering relies on a particular database engine (NDB) that is currently an in-memory-only engine. (Ie, all your data has to fit in memory.) Since our database is much bigger than our RAM, we couldn’t use it. (Future versions promise to allow data stored on disk.) We ended up using the InnoDB storage engine, and replication for availability and performance.

So what is a storage engine? If you don’t know, you need to find out before moving to MySQL.

MySQL implements a sort of data-handling architecture, into which you can “plug” various data-storing implementations. Ie, how you store your data on disk (among other things) depends on which storage engine you use.

The original storage engine for MySQL was ISAM (now MyISAM).

However, if you want ACID-compliant transactions (with commits, rollbacks, etc), or foreign keys in the database, you currently need to use InnoDB (or buy SolidDB). A new transactional engine from MySQL, Falcon, is in the works.

There are many storage engines, each with its own features and limitations. You can mix-and-match, and use more than one at the same time. Here are some sources where you can read more.

A nice intro by Arjen Lentz is available from MySQL. It includes a comparison of features.

Another intro, by Robin Schumacher, is also available from MySQL. It comes in two unlinked pages: part 1 and part 2.

A linux planet tutorial is also available online.

When you’re done with those, you can consult the manual (chapter 13) if you want further details on particular storage engines.

Quick Differences – product, community, support

I’m trying to work through the talk I gave, and give further notes, so I can then move on to other topics as they arise.

Briefly, then, late me state the obvious: Oracle and MySQL are different. Different products, with different philosophies (closed vs open source), different communities, and different support.

Oracle wants to be it all – database, app server, middleware, OS; everything. MySQL wants to be database software. Thank goodness for me – I’m a DBA because I like databases.

The communities are different. In Oracle-world, the best people like “Ask Tom” (OK, Tom Kyte), Jonathan Lewis, and the other Oak Table guys are trying to demystify the magic for us; we’re trying to understand what’s in the black box. In MySQL, everyone can read the code, and I’ve found that a lot of people are very knowledgeable about it and want to share their knowledge. They can all read (or write) the code. You can too.

Oracle support, in my experience, almost always stank. MySQL support has so far (for me) been the best I’ve ever had the pleasure to experience in my 20 years or so of being “in the business”. Speedy, and generally very helpful and knowledgeable. I always hear back in less than a day. With Oracle I usually had to have a “sev 1″ or never hear back. I had to fight my past level 1 support to get someone who wasn’t clueless. I could probably count on one hand the times Oracle support actually helped me. Etc etc. With MySQL, it’s almost the opposite. A lot of the support staff seem to really want to understand things and help make the product better. For example, one time, I found a workaround shortly after I filed an issue, but support asked me if I would have the time to document the issue anyway so they could figure out whether it really was a bug or not. I can’t imagine that happening with Oracle. (As I said in my talk, support is definitely worth it. Buy it if you can, even at the lowest level for one server. Then use it. You’ll be glad you did. )