Mysql – New Database vs New Table for User Registrations

This sounds like a good use case for Galera. http://codership.com/content/using-galera-cluster

You could also use something like this: Each server is a master for it's copy of the user table (I assume the user table is sharded?). Then there could be a central server with MariaDB or MySQL + Tungsten Replicator which is a slave of all the other servers.

And then you could create another master (extra instance on one of the servers?) which hosts the master copy of the content tables.

CM = Content Master U = User Master UC = User Combined

Server 1: CM + UC (2 Instances) Server 2-5: U

For the content tables (1 master, 4 slaves): CM -> U1 -> U2 -> U3 -> U4

For the user tables (4 masters, 1 fan-in slave): U1 -> UC U2 -> U3 -> U4 ->

Galera and MySQL Cluster provide synchronous cluster where you can write to any server. MySQL Cluster needs 2 data nodes and 2 SQL nodes as a minimal setup (the mgmt node can be combined with a SQL node). MySQL Cluster does not use InnoDB or MyISAM. Galera needs 3 nodes and does support InnoDB.

Investigate Galera, Tungsten (and MySQL Cluster aka NDB?).

Read MySQL High Availability (O'Reilly).

I would question the scalability of creating a database for each user, but other than that it looks like you are on the right track.

Presumably, you're not allowing users to connect directly to your MySQL server, in which case 'xxx'@'%' is probably too generous... Your MySQL server should be behind a firewall, of course, but even still, 'xxx'@'app.server.or.web.server.ip.address' might be a better option if it makes sense.

As for permissions, the minimum needed would be the correct route, and the ones you've listed would probably be the ones to start with. Maybe CREATE TEMPORARY TABLE.

Stored procedures and functions would require EXECUTE, though triggers do not. Triggers have their own set of security mechanisms.

Remember, also, that views, by default, use the permissions of the DEFINER to determine privileges on the tables underlying the views, unless you explicitly use SQL SECURITY INVOKER when declaring them, which causes the privileges of the invoking user to be used instead.

Update:

The Internet at large seems really good at off-target advice for MySQL.

In the manual, When Privilege Changes Take Effect explains why FLUSH PRIVILEGES is not needed in your scenario.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables.

You can actually change privileges by directly manipulating mysql.user and the other grant tables. These changes don't take effect until FLUSH PRIVILEGES causes the server to re-cache those tables, because live privilege checking never looks in the tables -- it only looks in the cached data in the privilege system's memory structures.

The potential scalability concerns I alluded to was not meant to persuade you to do otherwise, but was more of a reflection the fact that I didn't want to leave the impression of blanket endorsement of your plan, for reasons that included the one you mentioned -- that changes to your application's data structure needs will require a lot of changes to be made (not an impossible challenge), as well as the fact that your datadir will have a directory created for each database, which could eventually result in a constraint (depending on your OS limits), and I rarely deal with servers with more than about 500 schemas or 15,000 tables or 1 TB of data... though there are no preset limits to what you can provision.