MySQL privileges and replication

This is a response on MySQL security: inconsistencies and Less known facts about MySQL user grants.

As far as I know the privilege to grant PROXY privileges is also not very well understood. I blogged about that some time ago.

In addion to the already highlighted issues with GRANT replication and grants can very well create an unwanted situation:

master> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*4994A78AFED55B0F529C11C436F85458C1F8D4C2'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `somedb`.* TO 'user'@'host'
2 rows in set (0.00 sec)

master> GRANT SELECT,INSERT,UPDATE,DELETE ON anotherdb.* TO 'user'@'host';
Query OK, 0 rows affected (0.00 sec)

master> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*4994A78AFED55B0F529C11C436F85458C1F8D4C2'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `somedb`.* TO 'user'@'host'
*************************** 3. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `anotherdb`.* TO 'user'@'host'
3 rows in set (0.00 sec)

And on the slave:

slave> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `anotherdb`.* TO 'user'@'host'
2 rows in set (0.00 sec)

This could have happened if the user was dropped on the slave to prevent it from inserting on a ro-slave. The cure for this issue is setting sql_mode to NO_AUTO_CREATE_USER.

Another issue is mysql-proxy. MySQL proxy can be used for simple read/write splitting, failover and a lot more. But it could also make your database less secure:

• root is limited to localhost to prevent remote logins
• mysql proxy is installed on the database machine
• if you connect from a remote machine to mysql-proxy this will be proxied to mysql and the host will be localhost.

And the questions for the readers are:

• Do you allow access on your read-only slaves?
• Do you replicate mysql.* or not?

Regularly flushing the MySQL Query Cache without cron

This is a reply on Regularly flushing the MySQL Query Cache.


The original acticle is about regulary flushing the MySQL Query Cache as it will fragment over time.

There are some drawbacks for the cron method for flushing the query cache:

• It will only work on UNIX like platforms as MS Windows uses the task scheduler to schedule tasks.
• It needs credentials to login to the database.
• It's not included in your database backup

There is another method, which is native to MySQL: the event scheduler.

Step 1: Enable the event scheduler:

mysql> SET GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

And don't forget to set/change this in your my.cnf or my.ini

Step 2: Create the event:

mysql> CREATE EVENT flush_query_cache ON SCHEDULE EVERY 1 HOUR DO FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW EVENTS\G
*************************** 1. row ***************************
Db: test
Name: flush_query_cache
Definer: msandbox@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: HOUR
Starts: 2011-06-19 12:57:46
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Please keep in mind that the query cache doesn't always give you a performance benefit due to mutex contention. See also the query cache tuner from Domas Mituzas.

You schould create a stored procedure for multi statement and/or complex statements and call the procedure from your event instead of putting it directly in your event.

OSS-DB Database certification

What will be first? The new and updated MySQL certification or the new OSS-DB exam which is announced by LPI in Japan?

The OSS-DB is only for PostgreSQL for now, but will cover more opensource databases in the future.

There seem to be two levels:

• Silver: Management consulting engineers who can improve large-scale database


• Gold: Engineers who can design, development, implementation and operation of the database

The google translate version can be found here. I found this info on Tatsuo Ishii's blog

RE: A bit on SANs and system dependencies

This is a reply on A bit on SANs and system dependencies by Eric Bergen.

Lets first start by making a difference between entry level, midrange and high-end SAN's.

Entry level:
This is basically a bunch of disks with a network connection. The Oracle/Sun/StorageTek 2540 is an example for this category. This storage is aimed at lowcost shared storage.

Midrange:
This kind of storage generally features replication and more disks than entry level. HP EVA is what comes to mind for this category. This storage is aimed at a good price/performance.

High-End:
This is mainframe style storage which is made usable for open systems. Hitachi Data Systems VSP is a good example of this category. This category is for extreme performance and reliability.

Please make sure to not (mis-)judge High-End SAN by the experiences you had with entry-level storage.

Why should we use SAN/NAS storage?

• SAN's can offer more reliable storage than local storage
• SAN's offer all kinds of extra services like replication for disaster recovery.
• SAN's be used to make machines be easy replaceable. (Diskless, Boot from SAN)
• SAN's offer shared storage which can be used to make cluster setups.
• SAN's offer server-less backups and snapshots.
• SAN's offer storage pooling
• Most SAN's offer online upgrade of firmware
• SAN's offer online adding and replacement of disks

Why shouldn't we use SAN/NAS storage?

There are many issues which can arise with shared storage. I've encountered many issues with all of the above categories.

• Multipath setups which fail due to human error or due to firmware issues.
• Storage becomes slow because 1 machine is hammering the SAN. This bottleneck could arise on the network, the storage controller and on the disks.
• FC controllers are expensive and so are iSCSI controllers.
• Using ethernet might require a seperate network or a upgrade of your current network.
• Using SAN/NAS will require a storage administrator in many cases.

Most MySQL setups can offer the same features as a SAN

• Use MySQL (semisync) replication for disaster recovery instead of replication on the SAN level. This offers more efficient replication as statement based (or mixed) replication can be used.
• Use DRBD for repliation of disks for cluster setups. Or use a NDB Cluster.
• MySQL Enterprise Backup and XtraBackup can provide you with a low-impact backup wich can be used instead of a server-less backup solution. It could even be a no-impact backup when done on a slave. Using server-free backups with MySQL is not always possible.
• Using LVM will allow for flexible storage assignment and snapshots, but most filesystems only support growing and do not offer shrinking features. Think of it as the ever-growing ibdata file which happens if you're not using innodb_file_per_table

SAN or NAS
A typical NAS storage array like a NetApp Filer can be accessed using FC nowadays. And most SAN storage vendors do offer NAS heads. So this is not as big of a difference as it once was. SAN is block level storage which uses FC or iSCSI and NAS is file level storage using NFS or CIFS.
MySQL can be used on FC, NFS, iSCSI or any other type of storage. But when sync_binlog=1 and innodb_flush_trx_at_commit=1 is used MySQL will do a lot of fsyncs. This will slow down the storage a lot. A local battery backed RAID array will be much quicker as the network roundtrip will be eliminated. (Beware of raid auto learning!)

Some virtualization software like VMWare and VirtualBox do offer virtualized disks which can be located on a SAN. Make your virtualization layer is configured correctly to do a proper fsync.

Conclusion:
There is enough software in the MySQL ecosystem which can bring you SAN-like features with cheap/fast local storage. However if MySQL is not the only software which should be replicated it might be good choice to use a more generic way of managing your storage, this could include a SAN. Using a SAN might allow you to do things like upgrade the firmware of your disks and controllers without doing a failover on your master.

Do you use SAN/NAS for MySQL? Why? Why not? Do you use SAN features like replication and/or snapshots?